KB: 03042014-001: Dealing with possible back log problems with MySQL

Problem description

Given a server running MySQL with a high connection creation rate, if you receive the following notifications:

WARNING: found MySQL connection failure ((2003, "Can't connect to MySQL server on '127.0.0.1' (110)")), retrying query..

And however, the server is working well, and it is possible to query the server at the address/port provided, then it is possible that there is a full “back log TCP” problem.

This TCP incoming connection accept rate depends on the size of the queue (the size of the current backlog) and how much time a connection stays in the queue (for example, the server may be lagging behind picking connections from the queue).

In the case of MySQL, default value for back log is set to 50. This way we can have up to 50 connections waiting to be accepted.

Note this value has nothing to do with “max_connections” which is the number of concurrent connections, already accepted, that the server is going to work with.

Let’s say “back_log” is the queue to get inside a place, and “max_connections” is the place’s capacity.

Possible problem solution

Assuming there are not other problems causing this 2003 code, that is, that:

1. There’s no firewall blocking certain operations, not all, for example a burst rate limit.
   
2. There is not a routing problem that is causing connections to work only sometimes.

If this is not the case, and the server is working, but from time to time some connections get rejected without a reason, and there are available connections (running SHOW PROCESSLIST), then follow next steps:

1. Update file /etc/mysql/my.cnf, and inside section [mysqld] add the following declaration:

   back_log = 200

2. After that, restart mysql (you need to, this value does not take effect after a reload).

   >> service mysql restart

3. Then, connect and run the following instruction:  SHOW VARIABLES to check if backlog is correctly configured .

Using Core-Admin to configure back_log

Doing this configuration with Core-Admin is really easy. You only have to:

1. Load MySQL management tool (from inside the machine’s view).
   
    
    
2. Now, click on “Options”:
   
    
    
3. After that, click on “Configure”:
   
    
    
4. Then the configuration panel will appear. Now there, you can configure he back log value needed.