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

On 25.05.11 04:47, Tom Lane wrote:

> Florian Helmberger<fh(at)25th-floor(dot)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(at)25th-floor(dot)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
---------------------------------------