| |
One. Introduction
As a MySQL DBA, MySQL operation and maintenance during operation, the often encountered Too many connections error, then as a database maintenance staff are not landing database maintenance, how embarrassing thing. One might argue that you can modify the configuration file, and then restart the database to solve. Well, but this is the availability of a wide variety of solutions in the worst kind, this article describes the new features by 5.6 - management port to solve this problem.
Two recognized extra_port
MySQL introduced a parameter after the 5.6.14 version extra_port to solve the problem of too many connections.
percona official description:
From the official document describes the view, in the MySQL startup use this parameter to specify a port request (and not a normal database service port conflicts), Percona Server listens from the port. Enabling this parameter can be solved using thread_pool feature, since all the connection pool worker querey slow or busy processing result DBA can not be locked through the normal port to connect DB, so the DBA can properly maintain the database.
Used as follows:
mysql --port = 'extra-port-number' --protocol = tcp
Maridb official presentation (and percona introduced similar Readers can own translation)
Description:. Extra port number to use for tcp-connections in a one-thread-per-connection manner If set to 0, no other port is used Introduced for the MariaDB 5.1 threadpool..
extra_max_connections This parameter is mainly to control how many connections can be created through the management port.
Official description:
"This variable can be used to specify the maximum allowed number of connections plus one extra SUPER users connection on the extra_port. This can be used with the extra_port variable to access the server in case no new connections can be established due to all worker threads being busy or being locked when pool-of-threads feature is enabled. "
Three extra_port test
Test environment: 5.6.26-74.0-log Percona Server
parameter settings:
max_connections = 1
extra_max_connections = 1
extra_port = 13306
Through the above two parameters to set the number of connections of the entire database. By the above configuration parameters, you can create up to four connections.
Testing process by using the following command to try to connect to the database:
Scene 1 analog business connect to the database in two different session tries to connect
mysql -uyang -pyang -h127.0.0.1 -P3306
Scene 2 analog DBA database connection, at two different session tries to connect
mysql -uroot -h127.0.0.1 -P3306
Scenario 3 analog DBA database connection, at two different session tries to connect
mysql -S /srv/my_3306/run/mysql.sock
All connections Scene 4 filled database, and then use the port as root connection extra_port
mysql --port = 13306 --protocol = tcp -uroot -h127.0.0.1
All connections 5 scenes filled database, and then use extra_port port to connect business identity
mysql --port = 13306 --protocol = tcp -uyang -pyang -h127.0.0.1
Scenario 6 The communication protocol is not specified, try to connect
mysql --port = 13306 -uyang -pyang -h127.0.0.1
Scene 7 Analog DBA and operations simultaneously connect to the database, try to connect at two different session
mysql -uyang -pyang -h127.0.0.1 -P3306
mysql -S /srv/my_3306/run/mysql.sock
mysql -uroot -h127.0.0.1 -P3306
Test results
Scene 1, only connect 1, 2nd error ERROR 1040 (HY000): Too many connections
Scene 2, only connect 2nd, 3rd error ERROR 1040 (HY000): Too many connections
Scene 3, only connect 2nd, 3rd error ERROR 1040 (HY000): Too many connections
Scenario 4, you can connect 2nd, 3rd error ERROR 1040 (HY000): Too many connections
Scenario 5, you can not connect, suggesting "ERROR 1040 (HY000): Too many connections"
Scenario 6, the business occupied the connection pool is full, the administrator can connect to 2 times,
Scene 7 If the administrator log in first, business party is unable to connect; if the business side before landing, the administrator can log in once and could not land a second time
You can connect a total of 4 times.
Four summary
1 If you do not extra_port parameters, MySQL max_connection connections were all occupied, DBA can still root or that is a super permission to connect to DB on the DB management, but can only connect once. Business account can only login max_connection times.
2 If extra_port parameters, MySQL max_connection connections were all occupied, DBA can create extra_max_connections + 1 connection with administrator privileges.
3 max_connections property
MySQL anyway a reservation for an administrator (SUPER) landing connection, connect to the database administrator for maintenance operations, even if the current number of connections has been reached max_connections. Therefore, the actual maximum number of connections MySQL can max_connections + 1; the parameters of the actual functioning of the maximum (the actual maximum number of connections available) 16384, that is, the parameter must not exceed 16384, 16384 even exceed also prevail; increase max_connections value of the parameter will not take up too much system resources. System resources (CPU, memory) occupied mainly depends on the query density and efficiency; |
|
|
|