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

Resolution

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

Check the current max_connections value

The max_connections metric 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.

• RDS for MySQL:

  SHOW GLOBAL VARIABLES LIKE 'max_connections';

• RDS for PostgreSQL:

  postgres=> show max_connections;

Note: The default number of max_connections that you calculate with a formula might vary slightly from the output of the preceding commands. This variance is because Amazon RDS reserves some memory from the total DBInstanceClassMemory for underlying OS operations. The preceding commands use only the memory that's reserved for the PostgreSQL engine and not for the underlying host OS.

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 one of the following errors:

• MySQL: "Too many connections" error
• PostgreSQL: "FATAL: remaining connection slots are reserved for non replicate superuser connections"

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

Check that you closed all client and application connections after the end of an operation

When a server connection isn't closed, the client application opens up 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 INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Sleep';

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

CALL mysql.rds_kill(example-pid);

(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 terminate an idle connection in an RDS for PostgreSQL instance, run the following command:

SELECT pg_terminate_backend(example-pid);

Note: 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

Note: When you scale up an instance, you experience downtime and change how you're billed.

To increase the maximum connections, it's a best practice to scale up your DB instance to a DB instance class with more memory. It's not a best practice to 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 Amazon CloudWatch metric to monitor 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.

If your DB instance uses a default parameter group, then you must create and use a custom parameter group to modify max_connections. Associate the custom DB parameter group with your Amazon RDS instance, and then reboot the instance. After the new custom parameter group is associated with your DB instance, you can modify the max_connections parameter value. For more information, see How do I modify the values of an Amazon RDS DB parameter group. Set the max_connections value to slightly higher than the maximum number of connections that you expect to open on each DB instance.

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

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 memory structures are sized automatically based on server configuration variables.

Note: If you activate Performance Insights for an Amazon RDS for MySQL DB instance, then Performance Schema is automatically activated.

For your use case, 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 7.1.8 Server system variables on the MySQL website.

RDS for PostgreSQL best practices to increase max_connections

For your use case, optimize the timeout settings for the following PostgreSQL connection-related parameters:

• idle_in_transaction_session_timeout
• tcp_keepalives_idle
• tcp_keepalives_interval
• tcp_keepalives_count

For more information about these parameters, see idle_in_transaction_session_timeout (integer) and 19.3.2 TCP settings on the PostgreSQL website.