Tom Lane wrote:
>"Matthew T. O'Connor" <matthew@zeut.net> writes:
>
>
>>hmm.... the value in reltuples should be accurate after a vacuum (or
>>vacuum analyze) if it's not it's a vacuum bug or something is going on
>>that isn't understood. If you or pg_autovacuum are running plain
>>analyze commands, that could explain the invalid reltules numbers.
>>
>>
>>Was reltuples = 113082 correct right after the vacuum?
>>
>>
>
>Another thing to check is whether the reltuples (and relpages!) that
>autovacuum is reporting are the same as what's actually in the pg_class
>row for the relation. I'm wondering if this could be a similar issue
>to the old autovac bug where it wasn't reading the value correctly.
>
>
I don't think so, as he did some manual selects from pg_class and
pg_stat_all in one of the emails he sent that were showing similar
numbers to what autovac was reporting.
>If they are the same then it seems like it must be a backend issue.
>
>One thing that is possibly relevant here is that in 8.0 a plain VACUUM
>doesn't set reltuples to the exactly correct number, but to an
>interpolated value that reflects our estimate of the "steady state"
>average between vacuums. I wonder if that code is wrong, or if it's
>operating as designed but is confusing autovac.
>
>
Ahh.... Now that you mention it, I do remember the discussion during
8.0 development. This sounds very much like the cause of the problem.
Autovac is not vacuuming often enough for this table because reltuples
is telling autovac that there are alot more tuples in this table than
there really are.
Really this is just another case of the more general problem with
autovac as it stands now. That is, you can't set vacuum thresholds on a
per table basis, and databases like this can't survive with a one size
fits all threshold. I would suggest that Otto perform regular cron
based vacuums of this one table in addition to autovac, that is what
several people I have heard from in the field are doing.
Come hell or high water I'm gonna get autovac integrated into 8.1, at
which point per table thresholds would be easy todo.
>Can autovac be told to run the vacuums in VERBOSE mode? It would be
>useful to compare what VERBOSE has to say to the changes in
>reltuples/relpages.
>
Not as it stands now. That would be an interesting feature for
debugging purposes though.
