Home / server / Questions / 408550
Accepted
Hubro
Asked: 2012-07-18 07:51:33 +0800 CST

Connecting to MySQL from PHP is extremely slow

  • 772

I just did a fresh install of XAMPP. When first opening PHPMyAdmin I noticed it was extremely slow. It didn't make sense that on localhost it should take almost 5 seconds for every page to open. I made a small test case to shift the blame off PHPMyAdmin:

$con = new PDO("mysql:host=localhost;dbname=mysql", "root", "");
$statement = $con->query('SELECT host,user,password FROM user;');
$users = $statement->fetchAll(PDO::FETCH_ASSOC);

The above script takes just about 3 seconds to run (although it took closer to 8 seconds to load the first time I ran it.)

Then to check if it was PDO's fault I tried using mysql_connect instead:

$con = mysql_connect("localhost", "root", "");
mysql_select_db("mysql", $con);
$result = mysql_query('SELECT host,user,password FROM user;');

Takes exactly as long to finish.

I thought it was PHP's fault at first, but PHP code and static files are served snappier than I can click refresh. I tested PHP by running this little script:

header("Content-Type: text/plain");

for($i = 0; $i < 5000; $i++)
{
    echo sha1(rand()) . "\n";
}

5000 sha1 calculations and the page is still displayed snappier than I can refresh my window.

Then I figured it was MySQL's fault. But again, didn't take much testing to figure out that MySQL is working faster than I need it to. Using the MySQL CLI client the user select query doesn't even take measurable time - it's done before I've even let the return key up.

The issue must be PHP's connection to MySQL - that's as far as I've been able to reason. I can find tons of stuff about PHP being slow or MySQL being slow, but nothing about PHP+MySQL being extremely slow.

Thanks to anyone who can help me solve this!

I'm using XAMPP 1.8.0 for win32 (Download link)
PHP version: 5.4.4
MySQL version: 14.14

EDIT: After timing, it turns out it's the connect function that's taking so long:

$time = microtime(true);

$con = mysql_connect("localhost", "root", "");
mysql_select_db("mysql", $con);

$con_time = microtime(true);

$result = mysql_query('SELECT host,user,password FROM user;');

$sel_time = microtime(true);

printf("Connect time: %f\nQuery time: %f\n",
       $con_time-$time,
       $sel_time-$con_time);

Output:

Connect time: 1.006148
Query time: 0.000247

What can cause PHP to spend do much time connecting to the database? The CLI client, HeidiSQL and MySQL workbench connect instantly

windows mysql php xampp

6 Answers

  1. This is taken almost verbatim from my answer here, but I know we frown on link-only answers on SO so I imagine you guys do as well :-)

    If you are having this problem and using a version of Windows before Windows 7, this is probably not the answer to your problem.

    Why is this happening?

    The cause of this problem is IPv4 vs IPv6.

    When you use a host name instead of an IP address, the MySQL client first runs an AAAA (IPv6) host lookup for the name, and tries this address first if it successfully resolves the name to an IPv6 address. If either step fails (name resolution or connection) it will fallback to IPv4, running an A lookup and trying this host instead.

    What this means in practice is that if the IPv6 localhost lookup is successful but MySQL is not bound to the IPv6 loopback, you will need to wait for one connection timeout cycle before the IPv4 fallback occurs and the connection succeeds.

    This was not an issue prior to Windows 7, because localhost resolution was done via the hosts file, and it came preconfigured with only 127.0.0.1 - it did not come with it's IPv6 counterpart ::1.

    Since Windows 7, however, localhost resolution is built into the DNS resolver, for reasons outlined here. This means that the IPv6 lookup will now succeed - but MySQL is not bound to that IPv6 address, so the connection will fail, and you will see the delay outlined in this question.

    That's Nice. Just tell me how to fix it already!

    You have a few options. Looking around the internet, the general "solution" seems to be to use the IP address explicitly instead of the name, but there are a couple of reasons not to do this, both portability related, both arguably not important:

    • If you move your script to another machine that only supports IPv6, your script will no longer work.

    • If you move your script to a *nix-based hosting environment, the magic string localhost would mean the MySQL client would prefer to use a Unix socket if one is configured, this is more efficient than IP loopback based connectivity

    They sound pretty important though?

    They aren't. You should be designing your application so that this sort of thing is defined in a configuration file. If you move your script to another environment, chances are other things will need configuring as well.

    In summary, using the IP address is not the best solution, but it is most likely an acceptable one.

    So what's the best solution?

    The best way would be to change the bind address that the MySQL server uses. However, this is not as simple as one might like. Unlike Apache, Nginx and almost every other sane network service application ever made, MySQL only supports a single bind address, so it's not just a case of adding another one. Luckily though, operating systems do support a bit of magic here, so we can enable MySQL to use both IPv4 and IPv6 simultaneously.

    You need to be running MySQL 5.5.3 or later, and you need to start MySQL with the --bind-address= command line argument. You have 4 optionsdocs, depending on what you want to do:

    • The one you are probably familiar with, and the one that you are most likely (effectively) using, 0.0.0.0. This binds to all available IPv4 addresses on the machine. This actually is probably not the best thing to do even if you don't care about IPv6, as it suffers the same security risks as ::.

    • An explicit IPv4 or IPv6 address (for example 127.0.0.1 or ::1 for loopback). This binds the server to that address and only that address.

    • The magic string ::. This will bind MySQL to every address on the machine, both loopback and physical interface addresses, in IPv4 and IPv6 mode. This is potentially a security risk, only do this if you need MySQL to accept connections from remote hosts.

    • Use an IPv4-mapped IPv6 address. This is a special mechanism built into IPv6 for backwards compatibility during the 4 -> 6 transition, and it allows you bind to a specific IPv4 address and it's IPv6 equivalent. This is quite unlikely to be useful to you for anything other than the "dual loopback" address ::ffff:127.0.0.1. This is most likely the best solution for most people, only binding to the loopback but allowing both IPv4 and IPv6 connections.

    Do I need to modify the hosts file?

    NO. Don't modify the hosts file. The DNS resolver knows what to do with localhost, redefining it will at best have no effect, and at worst confuse the hell out of the resolver.

    What about --skip-name-resolve?

    This may also fix the problem, for a related but slightly different reason.

    Without this configuration option, MySQL will attempt to resolve all client connection IP addresses to a hostname via a PTR DNS query. If your MySQL server is already enabled to use IPv6 but connections are still taking a long time, it may be because the reverse DNS (PTR) record is not correctly configured.

    Disabling name resolution will fix this problem, but it does have other ramifications, notably that any access permissions configured to use a DNS name in the Host condition will now fail.

    If you are going to do this, you will need to configure all your grants to use IP addresses instead of names.

    • 34
  2. Best Answer

    Can it be that your mysql tries to run rev-dns query whenever you connect? try adding to my.cnf, section mysqld: skip-name-resolve.

    • 19

  3. Usually when IPv6 is enabled in the server connections to MySQL using localhost are extremely slow.

    Changing the mysql server address in the script to 127.0.0.1 solves the issue.

    • 13 
  4. mysql_connect("localhost", "root", "");

    Well, it's quite obvious what the reason is. PHP really good at some things, but not at directly translating 'localhost' into '127.0.0.1'. You must try that, it will really lower your overall website page loading time, because it holds PHP back from checking your HOSTS file and what not it's doing to get the real IP address behind 'localhost'

    • 2

  5. Adding this line to your hosts file solved the problem for me

    127.0.0.1 localhost

    Detailed answer can be found in this thread: https://stackoverflow.com/questions/13584360/php-with-mysql-is-slow

    • 1

  6. You can also eliminate the query slowdown by making a small adjustment to your db connection variable (which is hopefully in a separate file from your scripts for portability). Change the host value to "127.0.0.1" instead of "localhost". This bypasses the lengthy DNS lookup for localhost.

    Hope this helps!

    • 0