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

Resolution

View the current max_connections value

In Amazon RDS for MySQL, the max_connections metric monitors the set maximum number of (allowed) simultaneous client connections.

By default, the max_connections parameter is based on the following formula in Amazon RDS for MySQL (calculated from the DBInstanceClassMemory value):

max_connections = DBInstanceClassMemory/12582880

To check the current value of max_connections, run the following command after connecting to your Amazon RDS for MySQL instance:

SHOW GLOBAL VARIABLES LIKE 'max_connections';

In Amazon RDS for PostgreSQL, the max_connections metric monitors the set maximum number of concurrent connections. By default, the max_connections parameter is based on the following formula in Amazon RDS for PostgreSQL (calculated from the DBInstanceClassMemory value):

max_connections = LEAST({DBInstanceClassMemory/9531392}, 5000)

To check the current value for max_connections, run the following command after connecting to your Amazon RDS for PostgreSQL instance:

postgres=> show max_connections;

The default value of max_connections for both RDS for MySQL and RDS for PostgreSQL depends on the instance class used by the Amazon RDS instance. A DB instance class with more available memory supports a larger number of database connections.

Note that the default number of max_connections calculated using the formula might vary slightly from the number of default connections returned from the preceding command. This is because some of the memory out of the total DBInstanceClassMemory is reserved for the underlying OS operations. The preceding command considers only the memory that's reserved for the PostgreSQL engine and not for the underlying host OS.

Review reasons for too many connections

When the number of client connections exceeds the max_connections value, you get errors similar to the following:

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

The following factors might cause your database connections to exceed the max_connections value:

Increase in the number of client or application connections to the DB instance: This is caused by an increased workload or table/row-level locking.

Improperly closing a client or application connection after the end of an operation: When a server connection isn't properly 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 active 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;

Sleeping connections: Sleeping connections that are also known as inactive open connections are caused when you set higher values for connection timeout parameters, such as wait_timeout or interactive_timeout in MySQL. If you configure a connection limit that's very high, you might end up with higher memory usage even if those connections aren’t used. As a result, when the application server tries to open all client connections to the database, these connections might be refused. To terminate a sleeping connection in an RDS for MySQL DB instance, run the following command:

CALL mysql.rds_kill(example-pid);

Idle connections: You can view the idle connections in an RDS for PostgreSQL instance by running the following query. This query displays information about backend processes with one of the following states for more than 15 minutes: 'idle', 'idle in transaction', 'idle in transaction (aborted)' and 'disabled'.

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;

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

SELECT pg_terminate_backend(example-pid)

Tip: It's a best practice to configure only the active connections that are required for application performance. You might also consider upgrading to a larger Amazon RDS DB instance class.

Increase the max_connections value

You can increase the maximum number of connections to your RDS for MySQL or RDS for PostgresSQL DB instance using the following methods:

• Set a larger value for the max_connections parameter using a custom instance-level parameter group. Increasing the max_connections parameter doesn't cause any outage. Even though you can increase the value of max_connections beyond the default value, it's not a best practice. This is because, the instance might run into issues when workload increases and more memory is required. An increase in the number of connections can increase the memory usage. Instances running low on memory might crash. This is true especially for smaller instances. If you increase the max_connections value, be sure to monitor the usage of resources. Also, be sure to consult with your DBA about the increase. It's a best practice to keep the default value, or scale up to a larger instance class when more connections are required.
• If your DB instance is using a default parameter group, then change the parameter group to a custom parameter group. Be sure to associate the custom DB parameter group with your Amazon RDS instance and 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?
  Note: Changing the parameter group can cause an outage. For more information, see Working with DB parameter groups.
• Scale your DB instance up to a DB instance class with more memory. Note that scaling up RDS instances impacts the account’s billing. To learn more, see DB instance billing for Amazon RDS.
  Warning: Downtime occurs when you modify an Amazon RDS DB instance.

Follow best practices for configuring the max_connections parameter

Be sure to consider the following best practices 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. It's a best practice to scale up the instance class, instead of changing the instance class value. However, if your instances have a lot of free memory, then you can manually change this parameter. Before changing these settings for your DB Instance, consider adjusting the connection limit to account for the increase or decrease in available memory on the DB instances.
• Set the max_connections value to slightly higher than the maximum number of connections that you expect to open on each DB instance.
• For an RDS for MySQL instance, if you activated Performance Schema, then pay close attention to the max_connections parameter setting. The Performance Schema memory structures are sized automatically based on server configuration variables. The higher you set the variable, the more memory Performance Schema uses. In extreme cases, this condition might lead to out-of-memory issues on smaller instance types, such as T2 and T3. If you're using Performance Schema, then it's a best practice to leave the max_connections setting at the default value. If you plan to significantly increase the max_connections value (to higher than the default value), then consider disabling Performance Schema.
  Note: If you activate Performance Insights for an Amazon RDS for MySQL DB instance, then Performance Schema is automatically activated.
• For an RDS for MySQL instance, when you tune the max_connections parameter, be sure to review the following MySQL connection-related parameters: wait_timeout: Number of seconds the server waits for activity on a non-interactive TCP/IP or Unix File connection before closing the connection
  interactive_timeout: Number of seconds the server waits for activity on an interactive connection before closing the connection
  net_read_timeout: Number of seconds to wait for more data from a TCP/IP connection before dropping the read activity
  net_write_timeout: Number of seconds to wait on TCP/IP connections for a block to be written before dropping the write activity
  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
• For an RDS for PostgreSQL instance, when you tune the max_connections parameter, make sure to also review the following PostgreSQL connection-related parameters:
  idle_in_transaction_session_timeout: Terminates any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused. Also, the tuples visible only to this transaction are vacuumed.
  tcp_keepalives_idle: Number of seconds of inactivity after which the operating system sends TCP keepalive message to the client
  tcp_keepalives_interval: Number of seconds after which a TCP keepalive message that's not acknowledged by the client is transmitted again
  tcp_keepalives_count: Number of TCP keepalives that can be lost before the server's connection to the client is considered dead.

Note: This article doesn't include recommended values for the listed parameters because these values vary based on your use case.