AWS RDS connection limits

Question

RDS server come up with 40 connection max, as in the following documentation
I am using Magento 1.9, and at some points, i reach the max number then website is out of service.
Do you have any recommended way to solve this issue?

From my understanding, if i have 2 web servers connection to an RDS server.. then I should have 2 RDS connections, not more.

*"From my understanding, if i have 2 web servers connection to an RDS server.. then I should have 2 RDS connections, not more."* That's an incorrect understanding. It's typically 1 connection per worker process or thread. — Michael - sqlbot, Jul 16 '17 at 13:58
Thanks @Michael-sqlbot .. Yes i misunderstand this, but i wanted to make sure. But, why when we have 40 connection coming to RDS it refuses other connection and be unreachable till other connections are closed? — Alaa Badran, Jul 17 '17 at 05:39
That's by design, from the `max_connections` parameter. For most workloads, you should be able to safely increase it, because it's a safely conservative default value... but you'll want to investigate the cause of those spikes. — Michael - sqlbot, Jul 17 '17 at 08:46

Nick Tsai · Accepted Answer · 2017-07-17T08:04:25.843

110

AWS RDS max_connections limit variable is based on Instance type, so you can upgrade your RDS or make more replica.

The RDS types with max_connections limit:

t2.micro 66
t2.small 150
m3.medium 296
t2.medium 312
m3.large 609
t2.large 648
m4.large 648
m3.xlarge 1237
r3.large 1258
m4.xlarge 1320
m2.xlarge 1412
m3.2xlarge 2492
r3.xlarge 2540

Referring by max_connections at AWS RDS MySQL Instance Sizes in 2015

Update 2017-07

The current RDS MySQL max_connections setting is default by {DBInstanceClassMemory/12582880}, if you use t2.micro with 512MB RAM, the max_connections could be (512*1024*1024)/12582880 ~= 40, and so on.

Each Web server could have many connections to RDS, which depends on your SQL requests from Web server.

edited Jul 17 '17 at 08:04

answered Jul 16 '17 at 14:55

Nick Tsai

1,268
1
8
7

I know this, but when when reaching 40 connections, the server doesn't respond? Check this: https://drive.google.com/file/d/0B-_uggt0MBYOZElEMEItWDIwUEk/view?usp=sharing – Alaa Badran Jul 17 '17 at 05:45
@AlaaBadran What is your instance type of the RDS? – Nick Tsai Jul 17 '17 at 07:51
2

I check the RDS Parameter Groups, the max_connections is default by `{DBInstanceClassMemory/12582880}`, so when you use t2.micro with 512MB RAM, the max_connections could be `(512*1024*1024)/12582880 = 40.69` which could explain your 40 max connections. – Nick Tsai Jul 17 '17 at 07:59
We have r3.xlarge. – Alaa Badran Jul 17 '17 at 09:27
3

You could check the value by querying that RDS MySQL with command `show variables like 'max_connections';`. – Nick Tsai Jul 17 '17 at 14:00
t2.micro has 1GB of RAM, not 512MB. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html the following query will tell you your max connections: `SHOW max_connections;` – carlin.scott Oct 09 '17 at 17:05
Yes, I just gave a example. t2.micro may only has 512MB before I remembered. – Nick Tsai Oct 10 '17 at 01:38
3

You can create a new `parameter group` and change the value of `max_connections` to the value you want. This will be the new connection limit for your RDS instance. – backslashN Mar 26 '19 at 08:46
2

The RDS `max_connection` has changed to **DBInstanceClassMemory** / **9531392** For example - for `t2.medium` with 4GB ram. It's - 4*1024*1024*1024/9531392 = **450** – Gal Bracha Apr 15 '19 at 05:35
2

This answer holds up in 2019. You can always check yourself by doing `SHOW VARIABLES WHERE Variable_name='max_connections'` – Dave Stein Jun 04 '19 at 15:06

score 53 · Answer 2 · edited Apr 05 '19 at 16:44

53

You can change the max_connections value by either updating the default parameter policy or create a new one - I'd suggest going with the latter.

Go to RDS
Parameter Groups
Create a new Parameter Group (AWS wil leave everything as default)
search for the max_connections value
Change the value to use
Go to RDS instance and modify
Select new Parameter group created and restart the instance or let AWS reboot it during next maintenance window

Hope this helps!

edited Apr 05 '19 at 16:44

Community

1

answered Nov 01 '17 at 18:10

David

531
4
2

3

<3 this should be the accepted answer – bbozo Apr 05 '18 at 15:11
3

even though I set max_connections to 1000, rds still only allowed 100 max connections on a micro instance – Miguel Mota Apr 12 '18 at 19:14
@MiguelMota, that might be because of `mysqlx_max_connections` which is set to 100 and immutable. Correct me if I am wrong. – Burak Kaymakci Jun 26 '20 at 17:12

score 14 · Answer 3 · answered Sep 06 '19 at 09:51

Actual info for Postgresql t3-instances (default.postgres10 parameter group):

db.t3.micro - 112 max_connections
db.t3.small - 225 max_connections
db.t3.medium - 450 max_connections
db.t3.large - 901 max_connections
db.t3.xlarge - 1802 max_connections
db.t3.2xlarge - 3604 max_connections

Its similar for default.postgres9 and default.postgres11

score 8 · Answer 4 · answered Sep 04 '18 at 16:36

8

Login to your RDS instance (using a MySQL client) and run the following query:

SHOW VARIABLES LIKE 'max_connections';

answered Sep 04 '18 at 16:36

Shammi Shailaj

181
1
4

score 3 · Answer 5 · answered Apr 17 '20 at 09:12

The maximum number of simultaneous database connections varies by the DB engine type and the memory allocation for the DB instance class. The maximum number of connections is set in the parameter group associated with the DB instance, except for Microsoft SQL Server, where it is set in the server properties for the DB instance in SQL Server Managment Studio (SSMS).

MariaDB/MySQL {DBInstanceClassMemory/12582880}

Oracle LEAST({DBInstanceClassMemory/9868951}, 20000)

PostgreSQL LEAST({DBInstanceClassMemory/9531392}, 5000)

SQL Server 0 (unlimited)

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Limits.html#RDS_Limits.MaxConnections

score 2 · Answer 6 · answered Aug 21 '18 at 14:24

While increasing the max connections on the server config may fix the problem, you should consider verifying your application configurations and implementations.

I'm not an expert on Magento, but as I recently had a similar issue on a project I was working on, I noticed that the default implementation of the framework I was using created a connection with every call to the database.

While that may not cause any problems to some, the moment you have more visitors or some tasks that are database intensive and can run on multiple connections, the best way to prevent the server crashing with "TOO MANY CONNECTIONS" error is to implement a database connection pool.

This prevents the application from creating more connections that the server can handle, and not crashing the application to the users. A connection pool would keep a queue for the requests to access the database until a connection becomes available so it can proceed with processing the users request.

Just keep in mind that the connection pool should be thread safe in a multi thread scenario.

score 1 · Answer 7 · answered Nov 25 '21 at 09:56

Seems like ~45 connections / 2gb of Ram

I saw formulas, checked my config and it has way more complicated formula for max_connections by default and was having hard time to count what is real limit, so did in simple way - just tested. Monitoring dashboard shows red line when reaching close to the limit. Check below.

db.t3.small (2gb ram) Chart from monitoring console, as you can see it shows max connections ±46, that spike which crossed red line for a moment is 47

Upgrading now to 2x larger instance db.t3.medium (4gb ram), i expected to get extra 45 for each 2gb of ram and it seems it is true. Red line can be visible approaching ~90 connections with 4gb box:

score 1 · Answer 8 · answered Jul 16 '17 at 14:00

1

That's not 40 connections max, that is 40 RDS instances max. You are most likely only using 1 instance based on your description.

You can have thousands of connections to the RDS server because each time a session is opened with the database, that creates a new connection. You are probably running into performance constraints and should look into making the RDS instance larger.

answered Jul 16 '17 at 14:00

jason s

27
2

Thanks @jason I have the following information: When reaching 40 connection, RDS doesn't respond till other connections close. We have r3.xlarge RDS instance. Here is a screenshot of this: https://drive.google.com/file/d/0B-_uggt0MBYOZElEMEItWDIwUEk/view?usp=sharing We have idle CPU with high number of connections – Alaa Badran Jul 17 '17 at 05:41
This is connections, not other thing – Orlando Jan 06 '18 at 22:28

AWS RDS connection limits

8 Answers8

Update 2017-07

Linked