Re: pg_class reltuples/relpages not updated by autovacuum/vacuum

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?

Hi Tom,

Yes they do:

-[ RECORD 1 ]----+------------------------------
relid            | 16391
schemaname       | pg_toast
relname          | pg_toast_16386
seq_scan         | 0
seq_tup_read     | 0
idx_scan         | 298820512
idx_tup_fetch    | 1812697121
n_tup_ins        | 60907628
n_tup_upd        | 0
n_tup_del        | 56710637
n_tup_hot_upd    | 0
n_live_tup       | 4196999
n_dead_tup       | 20746580
last_vacuum      | 2011-05-21 06:33:49.869459+02
last_autovacuum  | 2011-05-15 18:40:49.746234+02
last_analyze     | NULL
last_autoanalyze | NULL

That was the last autovacuum run before I disabled it (via storage
parameter on the main table) and switched to manual vacuum's once per week.

I've also rechecked the "sister" database (same Hareware, OS/PostgreSQL
Version and database schema) which is working as intended.

Regards,
Florian

--

Florian Helmberger --------------------

25th-floor - Operating Custom Solutions
de Pretis & Helmberger KG

Gluckgasse 2/6, 1010 Wien, Austria

Mail: fh@25th-floor.com
Web : http://www.25th-floor.com
Tel.: +43 1 / 512 82 89 - 60
Fax : +43 1 / 512 82 89 - 76
Mob.: +43 699 / 109 24 24 5
---------------------------------------