2

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) ?

4
  • 2 seconds is a really slow query for a web page load.
    – jordanm
    May 23, 2016 at 12:50
  • That is actually the max query time.. there are lot of queries which are getting executed in ms. But this should not cause rds slowdown(cpu utilization is going to 100%) in that rds instance type
    – Neo
    May 23, 2016 at 13:08
  • Not much we can help - way too little data. We need at least: what queries are slow (check select now()-query_start, query from pg_stat_activity where state<>'idle' order by 1), how large RDS disk size (small sizes are extremely slow), which non-default db parameters you use. You may also use pg_stat_statements extension to see worst queries from some time span etc.
    – Tometzky
    May 26, 2016 at 14:25
  • We analyzed our system more.. Issue is not with queries and nither with number of concurrent requests.. throughput was very high at that given point of time.. So we tweaked some postgres configurations and monitoring how it performs now.
    – Neo
    May 30, 2016 at 9:03

2 Answers 2

5

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.

1
  • In our case we were not even able to login to postgres shell due to load for running commands.. On more analysis we found n/w traffic load of aws was failing on high load times.. We replicated db for solution of same
    – Neo
    Oct 12, 2016 at 5:58
0

I would bet that you have one query which is particularly slow. When this runs the connections pile up behind it. I'd start by identifying the problematic query and then figure out how to fix it.

1
  • Dave, do you have any practical suggestions on how to do that? Is there some kind of query monitor, a slow query log you can turn on, etc? If so I suggest you update your post to include it.
    – Tim
    May 24, 2016 at 0:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .