From: Craig James <craig_james@emolecules.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: Connection pooling for a mixture of lightweight and heavyweight jobs?
Date: Fri, 30 Jul 2010 10:09:44 -0700
Message-ID: <4C530758.7050804@emolecules.com> (raw)
In-Reply-To: <4C52B0240200002500033F10@gw.wicourts.gov>
References: <4C50ABA5.5010408@emolecules.com>
<4C52B0240200002500033F10@gw.wicourts.gov>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: craig_james@emolecules.com, Kevin.Grittner@wicourts.gov
Subject: Re: Connection pooling for a mixture of lightweight and heavyweight jobs?
In-Reply-To: <4C530758.7050804@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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