Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. 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, 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)

share|improve this question
    
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 –  randy melder 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

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

[mysqld]
skip-host-cache
skip-name-resolve

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 10.85.115.73) or a netblock (such as 10.85.115.%)

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

FLUSH HOSTS;

Give it a Try !!!

share|improve this answer
    
Thanks I will try that. –  Thomas Wang Oct 24 '11 at 4:43

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!

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.