Message-ID: <4C8FA663.9050409@emolecules.com>
Date: Tue, 14 Sep 2010 09:44:19 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.9) Gecko/20100825 Thunderbird/3.1.3
MIME-Version: 1.0
To: pgsql-performance@postgresql.org, dvlhntr@gmail.com
Subject: Re: Held idle connections vs use of a Pooler
References: <AANLkTi=MBWmUSQiLJ1jq5oPZafS-8VCuUKKPR8avjbMG@mail.gmail.com>
In-Reply-To: <AANLkTi=MBWmUSQiLJ1jq5oPZafS-8VCuUKKPR8avjbMG@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 9/14/10 9:10 AM, mark wrote:
> Hello,
>
> I am relatively new to postgres (just a few months) so apologies if
> any of you are bearing with me.
>
> I am trying to get a rough idea of the amount of bang for the buck I
> might see if I put in a connection pooling service into the enviroment
> vs our current methodology of using persistent open connections.
>
> We have a number of in house applications that connect to a central
> Postgres instance. (8.3.7). The box is admitting underpowered with
> only 8 cores, and 8gb or ram and not great disk IO out of an MSA-70.
> the database is about 35GB on disk and does mainly (~95%) OTLP type
> queries. I am currently begging for more ram.
>
> Most of the connections from the various apps hold idle connections
> until they need to execute a query once done go back to holding an
> open idle connection.  (there are ~600 open connections at any given
> time, and most of the time most are idle)
>
> this is typically fine while the number of active queries is low, but
> some other application (that doesn't use connection pooling or holding
> open connections when not in use) is hitting the db from time to time
> with 50-100 small queries (2ms queries from my testing) nearly all at
> once. when this happens the whole response time goes out the door
> however).

While connection pooling may be a good answer for you, there also appears to be a problem/bug in 8.3.x that may be biting you.  My installation is very similar to yours (hundreds of idle "lightweight" connections, occasional heavy use by certain apps).  Look at this thread:

   http://archives.postgresql.org/pgsql-performance/2010-04/msg00071.php

On the server that's been upgraded to 8.4.4, we're not seeing this problem.  But it's not in full production yet, so I can't say for sure that the CPU spikes are gone.

(Unfortunately, the archives.postgresql.org HTML formatting is horrible -- why on Earth can't it wrap lines?)

Craig

>
>
> I think from reading this list for a few weeks the answer is move to
> using connection pooling package elsewhere to better manage incoming
> connections, with a lower number to the db.
>
> I am told this will require some re-working of some app code as I
> understand pg-pool was tried a while back in our QA environment and
> server parts of various in-house apps/scripts/..etc started to
> experience show stopping problems.
>
> to help make my case to the devs and various managers I was wondering
> if someone could expand on what extra work is having to be done while
> queries run and there is a high (500-600) number of open yet idle
> connections to db. lots of the queries executed use sub-transactions
> if that makes a difference.
>
>
> basically what I am paying extra for with that many persistent
> connections, that I might save if I go to the effort of getting the
> in-house stuff to make use of a connection pooler ?
>
>
> thank you for your time.
>
> ..: mark
>