Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum - Mailing list pgsql-hackers
From | Tom Lane |
---|---|
Subject | Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum |
Date | |
Msg-id | 19738.1306338472@sss.pgh.pa.us Whole thread Raw |
Responses |
Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum |
List | pgsql-hackers |
Florian Helmberger <fh@25th-floor.com> writes: > On 25.05.11 04:47, Tom Lane wrote: >> Florian Helmberger<fh@25th-floor.com> writes: >>> I'm running a production database with PostgreSQL 9.0.3 (64-bit) on >>> Debian 5.0.4 and have an issue with a TOAST table and far to frequent >>> autovacuum runs. >>> >>> I think I've pinned the problem down to the values pg_class holds for >>> the affected TOAST table: >>> >>> relpages | 433596 >>> reltuples | 1868538 >>> >>> These values are significantly too low. Interestingly, the autovacuum >>> logout reports the correct values: >>> >>> pages: 0 removed, 34788136 remain >>> tuples: 932487 removed, 69599038 remain >>> >>> but these aren't stored in pg_class after each run. >> That's exceedingly weird. Do the pg_stat_all_tables columns update >> after autovacuums on that table? > Yes they do: I think I see what must be going on here: that toast table must contain a long run of all-visible-according-to-the-VM pages (which is hardly a surprising situation). This results in VACUUM choosing not to update the pg_class entry: /* * Update statistics in pg_class. But only if we didn't skip any pages; * the tuple count only includes tuplesfrom the pages we've visited, and * we haven't frozen tuples in unvisited pages either. The page count is *accurate in any case, but because we use the reltuples / relpages ratio * in the planner, it's better to not update relpageseither if we can't * update reltuples. */ if (vacrelstats->scanned_all) vac_update_relstats(onerel, vacrelstats->rel_pages, vacrelstats->rel_tuples, vacrelstats->hasindex, FreezeLimit); For an ordinary table this wouldn't be fatal because we'll still do an ANALYZE from time to time, and that will update the entries with new (approximate) values. But we never run ANALYZE on toast tables. And this would *still* be okay, because as noted in the comment, the planner only depends on the ratio being roughly correct, not on either individual value being current. But autovacuum didn't get the memo; it thinks it can use reltuples to make decisions. I can see two basic approaches we might take here: 1. Modify autovacuum to use something from the stats collector, rather than reltuples, to make its decisions. I'm not too clear on why reltuples is being used there anyway; is there some good algorithmic or statistical reason why AV should be looking at a number from the last vacuum? 2. Revise the vacuum code so that it doesn't skip updating the pg_class entries. We could have it count the number of pages it skipped, rather than just keeping a bool, and then scale up the rel_tuples count to be approximately right by assuming the skipped pages have tuple density similar to the scanned ones. Thoughts? regards, tom lane
pgsql-hackers by date: