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. 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