Yesterday I helped someone who was seeing a lot of "server has gone away" error messages on his website. While investigating this problem, I noticed several things amiss, which appeared to be related but really weren't. The biggest measurable sign was
PLAIN TEXT CODE:
- [percona@server ~]$ mysqladmin ext | grep Abort
- | Aborted_clients | 14835 |
- | Aborted_connects | 15598 |
These two status variables are actually unrelated (see the manual page that explains them). The first was related to the errors the client was seeing: the server was closing inactive connections after a while, and I fixed it by increasing the wait_timeout configuration variable.
The second error does not indicate that an active connection is closed at all. Rather, it shows that a connection cannot be made for some reason. Perhaps it's networking, or perhaps there's an issue with permissions or something else. The first thing I did was look for packet loss between the database server and the web server; the network appeared to be working fine.
With that ruled out (at least, to my satisfaction) I turned to tcpdump to see what was happening with these connections. I ran the following command in one window of my screen session, so I could see when a connection was aborted:
PLAIN TEXT CODE:
- [percona@server ~]$ mysqladmin ext | grep Abort | grep -v 0
And then I started tcpdump in another window:
PLAIN TEXT CODE:
- [percona@server ~]$ tcpdump -s 1500 -w tcp.out port 3306
After I saw an aborted connection, I cancelled tcpdump and looked at the resulting file. Inspecting the session with tcpdump -r showed that there was a complete TCP session; nothing bad was happening at that layer. So I used the strings utility to look at the text sent in the packets:
PLAIN TEXT CODE:
- [percona@server ~]$ strings tcpdump.out
- Host 'XXX.XX.XX.XXX' is not allowed to connect to this MySQL server
I've anonymized the offending IP address. However, I checked the server's grant tables and indeed. that IP address (which is a machine in the local network) is not allowed to connect.
I don't actually use tcpdump much, but this was a fun little exercise that I thought I'd share with you.
Entry posted by Baron Schwartz | 4 comments