Re: Connection pooling for a mixture of lightweight and heavyweight jobs?

On 7/30/10 8:57 AM, Kevin Grittner wrote:
> Craig James<craig_james(at)emolecules(dot)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
>