Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 73F50B5DBDC for ; Wed, 25 May 2011 03:56:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 88435-04 for ; Wed, 25 May 2011 06:56:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from zebrastreifen.25th-floor.com (zimbra.kmu-mail.at [78.142.162.221]) by mail.postgresql.org (Postfix) with ESMTP id 55A8EB5D801 for ; Wed, 25 May 2011 03:56:04 -0300 (ADT) Received: from lerchenferse.local (unknown [192.168.100.55]) by zebrastreifen.25th-floor.com (Postfix) with ESMTPSA id C2DE43B40009; Wed, 25 May 2011 08:56:01 +0200 (CEST) Message-ID: <4DDCA801.70805@25th-floor.com> Date: Wed, 25 May 2011 08:56:01 +0200 From: Florian Helmberger User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b3pre Thunderbird/3.1.10 MIME-Version: 1.0 To: Tom Lane CC: pgsql-admin@postgresql.org Subject: Re: pg_class reltuples/relpages not updated by autovacuum/vacuum References: <4DD28F73.6010108@25th-floor.com> <25017.1306291669@sss.pgh.pa.us> In-Reply-To: <25017.1306291669@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201105/165 X-Sequence-Number: 37602 On 25.05.11 04:47, Tom Lane wrote: > Florian Helmberger 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 ---------------------------------------