Is there a way to stop MYSQL Delayed Inserts from the MySQL configuration?

Question

Hi guys, I got stuck with a weird issue with one of our servers. I see there are delayed mysql inserts

+--------+----------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| Id     | User           | Host      | db               | Command        | Time | State              | Info                                                                                                 |
+--------+----------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| 219586 | DELAYED        | localhost | XXXX             | Delayed insert | 202  | Waiting for INSERT |                                                                                                      |

on a rarely used databases. From what I understood from the MySQL manual, those inserts wait for other ones in order to optimize highly used databases to write inserts in batches/blocks. Unfortunately they say that this method consumes a lot of memory and is extremely inefficient when used on rarely utilized databases. In this particular case there are only 10-20 queries a day to that database which makes the delays extremely huge -- up to a whole day. There are similar issues with other databases/users and when added up they seem to load MySQL's memory usage and CPU.

Is there a way to prevent delayed queries from being delayed ? Like make em act like regular queries ?

Thanks in advance!

Cheers, Venetsian.

Answer 1

1. You may FLUSH TABLES or 'KILL 219586' to force that thread complete its job and exit
2. Set max_delayed_threads=0 in config file and restart instance to disable DELAYED feature completely
3. Read docs - it will be faster than waiting 6h in forum

Answer 2

Don't worry about that thread. It's just kept around in case there's a delayed insert in the future. It's not actually doing anything..