For the past few days, I started to see this error “Too many connections” in the error log. Generally, the error is due to too many simultaneous connections (means all available connections are in use by other clients) or there might be old connections (sleeping process) that are not allowing new connections to get in.
I started to debug the issue by increasing ‘max_connections‘ variable from 151 (the default) to 200, because the number of connections are controlled by ‘max_connections‘ variable. However, that didn’t solve the issue. I increased it to ‘512’ and again, the error “Too many connection” didn’t stop from occurring in the logs.
Query the value of max_connections
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.07 sec)
Increase the value of max_connections in MySQL
mysql> set global max_connections = 200;
The above command will set max_connections immediately and it will last until ‘mysqld‘ is restarted. To set ‘max_connections‘ permanently, you should edit my.cnf
$ vi /etc/my.cnf
Lookout for [mysqld] section and add the following :
max_connections = 512
You will have to restart the MySQL daemon:
$ /etc/init.d/mysqld restart
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 512 | +-----------------+-------+ 1 row in set (0.07 sec)
As I told earlier, sometimes increasing the value of ‘max_connections‘ variable is not just enough. The reason, there might be plenty of other MySQL process that are sleeping and yet holding the connection and disallowing new connections. Also the value for ‘max_connections‘ depends upon the amount of RAM available in the system (based on how much RAM being used by each connection). So you can’t keep increasing ‘max_connections‘.
Check the list of sleeping processes in MySQL
mysql> show processlist; +--------+--------------+-----------+--------+---------+------+-------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+--------------+-----------+--------+---------+------+-------------+------------------+ | 597004 | localuser | localhost | NULL | Query | 1 | NULL | show processlist | | 597051 | localuser | localhost | kdb | Sleep | 800 | | NULL | | 597062 | localuser | localhost | testdb | Sleep | 710 | | NULL | | 597063 | localuser | localhost | testdb | Sleep | 70 | | NULL | | 597067 |localuser | localhost | testdb | Sleep | 61 | | NULL | | 597083 | localuser | localhost | testdb | Sleep | 522 | | NULL | | 597084 | localuser | localhost | testdb | Sleep | 5456 | | NULL | | 597087 | localuser | localhost | testdb | Sleep | 412 | | NULL | | 597088 | localuser | localhost | testdb | Sleep | 40 | | NULL | | 597092 | localuser | localhost | testdb | Sleep | 31 | | NULL | | 597093 | localuser | localhost | testdb | Sleep | 31 | | NULL | | 597102 | localuser | localhost | testdb | Query | 10 | cleaning up | NULL | | 597105 | localuser | localhost | testdb | Sleep | 119 | | NULL | | 597106 | localuser | localhost | testdb | Sleep | 180 | | NULL | +--------+--------------+-----------+--------+---------+------+-------------+------------------+ 14 rows in set (0.40 sec)
Kill MySQL process
mysql > kill 597084; Query OK, 0 rows affected (0.00 sec)
Why you should set right values for ‘interactive_timeout‘ and ‘wait_timeout‘
The variable ‘wait_timeout‘ should be set rightly to avoid too many ‘sleep’ process loaded in main memory. More number of sleep process in the RAM will reduce the MySQL performance and will quickly occupying all the available connections (the value set in max_connections). The ‘wait_timeout‘ will allow you to set number of seconds the MySQL server should wait before closing the connection on a non-interactive process.
Set ‘wait_timeout’ in runtime:
mysql > SET GLOBAL wait_timeout=180;
To set ‘wait_timeout‘ permanently:
$ vi /etc/my.cnf
Lookout for [mysqld] section and add the following :
wait_timeout=180;
interactive_timeout : Set the timeout for MySQL command-line sessions such as mysqldump or mysql client tools.
Restart MySQL as below:
$ /etc/init.d/mysqld restart
Set ‘interactive_timeout’ in runtime:
mysql > SET GLOBAL interactive_timeout=200;
To set ‘interactive_timeout’ permanently:
$ vi /etc/my.cnf
Lookout for [mysqld] section and add the following :
interactive_timeout=200;
Restart MySQL as below:
$ /etc/init.d/mysqld restart
Checkout the values of variables as shown below :
mysql> show variables like '%timeout%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | connect_timeout | 200 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 200 | | net_read_timeout | 200 | | net_write_timeout | 200| | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50 | | wait_timeout | 180 | +----------------------------+-------+ 10 rows in set (0.00 sec)
Note: The ‘wait_timeout‘ will clear the sleep process from RAM and ‘interactive_timeout‘ will affect only the command line sessions. Sometimes you might have to look at setting higher values for ‘connect_timeout‘, ‘net_read_timeout‘ and ‘net_write_timeout‘ (the default values are less) to skip timeout errors for queries that need longer time to execute.
Read this as well : If you are using WordPress, then you might want to hide those ugly database errors in the front end. Learn how to hide “Error Establishing Database Connection” Error in WordPress.
