pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedconnections slowing everything down?
5+ messages / 3 participants
[nested] [flat]
* connections slowing everything down?
@ 2008-04-21 09:50 Adrian Moisey <adrian@careerjunction.co.za>
2008-04-21 14:04 ` Re: connections slowing everything down? Erik Jones <erik@myemma.com>
2008-04-23 00:46 ` Re: connections slowing everything down? Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Adrian Moisey @ 2008-04-21 09:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: connections slowing everything down?
2008-04-21 09:50 connections slowing everything down? Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-04-21 14:04 ` Erik Jones <erik@myemma.com>
2008-04-21 14:15 ` Re: connections slowing everything down? Adrian Moisey <adrian@careerjunction.co.za>
1 sibling, 1 reply; 5+ messages in thread
From: Erik Jones @ 2008-04-21 14:04 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: connections slowing everything down?
2008-04-21 09:50 connections slowing everything down? Adrian Moisey <adrian@careerjunction.co.za>
2008-04-21 14:04 ` Re: connections slowing everything down? Erik Jones <erik@myemma.com>
@ 2008-04-21 14:15 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-04-21 14:46 ` Re: connections slowing everything down? Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Moisey @ 2008-04-21 14:15 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: connections slowing everything down?
2008-04-21 09:50 connections slowing everything down? Adrian Moisey <adrian@careerjunction.co.za>
2008-04-21 14:04 ` Re: connections slowing everything down? Erik Jones <erik@myemma.com>
2008-04-21 14:15 ` Re: connections slowing everything down? Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-04-21 14:46 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 5+ messages in thread
From: Erik Jones @ 2008-04-21 14:46 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: connections slowing everything down?
2008-04-21 09:50 connections slowing everything down? Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-04-23 00:46 ` Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Merlin Moncure @ 2008-04-23 00:46 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-04-23 00:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-21 09:50 connections slowing everything down? Adrian Moisey <adrian@careerjunction.co.za>
2008-04-21 14:04 ` Erik Jones <erik@myemma.com>
2008-04-21 14:15 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-04-21 14:46 ` Erik Jones <erik@myemma.com>
2008-04-23 00:46 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox