Mysql – How to prevent so many aborted clients in MySQL (increasing aborted_clients count)

MySQL

I am running MySQL 5.6.14 on an Ubuntu server and noticed the error log constantly filling with:

Aborted connection 17201 to db: 'somedb' user: 'sdb' host: 'x.x.x.x' (Got timeout reading communication packets)

(These happen about 10 times per minute. The counter is up to 13,000 after a server uptime of only 24 hours.)

We have perhaps twenty or thirty instances of a Windows client program that uses the .NET framework via Connector/NET (6.2.2.0) to connect to the database and I've made sure that every time it creates a connection (MySqlConnection.Open()) it is also properly closed (MySqlConnection.Close()).

I've adjusted various timeouts, max_allowed_packet, and other settings based on research on the web, but these still seem to persist.

Here are (what I believe are) the relevant settings:

connect_timeout = 20
wait_timeout = 60
net_read_timeout = 60
net_write_timeout = 60
max_allowed_packet = 33554432 // 32M

What can I do to stop these events from constantly happening?

Best Answer

It turns out that the MySQL Connector/NET behavior is at the heart of the matter.

By default, it will automatically create a pool of connections and reuse them as necessary. When you call .Open() a connection might be created anew, or you might get a connection that already exists but was idle (SLEEP). When you call .Close() the connection is returned to the pool.

There are a lot of other questions and answers which deal with the concept of "Should I create new connections for every action or should I reuse the same connection?" - the Connector/NET deals with that for you, so you should create, open and close them. The pooling is automatic and behind the scenes.

Something of importance here: The maximum number of connections that Connector/NET can create by default is 100. If you've got a lot of clients doing concurrent queries or what have you, it's possible the server could exceed its max_connections limit. If necessary you can limit the number of connections that Connector/NET creates by specifying a Maximum Pool Size option in its connection string options.

So, why all the aborted clients? The connections in the pool managed by Connector/NET are held open for longer than your server's wait_timeout, so the server closes them and considers them a client abort.

How long are they held open? Well, it depends on your Connector/NET version, but:

Starting with MySQL Connector/Net 6.2, there is a background job that runs every three minutes and removes connections from pool that have been idle (unused) for more than three minutes. The pool cleanup frees resources on both client and server side. This is because on the client side every connection uses a socket, and on the server side every connection uses a socket and a thread.

Prior to this change, connections were never removed from the pool, and the pool always contained the peak number of open connections. For example, a web application that peaked at 1000 concurrent database connections would consume 1000 threads and 1000 open sockets at the server, without ever freeing up those resources from the connection pool. Note, connections, no matter how old, will not be closed if the number of connections in the pool is less than or equal to the value set by the Min Pool Size connection string parameter.

-- Reference

Since you're using version 6.2.2.0, they're closed after three minutes by the client, which doesn't cause the server to treat it as a warning.

Solution: Increase wait_timeout to five minutes or so. Note: watch the number of connections to the server to make sure that having connections able to SLEEP longer doesn't cause the total number of connections to approach max_connections.

Related Question