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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
