Re: connections slowing everything down?

On Mon, Apr 21, 2008 at 5:50 AM, Adrian Moisey
<adrian(at)careerjunction(dot)co(dot)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