I am puzzled by the behavior of mysql lately, hoping someone can help.
We have a web app (a polling chat application) doing about 500 queries/sec and everything has been fine for the past few months. Until recently, we’ve been fortunate to run into few problems.
We have recently noticed a routine select query end up in our slow logs and an EXPLAIN indicates that mysql is doing a filesort. The select looks something like:
SELECT user_id, body, timestamp FROM messages WHERE active = 1 ORDER BY id DESC LIMIT 30
Normally this query would run and mysql would use the correct index to sort. However it now seems to resort filesort and causes huge lock ups resulting in a cascading problem slowing down the rest of the application.
Can anyone shed some light on this behavior?