Message-ID: <4C530758.7050804@emolecules.com>
Date: Fri, 30 Jul 2010 10:09:44 -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.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org
Subject: Re: Connection pooling for a mixture of lightweight	 and
	heavyweight jobs?
References: <4C50ABA5.5010408@emolecules.com>
	<4C52B0240200002500033F10@gw.wicourts.gov>
In-Reply-To: <4C52B0240200002500033F10@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 7/30/10 8:57 AM, Kevin Grittner wrote:
> Craig James<craig_james@emolecules.com>  wrote:
>
>> We create a bunch of high-performance lightweight Postgres clients
>> that serve up images (via mod_perl and Apache::DBI).  We have
>> roughly ten web sites, with ten mod_perl instances each, so we
>> always have around 100 Postgres backends sitting around all the
>> time waiting.  When a lightweight request comes in, it's a single
>> query on an primary key with no joins, so it's very fast.
>>
>> We also have a very heavyweight process (our primary search
>> technology) that can take many seconds, even minutes, to do a
>> search and generate a web page.
>>
>> The lightweight backends are mostly idle, but when a heavyweight
>> search finishes, it causes a burst on the lightweight backends,
>> which must be very fast. (They provide all of the images in the
>> results page.)
>>
>> This mixture seems to make it hard to configure Postgres with the
>> right amount of memory and such.  The primary query needs some
>> elbow room to do its work, but the lightweight queries all get the
>> same resources.
>>
>> I figured that having these lightweight Postgres backends sitting
>> around was harmless -- they allocate shared memory and other
>> resources, but they never use them, so what's the harm?  But
>> recent discussions about connection pooling seem to suggest
>> otherwise, that merely having 100 backends sitting around might be
>> a problem.
>
> Well, the "if it ain't broke, don't fix it" rule might come into
> play here.

I should have given one more detail here: We've been the victim of persistent "CPU spikes" that were discussed extensively in postgres-performance.  Tom suggested upgrading to 8.4.4, but that can't happen for a couple more months (we're working on it).

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

Craig

> The current configuration might leave you vulnerable to
> occasional less-than-optimal performance, if two or more heavyweight
> processes finish at the same time, and cause a "thundering herd" of
> lightweight processes.  Having the lightweight requests go through a
> connection pool could mitigate that problem, but they introduce
> their own overhead on every request.  So, I would advise keeping an
> eye out for problems which might match the above, but not to take
> hasty action in the absence of evidence.  You might buy back 400MB
> of RAM for caching (which you may or may not need) at the cost of
> extra latency and CPU per request.
>
> -Kevin
>