Thread: connections slowing everything down?

connections slowing everything down?

From
Adrian Moisey
Date:
Hi

# ps -ef | grep idle | wc -l
87
# ps -ef | grep SELECT | wc -l
5


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?

--
Adrian Moisey
Systems Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842

Re: connections slowing everything down?

From
Erik Jones
Date:
On Apr 21, 2008, at 4:50 AM, Adrian Moisey wrote:

> Hi
>
> # ps -ef | grep idle | wc -l
> 87
> # ps -ef | grep SELECT | wc -l
> 5
>
>
> 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.

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




Re: connections slowing everything down?

From
Adrian Moisey
Date:
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?


--
Adrian Moisey
Systems Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842

Re: connections slowing everything down?

From
Erik Jones
Date:
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




Re: connections slowing everything down?

From
"Merlin Moncure"
Date:
On Mon, Apr 21, 2008 at 5:50 AM, Adrian Moisey
<adrian@careerjunction.co.za> wrote:
> Hi
>
>  # ps -ef | grep idle | wc -l
>  87
>  # ps -ef | grep SELECT | wc -l
>  5
>
>
>  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?

Something is not quite with your assumptions. On an unloaded server,
open a bunch of connections (like 500) from psql doing nothing, and
cpu load will stay at zero. IOW, an 'idle' connection does not consume
any measurable CPU resources once connected.  It does consume some ram
but that would presumably at least partly swap out eventually.  What's
probably going on here is your connections are not really idle.  Top
by default aggregates usage every three seconds and ps is more of a
snapshot.  During the top a single connection might accept and dispose
0, 1, 50, 100, or 1000 queries depending on various factors.  Your
sampling methods are simply not accurate enough.

With statement level logging on (with pid on the log line),  you can
break out and measure query activity by connection.

merlin