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, I've crossposted this on serverfault, hope it's ok...

EDIT: Could I try to have a master master replication on ec2 to protect against the problem happening again? Half of the instances connecting to the mysql database are on ec2 (the rest directly from layeredtech)

  • Hi Thomas, welcome to Database Administrators. Did you notice how many users were connected, by chance? – Derek Downey Oct 23 '11 at 20:24
  • Please run this command, and past the results: SHOW ENGINE INNODB STATUS\G Also, past your /etc/my.cnf – randomx Oct 24 '11 at 1:31
  • Here's the output of SHOW ENGINE INNODB STATUS pastebin.com/tgk0wPSE – Thomas Wang Oct 24 '11 at 4:39
  • Content of my.cnf pastebin.com/R5PKBCyA – Thomas Wang Oct 24 '11 at 4:41
  • @DTest, At the time of the problem, there were 200 thin processes connecting to the mysql database (the app is a rails app) – Thomas Wang Oct 24 '11 at 4:42

You could try the following to improve things

skip-host-cache and skip-name-resolve

Please follow these steps:

Step 01) Add the following to /etc/my.cnf


Step 02) service mysql restart

Step 03) Stop using mysql users that have DNS names. Run thus query:

SELECT user,host FROM mysql.user;

Any user that has a DNS name, simply replace it with IP Address (such as or a netblock (such as 10.85.115.%)

You may also want to run this command intermittently (say one an hour in a crontab)


Give it a Try !!!

| improve this answer | |

First off….(Apologies…I just realized you did paste the RAM! 256 IS too small. I think you should be OK doubling that. So this is something else to try before wading into pr-stalk and pt-sift files

Second….Sorry to ask a seemingly 'duh' question but is this InnoDB Status output printed from while the slowdown is happening? This could be an InnoDB based deadlock of some sort (threads stepping all over each other). The issue with the raw SHOW INNODB STATUS command is that it has very short term data. For intermittent slowdown issues, you need multiple snapshots like that to find trends.

You might find this useful: http://www.percona.com/doc/percona-toolkit/pt-stalk.html

which is used closely with: http://www.percona.com/doc/percona-toolkit/pt-sift.html

There is a great webinar by the author of Maatkit (Percona toolkits predecessor) on this page: http://www.percona.com/webinars/2011-09-06-diagnosing-intermittent-mysql-problems/

Good luck!

| improve this answer | |

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.