I have 100 PC clients connecting to MySQL over LAN.
They're running INSERT querys with no particular period.Statistically, at the same time no more than 10 PCs are trying to run INSERT query. This is not a limitation by design, is just that query executions depends on each PC user activity.
Each client connects and disconnects every time it needs to run the INSERT query (connect – insert – disconnect).
Everything was working fine for several months, but a few days ago client starts to fail at connect.
About twice a day, during 5-10 minutes, clients can't connect. They receive timeout errors. All PC clients fail at connecting during this period of time. None of them can connect.
Hour of fail is random. Sometimes happens at 09:00, sometimes at 13:00, etc. Activity of each user is quite regular, so there are not many differences from one day to another. I mean, the hour of day seems no significant.
Looking on server side, i see no errors in mysql logs, no service crashes, no server reboots. I only have the error log enabled (no query log, no slow-query log).
Then i was searching for a network problem, but tracerts from PC to Server, and from Server to PC was right.
I took a network capture on server (with wireshark) to see if i can find the error there.
During the error period (those 5-10 minutes) i see connections from PC clients stuck in "greeting" message. Client PC does not send "login" request in response.
Best Answer
Digging into network traces I found some Name Resolution problems.
I'm running 5.1.41 version of MySQL Community Edition. I don't know if this is true for more actual releases, but in this version, "name resolve" feature is enabled by default.
With name resolution enabled, each time a network connection starts, MySQL tries to resolve client's PC name from its IP.
If this name resolution fails for something, or gets delayed, it can cause connection problems.
With name resolution down, we get more failing and delayed connections each time a client PC retries, and at the end no network connection success.
Solution is to disable name resolution. This feature is important if you have privileges defined for some MySQL users that relies on client host name. But as we don't have any privilege based in client's host name (instead we use IP address) we can safely disable "name resolve" feuture.
This is the link that explains that, and point me to the right direction and to what to search in network captures:
https://bugs.mysql.com/bug.php?id=49877