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.

I have a development MySQL server running on a dedicated CentOS virtual machine, under VirtualBox & Windows.

I've been using it for months with a pretty decent performance, and have an history of SQL patches to create a project's database from scratch that up to now, was taking less than a minute to run.

When I tried to re-run the patches today, it started to take several minutes, which is considerably slower than it used to be.

Here is a top output when it looks the most "busy":

top output

But most of the time, the output is the following:

top output

mysqladmin status says:

Uptime: 805  Threads: 3  Questions: 894  Slow queries: 0  Opens: 1277
Flush tables: 1  Open tables: 98  Queries per second avg: 1.110

So, for me, the server looks almost idle, although it's actually running a big SQL patch, and making me wait for ages...

Any idea what could be slowing it down?

share|improve this question
1  
Is it possible that your shared disk is having IO/disk issues or is Busy? Seeing that you are running a virtual host on your Windows PC. –  Danie Jan 15 '13 at 10:02
    
I just found the reason (see below). Thanks for your comment! –  Benjamin Jan 15 '13 at 10:06
add comment

1 Answer

Ok, I found the reason. The queries themselves weren't slow, but the connection to the MySQL server was. And because the mysql command-line was executed once for each patch, that made it painfully slow.

I added the following line to my.cnf:

[mysqld]
skip-name-resolve 

And everything's back to normal!

share|improve this answer
    
You may want to add skip-host-cache as well : See my DBA StackExchange post dba.stackexchange.com/a/7122/877 –  RolandoMySQLDBA Jan 15 '13 at 14:12
    
@Rolando What does skip-host-cache do when the name resolution is disabled? –  Benjamin Jan 15 '13 at 15:31
    
--skip-host-cache disables internal host cache for faster name-to-IP resolution. In this case, the server performs a DNS lookup every time a client connects. –  RolandoMySQLDBA Jan 15 '13 at 15:39
    
I thought that skip-name-resolve would prevent ip-to-name resolution, and that because a cache would make sense only when name resolution is On, skip-host-cache would be useless (ineffective) in this case? –  Benjamin Jan 15 '13 at 16:45
    
I usually recommend both be used anyway. –  RolandoMySQLDBA Jan 15 '13 at 16:47
add comment

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.