Why am I getting a "Too Many Connections" error when connecting to my Amazon Aurora MySQL instance?

Short description

If the client encounters a "Too Many Connections" error when you try to connect to an Amazon Aurora MySQL DB cluster or instance, this means that all available connections are in use by other clients. This is defined by the max_connections parameter.

You might see any of the following symptoms:

• The DatabaseConnections metric in Amazon CloudWatch is close to or equal to the max_connections value for your Aurora MySQL DB instance.
• The value for the max_connections parameter is higher than the available memory provisioned by the DB instance class for connections. Check for signs like a low FreeableMemory metric value in CloudWatch.
• You get an ERROR 1040(): Too many connections error in the MySQL error log.

You can reach a max_connections value for the following reasons:

• Sudden or gradual increase in the number of client/application connections to the DB instance. This has the following causes:
  • Increase in workload leading to increased connections.
  • Table/row level locking leading to an increase in the client/application connection.
• Client/application not closing connections properly after the end of the operation.
• Higher value for connection timeout parameters like wait_timeout and/or interactive_timeout that can lead to an increase in sleeping connections.

Before you resolve the max connection error, first view all threads that are currently running on your DB instance. Then, enable logging on your DB instance.

Show threads currently running on Aurora MySQL DB instance

The SHOW FULL PROCESSLIST command shows which threads are currently running on your DB instance. Log in to your DB instance, and then run the following query:

SHOW FULL PROCESSLIST\G

You can also run the following query to get the same result set:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

Note: You must grant your user account the administration privilege for the MySQL PROCESS server to see all the threads running on a MySQL DB instance. Otherwise, SHOW PROCESSLIST shows only the threads associated with the MySQL account that you're using. For more information, see the MySQL documentation for Privileges provided by MySQL.

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

Enable logging on Aurora MySQL DB instance

Enable logging on your Aurora MySQL DB instance by enabling general_log, slow_query_log or advanced auditing log parameters.

Resolution

Resolve the max connection error using one of the following methods:

• Review existing connections and, if possible, terminate them to release connection pressure. For example, start by terminating connections in sleep state.
• Increase the maximum number of connections to your DB instance.

Terminate existing connections on your DB instance

Terminate user sessions or queries currently running on your DB instance by running the rds_kill and rds_kill_query commands:

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

Increase the maximum connections to your DB instance

Increase the maximum number of connections to your DB instance using the following methods:

• Scale the instance up to a DB instance class with more memory. Note: Scaling the DB instance class causes an outage.
• Set a larger value for the max_connections parameter using a custom instance-level parameter group. Increasing the max_connections parameter doesn't cause an outage, but if your DB instance is using a default parameter group, then change the parameter group to a custom parameter group. Changing the parameter group causes an outage. For more information, see Working with DB parameter groups.

Note: The maximum number of connections allowed to an Aurora MySQL DB instance is determined by the max_connections parameter in the instance-level parameter group for the DB instance. See the following example:

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

Check the current value of the max_connections parameter for your DB instance. To do this, check the parameter group attached to your DB instance or by run the following query:

select @@max_connections;

The max_connections parameter has the following specifications:

• Can be set on both the DB cluster and DB instance parameter group. However, 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 on the default value of max_connections for each DB instance class available to Aurora MySQL, see Maximum connections to an Aurora MySQL DB instance.

Note: Aurora MySQL and Amazon Relational Database Service (Amazon RDS) MySQL DB instances have different amounts of memory overhead. The max_connections value can be different for Aurora MySQL and RDS MySQL DB instances that use the same instance class. The values listed apply only to Aurora MySQL.

Best practices for tuning the max_connections parameter

Make sure to consider the following when working with the max_connections parameter for your DB instance.

• The default connection limits are tuned for systems that use the default values for other major memory consumers, such as the buffer pool and query cache. If you change these settings for your DB cluster, consider adjusting the connection limit to account for the increase or decrease in available memory on the DB instances.
• Set max_connections slightly higher than the maximum number of connections you expect to open on each DB instance.
• If you also enabled performance_schema, be careful with the max_connections parameter setting. The Performance Schema memory structures are sized automatically based on server configuration variables, including max_connections. The higher you set the variable, the more memory Performance Schema uses. In extreme cases, this can lead to out-of-memory issues on smaller instance types, such as T2 and T3. It is a best practice to leave max_connections at the default value if you're using Performance Schema. If you plan to increase the max_connections to a value significantly higher than the default value, consider disabling Performance Schema. Note: If you enable Performance Insights for an Aurora MySQL DB instance, this automatically enables Performance Schema.

You can also consider the following MySQL connection parameters for tuning:

• 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.

Note: This article doesn't include recommended or custom values for these parameters, because these values vary based on individual use case.

---

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