What do I do if the number of connections to an ApsaraDB RDS for MySQL instance reaches the upper limit? - ApsaraDB RDS

Problem description

The number of connections to an ApsaraDB RDS for MySQL instance reaches the upper limit, and clients cannot connect to the RDS instance.

Causes

A large number of idle connections exist.
A large number of active connections exist.

Solutions

Note

Before you perform high-risk operations, such as modifying the configurations or data of an instance, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.
Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an RDS instance.
If you granted the permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.

Problem identification

In the ApsaraDB RDS console, access the Monitoring and Alerts page of your RDS instance. Find the Session metric and check whether the connection issue occurs due to a large number of idle connections or active connections. You can check the value of the mysql.active_session parameter.

Solutions to the issue caused by excessive idle connections

Reasons

Your application, such as a Java application, uses persistent connections, and a connection pool must be configured for your application. If you specify a large value for the initial number of connections in the connection pool and start the application, the application establishes multiple idle connections to the RDS instance.
Your application, such as a PHP application, uses short-lived connections, and does not explicitly close the connections after queries are complete.

Solution details

Use session management in the ApsaraDB RDS console, Data Management (DMS), or the kill command to terminate idle sessions. For more information, see How do I terminate sessions on an ApsaraDB RDS for MySQL instance?
If your application uses persistent connections, enable the reuse feature of the connection pool. We recommend that you also enable the connection detection feature.
If your application uses short-lived connections, modify application code to terminate connections after queries are complete.
For connections in non-interactive mode, log on to the ApsaraDB RDS console and decrease the value of wait_timeout on the Parameters page. wait_timeout determines the timeout period of a connection in non-interactive mode. Default value: 86400. Unit: seconds. The default value equals 24 hours. When a non-interactive connection is idle for a period of time that is longer than the value of wait_timeout, the RDS instance automatically terminates the connection.
For connections in interactive mode, log on to the ApsaraDB RDS console and decreases the value of interactive_timeout on the Parameters page. interactive_timeout determines the timeout period of a connection in interactive mode. Default value: 7200. Unit: seconds. The default value equals 2 hours. When an interactive connection is idle for a period of time that is longer than the value of interactive_timeout, the RDS instance automatically terminates the connection.

Note

If the number of connections to an RDS instance reaches the upper limit, you cannot connect your application to the RDS instance by using DMS or other methods. If your application uses persistent connections, we recommend that you set the maximum number of connections in the connection pool to a value that is smaller than the maximum number of connections that are supported by the instance type. For example, reserve 10 connections for DMS or other management operations. To fix the connection failure, we recommend that you decrease the value of wait_timeout in the ApsaraDB RDS console. This way, the RDS instance can automatically terminate idle connections if the connections time out.
In most cases, applications connect to RDS instances in non-interactive mode. If you want to know the connection mode, you must check the connection configuration of your application. You can specify the MYSQL_CLIENT_INTERACTIVE constant in the mysql_connect() function to enable the interactive mode for a PHP application.
Changes in the values of wait_timeout and interactive_timeout take effect only on new connections that are established after the changes. For connections that are established before the changes, the original settings are used.

Solutions to the issue caused by multiple active connections

Reasons

Slow queries increase.
Lock waits such as InnoDB lock waits and metadata lock waits exist.
CPU utilization is high.
IOPS is high.

Solution details

If the active connection issue is caused by InnoDB lock waits, resolve the issue based on the description in What do I do if row lock waits occur on an ApsaraDB RDS for MySQL instance and the row lock waits time out?
If the active connection issue is caused by metadata lock waits, see Use DMS to release metadata locks.
If the active connection issue is caused by high CPU utilization, resolve the issue based on the description in What do I do if the CPU utilization of an ApsaraDB RDS for MySQL instance or an ApsaraDB RDS for MariaDB instance is high?
If the active connection issue is caused by high IOPS usage, resolve the issue based on the description in What do I do if the IOPS usage of an ApsaraDB RDS for MySQL instance is high?