Periodically Slow Mysql Table - Why?

Question

I recently had a problem that I had never seen before, where for about 3 hours one of our Mysql tables was extremely slow. This table contains forum posts and currently contains about a million rows. A query that became slow was very common in our application:

SELECT * FROM `posts` WHERE (`posts`.forum_id = 1)  ORDER BY posts.created_at DESC LIMIT 1;

We have an index in the posts table (forum_id, created_at), which usually allows this query and sorting in memory. But, during these three hours, not only. Usually this instant request ranged from 2 seconds to 45 seconds during this time period. Then he returned to normal.

I looked through our slow query log and nothing else looks normal. I watched New Relic (this is a Rails application), and all other actions were performed, essentially, at the same speed as in normal mode. Today we did not have an unusual amount of messages. I can not find anything superfluous in our magazines. And the database did not change when it still had the ability to use memory.

I am wondering if Mysql can change its mind about which indexes to use for this query, and for some reason, he decided to decide to completely scan the table for this query for several hours today? But if that were the case, why would he stop doing a full table scan?

Has anyone else encountered an intermittent slow request that the mind ignored? Or do you have any creative ideas on how to debug such a problem?

Answer 1

I would try MySQLEXPLAIN ...

EXPLAIN SELECT * FROM `posts` WHERE (`posts`.forum_id = 1)  ORDER BY posts.created_at DESC LIMIT 1;

It might be worth checking out MySQL's response time in Rails code, and if it exceeds a threshold, run EXPLAINand register the details somewhere.

The lock column also reminds: is the message column updated with cronjob or a massive query when SELECTs are executed?

Hope this helps!

Answer 2

, , InnoDB MyISAM, , , WHERE ORDER BY ORDER BY, , (, , , , , !)

, LIMIT, ORDER BY ( ), , WHERE, ORDER BY, MySQL. , created_at , .

, MySQL , , ANALYZE TABLE . , LIMIT ORDER BY , ; USE INDEX, .

wait_timeout , , , ( ). - , wait_timeout, .