Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

A few days ago mysql became suddenly very slow with queries taking more than 10s to complete when they used to complete under 100ms... It lasted about an hour and then the speed was normal again. What could cause such a problem?

A few more information: The mysql version is 5.0.51a The mysql database is on a dedicated box with 1.5 GB ram. There was no swap being used when the mysql server was slow.

Here's the mysqlreport from the database http://pastebin.com/au6yMWqQ None of the caches are full..

I've noticed that the dns server used by the server for resolving hostname was a bit slow sometimes lately. Could this have an impact?

Thanks

share|improve this question
    
Yes it can. And probably 100 other causes. –  mailq Oct 23 '11 at 19:24

2 Answers 2

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.

share|improve this answer
    
Good suggestion, I'll install munin to monitor (I usually do that actually, but it was not me who had setup this machine originally) –  Thomas Wang Oct 24 '11 at 4:45
    
What is strange is that we never had the problem before even with much higher traffic and the same requests that took a long time became much quicker later on.. No other services apart from mysql were running on the server. –  Thomas Wang Oct 24 '11 at 4:46

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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