Is there a way to stop MYSQL Delayed Inserts from the MySQL configuration?
delayed-execution, mysql
Solution
- You may FLUSH TABLES or 'KILL 219586' to force that thread complete its job and exit
- Set `max_delayed_threads=0` in config file and restart instance to disable DELAYED feature completely
- Read docs - it will be faster than waiting 6h in forum
Problem
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.