Re: Pooling in Core WAS: Need help in performance tuning.

On 28/07/10 04:40, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Jul 22, 2010 at 5:29 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>>>> The problem is harder for us because a backend can't switch identities
>>>> once it's been assigned to a database. I haven't heard an adequate
>>>> explanation of why that couldn't be changed, though.
>
>>> Possibly it might decrease the performance significantly enough by
>>> reducing the cache locality (syscache, prepared plans)?
>
>> Those things are backend-local. The worst case scenario is you've got
>> to flush them all when you reinitialize, in which case you still save
>> the overhead of creating a new process.
>
> "Flushing them all" is not zero-cost; it's not too hard to believe that
> it could actually be slower than forking a clean new backend.
>
> What's much worse, it's not zero-bug. We've got little bitty caches
> all over the backend, including (no doubt) some caching behavior in
> third-party code that wouldn't get the word about whatever API you
> invented to deal with this.

In addition to caches, there may be some places where memory is just
expected to leak. Since it's a one-off allocation, nobody really cares;
why bother cleaning it up when it's quicker to just let the OS do it
when the backend terminates?

Being able to reset a backend for re-use would require that per-session
memory use be as neatly managed as per-query and per-transaction memory,
with no leaked stragglers left lying around.

Such cleanup (and management) has its own costs. Plus, you have a
potentially increasingly fragmented memory map to deal with the longer
the backend lives. Overall, there are plenty of advantages to letting
the OS clean it up.

... however, if the requirement is introduced that a given backend may
only be re-used for connections to the same database, lots of things get
simpler. You have to be able to change the current user (which would be
a bonus anyway), reset GUCs, etc, but how much else is there to do?

That way you can maintain per-database pools of idle workers (apache
prefork style) with ageing-out of backends that're unused. Wouldn't this
do the vast majority of what most pools are needed for anyway? And
wouldn't it potentially save quite a bit of load by avoiding having
backends constantly switching databases, flushing caches, etc?

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/