max_user_connections specifies the maximum number of user connections. If you receive an error message related to reaching the maximum number of user connections, such as "has more than 'max_user_connections' active connections" or "User 'xxx' has exceeded the 'max_user_connections' resource", you can follow the steps in this article to resolve the issue.
Symptoms
You cannot connect to an ApsaraDB RDS for MySQL instance from the Data Management (DMS) console or a database client. The system returns an error message related to "max_user_connections".
Cause
New connections fail because the ApsaraDB RDS for MySQL instance has reached its connection limit.
Identify connection sources
Console features
SQL Explorer and Audit
After you enable the SQL Explorer and Audit feature, it automatically records SQL statements from the database kernel, including the executing account, IP address, and other execution details. This feature does not affect instance performance. For more information, see SQL Explorer and Audit.
SQL Explorer and Audit is a paid feature. For more information about billing, see Billing of SQL Explorer and Audit.
Procedure
-
Go to the RDS instance list, select a region in the top navigation bar, and then click the ID of the target instance.
-
In the left-side navigation pane, choose .
-
Enable SQL Explorer and Audit.
Session Management
The Session Management feature lets you view all current, active, and abnormal sessions for an instance. It displays information such as the user, host, execution time, and status of each session. For more information, see Session Management.
Procedure
-
Go to the RDS instance list, select a region in the top navigation bar, and then click the ID of the target instance.
-
In the left-side navigation pane, choose Autonomy Services > Diagnostics.
-
The Session Management tab displays the user, host, execution time, and status of current sessions for the instance.
SQL command
When the database has reached its maximum connection limit, you cannot establish new sessions to execute SQL statements. If you have an existing Data Management (DMS) session, you can use it to run SQL commands.
Connect to the database from an Elastic Compute Service (ECS) instance or log on to Data Management (DMS) and run the following SQL command to view and troubleshoot slow threads. For more information, see:
SHOW PROCESSLIST;The Host column in the results shows the source IP address of the connection.
The results include the Id, User, Host, db, Command, and Time columns. Check the Command column to determine the state of each thread. For example, a Binlog Dump GTID command indicates a primary-replica replication connection, and a Daemon command indicates an event scheduler daemon. Troubleshoot any long-running threads.
Resolution
-
Review the instance session details.
-
Go to the RDS instance list, select a region in the top navigation bar, and then click the ID of the target instance.
-
In the left-side navigation pane, choose .
-
On the Session Management tab, view the details of the instance sessions. For more information, see Instance sessions.
-
-
View the max_user_connections configuration of the database instance.
You can view the value of the max_user_connections parameter in the following two ways:
-
From the ApsaraDB RDS console:
-
Go to the RDS instance list, select a region in the top navigation bar, and then click the ID of the target instance.
-
In the left-side navigation pane, click Parameters.
-
On the Editable Parameters tab, view the max_user_connections parameter value.
-
-
Log in to ApsaraDB RDS for MySQL and execute the following command to view the value of the max_user_connections parameter.
show variables like 'max_user_connections';
-
-
After you confirm that you have sufficient memory resources, increase the value of the max_user_connections parameter. For more information, see Modify the maximum number of connections.
-
If the issue persists, try the following methods:
-
Troubleshoot and optimize the high connection count. For more information, see What to do if the number of connections to an ApsaraDB RDS for MySQL instance reaches the upper limit.
-
Restart the instance to release all connections. For more information, see Restart an instance.
-