Tuesday, August 16, 2011

The case of slow MySQL connect

Quite of a sudden, PHP scripts on one of my servers became very slow. Other servers worked fine. PHP-FPM logs showed that the biggest delays happened during database operations. The database server, though, recorded nothing in the mysql-slow.log.

Then, we had a look at the process list in MySQL. There were some dozens of lines like these:

| 1409888 | unauthenticated user |    | NULL | Connect     |   NULL | login                                                          | NULL             |
| 1409889 | unauthenticated user |    | NULL | Connect     |   NULL | login                                                          | NULL             |
| 1409890 | unauthenticated user |    | NULL | Connect     |   NULL | login                                                          | NULL             |

where is the IP address of that application server whence the connection was opened. Google said that these messages usually indicate at DNS problems. One of possible solutions would be to restart MySQL with --skip-name-resolve option, but we didn't want to stop the database. So, we added the hostname of the application server to the /etc/hosts file on the database server, and voilá! Unauthenticated users were gone in some seconds and the performance was restored.

The hosting company had informed us before of the planned maintenance, but they only mentioned their own website and their 'whois' server. They must have done something wrong with their DNS servers, too, which caused our database accept new connections very slowly.