On Sat, Sep 7, 2013 at 12:26:23AM +0200, Andres Freund wrote:
> > So, what should trigger an auto-vacuum vacuum for these workloads?
> > Rather than activity, which is what normally drives autovacuum, it is
> > lack of activity that should drive it, combined with a high VM cleared
> > bit percentage.
> >
> > It seems we can use these statistics values:
> >
> > n_tup_ins | bigint
> > n_tup_upd | bigint
> > n_tup_del | bigint
> > n_tup_hot_upd | bigint
> > n_live_tup | bigint
> > n_dead_tup | bigint
> > n_mod_since_analyze | bigint
> > last_vacuum | timestamp with time zone
> > last_autovacuum | timestamp with time zone
> >
> > Particilarly last_vacuum and last_autovacuum can tell us the last time
> > of vacuum. If the n_tup_upd/n_tup_del counts are low, and the VM set
> > bit count is low, it might need vacuuming, though inserts into existing
> > pages would complicate that.
>
> I wonder if we shouldn't trigger most vacuums (not analyze!) via unset
> fsm bits. Perhaps combined with keeping track of RecentGlobalXmin to
Fsm bits? FSM tracks the free space on each page. How does that help?
> make sure we're not repeatedly checking for work that cannot yet be
> done.
The idea of using RecentGlobalXmin to see how much _work_ has happened
since the last vacuum is interesting, but it doesn't handle read-only
transactions; I am not sure how they can be tracked. You make a good
point that 5 minutes passing is meaningless --- you really want to know
how many transactions have completed. Unfortunately, our virtual
transactions make that hard to compute.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +