Re: bg worker: general purpose requirements

Hi,

On 09/16/2010 07:47 PM, Robert Haas wrote:
> It would be nice if there were a way to create
> a general facility here that we could then build various applications
> on, but I'm not sure whether that's the case.  We had some
> back-and-forth about what is best for replication vs. what is best for
> vacuum vs. what is best for parallel query.  If we could somehow
> conceive of a system that could serve all of those needs without
> introducing any more configuration complexity than what we have now,
> that would of course be very interesting.

Lets think about this again from a little distance. We have the existing 
autovacuum and the Postgres-R project. Then there are the potential 
features 'parallel querying' and 'autonomous transactions' that could in 
principle benefit from the bgworker infrastructure.

For all of those, one could head for a multi-threaded, a multi-process 
or an async, event based approach. Multi-threading seems to be out of 
question for Postgres. We don't have much of an async event framework 
anywhere, so at least for parallel querying that seems out of question 
as well. Only the 'autonomous transactions' feature seems simple enough 
to be doable within a single process. That approach would still miss the 
isolation that a separate process features (not sure that's required, 
but 'autonomous' sounds like it could be a good thing to have).

So assuming we use the multi-process approach provided by bgworkers for 
both potential features. What are the requirements?

autovacuum: only very few jobs at a time, not very resource intensive, 
not passing around lots of data

Postgres-R: lots of concurrent jobs, easily more than normal backends, 
depending on the amount of nodes in the cluster and read/write ratio, 
lots of data to be passed around

parallel querying: a couple dozen concurrent jobs (by number of CPUs or 
spindles available?), more doesn't help, lots of data to be passed around

autonomous transactions: max. one per normal backend (correct?), way 
fewer should suffice in most cases, only control data to be passed around


So, for both potential features as well as for autovacuum, a ratio of 
1:10 (or even less) for max_bgworkers:max_connections would suffice. 
Postgres-R clearly seems to be the out-breaker here. It needs special 
configuration anyway, so I'd have no problem with defaults that target 
the other use cases.

All of the potential users of bgworkers benefit from a pre-connected 
bgworker. Meaning having at least one spare bgworker around per database 
could be beneficial, potentially more depending on how often spike loads 
occur. As long as there are only few databases, it's easily possible to 
have at least one spare process around per database, but with thousands 
of databases, that might get prohibitively expensive (not sure where the 
boundary between win vs loose is, though. Idle backends vs. connection 
cost).

None the less, bgworkers would make the above features easier to 
implement, as they provide the controlled background worker process 
infrastructure, including job handling (and even queuing) in the 
coordinator process. Having spare workers available is not a perquisite 
to use bgworkers, it's just an optimization.

Autovacuum could possibly benefit from bgworkers by enabling a finer 
grained choice for what database and table to vacuum when. I didn't look 
too much into that, though.

Regarding the additional configuration overhead of the bgworkers patch: 
max_autovacuum_workers gets turned into max_background_workers, so the 
only additional GUCs currently are: min_spare_background_workers and 
max_spare_background_workers (sorry, I thought I named them idle 
workers, looks like I've gone with spare workers for the GUCs).

Those are used to control and limit (in both directions) the amount of 
spare workers (per database). It's the simplest possible variant I could 
think of. But I'm open to other mechanisms, especially ones that require 
less configuration. Simply keeping spare workers around for a given 
timeout *could* be a replacement and would save us one GUC.

However, I feel like this gives less control over how the bgworkers are 
used. For example, I'd prefer to be able to prevent the system from 
allocating all bgworkers to a single database at once. And as mentioned 
above, it also makes sense to pre-fork some bgworkers in advance, if 
there are still enough available. The timeout approach doesn't take care 
of that, but assumes that the past is a good indicator of use for the 
future.

Hope that sheds some more light on how bgworkers could be useful. Maybe 
I just need to describe the job handling features of the coordinator 
better as well? (Simon also requested better documentation...)

Regards

Markus Wanner