new autovacuum criterion for visible pages

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: new autovacuum criterion for visible pages
Date: 2016-08-10 20:39:00
Message-ID: CAMkU=1zGu5OshfzxKBqDmxxKcoDJu4pJux8UAo5h7k+GA_jS3Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wanted to create a new relopt named something like
autovacuum_vacuum_pagevisible_factor which would cause autovacuum to
vacuum a table once less than a certain fraction of the relation's
pages are marked allvisible.

I wanted some feedback on some things.

1) One issue is that pg_class.relpages and pg_class.relallvisible are
themselves only updated by vacuum/analyze. In the absence of manual
vacuum or analyze, this means that if the new criterion uses those
field, it could only kick in after an autoanalyze has already been
done, which means that autovacuum_vacuum_pagevisible_factor could not
meaningfully be set lower than autovacuum_analyze_scale_factor.

Should relallvisible be moved/copied from pg_class to
pg_stat_all_tables, so that it is maintained by the stats collector?
Or should the autovacuum worker just walk the vm of every table with a
defined autovacuum_vacuum_pagevisible_factor each time it is launched
to get an up-to-date count that way?

2) Should there be a guc in addition to the relopt? I can't think of
a reason why I would want to set this globally, so I'm happy with just
a relopt. If it were set globally, it would sure increase the cost
for scanning the vm for each table once each naptime.

3) Should there be a autovacuum_vacuum_pagevisible_threshold? The
other settings have both a factor and a threshold. I've never
understand what the point of the threshold settings is, but presumably
there is a point to them. Does that reason also apply to keeping vm
tuned up?

Cheers,

Jeff

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Palle Girgensohn 2016-08-10 20:42:01 Improved ICU patch - WAS: Implementing full UTF-8 support (aka supporting 0x00)
Previous Message Vladimir Sitnikov 2016-08-10 20:37:28 Re: Slowness of extended protocol