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? Oct 23, 2011 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, 2011 at 1:31
  • Here's the output of SHOW ENGINE INNODB STATUS pastebin.com/tgk0wPSE Oct 24, 2011 at 4:39
  • Content of my.cnf pastebin.com/R5PKBCyA Oct 24, 2011 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) Oct 24, 2011 at 4:42

2 Answers 2


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 !!!

  • Step 02) service mysql restart solved the problem. Thanks!
    – Ran
    Sep 27, 2021 at 6:10

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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