Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 214EA632307 for ; Thu, 15 Jan 2009 02:31:40 -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 02110-01-4 for ; Thu, 15 Jan 2009 02:31:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og113.obsmtp.com (exprod7og113.obsmtp.com [64.18.2.179]) by mail.postgresql.org (Postfix) with SMTP id 770A163236F for ; Thu, 15 Jan 2009 02:30:45 -0400 (AST) Received: from source ([74.125.78.145]) by exprod7ob113.postini.com ([64.18.6.12]) with SMTP ID DSNKSW7YE8M3lBNDihiz3e9Ck29wUg/PZhWN@postini.com; Wed, 14 Jan 2009 22:30:46 PST Received: by ey-out-1920.google.com with SMTP id 5so109945eyb.34 for ; Wed, 14 Jan 2009 22:30:42 -0800 (PST) Received: by 10.210.66.13 with SMTP id o13mr1188709eba.98.1232001042588; Wed, 14 Jan 2009 22:30:42 -0800 (PST) Received: from ?88.195.102.92? ([88.195.102.92]) by mx.google.com with ESMTPS id i3sm12336nfh.0.2009.01.14.22.30.40 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 14 Jan 2009 22:30:41 -0800 (PST) Message-ID: <496ED80A.2080804@enterprisedb.com> Date: Thu, 15 Jan 2009 08:30:34 +0200 Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.17 (X11/20081018) MIME-Version: 1.0 To: Gregory Stark CC: Bruce Momjian , PostgreSQL-development , Tom Lane Subject: Re: Visibility map, partial vacuums References: <200901150055.n0F0tLK27057@momjian.us> <87wscxfisu.fsf@oxford.xeocode.com> In-Reply-To: <87wscxfisu.fsf@oxford.xeocode.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit From: Heikki Linnakangas X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200901/1126 X-Sequence-Number: 131770 Gregory Stark wrote: > Bruce Momjian writes: > >> Would someone tell me why 'autovacuum_freeze_max_age' defaults to 200M >> when our wraparound limit is around 2B? > > I suggested raising it dramatically in the post you quote and Heikki pointed > it controls the maximum amount of space the clog will take. Raising it to, > say, 800M will mean up to 200MB of space which might be kind of annoying for a > small database. > > It would be nice if we could ensure the clog got trimmed frequently enough on > small databases that we could raise the max_age. It's really annoying to see > all these vacuums running 10x more often than necessary. Well, if it's a small database, you might as well just vacuum it. > The rest of the thread is visible at the bottom of: > > http://article.gmane.org/gmane.comp.db.postgresql.devel.general/107525 > >> Also, is anything being done about the concern about 'vacuum storm' >> explained below? > > I'm interested too. The additional "vacuum_freeze_table_age" (as I'm now calling it) setting I discussed in a later thread should alleviate that somewhat. When a table is autovacuumed, the whole table is scanned to freeze tuples if it's older than vacuum_freeze_table_age, and relfrozenxid is advanced. When different tables reach the autovacuum threshold at different times, they will also have their relfrozenxids set to different values. And in fact no anti-wraparound vacuum is needed. That doesn't help with read-only or insert-only tables, but that's not a new problem. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com