How do I resolve the "Too many connections" error in Amazon RDS for MySQL or Aurora MySQL?

Short description

If you get a "Too Many Connections" error when you connect to your DB instance, then all available connections are in use. The max_connections parameter in MySQL controls this limit for Amazon RDS for MySQL and Aurora MySQL.

You might receive this error when the DatabaseConnections metric in Amazon CloudWatch is close to or equal to the max_connections value for your DB instance. A low FreeableMemory metric value in Amazon CloudWatch can also show that the max_connections value exceeds the memory available for connections on your DB instance class. The error message looks similar to the following:

"Error 1040: Too many connections"

Your DB instance reaches the max_connections value for the following reasons:

• A sudden or gradual increase in client or application connections to the DB instance occurs because of increased workload or table and row level locking.
• A client or application doesn't properly close connections at the end of an operation.
• Sleeping connections increase because of a higher value for connection timeout parameters such as wait_timeout and interactive_timeout.

To resolve your max_connections error, use one of the following methods:

• Verify connections and identify sources
• Remove connections on your DB instance
• Increase the maximum number of connections to your DB instance
• Tune the max_connections parameter

Resolution

Verify connections and identify sources

Before you resolve your max_connections error, complete the following steps:

1. Connect to your MySQL DB instance using a MySQL client such as the MySQL command-line client or MySQL Workbench. For more information, see Connecting to your MySQL DB instance.
2. To view the threads that run on your MySQL instance, run one of the following commands:

   SHOW FULL PROCESSLIST\G

   SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

   Note: The SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST statements require a mutex and might negatively affect performance.
3. In the output, identify threads with a Command value of Sleep or threads with a high Time value. These threads represent idle or long-running connections that might contribute to the connection limit.
   Note: Make sure that your user account has the administration privilege for the MySQL PROCESS server. This privilege allows you to see all the threads that run on a MySQL DB instance. If you don't have the administration privilege, then you can view only the threads associated with the MySQL account that you use. For more information, see Privileges Provided by MySQL on the MySQL website.
4. To identify the connections and their source IP address, turn on logging on your DB instance. To turn on logging, modify the parameters in your DB parameter group. For more information, see Parameter groups for Amazon RDS.
   For RDS for MySQL, turn on slow_query and general_log parameters. Or, turn on audit logging through the MariaDB audit plugin in the Options group.
   For Aurora MySQL, turn on general_log, slow_query_log, or advanced auditing parameters. For more information, see Overview of Aurora MySQL database logs or Using Advanced Auditing with an Amazon Aurora MySQL DB cluster.
5. Review the DatabaseConnections metric in Amazon CloudWatch to confirm that the number of connections is close to or equal to the max_connections value. For more information, see Amazon CloudWatch metrics for Amazon RDS.

Remove connections on your DB instance

To remove user sessions or queries that currently run on your DB instance, use the mysql.rds_kill or mysql.rds_kill_query stored procedures. The mysql.rds_kill procedure terminates the entire connection to the MySQL server for the specified thread ID. The mysql.rds_kill_query procedure terminates only the running query for the specified thread ID, but keeps the connection intact.

To identify the thread ID, use the output from the SHOW FULL PROCESSLIST command in the preceding section. Look for threads with a Command value of Sleep or a high Time value.

Run the following commands as needed:

Note: Replace example-thread-ID with the thread ID from your SHOW FULL PROCESSLIST output.

CALL mysql.rds_kill(example-thread-ID);

CALL mysql.rds_kill_query(example-thread-ID);

For more information, see Ending a session or query.

Increase the maximum number of connections to your DB instance

To increase the maximum number of connections to your DB instance, use one of the following methods:

Scale the instance up to a DB instance class with more memory

To increase the maximum number of connections to your DB instance, scale your instance up to a DB instance class with more memory. Scaling your DB instance class causes an outage. For more information, see Modifying an Amazon RDS DB instance.

Increase the max_connections parameter value

To set a larger value for the max_connections parameter, use a custom instance-level parameter group. This change doesn't cause an outage. However, if you use a default parameter group, then an outage occurs when you change the parameter group from default to custom. For more information, see Parameter groups for Amazon RDS.

The max_connections parameter in the instance-level parameter group of the DB instance determines the maximum number of connections.

Example for RDS for MySQL:

{DBInstanceClassMemory/12582880}

Example for Aurora MySQL:

max_connections = GREATEST({log(DBInstanceClassMemory/805306368)*45},{log(DBInstanceClassMemory/8187281408)*1000})

To check the max_connections parameter value of your DB instance, run the following query:

select @@max_connections;

The max_connections parameter has the following specifications:

• For Aurora MySQL, you can set the max_connections parameter on both the DB cluster and DB instance parameter group. However, the instance-level parameter setting takes effect.
• The allowed value is an integer in the range of 1–16000.
• The parameter is dynamic. You don't need to reboot to change this parameter value.

For more information about Aurora MySQL, see Maximum connections to an Aurora MySQL DB instance.

For RDS for MySQL instances, the default value of max_connections depends on the memory available for the instance class. For more information, see How do I increase the max connections of my Amazon RDS for MySQL or Amazon RDS for PostgreSQL instance?

Tune the max_connections parameter

To tune the max_connections parameter, take the following actions:

• If you modify the settings for buffer pool and query cache, then also adjust your connection limits. This accounts for the increase or decrease in available memory on your DB instances.
• Set the max_connections parameter higher than the maximum number of connections that you plan to open on each DB instance.
• If you use Performance Schema, then leave your max_connections parameter at the default value. If you increase your max_connections parameter, then turn off Performance Schema. For more information, see Overview of the Performance Schema for Performance Insights on Amazon RDS for MariaDB or MySQL.
  Note: If you turn on Performance Schema for an Amazon RDS or Aurora MySQL instance, then the system automatically activates Performance Schema.

Check and adjust the following settings as required to further tune your Amazon RDS or Aurora MySQL connection parameters. To change these values, modify the parameters in your DB parameter group:

• The wait_timeout parameter sets the number of seconds the server waits for activity on a non-interactive TCP/IP or UNIX File connection before it closes the connection. The default value is 28800, which is 8 hours.
• The interactive_timeout parameter sets the number of seconds the server waits for activity on an interactive connection before it closes the connection. The default value is 28800, which is 8 hours.
• The net_read_timeout parameter sets the number of seconds to wait for more data from a TCP/IP connection before the server drops the read. The default value is 30.
• The net_write_timeout parameter sets the number of seconds to wait on TCP/IP connections for the server to write a block before it drops the write. The default value is 60.
• The max_execution_time parameter sets the execution timeout for SELECT statements, in milliseconds. The default value is 0, which means no timeout.
• The max_connect_errors parameter sets the number of interrupted connections after which the server blocks a host from further connections. The default value is 100.
• The max_user_connections parameter sets the maximum number of simultaneous connections for any given MySQL account. The default value is 0, which means no limit.

For more information, see Parameter groups for Amazon RDS.

Related information

Quotas and constraints for Amazon RDS

Managing connections