How to fix ERROR 1151: Too many delayed threads in use in MySQL

Before adjusting configurations, verify whether your MySQL version still supports INSERT DELAYED. In MySQL 8.0+, the DELAYED keyword is deprecated and ignored—the server treats DELAYED inserts as regular inserts.

SELECT VERSION();

If you are using MySQL 8.0 or later, consider refactoring your application to remove INSERT DELAYED entirely.

Connect to your MySQL instance and check the current number of active delayed insert threads and the configured limit:

SHOW STATUS LIKE "Delayed_writes";
SHOW STATUS LIKE "Delayed_insert_threads";
SHOW VARIABLES LIKE "max_delayed_threads";

This shows you how many delayed threads are currently in use and the maximum allowed. If the current count is at or near the max_delayed_threads limit, you have found the bottleneck.

If you are still using a MySQL version that supports INSERT DELAYED (5.1 through 5.7), you can temporarily increase the max_delayed_threads limit. Add or modify the following in your MySQL configuration file (my.cnf or my.ini):

[mysqld]
max_delayed_threads = 50

Then restart the MySQL service:

sudo systemctl restart mysql
# or on some systems:
sudo service mysql restart

Monitor the impact with:

SHOW STATUS LIKE "Delayed_insert_threads";

Note: Be cautious about setting this too high, as each thread consumes server memory and resources.

The long-term solution is to eliminate INSERT DELAYED from your application code. Convert delayed insert calls to regular, non-delayed inserts:

Before (INSERT DELAYED):

INSERT DELAYED INTO logs (user_id, action, timestamp)
VALUES (123, "login", NOW());

After (Regular INSERT):

INSERT INTO logs (user_id, action, timestamp)
VALUES (123, "login", NOW());

If you need non-blocking inserts for performance, consider these alternatives:
- Use connection pooling to handle concurrent requests efficiently
- Implement a message queue (Redis, RabbitMQ) for async logging
- Batch multiple inserts into a single transaction
- Use prepared statements to reduce overhead

For MySQL versions prior to 8.0, you can fine-tune related parameters to handle delayed inserts more efficiently:

[mysqld]
delayed_insert_limit = 200      # Rows processed before checking for SELECT statements
delayed_insert_timeout = 600     # Seconds before idle thread terminates
delayed_queue_size = 2000        # Per-table queue size limit

These parameters help the server batch delayed operations more efficiently:
- Higher delayed_insert_limit allows more rows to be processed in one batch
- Higher delayed_insert_timeout keeps threads alive longer if traffic is intermittent
- Higher delayed_queue_size allows more rows to be queued per table

After making changes, restart MySQL and monitor performance.

Set up monitoring to track delayed insert performance and ensure the error does not reoccur:

-- Check delayed insert statistics
SHOW STATUS LIKE "Delayed%";

-- Monitor in real-time (run periodically)
WATCH -n 1 "mysql -e \"SHOW STATUS LIKE 'Delayed%';\"";

Key metrics to track:
- Delayed_writes: Total number of INSERT DELAYED statements executed
- Delayed_insert_threads: Current active delayed insert threads
- Delayed_errors: Failed delayed insert operations

If thread count approaches your configured limit again, you need further optimization or a migration strategy.