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?

  • 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.
| improve this answer | |
  • Also... large updates can cause index statistics to diverge from meaningful, resulting in transiently shifting query plans. I have a 5.1.73 server (running the InnoDB plugin) on a legacy system that does this most mornings after a batch update that sometimes makes significant changes to one table. At least, I assume that's the mechanism -- a script that runs every few minutes, capturing and logging the result of EXPLAIN SELECT ... shows the optimizer meandering through different plans, including full table scans where the optimum query plan seems obvious. It stabilizes without intervention. – Michael - sqlbot Feb 11 '17 at 21:38
  • I think it was MySQL 5.6.6 and MariaDB 10.1 when they finally improved the "stats". Stefan -- consider upgrading. – Rick James Feb 11 '17 at 21:45

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.