I have some strange bug into a application(or is it the MySQL build?) that causes queries to remain in "locked" state forever, filling up the max number of threads.
I read about setting the wait_timeout variable to kill the "bogus" threads after a period of time. This works ok, but I would like to log the killed queries for further inspection/making sure backup scripts are not killed.
Is there any possibility to do that?
Thanks.
You might be able to use the slow log, but I'm not sure if the problem is that they never complete. Worth a shot.
Also, you may be able to see what's going on by running SHOW FULL PROCESSLIST while you've got dead threads. It should show you what the problem is and what the query was.
If you can simulate this in a development environment, you could also turn on general query logging (which records every statement) and then just tail the log after it crashes.
In the past, I have tagged queries with a unique comment (per query type):
/* Query_12345 */ SELECT ... FROM ... WHERE ... LIMIT ...
A background process would poll SHOW FULL PROCESSLIST and look for any queries that were more than X seconds long, and tagged with Query_NNNNN.
Finally, it would kill them if they went on too long. This allowed the server to breath while we figured out how to optimize the 80,000,000 record table that was slowing things down.