I have many app servers using connection pooling. At any time, there are about 1000 total connection to the database from all the app servers; however, only few random connections are active.
The application often executes relatively big sorts. The work_mem size is 32MB, and eventually many sessions have a chance to run a sort and allocate a big sort area. I see hundreds of postgres processes with DATA segment > 15MB.
Eventually, it consumes all the available memory. Most of this memory is allocated to the sessions that are idle. I cannot change the connection pooling on the application side, and the big sorts cannot be eliminated. I need a solution on the DB side.
I see two ways to solve the problem.:
- Reducing the work_mem to something like 10MB will cap the total memory consumption. However, the memory will still be allocated to the mostly idle sessions. Also, reduced work_mem will cause increased disk I/O, which is already high.
- We can kill the idle sessions periodically. This will free up a big chunk of memory already allocated to the sessions. The application will gradually reestablish the connections, and the new sessions will start with small memory foot-print.
The question is, how safe it is to kill an idle session? If a session just became idle in pg_stats_activity, is it possible that it is still returning data to the client, or doing some other useful work?
In case it makes a difference: The Postgres version is 8.2.4 on Linux Suse 10.
Thank you.
