24

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

2

6 Answers 6

19

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.

12
  • Oddly enough both PHPMyAdmin and the MySQL CLI client now gives me "Host '127.0.0.1' is not allowed to connect to this MySQL server". For some reason the PHP script still works, but just as slow as before
    – Hubro
    Jul 17, 2012 at 16:04
  • are 'fat applications' for managing mysql - like mysql workbench - as slow?
    – pQd
    Jul 17, 2012 at 16:06
  • Running the same query from MySQL Workbench is just as fast as the CLI client, and so is HeidiSQL
    – Hubro
    Jul 17, 2012 at 16:10
  • add some timing in php and check if it's connecting or running the query that takes a lot of time.
    – pQd
    Jul 17, 2012 at 16:17
  • Thanks for that comment, I updated my question. Both the connection and query is super fast
    – Hubro
    Jul 17, 2012 at 16:25
34

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.

9
  • 2
    Finally! Thank you, thank you! At last I found a proper, complete and clear explanation of all the causes, possible solutions and their counterindications. You should write a book about it! Jan 22, 2014 at 9:03
  • 4
    I was having a 1 second delay connecting to MySQL on localhost until I changed the bind-address to ::1. Unfortunately ::ffff:127.0.0.1 continued to give me a 1 second delay (regardless of using skip-name-resolve or not), any ideas why? (on Windows 8.1)
    – Simon East
    Feb 5, 2014 at 12:07
  • 1
    @Simon Not a clue without looking, but first step to debug would be to try to connect to both the IPv6 loopback and the IPv4 loopback directly using the explicit addresses to verify that MySQL is actually listening and connectable on both stacks, and debugging from there.
    – DaveRandom
    Feb 5, 2014 at 13:25
  • 1
    yea, ::ffff:127.0.0.1 doesnt work... Jul 17, 2014 at 13:37
  • If I bind it with ::1, Sqlyog doesnt work... what to do?? Jul 17, 2014 at 13:41
13

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.

3
  • +1 this was the correct answer for me. I guess in Windows 8 they moved the resolution of localhost to the DNS resolver for the reason that @DaveRandom linked to: serverfault.com/questions/4689/…
    – wwarren
    Mar 19, 2013 at 0:11
  • It worked for me :D
    – FosAvance
    Oct 25, 2014 at 15:05
  • 1
    This can happen for other servers than localhost. I had the same delay issue for a server address on the form xx.xxxx.xxxxx.xxxxx.com. Once I changed the server name to its IP address, the problem was gone.
    – Gruber
    Nov 21, 2014 at 9:01
2
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'

4
  • I can't figure out what you are trying to suggest the problem is.
    – kasperd
    Dec 8, 2014 at 10:10
  • @kasperd DNS resolving of 'localhost'
    – Xesau
    Dec 9, 2014 at 15:03
  • note from '17 - mysql_* functions are deprecated now and removed in php7
    – treyBake
    Jul 31, 2017 at 9:17
  • This worked for me. Funnily enough on another almost identical machine I configured months ago the other methods worked, but in this machine it didn't work until I changed this on PHP. It is my understanding that the hosts file should resolve this, so I must have something wrong in my machine configuration.
    – kissumisha
    Jul 18, 2022 at 10:52
1

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

0

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!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .