Re: bg worker: general purpose requirements

On Fri, Sep 17, 2010 at 4:49 PM, Markus Wanner <markus@bluegap.ch> wrote:
>> If you're optimizing for
>> ability to respond quickly to a sudden load, keeping idle backends
>> will probably win even when the number of them you're keeping around
>> is fairly high.  If you're optimizing for minimal overall resource
>> consumption, though, you'll not be as happy about that.
>
> What resources are we talking about here? Are idle backends really that
> resource hungry? My feeling so far has been that idle processes are
> relatively cheap (i.e. some 100 idle processes shouldn't hurt on a modern
> server).

Wow, 100 processes??! Really?  I guess I don't actually know how large
modern proctables are, but on my MacOS X machine, for example, there
are only 75 processes showing up right now in "ps auxww".  My Fedora
12 machine has 97.  That's including a PostgreSQL instance in the
first case and an Apache instance in the second case.  So 100 workers
seems like a ton to me.

>> What I'm
>> struggling to understand is this: if there aren't any preforked
>> workers around when the load hits, how much does it slow things down?
>
> As the startup code is pretty much the same as for the current avlauncher,
> the coordinator can only request one bgworker at a time.
>
> This means the signal needs to reach the postmaster, which then forks a
> bgworker process. That new process starts up, connects to the requested
> database and then sends an imessage to the coordinator to register. Only
> after having received that registration, the coordinator can request another
> bgworker (note that this is a one-overall limitation, not per database).
>
> I haven't measured the actual time it takes, but given the use case of a
> connection pool, I so far thought it's obvious that this process takes too
> long.

Maybe that would be a worthwhile exercise...

> (It's exactly what apache pre-fork does, no? Is anybody concerned about the
> idle processes there? Or do they consume much less resources?)

I think the kicker here is the idea of having a certain number of
extra workers per database.  On my vanilla Apache server on the
above-mentioned Fedora 12 VM, there are a total of 10 processes
running.  I am sure that could balloon to 100 or more under load, but
it's not keeping around 100 processes on an otherwise idle system.  So
if you knew you only had 1 database, keeping around 2 or 3 or 5 or
even 10 workers might seem reasonable, but since you might have 1
database or 1000 databases, it doesn't.  Keeping 2 or 3 or 5 or 10
workers TOTAL around could be reasonable, but not per-database.  As
Tom said upthread, we don't want to assume that we're the only thing
running on the box and are therefore entitled to take up all the
available memory/disk/process slots/whatever.  And even if we DID feel
so entitled, there could be hundreds of databases, and it certainly
doesn't seem practical to keep 1000 workers around "just in case".

I don't know whether an idle Apache worker consumes more or less
memory than an idle PostgreSQL worker, but another difference between
the Apache case and the PostgreSQL case is that presumably all those
backend processes have attached shared memory and have ProcArray
slots.  We know that code doesn't scale terribly well, especially in
terms of taking snapshots, and that's one reason why high-volume
PostgreSQL installations pretty much require a connection pooler.  I
think the sizes of the connection pools I've seen recommended are
considerably smaller than 100, more like 2 * CPUs + spindles, or
something like that.  It seems like if you actually used all 100
workers at the same time performance might be pretty awful.

I was taking a look at the Mammoth Replicator code this week
(parenthetical note: I couldn't figure out where mcp_server was or how
to set it up) and it apparently has a limitation that only one
database in the cluster can be replicated.  I'm a little fuzzy on how
Mammoth works, but apparently this problem of scaling to large numbers
of databases is not unique to Postgres-R.

>> Is the ramp-up time longer than that,
>> or is even that much delay unacceptable for Postgres-R, or is there
>> some other aspect to the problem I'm failing to grasp?  I can tell you
>> have some experience tuning this so I'd like to try to understand
>> where you're coming from.
>
> I didn't ever compare to a max_spare_background_workers = 0 configuration,
> so I don't have any hard numbers, sorry.

Hmm, OK.

>> I think this is an interesting example, and worth some further
>> thought.  I guess I don't really understand how Postgres-R uses these
>> bgworkers.
>
> The given example doesn't only apply to Postgres-R. But with fewer bgworkers
> in total, you are more likely to want to use them all for one database, yes.
>
>> Are you replicating one transaction at a time, or how does
>> the data get sliced up?
>
> Yes, one transaction at a time. One transaction per backend (bgworker). On a
> cluster with n nodes that has only performs writing transactions, avg. at a
> rate of m concurrent transactions/node, you ideally end up having m normal
> backends and (n-1) * m bgworkers that concurrently apply the remote
> transactions.

What is the granularity of replication?  Per-database?  Per-table?
How do you accumulate the change sets?  Some kind of bespoke hook, WAL
scanning, ...?

> Thank you for your feedback and constructive criticism.

My pleasure.  Interesting stuff.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company