Mysql – Does thesql make some internal maintenance after some uptime

maintenanceMySQLmysql-5.5

I've got a dedicated mysql server (5.5.44) which runs now about 202 days. Between Friday at about 11:00 a.m. and Saturday at about 3:30 a.m. I've got a very high load on that server (my monitoring shows a system load from up to 25). I've checked serveral log files but can't find anything what goes wrong. I've also checked the mysql-slow.log and there I discovered, that there are queries logged, which are normally never logged. The first query from that file shows me 

Query_time: 11.387071  Lock_time: 0.000045 Rows_sent: 8  Rows_examined: 9975109

When I take the exact same query now and run it directly, I got in the explanation that only 19023 rows have to be examined. The slow query log is full of such examples.

So it looks to me, that some internal indexes from mysql have failed. Could that be? Or is there anything internal maintenance running in mysql?

By now, the mysql server is fast as before. But what happened in the time between friday and saturday?

Best Answer

  • If you are using a "hosted service", they could be doing something, such as a backup.
  • EVENTs can do things at specific times.
  • Otherwise, nothing in mysql should cause what you are seeing.
  • Could the entire server be taking a dump? Do ps when the load average goes high.
  • Let's see the query -- "Rows examined" will stay the same unless the table or the query is being modified. It's an exact number, unlike "Rows" in EXPLAIN. If you got 19023 from EXPLAIN, that is mighty far off, but it is not impossible. Let's also see SHOW CREATE TABLE, maybe you have a lot of TEXTs or BLOBs; these sometimes confuse EXPLAIN.
  • Could there be a long-running transaction (or something) that happens every weekend. "Interference" from other tasks can cause a burst of "slow queries".
  • 11 seconds to touch 10M rows -- not bad.
  • Lower long_query_time down to at least 2.
  • Use `pt-query-digest to summarize the naughty queries. This catches both (1) slow, but rare, queries, and (2) frequent, but fast, queries.
Related Question