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

November 2, 2022 . 3 MIN READ

Before increasing the maximum number of database connections, it is recommended to first optimize your existing configuration. Increasing connection limits without optimization can lead to unnecessary memory usage and potential performance issues.

Check the Current `max_connections` Value

The max_connections parameter defines the maximum number of simultaneous database connections allowed for Amazon RDS for MySQL and Amazon RDS for PostgreSQL.

Its default value depends on the DB instance class, as instances with more available memory can support more connections.

You can check the current value by connecting to your RDS instance and running:

For RDS MySQL

SHOW GLOBAL VARIABLES LIKE ‘max_connections’;

For RDS PostgreSQL

SHOW max_connections;

Note: The calculated default value may slightly differ from the displayed result because Amazon RDS reserves a portion of memory for operating system processes, leaving the remaining memory for the database engine.

Optimize Existing Connections

Before increasing max_connections, determine whether the current number of connections can be reduced.

When the number of connections exceeds the configured limit, you may see these errors:

MySQL: Too many connections
PostgreSQL: FATAL: remaining connection slots are reserved for non-replication superuser connections

These errors often occur during high workloads or when table/row-level locking is present. If the workload is legitimate and cannot be optimized, then increasing max_connections may be necessary.

Ensure Connections Are Properly Closed

Applications should close connections once operations are complete. If connections remain open, new ones continue to accumulate and may eventually exceed the maximum connection limit.

To view active connections:

MySQL

SHOW FULL PROCESSLIST;

PostgreSQL

SELECT datname, numbackends FROM pg_stat_database;

Identify Idle or Sleeping Connections

MySQL: Sleeping Connections

Inactive connections can remain open when timeout values such as wait_timeout or interactive_timeout are set too high. Even if unused, these connections still consume memory.

To view sleeping connections:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND=‘Sleep’;

To terminate a sleeping connection:

CALL mysql.rds_kill(example–pid);

PostgreSQL: Idle Connections

You can identify idle backend processes with 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 minutes’;

To terminate an idle connection:

SELECT pg_terminate_backend(example–pid);

Best practice: Configure only the number of active connections needed for application performance. If your application requires many idle connections, consider using Amazon RDS Proxy for connection management.

Increasing the Maximum Number of Connections

Note: Scaling up a database instance to increase memory may cause temporary downtime and billing changes.

The recommended way to increase connection capacity is to upgrade to a DB instance class with more memory. Simply raising max_connections beyond the default value can lead to memory exhaustion and potential crashes.

If you increase max_connections, monitor the FreeableMemory metric in Amazon CloudWatch to ensure sufficient resources remain available.

If your instance has enough free memory, you can manually increase the parameter by modifying the DB parameter group.

Steps include:

Create a custom DB parameter group (if the instance uses the default group).
Modify the max_connections value in the custom parameter group.
Associate the parameter group with your DB instance.
Reboot the instance to apply the changes.

Note: A short outage occurs during the reboot process.

Set the max_connections value slightly above the expected maximum number of simultaneous connections.

Best Practices for RDS MySQL

If Performance Schema is enabled, it is recommended to keep the default max_connections value because the schema automatically allocates memory based on server configuration.

Note: Enabling Performance Insights automatically activates Performance Schema.

You should also optimize connection-related timeout settings, including:

wait_timeout
interactive_timeout
net_read_timeout
net_write_timeout
max_execution_time
max_connect_errors
max_user_connections

Best Practices for RDS PostgreSQL

For PostgreSQL instances, consider tuning the following parameters to better manage connections:

idle_in_transaction_session_timeout
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count

Adjusting these settings helps control idle sessions and improves overall connection management.

Reference:

https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-max-connections/

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