X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B5A7B9F9B01 for ; Thu, 25 May 2006 17:43:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 30421-05 for ; Thu, 25 May 2006 17:43:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from alvh.no-ip.org (201-221-200-231.bk11-dsl.surnet.cl [201.221.200.231]) by postgresql.org (Postfix) with ESMTP id 6973A9F94EA for ; Thu, 25 May 2006 17:43:22 -0300 (ADT) Received: by alvh.no-ip.org (Postfix, from userid 1000) id CA3B6C37378; Thu, 25 May 2006 16:43:19 -0400 (CLT) Date: Thu, 25 May 2006 16:43:19 -0400 From: Alvaro Herrera To: Tom Lane Cc: Benjamin Krajmalnik , pgsql-admin@postgresql.org, "Matthew T. O'Connor" Subject: Re: pg_clog questions Message-ID: <20060525204319.GL13700@surnet.cl> Mail-Followup-To: Tom Lane , Benjamin Krajmalnik , pgsql-admin@postgresql.org, "Matthew T. O'Connor" References: <8511B4970E0D124898E973DF496F9B432516E3@stash.stackdump.local> <15596.1148588444@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <15596.1148588444@sss.pgh.pa.us> User-Agent: Mutt/1.5.11+cvs20060403 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/366 X-Sequence-Number: 21898 Tom Lane wrote: > "Benjamin Krajmalnik" writes: > >>> While checking our server, I noticed quite a few files in the pg_clog > >>> directory. > >>> Is there a maintenance task which can be run to purge the files which > >>> are no longer needed? > > >> VACUUM. If they're not disappearing, you aren't running an adequate > >> vacuum regime --- missing out some databases, perhaps? > > > I have autovacuum turned on with the default settings. > > Hm. I believe that autovac only does database-wide vacuums when it > thinks they're necessary to prevent transaction wraparound failures. > Which would mean that it'd let pg_clog grow to something on the order > of half a gig before any truncation would happen. That's probably > insufficiently aggressive :-( > > Alvaro, Matthew, any thoughts about improving that? Hum, IIRC there is a test somewhere to check pg_database.datvacuumxid and issue a database-wide vacuum if it gets too old, but the test uses a-little-less-than-2-billion to fire :-( This is the code: /* * We look for the database that most urgently needs a database-wide * vacuum. We decide that a database-wide vacuum is needed 100000 * transactions sooner than vacuum.c's vac_truncate_clog() would * decide to start giving warnings. If any such db is found, we * ignore all other dbs. * * Unlike vacuum.c, we also look at vacuumxid. This is so that * pg_clog can be kept trimmed to a reasonable size. */ freeze_age = (int32) (nextXid - tmp->frozenxid); vacuum_age = (int32) (nextXid - tmp->vacuumxid); tmp->age = Max(freeze_age, vacuum_age); this_whole_db = (tmp->age > (int32) ((MaxTransactionId >> 3) * 3 - 100000)); Maybe it was foolish to use such a large constant in the vacuumxid case :-( > It strikes me that Alvaro's work-in-progress on maintaining per-table > xmin info would allow truncation of clog without actually insisting on > a database-wide VACUUM, but that's not going to be any help for > existing releases. Yeah. The patch is almost ready BTW and I was about to post it on Saturday but I got distracted. I have a problem with sinval messages :-( I'll post it later today. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support