Increase the max connections of my Amazon RDS for MySQL or PostgreSQL instance (2024)

I want to increase the max connections for my Amazon Relational Database Service (Amazon RDS) for MySQL or Amazon RDS for PostgreSQL DB 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_activityWHERE 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.

Increase the max connections of my Amazon RDS for MySQL or PostgreSQL instance (2024)

FAQs

How to increase AWS RDS connections? ›

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.

How do I increase the maximum number of connections in PostgreSQL? ›

Firstly, run the following command in a Postgres console: alter system set max_connections = 30; (or whatever number of connections you'd like). Next, you need to restart your Postgres server.

How many connections can RDS Postgres handle? ›

Note
DB engineParameterAllowed values
MariaDB and MySQLmax_connections1–100000
Oracleprocesses80–20000
Oraclesessions100–65535
PostgreSQLmax_connections6–8388607
1 more row

How to check max connections in Postgres? ›

How to Alter max_connections Parameter in PostgreSQL
  1. Step 1: Show Max Connections. Open the SQL Shell and type the following command to check the value of the max_connections parameter: SHOW MAX_CONNECTIONS; ...
  2. Step 2: Alter Max Connections. ...
  3. Step 3: Restart Postgres Server. ...
  4. Step 4: Verify Altered Connections.
Jun 30, 2023

How to check max connections in MySQL? ›

How to check number of existing Connections/Max Connections in MySQL
  1. select count(host) from information_schema.processlist;
  2. or. show status where variable_name = 'threads_connected';
  3. Run the following query in MySQL Work Bench:
  4. select @@max_connections.
Dec 17, 2020

What is max_user_connections in MySQL? ›

This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected. In addition, setting max_user_connections does not enable management of individual accounts. Both types of control are of interest to MySQL administrators.

How to improve RDS MySQL performance? ›

An Amazon RDS performance best practice is to allocate enough RAM so that your working set resides almost completely in memory. The working set is the data and indexes that are frequently in use on your instance. The more you use the DB instance, the more the working set will grow.

Can we increase max connections in MySQL? ›

The permitted number of connections is controlled by the max_connections system variable. To support more connections, set max_connections to a larger value.

How many connections can MySQL handle? ›

MySQL Connection Limits

At provision, Databases for MySQL sets the maximum number of connections to your MySQL database to 200. Raise this value by Changing the MySQL Configuration. Leave some connections available, as a number of them are reserved internally to maintain the state and integrity of your database.

How do I increase max connections in SQL Server? ›

In Object Explorer, right-click a server and select Properties. Select the Connections node. Under Connections, in the Max number of concurrent connections box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect simultaneously to the instance of SQL Server.

What is the maximum capacity of RDS MySQL? ›

Amazon RDS storage types

You can create Db2, MySQL, MariaDB, Oracle, SQL Server, and PostgreSQL RDS DB instances with up to 64 tebibytes (TiB) of storage.

Can Postgres handle multiple connections? ›

PostgreSQL uses a max_connections setting to limit the number of connections (and resources that are consumed by connections) to prevent run-away connection behavior from overwhelming your deployment's resources. You can check the value of max_connections with your admin user and psql .

How many active connections can Postgres handle? ›

The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.

How can I improve my RDS performance? ›

DB instance RAM recommendations

An Amazon RDS performance best practice is to allocate enough RAM so that your working set resides almost completely in memory. The working set is the data and indexes that are frequently in use on your instance. The more you use the DB instance, the more the working set will grow.

What is the maximum connection for RDS proxy? ›

Each proxy can have up to 200 associated Secrets Manager secrets. Thus, each proxy can connect to with up to 200 different user accounts at any given time. Each proxy has a default endpoint.

How to configure RDS high availability? ›

Configure high availability for the RD Connection Broker:
  1. In Server Manager, click Remote Desktop Services > Overview.
  2. Right-click RD Connection Broker, and then click Configure High Availability.
  3. Page through the wizard until you get to the Configuration type section.
Jul 3, 2024

Which feature of RDS can ensure high availability? ›

Amazon RDS Multi-AZ deployments provide enhanced availability and durability for your Amazon RDS database (DB) instances, making them a natural fit for production database workloads. With two different deployment options, you can customize your workloads for the availability they need.

References

Top Articles
Latest Posts
Article information

Author: Wyatt Volkman LLD

Last Updated:

Views: 5917

Rating: 4.6 / 5 (66 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Wyatt Volkman LLD

Birthday: 1992-02-16

Address: Suite 851 78549 Lubowitz Well, Wardside, TX 98080-8615

Phone: +67618977178100

Job: Manufacturing Director

Hobby: Running, Mountaineering, Inline skating, Writing, Baton twirling, Computer programming, Stone skipping

Introduction: My name is Wyatt Volkman LLD, I am a handsome, rich, comfortable, lively, zealous, graceful, gifted person who loves writing and wants to share my knowledge and understanding with you.