Impact of name resolution on mysql_connect perfomance

I spend lot of time profiling popular PHP applications finding where exactly processing time is spent during PHP execution. Few months ago, one thing which caught my attention was performance of mysql_connect API. On my windows test bench, xdebug output showed that one call to mysql_connect was taking 0.31 seconds which is huge. While playing with the API, I noticed that when IP address of the MySQL machine is used instead of the hostname, performance of mysql_connect API is much better. Below is a simple script I ran to test this.
 
1. <?php 
2.     $hostname = "hostname";
3.     $ipaddress = "1.1.1.1";
4. 
5.     $starttime = microtime(true);
6.     for($cnt = 0; $cnt < 10; $cnt++) {
7.         $connection = mysql_connect($hostname, 'username', 'password');
8.         mysql_close($connection);
9.     }
10.    $endtime = microtime(true);
11.    echo ($endtime $starttime . "\n");
12.
13.    $starttime = microtime(true);
14.    for($cnt = 0; $cnt < 10; $cnt++) {
15.        $connection = mysql_connect($ipaddress, 'username', 'password');
16.        mysql_close($connection);
17.    }
18.
19.    $endtime = microtime(true);
20.    echo ($endtime $starttime);
21. ?>
Below is the output I got when I ran the above script on my test bench.

3.14003 seconds (time taken for 10 mysql_connect calls when hostname was used)
0.01396 seconds (time taken for 10 mysql_connect calls when IP address was used)

As you can see in the results, hostname resolution slows down mysql_connect significantly and the performance hit seems to happen on each mysql_connect call. This slowdown only happens when connection is made to a remote machine. When MySQL and web server both ran on the same machine, there was no performance difference between hostname and ipaddress case. So if you are running MySQL on a remote machine, use IP address in the mysql_connect call. Alternatively you can add an entry in %windir%\system32\drivers\etc\hosts file to tell your system IP address of MySQL machine. System will find this entry in the hosts file and pick the IP address of MySQL box directly without going through more expensive name resolution. Change hosts file only if you have a static IP address. Name resolution performance will depend on your DNS/WINS configuration and on your network topology. So you should run the script above to find out performance impact of hostname resolution before making any changes.

Hope this helps.
Kanwal

8 thoughts on “Impact of name resolution on mysql_connect perfomance

  1. Pingback: Impact of name resolution on mysql_connect perfomance « Kanwaljeet … | IP address.co.uk

  2. Good catch!
    I assume mssql_connect() and sqlsrv_connect() are affected by this also?

  3. Hi Sean.

    I didn’t try mssql_connect and sqlsrv_connect but they may or may not have this issue. If these drivers maintain some kind of name resolution cache, they might not have this problem.

    Thanks.
    Kanwal

  4. Pingback: PHP 5.3 and IIS 7 - beware of MySQL issues with IPv6 - James Crowley

  5. Working on one machine (ie. with PHP and mySQL both running on my dev P.C. I could not get mysql_connect("localhost", "root", "passwd"); to work at all. Inspired by your article I switched "localhost" to "127.0.0.1" and it worked immediately!Great relief! Thanks. John B Harris

  6. Thanks for this. As you mention on the same box there's no performance difference of IP vs hostname.
     
    But how about the use of named pipes? I've never gotten it to work. But would named pipes be even faster than IP address?
    thanks

  7. Pingback: PHP 5.3 and IIS 7 – beware of MySQL issues with IPv6 - Coding, content and startups

Leave a Reply

Your email address will not be published. Required fields are marked *