How do I resolve the "Too Many Connections" error I receive when connecting to my Amazon RDS for MySQL or Aurora MySQL-Compatible DB instance?

Short description

If you encounter a Too Many Connections error when you connect to your DB instance, then all available connections are already in use. This error is defined by the max_connections parameter in MySQL (Amazon RDS or Amazon Aurora).

The following are symptoms that might occur from this error:

• The DatabaseConnections metric in Amazon CloudWatch is close to or equal to the max_connections value for your DB instance.
• The max_connections parameter value is higher than the available memory provisioned by the DB instance class for connections. This causes a low FreeableMemory metric value in Amazon CloudWatch.
• Error 1040(): Too many connections in your MySQL error log is received.

Your max_connections value might be reached because of the following reasons:

• These is a sudden or gradual increase in the number of client or application connections to the DB instance. This is caused by either an increase in workload or table and row level locking.
• A client or application isn't closing connections properly at the end of an operation.
• There is an increase in sleeping connections 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:

• Remove existing connections on your DB instance
• Increase the maximum number of connections to your DB instance

Resolution

Prerequisites

Note: If you receive errors when running AWS Command Line Interface (AWS CLI) commands, confirm that you're running a recent version of the AWS CLI.

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

1.    Log in to your DB instance. Then, view the threads that are running on your MySQL instance. Run one of the following commands:

Note: The SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST statements require a mutex and might negatively affect performance.

SHOW FULL PROCESSLIST\G

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

Note: Make sure that your user account has the administration privilege for the MySQL PROCESS server. This allows you to see all the threads running on a MySQL DB instance. If you don't have the administration privilege, then only the threads associated with the MySQL account that you're using can be viewed. For more information, see Privileges Provided by MySQL on the MySQL website.

2.    Turn on logging on your DB instance to identify the connections and their source IP address:

• For Amazon 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 Aurora MySQL database log files or Using Advanced Auditing with an Aurora MySQL DB cluster.

Remove existing connections on your DB instance

To remove user sessions or queries that are currently running on your DB instance, run the following commands:

Note: Make sure that you review all commands and replace instances of example strings with your required values.

CALL mysql.rds_kill(example-thread-ID)

CALL mysql.rds_kill_query(example-thread-ID)

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.
• Increase the max_connections parameter value.

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. Note, however, that scaling your DB instance class causes an outage. For more information, see Changing your DB instance class.

Increase the max_connections parameter value

To set a larger value for the max_connections parameter, use a custom instance-level parameter group. Increasing this parameter 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 Working with DB parameter groups.

The maximum number of connections allowed to a MySQL instance is determined by the max_connections parameter in the instance-level parameter group of the DB instance.

Example for Amazon 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, check the parameter group attached to your DB instance:

select @@max_connections;

The max_connections parameter has the following specifications:

• For Aurora MySQL, the max_connections parameter can be set on both the DB cluster and DB instance parameter group. However, the instance-level parameter setting takes effect.
• Allowed value is an integer in the range of 1-16000.
• Dynamic in nature (no reboot is required to change this parameter value).

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

For Amazon RDS for MySQL instances, the default value of max_connections is based on the memory available as per 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?

Best practices for tuning the max_connections parameter

• If you modify the settings for buffer pool and query cache, then also adjust your connection limits. Adjust your connection limits to account 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, it's a best practice to leave your max_connections parameter at the default value. If you increase your max_connections parameter, it's a best practice to turn off Performance Schema.

Note: If you turn on Performance Schema for an Amazon RDS or Aurora MySQL instance, then Performance Schema is automatically turned on.

Check and adjust the following settings as required if you need to further tune your Amazon RDS or Aurora MySQL connection parameters:

• wait_timeout: Number of seconds the server waits for activity on a non-interactive TCP/IP or UNIX File connection before closing it.
• interactive_timeout: Number of seconds the server waits for activity on an interactive connection before closing it.
• net_read_timeout: Number of seconds to wait for more data from a TCP/IP connection before dropping the read.
• net_write_timeout: Number of seconds to wait on TCP/IP connections for a block to be written before dropping the write.
• max_execution_time: Execution timeout for SELECT statements, in milliseconds.
• max_connect_errors: A host is blocked from further connections if there are more than this number of interrupted connections.
• max_user_connections: Maximum number of simultaneous connections allowed to any given MySQL account.

Related information

Maximum connections to an Aurora MySQL DB instance

How do I check running queries for my Amazon RDS MySQL DB instance?

Ending a session or query