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: 'a.b.c.com' (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 http://dev.mysql.com/doc/refman/5.5/en/communication-errors.html
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.

2 comments:

  1. Just got asked a question about this privately: No - mysqld doesn't log every connection but it does log when a connection is terminated improperly.

    ReplyDelete
  2. One other question I got was concerning log rotation - yes we should rotate logs on a regular basis but doing so without paying attention to what's in those logs can make for tough times when trying to troubleshoot problems. I don't recommend allowing lot files to cause disk full but at the same time, I need to make sure my logs are actually looked at from time to time.

    ReplyDelete