Re: bg worker: general purpose requirements

On Mon, Sep 20, 2010 at 1:45 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> Hm.. I see. So in other words, you are saying
> min_spare_background_workers isn't flexible enough in case one has
> thousands of databases but only uses a few of them frequently.

Yes, I think that is true.

> I understand that reasoning and the wish to keep the number of GUCs as
> low as possible. I'll try to drop the min_spare_background_workers from
> the bgworker patches.

OK. At least for me, what is important is not only how many GUCs
there are but how likely they are to require tuning and how easy it
will be to know what the appropriate value is. It seems fairly easy
to tune the maximum number of background workers, and it doesn't seem
hard to tune an idle timeout, either. Both of those are pretty
straightforward trade-offs between, on the one hand, consuming more
system resources, and on the other hand, better throughput and/or
latency. On the other hand, the minimum number of workers to keep
around per-database seems hard to tune. If performance is bad, do I
raise it or lower it? And it's certainly not really a hard minimum
because it necessarily bumps up against the limit on overall number of
workers if the number of databases grows too large; one or the other
has to give.

I think we need to look for a way to eliminate the maximum number of
workers per database, too. Your previous point about not wanting one
database to gobble up all the available slots makes sense, but again,
it's not obvious how to set this sensibly. If 99% of your activity is
in one database, you might want to use all the slots for that
database, at least until there's something to do in some other
database. I feel like the right thing here is for the number of
workers for any given database to fluctuate in some natural way that
is based on the workload. If one database has all the activity, it
gets all the slots, at least until somebody else needs them. Of
course, you need to design the algorithm so as to avoid starvation...

> The rest of the bgworker infrastructure should behave pretty much like
> what you have described. Parallelism in starting bgworkers could be a
> nice improvement, especially if we kill the min_space_background_workers
> mechanism.

Works for me.

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