How do I increase the max connections of my Amazon RDS for MySQL or Amazon RDS for PostgreSQL instance?

Resolution

Note: It's a best practice to optimize your existing configurations before you increase the maximum number of connections.

Check the current max_connections value

The max_connections parameter sets the maximum number of database connections for both RDS for MySQL and RDS for PostgreSQL. The default value of max_connections depends on the instance class that the Amazon RDS instance uses. A DB instance class with more available memory can have a larger number of database connections.

To check the max_connections value, connect to the Amazon RDS DB instance for your DB engine and run the following command.

For RDS for MySQL:

SHOW GLOBAL VARIABLES LIKE 'max_connections';

For RDS for PostgreSQL:

postgres=> show max_connections;

Note: The default number of max_connections that you can calculate with the formula might vary slightly from the output of the preceding commands. Amazon RDS reserves some memory from the total DBInstanceClassMemory for underlying OS operations. The preceding commands use only the memory that the PostgreSQL engine reserves, not the memory for the underlying host OS.

Determine your required connection count

Before you change max_connections, determine how many connections your application requires. Use the DatabaseConnections CloudWatch metric to track peak connection usage over time. To identify your peak concurrent connections, review the metric over a period of 1–2 weeks.

Set the max_connections value about 10–20% higher than your observed peak. This buffer accounts for connection spikes without over-allocating memory.

Optimize your existing connections

Before you increase the value of max_connections, check whether you can reduce the number of existing connections. When the number of client connections exceeds the max_connections value, you receive an error. For MySQL, you receive the "Too many connections" error. For PostgreSQL, you receive the "FATAL: remaining connection slots are reserved for non-replication superuser connections" error.

These errors can occur when Amazon RDS has an increased workload or has table or row-level locking. If the workload on your instance works as expected, then you must increase the max_connections parameter.

Close unused client and application connections

When you don't close a server connection, the client application opens a new connection. Over time, these new server connections can cause your instance to exceed the max_connections value.

To list all connections for your RDS for MySQL DB instance, run the following command:

SHOW FULL PROCESSLIST;

To view the connections for each database for your RDS for PostgreSQL instance, run the following command:

SELECT datname, numbackends FROM pg_stat_database;

(MySQL only) Check for sleeping connections

Sleeping or inactive open connections occur when you set higher values for connection timeout parameters such as wait_timeout or interactive_timeout. If you configure a high connection quota, then memory usage might be high even if you aren't using those connections.

To view the idle connections in an RDS for MySQL instance, run the following query:

SELECT * FROM performance_schema.PROCESSLIST WHERE COMMAND='Sleep';

To stop a sleeping connection in an RDS for MySQL DB instance, run the following command:

CALL mysql.rds_kill(example-pid);

Note: Replace example-pid with the process ID of the connection you want to stop.

(PostgreSQL only) Check for idle connections

To view the idle connections in an RDS for PostgreSQL instance, run the following query:

SELECT * FROM pg_stat_activity WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE;

The query's output shows backend processes that are in an idle, idle in transaction, idle in transaction (aborted), or disabled state for over 15 minutes.

To stop an idle connection in an RDS for PostgreSQL instance, run the following command:

SELECT pg_terminate_backend(example-pid);

Note: Replace example-pid with the process ID of the connection you want to stop.

It's a best practice to configure only the active connections that you require for application performance. If your application structure requires idle connections, then it's a best practice to use Amazon RDS Proxy.

Increase the maximum number of connections

It's a best practice to scale up your DB instance to a DB instance class with more memory before you increase the maximum connections. Scaling up automatically increases the default max_connections value because the formula uses DBInstanceClassMemory. When you scale up an instance, you experience downtime and your billing changes.

Don't increase the max_connections parameter beyond the default value. The instance might encounter issues when more connections require higher memory usage. Instances that are low on memory might crash.

If you increase the max_connections value, then monitor the FreeableMemory and DatabaseConnections CloudWatch metrics for Amazon RDS to track your resource usage.

However, if your instances have a lot of free memory, then manually change the max_connections parameter. Before you adjust max_connections, adjust the connection limit in the parameter group to account for the changes in available memory on the DB instances.

To manually increase max_connections, complete the following steps:

1. If your DB instance uses a default parameter group, then create a custom parameter group.

   Note: You can't modify parameters in a default parameter group.

2. Associate the custom DB parameter group with your Amazon RDS instance.

3. To apply the new parameter group association, reboot your instance.

   Note: During the reboot, you experience a brief outage.

4. Modify the max_connections parameter value in the custom parameter group. For more information, see How do I modify the values of an Amazon RDS DB parameter group?
   Note: For RDS for MySQL, max_connections is a dynamic parameter, and the change takes effect without a reboot. For RDS for PostgreSQL, max_connections is a static parameter, and you must reboot the instance again for the change to take effect.

RDS for MySQL best practices to increase max_connections

If you activated Performance Schema, then it's a best practice to use the default max_connections setting. Performance Schema automatically sizes memory structures based on server configuration variables. If you activate Performance Insights for an Amazon RDS for MySQL DB instance, then Performance Insights automatically activates Performance Schema.

Optimize the timeout settings for the following MySQL connection-related parameters:

• wait_timeout
• interactive_timeout
• net_read_timeout
• net_write_timeout
• max_execution_time
• max_connect_errors
• max_user_connections

For more information about these parameters, see Server system variables on the MySQL website.

RDS for PostgreSQL best practices to increase max_connections

Optimize the timeout settings for the following PostgreSQL connection-related parameters:

• idle_in_transaction_session_timeout
• idle_session_timeout
• tcp_keepalives_idle
• tcp_keepalives_interval
• tcp_keepalives_count

For more information about these parameters, see idle_in_transaction_session_timeout, idle_session_timeout, and TCP settings on the PostgreSQL website.

Related information

Best practices for configuring parameters for Amazon RDS for MySQL, part 3: Parameters related to security, operational manageability, and connectivity timeout

Performance impact of idle PostgreSQL connections

Resources consumed by idle PostgreSQL connections

Working with parameters on your RDS for PostgreSQL DB instance