Re: connections slowing everything down? - Mailing list pgsql-performance

On Apr 21, 2008, at 9:15 AM, Adrian Moisey wrote:

> Hi
>
>>> # ps -ef | grep idle | wc -l
>>> 87
> [...]
>
>>> I have 2 web servers which connect to PGPool which connects to our
>>> postgres db.  I have noticed that idle connections seem to take up
>>> CPU and RAM (according to top).  Could this in any way cause
>>> things to slow down?
>> Dependant on how much memory you have in your system, yes.  You can
>> fix the constant use of memory by idle connections by adjusting the
>> child_life_time setting in your pgpool.conf file.  The default if 5
>> minutes which a bit long.  Try dropping that down to 20 or 30
>> seconds.
>
> We have 32GBs.  If I get it to close the connections faster, will
> that actually help?  Is there a way i can figure it out?

First, sorry, I gave you the wrong config setting, I meant
connection_life_time.  child_life_time is the lifetime of an idle pool
process on the client machine and the connection_life_time is the
lifetime of an idle connection (i.e. no transaction running) on the
server.  With the default connection_life_time of 5 minutes it's
easily possible to keep an connection open indefinitely.  Imagine a
client gets a connection and runs a single query, then nothing happens
on that connection for 4:30 minutes at which point another single
query is run.  If that pattern continues that connection will never be
relinquished.  While the point of a pool is to cut down on the number
of connections that need to be established, you don't necessarily want
to go the extreme and never tear down connections as that will cause a
degradation in available server resources.  With a smaller, but not 0,
connection life time, connections will stay open and available during
periods of high work rates from the client, but will be relinquished
when there isn't as much to do.

Without more details on what exactly is happening on your system I
can't say for sure that this is your fix.  Are you tracking/monitoring
your server's free memory?  If not I'd suggest getting either Cacti or
Monit in place to monitor system stats such as free memory (using
vmstat), system IO (using iostat), db transaction rates (using db
queries).  Then you'll be able to draw correlations between
application behavior (slowness, etc) and actual system numbers.  I
know that I had issues with connections being held open for long times
(using the default 300s) causing our free memory to gradually decrease
over the day and resetting our pools would clear it out so there was a
direct cause and effect relationship there.  When I dropped the
connection_life_time to 30s the problem went away.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com