Postgresql – AWS RDS Postgres performance issue

amazon-web-servicesdjangopostgresqlpython

We are using rds(postgres) with instance type as db.m4.2xlarge.

Usually most of the time number of connections are 8-10. But on some occasion as the number of connections increases to 100-200. DB becomes non-responsive. DB non-responsiveness we have seen many time in cases of sudden spike in number or read connections(so even in cases where connections have increases from 10 to 100).

Queries which are being executed at max takes 2sec to execute.

My application server is running on django/python stack(with Gunicorn). Latency of these servers go high when DB server response time increases.

Any changes in configuration of postgres rds we should do to improve performance(currently most of the settings are default) ?

Best Answer

I was having the same issue. The postgresql is setup on AWS RDS and it was having 100% cpu utilisation even after increasing the instance. I debugged with the method shown here and one of the method worked for me.

I checked for the query running for the longest time and came to know that certain queries was stuck and was running since more than 3-4 hours. To check since how much time the query is running, run the following command:

SELECT max(now() - xact_start) FROM pg_stat_activity
                               WHERE state IN ('idle in transaction', 'active');

If this is more than an hour, than this is the issue. Kill the long running connection and limit the max age of the connection from application side.

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Postgresql – Django: CONN_MAX_AGE persists connections, but doesn’t reuse them with PostgreSQL

Doing some more experimenting, I have found that the cause of our problem was indeed gunicorn's eventlet worker class. Each microthread made it's own persistent connection, and there was no way at all to reuse any of them.

Disabling eventlet has made the load on our webservers go up (but not by much), but the postgres load is now down to an average of 3. From 30.

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Postgresql – Django: CONN_MAX_AGE persists connections, but doesn’t reuse them with PostgreSQL

Related Topic