Horton

Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.

daveg <daveg@sonic.net>

View thread View on postgr.es
From: David Gould <daveg@sonic.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Janes <jeff.janes@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Alina Alexeeva <alexeeva@adobe.com>, Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Date: 2018-03-13T06:14:17Z
Lists: pgsql-hackers

Attachments

On Mon, 12 Mar 2018 10:43:36 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:


> Re-reading that thread, it seems like we should have applied Jeff's
> initial trivial patch[1] (to not hold   across
> table_recheck_autovac) rather than waiting around for a super duper
> improvement to get agreed on.  I'm a bit tempted to go do that;
> if nothing else, it seems simple enough to back-patch, unlike most
> of the rest of what was discussed.

This will help. In my testing it reduced the lock contention considerably. I
think a lot of users with lots of tables will benefit from it. However it does
nothing about the bigger issue which is that autovacuum flaps the stats temp
files.

I have attached the patch we are currently using. It applies to 9.6.8. I
have versions for older releases in 9.4, 9.5, 9.6. I fails to apply to 10,
and presumably head but I can update it if there is any interest.

The patch has three main features:
- Impose an ordering on the autovacuum workers worklist to avoid
  the need for rechecking statistics to skip already vacuumed tables.
- Reduce the frequency of statistics refreshes
- Remove the AutovacuumScheduleLock

The patch is aware of the shared relations fix. It is subject to the problem
Alvero noted in the original discussion: if the last table to be
autovacuumed is large new workers will exit instead of choosing an
earlier table. Not sure this is really a big problem in practice, but I agree
it is a corner case.

My patch does not do what I believe really needs doing:

 Schedule autovacuum more intelligently.

Currently autovacuum collects all the tables in the physical order of
pg_class and visits them one by one. With many tables it can take too long to
respond to urgent vacuum needs, eg heavily updated tables or wraparound.
There is a patch in the current commit fest that allows prioritizing tables
manually. I don't like that idea much, but it does recognize that the current
scheme is not adequate for databases with large numbers of tables.

-dg


-- 
David Gould                                   daveg@sonic.net
If simplicity worked, the world would be overrun with insects.

Commits

  1. Sync up our various ways of estimating pg_class.reltuples.

  2. Fix tuple counting in SP-GiST index build.

  3. Fix errors in contrib/bloom index build.

  4. When updating reltuples after ANALYZE, just extrapolate from our sample.

  5. Avoid holding AutovacuumScheduleLock while rechecking table statistics.