Currently working on a Symfony application, I have a MySQL database server and an Apache server set up in a VirtualBox virtual machine. My own machine and my VirtualBox machine are on a VirtualBox Host Only Network. This means that there is a virtual network on which only those two machines can communicate with each other. My machine is 192.168.56.1, whereas the VM is 192.168.56.2. As I want to access this database from both my Apache inside the VM as well as from the command line outside of the VM, I have set the IP address of my VM (192.168.56.2) as the database IP address in my Symfony config.
This did work, but my Symfony site was terribly slow. It took more than 30 seconds for each HTTP request to complete, meaning I had to wait 30 seconds for the actual page to be loaded and on top of that 30 seconds for each asset the page required. All in all, loading a single page took about 2 minutes. Obviously, this speed is inacceptable when working on an application.
After quite a bit of investigation with the Xdebug Profiler, I realized that creating the database connection was the main bottleneck. I verified this manually by connecting to the IP from the command line inside the VM using the MySQL CLI client. Even though I was on the same maching as the IP I was connecting to, establishing the connection took almost 30 seconds. Having narrowed it down to this issue, finding the solution was straightforward. There are several questions on StackOverflow regarding this problem, for example: Why is connecting to MySQL server so slow?. And indeed: Changing the server IP to 127.0.0.1 fixed the problem immediately.
It might seem counter-intuitive that connecting to an IP address which is associated with your local machine is this much slower than connecting via the loopback interface on 127.0.0.1. The reason can be found when looking at the linked MySQL documentation on how MySQL handles DNS lookup during connection: When someone connects to the MySQL server, the server tries to do a reverse DNS lookup of the connecting person's IP address to determine their host name. This is then used to match the host name against the access list to find out whether the connecting user is allowed to access the server and with what permissions. Obviously, this needs to be done before letting the user into the system, so the connection is halted. On a private network, however, IP addresses can not be looked up as there will with a very high probability no DNS server available which can resolve them. This means MySQL keeps on trying until it runs into a timeout, which seems to be set to 30 seconds. The reason that connecting via 127.0.0.1 is not affected by this problem is that MySQL recognizes this as the loopback interface and knows that looking up IPs on this interface will never work, so it skips the lookup directly.
So what can be done to fix this? The easiest way is to connect via the loopback interface on 127.0.0.1. However, if you need to connect to an intranet IP address because your database is on a different machine, you should either make sure that IPs can be looked up or disable IP lookup on the MySQL server using the skip-name-resolve option on your MySQL server. Note that disabling this option will mean that you can no longer use hosts in your user permissions and need to set permissions based on IP addresses instead.
No comments:
Post a Comment