From: Florian Helmberger <fh@25th-floor.com>
To: pgsql-admin@postgresql.org
Subject: pg_class reltuples/relpages not updated by autovacuum/vacuum
Date: Tue, 17 May 2011 17:08:35 +0200
Message-ID: <4DD28F73.6010108@25th-floor.com> (raw)
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
---------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: fh@25th-floor.com
Subject: Re: pg_class reltuples/relpages not updated by autovacuum/vacuum
In-Reply-To: <4DD28F73.6010108@25th-floor.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox