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