MySQL

How to Resolve MySQL Too many connections

MySQL Too many connections

Recently, I saw some blank pages on my website without any error, especially when the web server was very busy. I suspected that somewhere in PHP codes caught the exception and threw nothing. So I decided to display the error on the screen and saw MySQL too many connections error like this:

SQLSTATE[HY000] [1040] Too many connections

It looked like a resource limitation on connections was over, so I first checked the current value of max_connections, which is the limitation on how many connections is allowed to use.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

It’s the default value. So I decided to lift the connection limit of mysqld like this:

[root@test ~]# vi /etc/my.cnf
...
[mysqld]
...
max_connections=1000

After that, you should restart mysqld to make max_connections take effect.

[root@test ~]# systemctl restart mysqld

Please periodically check how many connections that is created during the busy hours of server. Furthermore, you may need to raise innodb_buffer_pool_size to meet the new value of max_connections.

mysql> show processlist;
...

Then you will know how many connections that MySQL really needs, that’s how we prevent MySQL Too many connections.

Leave a Reply

Your email address will not be published. Required fields are marked *