Mysql – Unexpected slow queries


Using LEMP on popular cloud-service virtual server, with MyISAM tables.

The problem is that sometimes once in few hours simple mysql queries (select from where) executing several seconds (5-9) instead of milliseconds (0.002) like in other time.

MySQL slow.log has these records:

Query_time: 5.369666 Lock_time: 0.000120 …

error.log is empty and in other system logs have nothing special too

Such slow queries goes one by one to several databases and tables so its not looks like the table lock and lock_time value in slow.log is also insignificant.

I was enabling general log but there was nothing strange. Just regular queries so I cant find any correlation. I also didnt find out memory or cpu leak. Looks like sometimes something load MySQL a lot and it slowing down.

What can be the reason of such peaks of MySQL slowdown and how can find it out?

Best Answer

"0.002" sounds like the Query cache came into play.

When the QC is turned on exactly the same SELECT is run a second time before any changes happen to the table(s) involved, the resultset is looked up in a Hash (called the Query Cache). This will take on the order of 1ms regardless of how long the query normally takes.


Since you are using MyISAM, virtually any operation that touches the same table(s) will 'lock' the table and cause a normally fast query to stall for as long as the other query (or queries) take. Switching to InnoDB is likely to help.

If you would like to discuss it further, please provide the query, SHOW CREATE TABLE and EXPLAIN SELECT ..., SHOW VARIABLES LIKE 'query%';, SHOW GLOBAL STATUS LIKE 'Qc%'; The infomation in those may lead to eliminating one or the other of the above explanations.