0

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?

  • Which other processes are running on this 'cloud-service virtual server' which could have an effect on performance? – Luuk Sep 21 at 11:27
  • Got a cron job doing database backup or other heavy periodic load? – peufeu Sep 21 at 13:03
  • Check linux syslog near the time of slowdown for "soft lockup" messages. – mustaccio Sep 21 at 13:46
  • > Which other processes are running on this 'cloud-service virtual server' which could have an effect on performance? Lot of usual for a server running websites, but i have no idea which of them can affect mysql. > Got a cron job doing database backup or other heavy periodic load? Yes, but backups run less often than this slowdowns. > Check linux syslog near the time of slowdown for "soft lockup" messages. Checked, nothing like this. – Dimec Sep 21 at 15:27
  • Post complete last 400 lines of your Slow Query Log so we can see content of queries involved. – Wilson Hauck Sep 25 at 23:32
0

"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.

Or...

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.