Обсуждение: pg_class reltuples/relpages not updated by autovacuum/vacuum
Hi. 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. Currently, there are no long running transactions and/or dumps running. I've confirmed that PostgreSQL is using the values stored in pg_class for it's calculations, it starts autovacuum for the table at around 375k dead rows (threshold is 50, scale_factor 0.2 (both default)). Additionally I've done manual VACUUM ANALYZE of both the parent table and the TOAST table, which didn't help either. Other databases with the same hardware, PostgreSQL and OS versions don't have this issue. Currently I've worked around the issue by disabling autovacuum for the TOAST table and doing manual VACUUM ANALYZE once a week. Any clue how to get PostgreSQL to store the correct values? Side note: while trying to debug this I've noticed, that the TOAST chunks on 32-bit systems have the documented size of 2000 bytes, on 64-bit systems they have 1996 bytes. Is this normal/on purpose? Regards, Florian Helmberger -- 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 ---------------------------------------
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? regards, tom lane
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 ---------------------------------------
Florian Helmberger <fh@25th-floor.com> writes: > 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. I've moved discussion of this to pgsql-hackers, since this appears to be an actual bug. > Side note: while trying to debug this I've noticed, that the TOAST > chunks on 32-bit systems have the documented size of 2000 bytes, on > 64-bit systems they have 1996 bytes. Is this normal/on purpose? I don't have the exact numbers in my head, but the TOAST chunk size does depend on a MAXALIGN calculation, so it being different between 32- and 64-bit isn't surprising. regards, tom lane