2016-03-07

Slow MySQL connections when using local IP but not loopback address

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.

2016-03-01

StrokesPlus and GTK applications: clicking problems resolved

StrokesPlus is a neat tool for mouse gestures under Windows. The basic gist of mouse gestures is that you hold a dedicated button down and then perform a gesture by moving your mouse in a certain way. A mouse gestures tool, such as StrokesPlus, can then recognize the gesture and perform various actions based on what you configured it to do.

I have been using StrokesPlus heavily on Windows 8 and am still very satisfied with it. However, it does not work well together with GTK-based applications. I had always assumed this was a Windows 8 issue, but today found out it is actually a StrokesPlus problem. Whenever I use a GTK application, such as, e.g., GIMP, Meld, or Wireshark, I can only make one click in the application's window. Afterwards, I need to move the mouse out of the application window and back in to make the next click. This is quite annoying, especially when you have to do a lot of clicks, such as when editing images with GIMP.

Today, I stumbled upon a solution to this problem. It seems StrokesPlus draws an invisible window over all other applications to capture the mouse gestures. This probably prevents GTK applications from correctly capturing clicks, resulting in the behavior described above. The fix is to change the StrokesPlus settings so that this window is not always kept above all other windows.

To do this, go to the StrokesPlus settings and uncheck the checkbox "Keep Gesture Draw Window On Top". Afterwards, close and re-open StrokesPlus and all your GTK applications should work fine!