Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5542564FCDF for ; Tue, 2 Sep 2008 02:32:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47740-04 for ; Tue, 2 Sep 2008 02:32:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 6E311650090 for ; Tue, 2 Sep 2008 02:32:09 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m825W3uS008023; Tue, 2 Sep 2008 01:32:03 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m825W20c008020; Tue, 2 Sep 2008 01:32:02 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Tue, 2 Sep 2008 01:32:01 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Duan Ligong cc: Alvaro Herrera , pgsql-performance@postgresql.org Subject: Re: too many clog files In-Reply-To: <20080902091602.CA09.DUANLG@nec-as.nec.com.cn> Message-ID: References: <20080901191255.E398.DUANLG@nec-as.nec.com.cn> <20080901143359.GC3776@alvh.no-ip.org> <20080902091602.CA09.DUANLG@nec-as.nec.com.cn> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed 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: 200809/22 X-Sequence-Number: 31335 On Tue, 2 Sep 2008, Duan Ligong wrote: > - Does Vacuum delete the old clog files? Yes, if those transactions are all done. One possibility here is that you've got some really long-running transaction floating around that is keeping normal clog cleanup from happening. Take a look at the output from "select * from pg_stat_activity" and see if there are any really old transactions floating around. > - Can we controll the maximum number of the clog files? The reference Alvaro suggested at http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND goes over that. If you reduce autovacuum_freeze_max_age, that reduces the expected maximum size of the clog files. "The default, 200 million transactions, translates to about 50MB of pg_clog storage." If you've got significantly more than 50MB of files in there, normally that means that either you're not running vacuum to clean things up usefully, or there's an old open transaction still floating around. > - When, or in what case is a new clog file produced? Every 32K transactions. See http://wiki.postgresql.org/wiki/Hint_Bits for some clarification about what the clog files actually do. I recently collected some notes from this list and from the source code README files to give a high-level view there of what actually goes on under the hood in this area. > - Is there a mechanism that the clog files are recycled? If there is , > what is the mechanism? The old ones should get wiped out by vacuum's freezing mechanism. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD