Occasionally, in my live web app, I'll get a query that locks up the database for 1-2 minutes. Sometimes it is hard to predict which queries will do this until the load gets to a certain point, or a particular user with a particular set of data runs through that query.
The current solution I have for handling this (and being aware of it), is a crude crontab script which checks "Threads_running" in "show global status" of the mysql server. If the number is over 30, then I log out all the queries with "show full processlist"
Im finding that i'll get a long query stuck query about ~ once a week.
Once I find this query, sometimes EXPLAIN will help me figure out why its slow. But sometimes, I just want to re-simulate it happening. When I run the query again, it is fast, (because of query cache?). Then I run RESET QUERY CACHE and FLUSH QUERY CACHE, and expect to be able to simulate that slow query, but it is fast still. Why?
ALSO, side note: I'm curious how other people monitor long queries?