From: Tom Lane <tgl@sss.pgh.pa.us>
To: Florian Helmberger <fh@25th-floor.com>
Cc: pgsql-hackers@postgreSQL.org, Alvaro Herrera <alvherre@commandprompt.com>
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Date: Wed, 25 May 2011 11:47:52 -0400
Message-ID: <19738.1306338472@sss.pgh.pa.us> (raw)
In-Reply-To: <4DDCA801.70805@25th-floor.com>
References: <4DD28F73.6010108@25th-floor.com>
<25017.1306291669@sss.pgh.pa.us>
<4DDCA801.70805@25th-floor.com>
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 tuples from 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 relpages either 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, fh@25th-floor.com, alvherre@commandprompt.com
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
In-Reply-To: <19738.1306338472@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox