Django/Postgres performance worsening after repeatedly processing the same query

Question

I am running Django on Apache. I have several client computers which should call urllib2.urlopen() and send over some data which my server will process and immediately send back a reply. However, when I am testing this I found a very tricky issue. I have one client repeatedly send the same data to be processed. The first time, it takes around ~20 seconds, second time, it takes about 40 seconds, third time I get a 504 (gateway timeout) error. If I try to send data some more 504 errors randomly pop up. I am pretty sure this is an issue with Postgres as the function that processes the information makes many database calls, however, I do not know why the performance of Postgres would decline so much. I have tried several database optimization tricks, including this one: (http://stackoverflow.com/questions/1125504/django-persistent-database-connection), to no avail.

Thanks in advance.

Edit: The requests are not coming concurrently. They are coming in back to back and each query involves a lot of SELECTs and JOINs, and there are a few INSERTs and UPDATEs as well. The apache error logs show that it is just a simple timeout, where the function to process the client posted data takes over 90 seconds.

Answer 1

If it's really Postgres, then you should turn on the logging of slow statements in the Postgres configuration to find out which statement exactly is taking so much time.

This can be done by setting the configuration property log_min_duration.

Details are in the manual: http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT

You say the function makes "many database calls" so I'd start with a very low number, or even 0 to log the duration of all statements, then you might be able to identify the slow ones.

It could also be a locking issued. Maybe the first call does not end its transaction properly and subsequent calls run into a timeout when waiting for a resource.

You can verify this by checking the system view pg_locks after the first call.

Answer 2

Have you checked the Apache error_logs? Have you set django DEBUG = True or ADMINS = ('email@addr.com',) so you can get a detailed error report about what the actual cause of the issue is? If so, how about pasting some information here.

Why are you certain that it's postgres? Have you done diagnostics to come to that conclusion? If so, please let us know.

Are you running apache with mod_wsgi? How many processes and threads have you allocated to your django application?

Also, 20 seconds to process the first transaction is a huge amount of time. Perhaps you could show us the view code that is causing the time out. We may be able to help there.

I sincerely doubt that it's going to be postgres alone that is causing the issue. It probably has something to do with application code, or server configuration.