Processing of RDS MySQL connections with a full number of cases

Processing of RDS MySQL connections with a full number of cases

2 cases of RDS MySQL connections

1. Too many idle connections

Reason:

• Applications use long connection mode-for long connection modes (such as Java apps), the application side should configure the connection pool. The number of initial connections to the connection pool is set too high, and after the app starts, multiple idle connections to the RDS instance are established. If you are having problems with a connection that is full (too many connections), check that the connection pooling is enabled for multiplexing connections.

• Applications use short connection mode-for short connection modes such as PHP applications, a large number of idle connections indicate that the application does not explicitly close the connection after the query has finished executing. The user should check to see if the app has explicitly closed the connection to the RDS instance after the page query ends by invoking the connection shutdown method.

Solve:

• To terminate the current idle session with the DMS or kill command, please refer to the following steps: How to terminate the connection with RDS MySQL

• To modify the application, the long connection mode requires the reuse of the connection pooling feature (it is also recommended to enable the connection detection feature), please refer to the connection pooling configuration document for specific settings.

• To modify the application, the short connection mode requires the method of closing the connection to be called after the query ends in the code.

• For non-interactive mode connections, the console = "parameter setting =" Setting the Wait_timeout parameter to a smaller value, the Wait_timeout parameter controls the timeout period for non-interactive mode connections (in seconds, the default value is 24 hours-86,400 seconds), when the non-interactive connection is idle for more than Wait_ After the time specified by timeout, the RDS instance will actively close (disconnect) the connection.

• For interactive mode connections, the console = "parameter setting =" Setting the Interactive_timeout parameter to a smaller value, the Interactive_timeout parameter controls the time-out of the interactive mode connection (in seconds, the default value is 3 hours-7,200 seconds), When the interactive connection is idle for longer than the time specified by Wait_timeout, the RDS instance actively shuts down (disconnects) the connection.

Suggestions and instructions:

• In cases where the RDS MySQL instance connection is completely full, the instance cannot be connected through DMS or otherwise, so for long connection mode, it is recommended that the maximum number of connections for the connection pool is slightly smaller than the number of connection limits for the instance specification, such as 10 connections for DMS or other administrative operations. In the event of an inability to connect, it is recommended to modify the Wait_timeout parameter to a smaller value in the console, prompting the RDS instance to actively shut down connections that have idle time exceeding the threshold.

• Typically, a non-interactive mode is applied to an RDS instance, and which mode is used to see how the application's connection is configured, such as PHP's interactive mode of connecting by passing the Mysql_client_interactive constant to the mysql_connect () function.

• RDS MySQL acts as a server, passively receives a connection from an application or client, processes a query or command submitted by an application or client, and returns a result. The RDS instance itself is not actively initiating the connection.

Note: Before a large number of idle connections, there may be too many transient connections, because RDS as a passive server receive connections, usually the application of SQL is not optimized for the problem, so you need to start with SQL optimization to solve the problem at the root.

2. Too many active connections

Reason:

• Lock waits cause increased number of active connections (including InnoDB lock waits, MyISAM table-level lock waits, table metadata lock waits)

• High CPU usage causes increased number of active connections

• High IOPS usage leads to increased number of active connections

Solve:

• InnoDB lock Wait processing, please refer to: RDS MySQL InnoDB lock wait and lock wait timeout processing

• MyISAM table level lock wait processing, please refer to: RDS MySQL MyISAM Table level lock wait generation and processing

• Table metadata Lock waits, please refer to: Generation and processing of Metadata lock on RDS MySQL table

• For processing of increased active connections due to high CPU usage, refer to: Causes and solutions for high RDS MySQL CPU usage

• High IOPS usage leads to increased number of active connections, please refer to: Causes and handling of high RDS MySQL IOPS usage

If the problem is not resolved, please contact after-Sales technical support.

Processing of RDS MySQL connections with a full number of cases