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=20=
=20
>>> postgres db. I have noticed that idle connections seem to take up=20=
=20
>>> CPU and RAM (according to top). Could this in any way cause=20=20
>>> things to slow down?
>> Dependant on how much memory you have in your system, yes. You can=20=
=20
>> fix the constant use of memory by idle connections by adjusting the=20=
=20
>> child_life_time setting in your pgpool.conf file. The default if 5=20=
=20
>> minutes which a bit long. Try dropping that down to 20 or 30=20=20
>> seconds.
>
> We have 32GBs. If I get it to close the connections faster, will=20=20
> that actually help? Is there a way i can figure it out?
First, sorry, I gave you the wrong config setting, I meant=20=20
connection_life_time. child_life_time is the lifetime of an idle pool=20=
=20
process on the client machine and the connection_life_time is the=20=20
lifetime of an idle connection (i.e. no transaction running) on the=20=20
server. With the default connection_life_time of 5 minutes it's=20=20
easily possible to keep an connection open indefinitely. Imagine a=20=20
client gets a connection and runs a single query, then nothing happens=20=
=20
on that connection for 4:30 minutes at which point another single=20=20
query is run. If that pattern continues that connection will never be=20=
=20
relinquished. While the point of a pool is to cut down on the
number=20=20
of connections that need to be established, you don't necessarily want=20=
=20
to go the extreme and never tear down connections as that will cause a=20=
=20
degradation in available server resources. With a smaller, but not 0,=20=
=20
connection life time, connections will stay open and available
during=20=20
periods of high work rates from the client, but will be relinquished=20=20
when there isn't as much to do.
Without more details on what exactly is happening on your system I=20=20
can't say for sure that this is your fix. Are you tracking/monitoring=20=
=20
your server's free memory? If not I'd suggest getting either Cacti or=20=
=20
Monit in place to monitor system stats such as free memory (using=20=20
vmstat), system IO (using iostat), db transaction rates (using db=20=20
queries). Then you'll be able to draw correlations between=20=20
application behavior (slowness, etc) and actual system numbers. I=20=20
know that I had issues with connections being held open for long times=20=
=20
(using the default 300s) causing our free memory to gradually decrease=20=
=20
over the day and resetting our pools would clear it out so there was a=20=
=20
direct cause and effect relation****p there. When I dropped the=20=20
connection_life_time to 30s the problem went away.
Erik Jones
DBA | Emma=AE
erik@[EMAIL PROTECTED]
or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
