Optimal / best pratice to maintain continuos connection between Python and Postgresql using Psycopg2

Question

Hi,

I'm writing an application in Python with Postgresql 8.3 which runs on several machines on a local network.

All machines

1) fetch huge amount of data from the database server ( lets say database gets 100 different queries from a machine with in 2 seconds time) and there are about 10 or 11 machines doing that.

2) After processing data machines have to update certain tables (about 3 or 4 update/insert queries per machine per 1.5 seconds).

What I have noticed is that database goes down some times by giving server aborted process abnormally or freezes the server machine (requiring a hard reset).

By the way all machines maintain a constant connection to the database at all times i.e. once a connection is made using Psycopg2 (in Python) it remains active until processing finishes (which could last hours).

What's the best / optimal way for handling large number of connections in the application, should they be destroyed after each query ?

Secondly should I increase max_connections ?

Would greatly appreciate any advice on this matter.

Answer 1

This sounds a bit like your DB server might have some problems, especially if your database server literally crashes. I'd start by trying to figure out from logs what is the root cause of the problems. It could be something like running out of memory, but it could also happen because of faulty hardware.

If you're opening all the connections at start and keep them open, max_connections isn't the culprit. The way you're handling the DB connections should be just fine and your server shouldn't do that no matter how it's configured.

Answer 2

The most likely cause indeed sounds like running out of memory. If these are Linux servers, triggering an out-of-memory condition invokes the "OOM-killer", which simply terminates the memory hog processes (hence "server aborted process abnormally"). A low-memory situation often means very high disk swapping/paging load, which makes the server seem unresponsive.

See your kernel log files (or the dmesg command) for anything resembling "Out of Memory: Killed process 1234 (postgres)". This is caused by the default that permits the kernel to overcommit memory. The first thing you should do is disable overcommit, to allow graceful handling of out-of-memory situations:

echo 2 > /proc/sys/vm/overcommit_memory

Plan A:

A likely culprit is the work_mem setting which specifies how much memory each individual operation can allocate. One query may consist of multiple memory-intensive steps, so each backend can allocate a few times the work_mem amount of memory, in addition to the global shared_buffers setting. In addition, you also need some free memory for operating system cache.

For more info see the PostgreSQL manual on resource consumption settings: PostgreSQL 8.3 Documentation, Resource Consumption

Plan B:

It might be that reducing these tunables slows your queries down so much that you will still get no work done. An alternative to this is artificially limiting the number of queries that can run in parallel. Many connection pooling middlewares for PostgreSQL can limit the number of parallel queries, and provide queueing instead. Examples of this software are pgbouncer (simpler) and pgpool-II (more flexible).

EDIT: Answering your questions:

What's the best / optimal way for handling large number of connections in the application, should they be destroyed after each query ?

In general, establishing new connections to PostgreSQL is not fast because PostgreSQL spawns a new process for each backend. However, processes are not cheap in terms of memory, so keeping many idle connections to the database is not a good idea.

The connection pooling middlewares I mentioned in Plan B will take care of keeping a reasonable number of connections to Postgres -- regardless of when or how often you connect or disconnect from the pooler. So if you choose that route, you don't need to worry about manually opening/closing connections.

Secondly should I increase max_connections ?

Unless your database server has large amounts of RAM (over 8GB) I would not go over the default limit of 100 connections.