From: Florian Helmberger <fh@25th-floor.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_class reltuples/relpages not updated by autovacuum/vacuum
Date: Wed, 25 May 2011 08:56:01 +0200
Message-ID: <4DDCA801.70805@25th-floor.com> (raw)
In-Reply-To: <25017.1306291669@sss.pgh.pa.us>
References: <4DD28F73.6010108@25th-floor.com>
<25017.1306291669@sss.pgh.pa.us>
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
---------------------------------------
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, tgl@sss.pgh.pa.us
Subject: Re: pg_class reltuples/relpages not updated by autovacuum/vacuum
In-Reply-To: <4DDCA801.70805@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