Re: autovacuum next steps, take 2

On Mon, Feb 26, 2007 at 10:18:36PM -0500, Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:
> >On Mon, Feb 26, 2007 at 06:23:22PM -0500, Matthew T. O'Connor wrote:
> >>I'm not sure how pg_class.relpages is maintained but what happens to a
> >>bloated table? For example, a 100 row table that is constantly updated
> >>and hasn't been vacuumed in a while (say the admin disabled autovacuum
> >>for a while), now that small 100 row table has 1000 pages in it most of
> >>which are just bloat, will we miss this table? Perhaps basing this on
> >>reltuples would be better?
> >
> >The entire point of this is to ensure that the second daemon will only
> >vacuum tables that it can finish very quickly. If you let a table bloat
> >so it's too big, then you just can't vacuum it very frequently without
> >risking all your other hot tables bloating because they're no longer
> >getting vacuumed.
> >
> >The reality is that you can actually vacuum a pretty good-sized table in
> >60 seconds with typical cost-delay settings (ie: defaults except
> >cost_delay set to 10). That means you can do 9 pages ~100 times a
> >second, or 54k pages a minute. Even with a vacuum_cost_delay of 20,
> >that's still 27k pages per minute.
>
> At the risk of sounding like a broken record, I still think the size
> limit threshold is unnecessary. Since all workers will be working in on
> tables in size order, younger workers will typically catch older workers
> fairly quickly since the tables will be either small, or recently
> vacuumed and not need work. And since younger workers exit when they
> catch-up to an older worker, there is some inherent stability in the
> number of workers.
>
> Here is a worst case example: A DB with 6 tables all of which are highly
> active and will need to be vacuumed constantly. While this is totally
> hypothetical, it is how I envision things working (without the threshold).

I fail to see how a simple 6 table case is 'worst case'. It's common to
see hundreds of tables, and I've run across more than one database with
thousands of tables (think partitioning). In cases like those it's
certainly possible, perhaps even likely that you would get many daemons
running in the database at one time just from different tables suddenly
needing vacuuming and appearing at a higher point in the list than other
tables. With 100 ~1G tables getting updates it certainly wouldn't be
hard to end up with 10 of those being vacuumed all at the same time.

I do like the idea since it should be easier to tune, but I think we
still need some limit on it. Perhaps as a first-pass we could just have
a hard limit and log a message and/or set a flag any time we hit it.
That would hopefully allow us to get information about how big a problem
it really is. We could go one step further and say that the last daemon
that can start in a database will only vacuum tables that can be done
quickly; that's essentially what we've been talking about, except the
limit we've been discussing would be hard-coded at 2.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)