Failure Analysis | MySQL 8.0 solves the problem of full connection

Author: Yang Taotao

Senior database expert, specializing in MySQL for more than ten years. Good at backup and recovery related to open source databases such as MySQL, PostgreSQL, MongoDB, SQL tuning, monitoring operation and maintenance, and high-availability architecture design. Currently working at Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial companies.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

Just a few days ago, I was discussing the problem of full MySQL connections with a customer: ERROR 1040 (HY000): Too many connections

From a practical point of view, the full number of connections is an old problem. The reasons for this problem may be as follows:

1. The app is not used to restricted user permissions, and is addicted to using ALL permission users.

2. In order to save trouble, the DBA establishes multiple ALL authority users and assigns them to development, operation and maintenance, etc.

3. The MySQL database server does not use the connection pool (similar to the MySQL Enterprise Edition connection pool plug-in), and more and more database requests accumulate and the connection is full.

4. There is no connection pool deployed between the application side and the MySQL side, and the daily business is handled by a direct connection to MySQL, and the connection is full due to too many database requests.

5. The setting of MySQL parameter max_connections is unreasonable, and there is a big deviation from the current database request, which causes an error when the connection is not enough.

If subdivided into users with different permissions according to business functions, and only one administrator user is retained, when the problem is exposed, the administrator can use the reserved connection to enter the database to view the specific problem. By default, MySQL reserves an additional connection for the administrator to handle scenarios where the connection is full; however, this is not the case in real-world scenarios. Most MySQL databases share an administrator user for all business modules or use multiple differently named ones with administrator privileges. User. After a problem occurs, the DBA can only increase the max_connections value (under the premise that the database server load is controllable).

MySQL 8.0 comes with an administrative connection interface (administrative connection interface) that can help DBAs solve this type of problem.

The connection management interface restrictions are relaxed a lot. There is only one connection reserved for the old version, and connection management does not limit the number of connections (hardware level limitation).

specifically in MySQL 8.0? Globally set variables starting with admin:

admin_address: Connect to the management interface to monitor the IP address or domain name. Only a single value can be set.

admin_port: connect to the monitoring port of the management interface, the default is 33062, you can specify it yourself, and it should not exceed 65535.

The beginning of admin_ssl, the beginning of admin_tsl, etc. are related to setting up a secure connection. The default is empty and optional configuration.

create_admin_listener_thread: Whether to create a separate listener thread for the connection management interface, it is not created by default.

The prerequisite for using the connection management interface is that the user must have super static permissions or service_connection_admin dynamic permissions.

see how to use the connection management interface:

Confirm that the parameters are all turned on:

localhost:(none)>select @@admin_address,@@admin_port,@@create_admin_listener_thread;
+-----------------+--------------+--------------------------------+
| @@admin_address | @@admin_port | @@create_admin_listener_thread |
+-----------------+--------------+--------------------------------+
| debian-ytt1     |        18027 |                              1 |
+-----------------+--------------+--------------------------------+
1 row in set (0.00 sec)

Create a user with service_connection_admin permissions:

localhost:(none)>create user ytt_admin;
Query OK, 0 rows affected (0.02 sec)

localhost:(none)>grant select, insert,update,delete,service_connection_admin on *.* to ytt_admin;
Query OK, 0 rows affected (0.02 sec)

In order to highlight the effect, change max_connections to the minimum value of 1. At this time, the maximum number of connections that a MySQL administrator can apply for is 2, and the maximum number of connections that an ordinary user can apply for is 1.

localhost:(none)>select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

Although the newly created user ytt_admin is an ordinary user, but holds the service_connection_admin authority, it can also enjoy the treatment of being a "deputy" administrator, and can request two connections at most. When the third connection is requested, it will report an error and exit.

root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)" &
[1] 7474
root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)" &
[2] 7475
root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)" &
[3] 7477
root@debian-ytt1:~/sandboxes/msb_8_0_25# ERROR 1040 (HY000): Too many connections

[3]+  退出 1                mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)"

Use the connection management interface to connect:

root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -h debian-ytt1 -uytt_admin -P 18027 -e "select sleep(3600)" &
[3] 8516

Check the connections currently connected to the MySQL service, and still use the connection management interface: two normal connections and two additional connections

root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -h debian-ytt1 -uytt_admin -P 18027 -e "show processlist;" -ss
8    ytt_admin    debian-ytt1:57946    NULL    Query    154    User sleep    select sleep(1000)
9    ytt_admin    debian-ytt1:57948    NULL    Query    148    User sleep    select sleep(3600)
13    ytt_admin    debian-ytt1:60718    NULL    Query    138    User sleep    select sleep(3600)
17    ytt_admin    debian-ytt1:60726    NULL    Query    0    init    show processlist

There is a common prerequisite for using the connection management interface and the additional connection functions reserved for the administrator in the old version: the connection must be established successfully. That is to say, there must be hardware resources required to establish this connection. If the resources are insufficient, the MySQL server will refuse the connection. For example, there may be this error: ERROR 2003 (HY000): Can't connect to MySQL server on'...' ( 110)

Although MySQL 8.0 comes with a connection management interface that has a good alternative solution to the problem of full connections, it is after all a temporary solution on the database side. To solve this problem fundamentally, you have to optimize before the request enters the database. After all, the database is not a panacea. For example, you can use the following possible methods to circumvent this problem:

1. User permissions are clearly divided, super permissions can only be given to administrators, and other personnel withdraw this permission.
2. Limit the current before requesting to enter the database, and set it in strict accordance with the relevant parameters of the pressure test performance of the database.
3. Optimize business SQL and table structure to achieve the ultimate performance as much as possible without dragging down the database.
4. The database side dismantles the database and dismantles the table, and uses a distributed solution to split a large number of requests.