AWS RDS PostgreSQL error "remaining connection slots are reserved for non-replication superuser connections"

Question

In the dashboard I see there are currently 22 open connections to the DB instance, blocking new connections with the error:

remaining connection slots are reserved for non-replication superuser connections.

I'm accessing the DB from web service API running on EC2 instance and always keep the best practise of:

Connection connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
Class.forName(DB_CLASS);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(SQL_Query_String);
...
resultSet.close();
statement.close();
connection.close();

Can I do something else in the code?
Should I do something else in the DB management?
Is there a way to periodically close connections?

score 46 · Accepted Answer · edited Mar 20 '19 at 08:18

46

Amazon has to set the number of connections based on each model's right to demand a certain amount of memory and connections

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

But if you want you can change the max connection size to custom value by

From RDS Console > Parameter Groups > Edit Parameters,

You can change the value of the max_connections parameter to a custom value.

For closing the connections periodically you can setup a cron job some thing like this.

select pg_terminate_backend(procpid)
from pg_stat_activity
where usename = 'yourusername'
 and current_query = '<IDLE>'
 and query_start < current_timestamp - interval '5 minutes';

edited Mar 20 '19 at 08:18

Hearen

7,420
4
53
63

answered Jul 24 '16 at 19:16

Piyush Patil

14,512
6
35
54

1

Thanks, is there a way to periodically close connections? – michael Jul 24 '16 at 19:24
Thanks, by the way - how can I add cron job in AWS RDS? Thanks, – michael Jul 24 '16 at 20:25
Well you will have to run that from your local or some server. – Piyush Patil Jul 24 '16 at 20:26
I have EC2/Elastic beanstalk env. Is there a way to run from there? never coded cron job... Thanks, – michael Jul 24 '16 at 20:27
1

First you will have to make sure you can access the RDS from that instance. And setting up cron job is very easy just google running cron job for a script file you will find tons of documentation. – Piyush Patil Jul 24 '16 at 20:29
Where did this table of info come from? A reference link would be helpful. Some of it is out of date and there are more instance versions and types now, e.g. m5 – Davos Apr 01 '20 at 02:46
In the AWS console, RDS -> Parameter Groups -> I see the default postgres 11 setting is now `LEAST({DBInstanceClassMemory/9531392},5000)` so it caps it as 5000. Is it unwise to exceed the cap? – Davos Apr 01 '20 at 03:08
@error2007s "Error saving: Cannot modify a default parameter group. (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue" is the error I get when changing it – user1670816 Nov 08 '20 at 15:11
2

Here is the official source for # of connections : https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Performance.html – poitroae Jan 05 '21 at 10:40

score 12 · Answer 2 · answered Feb 06 '18 at 13:46

I'm using Amazon RDS, SCALA, Postgresql & Slick. First of all - number of available connections in RDS depends on the amount of available RAM - i.e. size of the RDS instance. It's best not to change the default conn number.

You can check the max connection number by executing the following SQL statement on your RDS DB instance:

show max_connections;

Check your SPRING configuration to see how many threads you're spawning:

database {
  dataSourceClass = org.postgresql.ds.PGSimpleDataSource
  properties = {
    url = "jdbc:postgresql://test.cb1111.us-east-2.rds.amazonaws.com:6666/dbtest"
    user = "youruser"
    password = "yourpass"
  }
  numThreads = 90
}

All of the connections ARE made upon SRING BOOT initialization so beware not to cross the RDS limit. That includes other services that connect to the DB. In this case the number of connections will be 90+.

The current limit for db.t2.small is 198 (4GB of RAM)

score 8 · Answer 3 · answered Dec 01 '20 at 09:06

You can change in the parameter group idle_in_transaction_session_timeout to remove idle connections.

idle_in_transaction_session_timeout (integer)

Terminate 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; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

The default value of 0 disables this feature.

The current value in AWS RDS is 86400000 which when converted to hours (86400000/1000/60/60) is 24 hours.

score 5 · Answer 4 · answered Jul 24 '16 at 19:12

5

you can change the max connections in the Parameters Group for your RDS instance. Try to increase it. Or you can try to upgrade your instance, as the max connexions is set to {DBInstanceClassMemory/31457280}

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

answered Jul 24 '16 at 19:12

Cédric Nilly

380
1
5

3

Thanks, is there a way to periodically close connections? – michael Jul 24 '16 at 19:24

AWS RDS PostgreSQL error "remaining connection slots are reserved for non-replication superuser connections"

4 Answers4