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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
