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
---------------------------------------