jollygoodcode.github.io

jollygoodcode.github.io copied to clipboard

Do you use Amazon RDS for PostgreSQL?

First, what is max_connections?

Determines the maximum number of concurrent connections to the database server. max_connections on PostgreSQL Documentation

When running a standby server, you must set this parameter to the same or higher value than on the master server. Otherwise, queries will not be allowed in the standby server.

Do you know that, by default, each instance comes with a different number of max_connections? Grab a PSQL console to your Postgres database and check now with show max_connections;.

According to this answer on StackExchange (for MySQL), this is how it scales:

MODEL	max_connections
t1.micro	34
m1-small	125
m1-large	623
m1-xlarge	1263
m2-xlarge	1441
m2-2xlarge	2900
m2-4xlarge	5816

The numbers in the max_connections column looks slightly awkward because they are actually calculated from a formula DBInstanceClassMemory/magic_number where magic_number differs according to the class of your instance.

To know exactly what's the magic_number for an instance, head over to "Parameter Groups" in your Amazon console:

Click on the default Parameter Group and search for max_connections and you'll see the formula. In my case, it's {DBInstanceClassMemory/31457280}.

Fortunately, unlike Heroku Postgres where you can't change any of the Postgres configuration, you actually can modify Amazon RDS's configuration options!

This means, you can, e.g., increase the max_connections for a t1.micro instance from 34 to 100!

To do that, you can create a new Parameter Group:

And update the max_connections to 100:

Then, modify your existing instance's DB Parameter Group to use your new Parameter Group:

Save and restart your instance, and it should now have 100 connections.

Finally, remember to update your Rails app (database.yml) to make use of these 100 connections.

Resources:

• "Tune your Postgres RDS instance via parameter groups"
• "Should I increase max_connections in AWS RDS t1-micro for MySQL?"

Thanks for reading!

@winston :pencil2: Jolly Good Code

About Jolly Good Code

We specialise in Agile practices and Ruby, and we love contributing to open source. Speak to us about your next big idea, or check out our projects.

Hey! Thank you for this detailed post. Helped us quite a lot.

Helps a lot!

Thanks a lot!

Thanks a lot!

Hi!

Thank you for the suggestion, but in our case, the result is the following:

• We have RDS PostgreSQL 10 on t2.large.
• We have set the parameter max_connections to 20000.
• DB server is restarted after changing that parameter, and we are sure this parameter group is set for that instance.
• When querying the DB: show MAX_CONNECTIONS; returns the result of 1000 (not sure why).
• Anyway, when DB reaches 648 connections, it doesn't allow new connections anymore - everything goes down. 😱
• It has enough free memory, CPU utilization is below 25%.

Any ideas as to why it still won't go past the 648 limit?

Thanks so much!

@marisveide I don't have any information why 648 is the limit for you, but 1000 (or even 648) is way too big for Postgres, imo. Put a pgBouncer (in transactional mode) in front of your pg instance, and I bet you will be able to get away with 100-200 connections (this number is still very big, Postgres wiki says the formula is something like 3 x number_of_cores)

Hey, @ivanovv !

Thanks so much for your reply. We did exactly that - have put the PgBouncer infront of it, and now have just 20 connections to DB, instead of 800+.

Works like a charm! For already at least 6 months+, and has been very stable. 👍