mysqli - PHP to EasyPHP MySQL server 1 second connection delay

Question

I'm asking this here because I think it applies to more than just EasyPHP itself.

I currently use EasyPHP as my WAMP development server so I can create web applications. The problem is, when I get the mysqli object to connect to the database it takes 1 second. Running the same query on shared hosting results in speeds over 200 times faster. Should this be something I should be worried about? In terms of scalability or moving my application to another server would it be smart to spend a while seeing what the issue is? I just assumed that perhaps EasyPHP was slow. Not a major, just something that struck me as interesting.

Answer 1

_{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 (evidently on the OP's machine this is 1 second) 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 (or set the corresponding option in my.ini). You have 4 options^docs, 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/be required to 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.