Re: Autovacuum launcher patch

Hello,

This patch separates autovacuum in two families of processes: one is the
"launcher", in charge of examining statistics and deciding when to start
a worker. The other is the worker, which is started by the postmaster
under command of the launcher, and processes what the launcher tells it
to process (by way of setting info up in shared memory).

The postmaster treats workers as regular backends; they are listed in
the backend list, so when it wants to shut down, it'll send a SIGTERM
signal just like everyone else, meaning it'll Just Work(tm).

The launcher is a dummy process; it never connects to any database.
Right now, the scheduling is more or less the same as before: it'll only
start a single worker, which will process a whole database. Or rather,
all tables in it that are determined to need vacuuming, per the old
rules. Currently, the launcher first examines the last autovacuum time
to determine which database to vacuum; the worker then examines the
stats to determine which tables to vacuum. Eventually this will need to
be changed so that the launcher tells the worker exactly what table to
work on.

I've been wondering how to make the scheduling work in the future, when
we need to have the launcher read stuff from catalogs to configure the
scheduling ... Maybe the solution will be to store flatfiles based on
the catalogs, like we do for pg_database and pg_authid.

Comments are welcome.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> The launcher is a dummy process; it never connects to any database.
> ... Eventually this will need to
> be changed so that the launcher tells the worker exactly what table to
> work on.

I detect a certain lack of clarity of thinking here. Either the
launcher can read databases or it can't. Do you intend to solve the
problem of all the transaction/catcache infrastructure being designed
on the assumption of being in exactly one database?

I'd suggest sticking to something closer to the current two-phase design
where you make some preliminary decision which database to send a worker
to, and then the worker determines exactly what to do once it can look
around inside the DB. Possibly we need some back-signaling mechanism
whereby a worker can tell the launcher "hey boss, send help" if it sees
that there are enough tables that need work, but I'm unenthused about
having the launcher figure that out itself.

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > The launcher is a dummy process; it never connects to any database.
> > ... Eventually this will need to
> > be changed so that the launcher tells the worker exactly what table to
> > work on.
>
> I detect a certain lack of clarity of thinking here. Either the
> launcher can read databases or it can't. Do you intend to solve the
> problem of all the transaction/catcache infrastructure being designed
> on the assumption of being in exactly one database?

I had the same thought, but then I realized that most of the needed data
is actually stored in pgstat, so we don't need to connect to any
database to get it. (Hmm, except pg_class.reltuples).

What will probably live in databases will be the scheduling catalogs,
but I think I suggested that we could solve that problem by storing the
contents of those in plain text files, like pg_database.

I don't think this is a fundamental problem with the current patch
though. I've refrained from committing it mostly because I'd like
someone else to eyeball it just for safety, so I'll still allow for
several days to pass (unless there is a rush for getting it in ...)

> I'd suggest sticking to something closer to the current two-phase design
> where you make some preliminary decision which database to send a worker
> to, and then the worker determines exactly what to do once it can look
> around inside the DB. Possibly we need some back-signaling mechanism
> whereby a worker can tell the launcher "hey boss, send help" if it sees
> that there are enough tables that need work, but I'm unenthused about
> having the launcher figure that out itself.

Hmm, yeah, we'll probably need some communication channel eventually.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
>> I'd suggest sticking to something closer to the current two-phase design
>> where you make some preliminary decision which database to send a worker
>> to, and then the worker determines exactly what to do once it can look
>> around inside the DB. Possibly we need some back-signaling mechanism
>> whereby a worker can tell the launcher "hey boss, send help" if it sees
>> that there are enough tables that need work, but I'm unenthused about
>> having the launcher figure that out itself.
>
> Hmm, yeah, we'll probably need some communication channel eventually.

Maybe my IMessages code could be of use?

It's still awfully slow compared with UNIX pipes or even System V IPC
message queues, since it uses LWLocks for sending and retrieving
messages. That could certainly be optimized, maybe even towards a
lock-free implementation, which could theoretically be as fast as System
V IPC messages. OTOH, such stuff is hard to get right.

Regards

Markus

Markus Schiltknecht wrote:
> Alvaro Herrera wrote:
> >>I'd suggest sticking to something closer to the current two-phase design
> >>where you make some preliminary decision which database to send a worker
> >>to, and then the worker determines exactly what to do once it can look
> >>around inside the DB. Possibly we need some back-signaling mechanism
> >>whereby a worker can tell the launcher "hey boss, send help" if it sees
> >>that there are enough tables that need work, but I'm unenthused about
> >>having the launcher figure that out itself.
> >
> >Hmm, yeah, we'll probably need some communication channel eventually.
>
> Maybe my IMessages code could be of use?
>
> It's still awfully slow compared with UNIX pipes or even System V IPC
> message queues, since it uses LWLocks for sending and retrieving
> messages. That could certainly be optimized, maybe even towards a
> lock-free implementation, which could theoretically be as fast as System
> V IPC messages. OTOH, such stuff is hard to get right.

Hmm, I remember eyeballing that code. Would you mind sending me an URL
to that file, or something? Or maybe send me the files themselves?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
> Hmm, I remember eyeballing that code. Would you mind sending me an URL
> to that file, or something? Or maybe send me the files themselves?

Sure, here's a patch against current CVS. Please remove all the
functions referencing to "buffer" and "buffer.h" to compile.

Remember that it's a work in progress thing. It has flaws. One issue
that currently bugs me is, that processes can deadlock if they keep
trying to create a message (IMessagesCreate), but fail because the queue
is full of messages for themselves. A process should thus always try to
fetch messages (via IMessagesCheck) and remove pending ones before
retrying to send one. That's not always practical.

One design limitation is, that you have to know how large your message
is as soon as you reserve (shared) memory for it, but that's intended.

At least I've stress tested the wrap-around code and it seems to work.
No guarantees, though ;-)

Regards

Markus