Thursday, January 13, 2011

Database connection (mysql_connect) taking a long time

After setting up a new database, the connection from the web servers was really slow. We had enough combined traffic to slow down the response of the PHP function mysql_connect() to between 5 and 20 seconds. But the current load on the MySQL server wasn't that high... something fishy was going on.

What I found was that the database server was trying to do a reverse look-up on every connection using the IP. It was slowing down every connection for this. You can disable this by using the following option, as explained here.

--skip-name-resolve

"Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost. See Section 7.9.8, "How MySQL Uses DNS"." --> http://dev.mysql.com

Anyway, you can make this change in the configuration file to be loaded at start-up (it doesn't have to be a command-line option). Simply add "skip-name-resolve" on a new line in the /etc/my.cnf file and restart your DB server. VoilĂ !

1 comment:

Anonymous said...

Solved my problem too. Thanks!