MySQL 5.6 parameter extra_port

MySQL 5.6 parameter extra_port

Preface
As a MySQL DBA, the Too connector connections error is often encountered during the O & M of MySQL. At this time, it is embarrassing that database maintenance personnel cannot log on to the database for maintenance. Some may say that you can modify the configuration file and restart the database. Well, this is the worst availability among many solutions. This article describes how to solve this problem through the Management port, a new feature of 5.6.
Understanding extra_port
MySQL versions 5.6.14 and later introduce an extra_port parameter to solve the problem of too connector connections.
Percona official introduction:
According to the official documentation, when MySQL is started, the Percona Server will listen to requests from this port by specifying a port number using this parameter (do not conflict with the normal Database Service port. Enabling this parameter can solve the problem of using the thread_pool feature. Because all connection pool workers are busy processing slow querey or are locked, DBAs cannot connect to the database through the normal port, so that DBAs can maintain the database normally.
The usage is as follows:

Mysql -- port = 'extra-port-number' -- protocol = tcp
Official Maridb introduction (similar to percona introduction, readers can translate it by themselves)

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 mainly controls how many connections can be created through the Management port.
Official introduction:
"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."
3. 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
Use the preceding two parameters to set the number of connections to the entire database. The preceding parameter configuration allows you to create up to four connections.
During the test, try to connect to the database by using the following command:
Scenario 1 simulate business connection to the database and try to connect to the database under two different sessions

Mysql-uyang-pyang-h127.0.0.1-P3306
Scenario 2 simulate DBA connection to database and try connection under two different sessions

Mysql-uroot-h127.0.0.1-P3306
Scenario 3 simulate DBA connection to database and try connection under two different sessions

Mysql-S/srv/my_3306/run/mysql. sock
Scenario 4 occupies all the connections of the database, and then uses the extra_port to connect as root.

Mysql -- port = 13306 -- protocol = tcp-uroot-h127.0.0.1
Scenario 5 occupies all the connections of the database, and then uses the extra_port to connect to the database as a Service Identity.

Mysql -- port = 13306 -- protocol = tcp-uyang-pyang-h127.0.0.1
Scenario 6 try to connect this time without specifying the communication protocol

Mysql -- port = 13306-uyang-pyang-h127.0.0.1
Scenario 7 simulate DBA and business connection to the database at the same time, and try to connect under two different sessions

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
Scenario 1: Only one connection is allowed. ERROR 2nd (HY000): Too connector connections
Scenario 2: only two connections are allowed. ERROR 3rd (HY000): Too connector connections is reported for 1040 times.
Scenario 3: only two connections are allowed. ERROR 3rd (HY000): Too connector connections is reported for 1040 times.
Scenario 4: connect twice and report ERROR 3rd (HY000): Too connector connections
Scenario 5: Unable to connect. The ERROR message "ERROR 1040 (HY000): Too required connections" is displayed"
Scenario 6: when the connection pool is full for the business, the administrator can connect the connection twice,
Scenario 7 if the Administrator logs in first, the business party cannot connect. If the business party logs in first, the administrator can log in once and cannot log in for the second time.
A total of four connections are allowed.
Iv. Summary
1. If the extra_port parameter is not used, all MySQL max_connection connections are occupied. DBAs can still use root or have super permission to connect to DB for database management, but only connect once. The business account can only log on to max_connection.
2. If the extra_port parameter is used, all MySQL max_connection connections are occupied. DBA can create extra_max_connections + 1 connection as administrator.
3. Features of max_connections
MySQL retains a connection used to log on to the Administrator (SUPER), which is used to connect the administrator to the database for maintenance, even if the current number of connections has reached max_connections. Therefore, the actual maximum number of available connections in MySQL is max_connections + 1. The maximum value (actual maximum number of connections) for this parameter is 16384, that is, the maximum value of this parameter cannot exceed 16384, the value of the max_connections parameter does not occupy too many system resources. The usage of system resources (CPU and memory) depends on the query density and efficiency;

This article permanently updates the link address: