Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 714C564FD2E for ; Wed, 3 Dec 2008 18:44:30 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 00277-03 for ; Wed, 3 Dec 2008 18:44:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by mail.postgresql.org (Postfix) with ESMTP id DCA2A6500BA for ; Wed, 3 Dec 2008 18:44:27 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id c7so1922869nfi.23 for ; Wed, 03 Dec 2008 14:44:25 -0800 (PST) Received: by 10.210.42.13 with SMTP id p13mr15945150ebp.10.1228343938723; Wed, 03 Dec 2008 14:38:58 -0800 (PST) Received: from oxford.xeocode.com.enterprisedb.com ([87.127.95.198]) by mx.google.com with ESMTPS id y34sm8676938iky.13.2008.12.03.14.38.56 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 03 Dec 2008 14:38:57 -0800 (PST) To: Heikki Linnakangas Cc: PostgreSQL-development , Tom Lane Subject: Re: Visibility map, partial vacuums In-Reply-To: <87bpvtqh2b.fsf@oxford.xeocode.com> (Gregory Stark's message of "Wed\, 03 Dec 2008 17\:55\:40 +0000") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:HACKERS" 50213) References: <4905AE17.7090305@enterprisedb.com> <491D376B.9000608@enterprisedb.com> <491D7F52.6070908@enterprisedb.com> <4925664C.3090605@enterprisedb.com> <26361.1227467112@sss.pgh.pa.us> <492A6032.6080000@enterprisedb.com> <18086.1227537479@sss.pgh.pa.us> <492D4460.1000809@enterprisedb.com> <5856.1227705135@sss.pgh.pa.us> <492EF88F.9050709@enterprisedb.com> <4936884B.6050205@enterprisedb.com> <87tz9lqs2a.fsf@oxford.xeocode.com> <4936B88E.5090108@enterprisedb.com> <87bpvtqh2b.fsf@oxford.xeocode.com> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 03 Dec 2008 22:38:53 +0000 Message-ID: <87myfcq3ya.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/183 X-Sequence-Number: 128812 Gregory Stark writes: > Heikki Linnakangas writes: > >> Gregory Stark wrote: >>> 1) Raise autovacuum_max_freeze_age to 400M or 800M. Having it at 200M just >>> means unnecessary full table vacuums long before they accomplish anything. >> >> It allows you to truncate clog. If I did my math right, 200M transactions >> amounts to ~50MB of clog. Perhaps we should still raise it, disk space is cheap >> after all. Hm, the more I think about it the more this bothers me. It's another subtle change from the current behaviour. Currently *every* vacuum tries to truncate the clog. So you're constantly trimming off a little bit. With the visibility map (assuming you fix it not to do full scans all the time) you can never truncate the clog just as you can never advance the relfrozenxid unless you do a special full-table vacuum. I think in practice most people had a read-only table somewhere in their database which prevented the clog from ever being truncated anyways, so perhaps this isn't such a big deal. But the bottom line is that the anti-wraparound vacuums are going to be a lot more important and much more visible now than they were in the past. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!