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.

 

Cause

  • Too many idle connections exist.
  • Too many active connections exist.

 

Solution

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

 

Too many idle connections

Description

  • If your application, such as a Java application, uses persistent connections: A connection pool must be configured for your application. However, the initial number of connections in the connection pool is too high. After your application starts, it establishes multiple idle connections to the RDS instance.
  • If your application, such as a PHP application, uses short-lived connections: Your application does not explicitly close the connections after queries are completed.

 

Solution

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

Description

  • 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 that allowed in the instance specifications. For example, reserve 10 connections for DMS or other management operations. To fix the connection failure, we recommend that you first reduce the value of the wait_timeout parameter in the ApsaraDB for RDS console. This way, the RDS instance can automatically close idle connections if these connections time out.
  • In normal cases, applications connect to RDS instances in non-interactive mode. If you want to determine the connection mode, you must check the connection configuration of the target application. A PHP application can deliver the MYSQL_CLIENT_INTERACTIVE constant to the mysql_connect() function to enable the interactive mode.
  • Changes to the wait_timeout and interactive_timeout parameters take effect only on new connections that are created after the changes. For connections that are created before the changes, the original settings are used.

 

Too many active connections

Description

  • Slow queries increase.
  • Lock waits exist, such as InnoDB and metadata lock waits.
  • CPU utilization is high.
  • Input/output operations per second (IOPS) is high.

 

Solution

 

Application scope

  • ApsaraDB RDS for MySQL