Mysql sudden slowdown, what potential causes?
Solution 1:
There are a lot of factors that could be the cause of this.
Slow resolver shouldn't have an impact if you can measure queries by themselves. However, a slow resolver can definitely have an effect when connecting to the server - if users get access based on the hostname they connect from. MySQL also have an option for logging the hostname of connections.
The mysql-report doesn't say much about indexes. What I usually do when I see this happening, is that I take one of the queries that takes a long time, and I run EXPLAIN on it. If it doesn't use any indexes, and needs to do a full table scan - I'd look to see if I'm able to add a index that would make it faster. I've seen indexes disappear by accident before (someone deleted it by accident, an upgrade script deleted it and didn't put it back afterwards, or similar).
The server could be overloaded in lots of ways:
- What does your CPU-metrics say? Is it stuck in IOWait, System time, User Time, or is it actually idling?
- Depending on the database, it could actually be a cold query cache that needs some time to get warm after a restart of the server. This is unlikely if the data changes a lot - but could happen if it's used mostly for lookups.
It could also be a series of queries which locks the table - preventing the lookups from occurring in ~10 seconds at a time. You can get information about that if you log slow queries to a file.
It could be a bug in MySQL. There is a quite heavy burden on proving that, but it happens.
You need much more data to figure out the cause of this. You need metrics of CPU, memory, MySQL metrics and such. May I suggest a monitoring tool such as Munin? There is a very good MySQL plugin that will give you interesting data as it happens.
Solution 2:
Probably the quickest way to get a real feel for what might be chewing up resources on your MySQL server is this simple command:
SHOW PROCESSLIST;
It'll tell you what MySQL processes are active, how long they've been active, and what they're up to. Any time things have gone wonky with my MySQL server, SHOW PROCESSLIST has been my first line of 'defense'.