Saturday, May 28, 2011

Heavy improper connection termination creates disk full risk

Logging warnings, is generally a good thing for a DBA. Without them, we may not be able to understand when there are problems that are otherwise hard to find. One of the warnings mysqld logs looks a bit like this:

110528 11:35:44 [Warning] Aborted connection 4185247 to db: 'db_name' user 'user_name' host: '' (Got an error reading communication packets)
There are two primary candidates for this type of warning: a) the client application, or b) the network.

On the client side, this is often caused by programs that terminate the connection without calling mysql_close after completing use of the connection. This problem is so common, in fact, that it is documented in
I can't count how many times I've had to speak with developers about making sure they close connections properly in order to prevent this kind of warning from occurring.

At the root of this issue, mysqld expects to receive some sort of information back from each client every so many (interactive_timeout or wait_timeout) seconds. If that time elapses without receiving anything, mysqld assumes that the connection has died. Rather than letting the valuable information go unnoticed, it logs that as a warning. mysql_close() tells mysqld that the connection is closing normally and the server should do its normal cleanup (no need to log normal closures).

If your code or the library it utilizes on doesn't call mysql_close when your program exits, mysqld will eventually log an error like the one above. If you utilize persistent connections, you will need to make sure that your connection talks to mysqld on a regular basis to keep that connection alive. If your library handles that for you, great. Otherwise, you'll need to "ping" mysqld at least once every interactive_timeout-1 or wait_timeout-1 seconds depending on your connection type. Most people should use wait_timeout for programs and interactive_timeout for mysql command-line client connections.

In rare occasions, some developers may also be concerned with net_read_timout - the amount of time mysqld will wait for data being transmitted to it in order to complete an operation. If you're sending data to mysqld and the sending process gets interrupted for a long time, this may also trigger the error listed above.

On the network side, I've seen this type of error is when the network is having a problem between the application layer and the database. How do we know which is which? We do all we can to make sure that the application layer isn't causing these issues so that when this error pops up, it's far less likely to be a preventable software coding issue.

Saturday, May 7, 2011

Just for fun...

There are moments in every life when we ask ourselves - why do I do this?

One of the reasons I enjoy doing what I do is to get away at times and go ride... In my case, a Honda Silver Wing (FSC600). I enjoy the "SWing" so much that I've made it a point to take it with me when I can. :-)

Here are some of the places I've ridden so far... (updated 30May11)