Causes and solutions for MySQL too many connections error

At noon today, the MySQL service in the development and testing environment reported an error of too many connections. Judging from the problem, the connection pool may be full, causing all connections to be unavailable.

In this case, the most direct way is to reset the maximum number of connections. Check the my.cnf file, which contains two parameters about the number of connections:

max_connections: Maximum number of connections

max_user_connections: Maximum number of user connections

The first parameter determines the maximum number of connections for the instance, and the second parameter determines the maximum number of connections for a single user.

In general online environments, for the sake of safety, these two parameters cannot be set to be equal. The max_user_connections parameter can be set slightly smaller to leave a certain margin to prevent a single user from occupying all the connection pools.

When I saw the above problem, my first reaction was to log in and kill some connections. However, when I tried to log in, I found that I could no longer log in. Even the DBA management account dba_admin could no longer establish a connection. View the error log:

2019-08-12T06:02:42.928412Z 0 [Warning] Too many connections
2019-08-12T06:02:42.930181Z 0 [Warning] Too many connections
2019-08-12T06:02:44.595199Z 0 [Warning] Too many connections
2019-08-12T06:02:44.597160Z 0 [Warning] Too many connections
2019-08-12T06:02:44.782042Z 0 [Warning] Too many connections

There are too many connections in all of them. Fortunately, it is a development environment. In this case, we can use the method of stopping the database service to restart it, and then increase the maximum number of connections again. Of course, the amount of this increase needs to be considered. Generally, it needs to be set in combination with the wait_timeout value, that is, the waiting timeout. Generally, if the wait_timeout value is relatively large, it will often result in a large number of connections, and a connection will consume about 2M of memory. If you set more connections, it is likely that memory will be exhausted. If the wait_timeout value is set relatively small, connections will be constantly created and destroyed, which will waste certain IO resources.

Therefore, how to obtain a balanced value becomes the key, as explained in the MySQL official documentation:

According to the official documentation, several GB of memory can fully support you to set the number of connections between 500-1000. In fact, the value in this range can meet most needs.

In addition, the official document also mentions a feature, which is quite good.

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator who also has the PROCESS privilege can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected.

In fact, the maximum number of connections allowed is the value of max_connections + 1 connection. The last extra connection is for users with super privileges. In this way, when the connections are exhausted, you can log in with super privileges, use the show processlist method to view the connections, and kill some unnecessary connections to make the service available again.

However, in actual applications, we often assign super permissions to some unnecessary accounts for the sake of convenience, resulting in ordinary users with super permissions occupying an extra connection. In today's problem, such settings are of no help and it is still impossible to log in, because, after all, one connection is too few. If other accounts have already occupied this super connection, then this instance is still inaccessible.

In this case, in addition to restarting the MySQL instance and modifying the maximum number of connections in the my.cnf file, is there any way to handle it without stopping the service? The answer is yes. But there is a prerequisite, your service must be Percona-server. If you use the official version of MySQL, then this method will not work. It is said that MariaDB can also use this method. I haven't tested it and don't quite understand it. If you are interested, you can test it.

Starting with Percona Server 5.5.29 you can simply add extra_port to your my.cnf and the next time you restart the port will become available and will listen on the same bind_address as regular connections. If the extra_port variable is not set, no additional port will be available by default. You can also define extra_max_connections the number of connections this port will handle. The default value is 1.

The above is the description on the official website of Percona-Server. The login problem after the connection pool is occupied can be solved by two other parameters:

mysql:(none) 22:12:51>>show variables like '%extra%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| extra_max_connections | 10 || extra_port | 43130 |
+----------------------------------+-------+
2 rows in set (0.00 sec)

These are the two parameters, one is extra_max_connections, and the other is extra_port. These two parameters allow us to have extra connections and extra ports to connect to the server, so that we can use show processlist to observe the connection status, kill some unnecessary connections, and make the server available again.

Note, however, that extra_port has been removed in Percona Server 8.0.14 and newer because the MySQL community has implemented admin_port, which duplicates this functionality. So make sure to edit my.cnf when upgrading to Percona Server 8.0.14 and update extra_port if it is already defined there!

Finally, let's take a look at the connection. In this example, we set extra_port to 43130:

[root@ log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P4313
Enter password:
 Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71920
Server version: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected]:(none) 22:40:59>>exit
Bye

---------------------------------------------------------------------------

[root@ log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P43130
Enter password:
 Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71941
Server version: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected]:(none) 22:41:05>>

Brief summary

• If using the official MySQL version:

When the number of connections exceeds the maximum value, first log in with a brand new super account to see if you can log in directly. If not, you can only stop the service, then reset the number of connections, and then restart the service.

• If using Percona-Server or MariaDB:

You can try to configure extra_port in advance to prevent the number of connections from being full. If this happens and is not configured in advance, it is best to supplement it after stopping the service to prevent it from happening again.

The above is the detailed content of the cause and solution of MySQL too many connections error. For more information about MySQL too many connections error, please pay attention to other related articles on 123WORDPRESS.COM!