From scott@journyx.com Wed Mar 9 19:57:44 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E773B1337B93 for ; Wed, 9 Mar 2011 15:57:44 -0400 (AST) 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 24509-02 for ; Wed, 9 Mar 2011 19:57:35 +0000 (UTC) X-Greylist: delayed 00:07:32.397534 by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id 68BE11337B82 for ; Wed, 9 Mar 2011 15:57:34 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id 07570C4003 for ; Wed, 9 Mar 2011 13:50:01 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id zVrGlRanuqbD for ; Wed, 9 Mar 2011 13:49:56 -0600 (CST) Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5]) by mail.int.journyx.com (Postfix) with ESMTP id 77305CC00D for ; Wed, 9 Mar 2011 13:49:56 -0600 (CST) Date: Wed, 9 Mar 2011 13:49:56 -0600 (CST) From: Scott Whitney Reply-To: Scott Whitney To: pgsql-admin@postgresql.org Message-ID: <4505952.13818.1299700196217.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <12906437.13813.1299700160671.JavaMail.root@zimbra.int.journyx.com> Subject: pg_clogs hanging around MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13817_10064401.1299700196214" X-Originating-IP: [192.168.100.188] X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0 (Win)/6.0.6_GA_2331.RHEL4) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/104 X-Sequence-Number: 36976 ------=_Part_13817_10064401.1299700196214 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit I had this issue back in pg 7.x, and it was resolved by using -a in vacuumdb. I'm having it again in v8.4.4. So, my pg_clog directory contains files going back to Jul 13 of 2010. Every Saturday, I run: "vacuumdb -a -v -F" I _thought_ that was supposed to clear those out. Am I wrong? ------=_Part_13817_10064401.1299700196214 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit
I had this issue back in pg 7.x, and it was resolved by using -a in vacuumdb.

I'm having it again in v8.4.4.

So, my pg_clog directory contains files going back to Jul 13 of 2010.

Every Saturday, I run:

"vacuumdb -a -v -F"


I _thought_ that was supposed to clear those out. Am I wrong?


------=_Part_13817_10064401.1299700196214-- From Kevin.Grittner@wicourts.gov Wed Mar 9 20:36:17 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 08ACE1337B82 for ; Wed, 9 Mar 2011 16:36:17 -0400 (AST) 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 33526-04 for ; Wed, 9 Mar 2011 20:36:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 31F9E1337B9C for ; Wed, 9 Mar 2011 16:36:06 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Wed, 09 Mar 2011 14:36:05 -0600 Message-Id: <4D77904C020000250003B703@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Wed, 09 Mar 2011 14:35:56 -0600 From: "Kevin Grittner" To: "Scott Whitney" , Subject: Re: pg_clogs hanging around References: <12906437.13813.1299700160671.JavaMail.root@zimbra.int.journyx.com> <4505952.13818.1299700196217.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <4505952.13818.1299700196217.JavaMail.root@zimbra.int.journyx.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/105 X-Sequence-Number: 36977 Scott Whitney wrote: > my pg_clog directory contains files going back to Jul 13 of 2010. > > Every Saturday, I run: > > "vacuumdb -a -v -F" > > > I _thought_ that was supposed to clear those out. Am I wrong? SELECT datname, datfrozenxid FROM pg_database; to see which database is causing them to be retained. -Kevin From scott@journyx.com Thu Mar 10 13:01:25 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D2AA61337B97 for ; Thu, 10 Mar 2011 09:01:25 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 75248-10 for ; Thu, 10 Mar 2011 13:01:18 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id 3EAC51337B82 for ; Thu, 10 Mar 2011 09:01:18 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id A1020C4047 for ; Thu, 10 Mar 2011 07:01:15 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id pt5mOaGchXzQ for ; Thu, 10 Mar 2011 07:01:11 -0600 (CST) Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5]) by mail.int.journyx.com (Postfix) with ESMTP id 1A2E6C4041 for ; Thu, 10 Mar 2011 07:01:11 -0600 (CST) Date: Thu, 10 Mar 2011 07:01:10 -0600 (CST) From: Scott Whitney Reply-To: Scott Whitney To: pgsql-admin@postgresql.org Message-ID: <11997393.14072.1299762070874.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <358079.14070.1299761935620.JavaMail.root@zimbra.int.journyx.com> Subject: Re: pg_clogs hanging around MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.100.188] X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0 (Win)/6.0.6_GA_2331.RHEL4) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/111 X-Sequence-Number: 36983 Ooops...I accidentally took this off list, as Kevin was nice enough to point out. >> What am I looking for? >Outliers. > Yeah. It's just those 2. I'd assume that the db I created > yesterday would be an outlier, but template0 has been there all along > (of course) and is still listed as 648, a significantly smaller number. >> The output shows me 345 rows, most of which are 132xxxxx numbers. >> Two of them (template0 and a database created yesterday) say 648. >The template0 database is what's keeping the clog files from being >cleaned up, but I guess the big question is why you care. They will >go away eventually, and shouldn't affect performance. Are they >taking enough space to merit extraordinary effort to clean them up? > -Kevin My concern is that when we had a failure a few years ago, and one of the clog files went bad. I had to manually recreate some customer data after bringing up the previous backup. So, I'd rather have them not there, because, well, if there are 200 of them in the dir, there's a higher chance in a case of a crash that one goes bad than if I have 15. Would adding -f (full) clean these up? I seem to recall it did in earlier versions. I've added the -F to it already, and that didn't seem to help. From ktm@rice.edu Thu Mar 10 13:47:37 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4EB501337BB7 for ; Thu, 10 Mar 2011 09:47:37 -0400 (AST) 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 10049-10 for ; Thu, 10 Mar 2011 13:47:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh4.mail.rice.edu (mh4.mail.rice.edu [128.42.199.11]) by mail.postgresql.org (Postfix) with ESMTP id A58391337B97 for ; Thu, 10 Mar 2011 09:47:25 -0400 (AST) Received: from mh4.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh4.mail.rice.edu (Postfix) with ESMTP id E80D928FDC7; Thu, 10 Mar 2011 07:47:25 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh4.mail.rice.edu, auth channel Received: from mh4.mail.rice.edu ([127.0.0.1]) by mh4.mail.rice.edu (mh4.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id LwGu80RdRWCq; Thu, 10 Mar 2011 07:47:25 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh4.mail.rice.edu (Postfix) with ESMTP id BA72028FDC6; Thu, 10 Mar 2011 07:47:25 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id A92E12D5A6; Thu, 10 Mar 2011 07:47:24 -0600 (CST) Date: Thu, 10 Mar 2011 07:47:23 -0600 From: Kenneth Marshall To: Scott Whitney Cc: pgsql-admin@postgresql.org Subject: Re: pg_clogs hanging around Message-ID: <20110310134723.GX8169@aart.is.rice.edu> References: <358079.14070.1299761935620.JavaMail.root@zimbra.int.journyx.com> <11997393.14072.1299762070874.JavaMail.root@zimbra.int.journyx.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <11997393.14072.1299762070874.JavaMail.root@zimbra.int.journyx.com> User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/112 X-Sequence-Number: 36984 On Thu, Mar 10, 2011 at 07:01:10AM -0600, Scott Whitney wrote: > Ooops...I accidentally took this off list, as Kevin was nice enough to point out. > > > >> What am I looking for? > > >Outliers. > > > Yeah. It's just those 2. I'd assume that the db I created > > yesterday would be an outlier, but template0 has been there all along > > (of course) and is still listed as 648, a significantly smaller number. > > > >> The output shows me 345 rows, most of which are 132xxxxx numbers. > >> Two of them (template0 and a database created yesterday) say 648. > > >The template0 database is what's keeping the clog files from being > >cleaned up, but I guess the big question is why you care. They will > >go away eventually, and shouldn't affect performance. Are they > >taking enough space to merit extraordinary effort to clean them up? > > -Kevin > > > My concern is that when we had a failure a few years ago, and one of the clog files went bad. I had to manually recreate some customer data after bringing up the previous backup. So, I'd rather have them not there, because, well, if there are 200 of them in the dir, there's a higher chance in a case of a crash that one goes bad than if I have 15. > > Would adding -f (full) clean these up? I seem to recall it did in earlier versions. I've added the -F to it already, and that didn't seem to help. > If you have hardware problems like that you have way more problems. You could have corruption (silent) occurring in any of the other database files. Good luck. Cheers, Ken From scott@journyx.com Thu Mar 10 13:56:40 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8EEB31337B97 for ; Thu, 10 Mar 2011 09:56:40 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 59219-08 for ; Thu, 10 Mar 2011 13:56:33 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id DB2B11337B6E for ; Thu, 10 Mar 2011 09:56:32 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id B545BDC003; Thu, 10 Mar 2011 07:56:32 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id B+F9O2wJt8EF; Thu, 10 Mar 2011 07:56:26 -0600 (CST) Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5]) by mail.int.journyx.com (Postfix) with ESMTP id B23BFC4041; Thu, 10 Mar 2011 07:56:26 -0600 (CST) Date: Thu, 10 Mar 2011 07:56:26 -0600 (CST) From: Scott Whitney Reply-To: Scott Whitney To: Kenneth Marshall Cc: pgsql-admin@postgresql.org, Scott Whitney Message-ID: <15608934.14100.1299765386313.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <5879018.14098.1299765304748.JavaMail.root@zimbra.int.journyx.com> Subject: Re: pg_clogs hanging around MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.100.188] X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0 (Win)/6.0.6_GA_2331.RHEL4) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/113 X-Sequence-Number: 36985 > > Ooops...I accidentally took this off list, as Kevin was nice enough > > to point out. > > > > > > >> What am I looking for? > > > > >Outliers. > > > > > Yeah. It's just those 2. I'd assume that the db I created > > > yesterday would be an outlier, but template0 has been there all > > > along (of course) and is still listed as 648, a significantly > > > smaller number. > > > > > > >> The output shows me 345 rows, most of which are 132xxxxx numbers. > > >> Two of them (template0 and a database created yesterday) say 648. > > > > >The template0 database is what's keeping the clog files from being > > >cleaned up, but I guess the big question is why you care. They will > > >go away eventually, and shouldn't affect performance. Are they > > >taking enough space to merit extraordinary effort to clean them up? > > > -Kevin > > > > > > My concern is that when we had a failure a few years ago, and one of > > the clog files went bad. I had to manually recreate some customer > > data after bringing up the previous backup. So, I'd rather have them > > not there, because, well, if there are 200 of them in the dir, > > there's a higher chance in a case of a crash that one goes bad than > > if I have 15. > > > > Would adding -f (full) clean these up? I seem to recall it did in > > earlier versions. I've added the -F to it already, and that didn't > > seem to help. > > > > If you have hardware problems like that you have way more problems. > You could have corruption (silent) occurring in any of the other > database files. Good luck. I am, in fact, aware of that, but every single machine ever manufactured will have hardware problems such at this at some point. It stems quite simply from Ohm's Law, one gross over-simplification of which is as simple as "if it's got a resistor in it, it's going to fail at some point", as I'm sure you know. It's merely a matter of whether proactive replacement, backups, standby systems, etc ameliorate that risk. When we had our failure a couple of years ago, it did not. Regardless, my question still stands, and I do, in fact, care about ANY database blocking cleanup of clogs (or anything else). There's this concept of "if this then what else," and if template0 (or anyone else) is blocking that ability to properly clean those up, what else is possibly screwed up in a similar fashion. So, what can I do to resolve this issue? From ktm@rice.edu Thu Mar 10 14:04:19 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 499A91337B82 for ; Thu, 10 Mar 2011 10:04:19 -0400 (AST) 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 14464-08 for ; Thu, 10 Mar 2011 14:04:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh5.mail.rice.edu (mh5.mail.rice.edu [128.42.199.32]) by mail.postgresql.org (Postfix) with ESMTP id 3BC191337BA8 for ; Thu, 10 Mar 2011 10:04:07 -0400 (AST) Received: from mh5.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh5.mail.rice.edu (Postfix) with ESMTP id 3B92E28FA4D; Thu, 10 Mar 2011 08:04:07 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh5.mail.rice.edu, auth channel Received: from mh5.mail.rice.edu ([127.0.0.1]) by mh5.mail.rice.edu (mh5.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id UvlCM4M9ijyu; Thu, 10 Mar 2011 08:04:07 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh5.mail.rice.edu (Postfix) with ESMTP id 0BBD728FA4A; Thu, 10 Mar 2011 08:04:07 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id 787AB2D5A6; Thu, 10 Mar 2011 08:04:06 -0600 (CST) Date: Thu, 10 Mar 2011 08:04:05 -0600 From: Kenneth Marshall To: Scott Whitney Cc: pgsql-admin@postgresql.org Subject: Re: pg_clogs hanging around Message-ID: <20110310140405.GZ8169@aart.is.rice.edu> References: <5879018.14098.1299765304748.JavaMail.root@zimbra.int.journyx.com> <15608934.14100.1299765386313.JavaMail.root@zimbra.int.journyx.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <15608934.14100.1299765386313.JavaMail.root@zimbra.int.journyx.com> User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/114 X-Sequence-Number: 36986 On Thu, Mar 10, 2011 at 07:56:26AM -0600, Scott Whitney wrote: > > > > If you have hardware problems like that you have way more problems. > > You could have corruption (silent) occurring in any of the other > > database files. Good luck. > > I am, in fact, aware of that, but every single machine ever manufactured will have hardware problems such at this at some point. It stems quite simply from Ohm's Law, one gross over-simplification of which is as simple as "if it's got a resistor in it, it's going to fail at some point", as I'm sure you know. It's merely a matter of whether proactive replacement, backups, standby systems, etc ameliorate that risk. When we had our failure a couple of years ago, it did not. > > Regardless, my question still stands, and I do, in fact, care about ANY database blocking cleanup of clogs (or anything else). There's this concept of "if this then what else," and if template0 (or anyone else) is blocking that ability to properly clean those up, what else is possibly screwed up in a similar fashion. > > So, what can I do to resolve this issue? > True, entropy rules. I think that you can use "VACUUM FREEZE" to allow the clogs to be cleaned up. Cheers, Ken From scott@journyx.com Thu Mar 10 14:18:49 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 2F8431337B93 for ; Thu, 10 Mar 2011 10:18:49 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 07772-08 for ; Thu, 10 Mar 2011 14:18:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id AE0021337B82 for ; Thu, 10 Mar 2011 10:18:41 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id DD265DC009; Thu, 10 Mar 2011 08:18:39 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id sTrsi5j-nrjL; Thu, 10 Mar 2011 08:18:35 -0600 (CST) Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5]) by mail.int.journyx.com (Postfix) with ESMTP id 2A01EC404C; Thu, 10 Mar 2011 08:18:35 -0600 (CST) Date: Thu, 10 Mar 2011 08:18:34 -0600 (CST) From: Scott Whitney Reply-To: Scott Whitney To: Kenneth Marshall Cc: pgsql-admin@postgresql.org, Scott Whitney Message-ID: <5606497.14104.1299766714935.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <20110310140405.GZ8169@aart.is.rice.edu> Subject: Re: pg_clogs hanging around MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.100.188] X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0 (Win)/6.0.6_GA_2331.RHEL4) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/115 X-Sequence-Number: 36987 > On Thu, Mar 10, 2011 at 07:56:26AM -0600, Scott Whitney wrote: > > > > > > If you have hardware problems like that you have way more > > > problems. You could have corruption (silent) occurring in any of > > > the other > > > database files. Good luck. > > > > I am, in fact, aware of that, but every single machine ever > > manufactured will have hardware problems such at this at some point. > > It stems quite simply from Ohm's Law, one gross over-simplification > > of which is as simple as "if it's got a resistor in it, it's going > > to fail at some point", as I'm sure you know. It's merely a matter > > of whether proactive replacement, backups, standby systems, etc > > ameliorate that risk. When we had our failure a couple of years ago, > > it did not. > > > > Regardless, my question still stands, and I do, in fact, care about > > ANY database blocking cleanup of clogs (or anything else). There's > > this concept of "if this then what else," and if template0 (or > > anyone else) is blocking that ability to properly clean those up, > > what else is possibly screwed up in a similar fashion. > > > > So, what can I do to resolve this issue? > > > > True, entropy rules. I think that you can use "VACUUM FREEZE" > to allow the clogs to be cleaned up. > I thought I was. I've got -F in there. vacuumdb -a -v -F From ktm@rice.edu Thu Mar 10 15:09:09 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5EEA5133655C for ; Thu, 10 Mar 2011 11:09:09 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 74808-04 for ; Thu, 10 Mar 2011 15:09:02 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh5.mail.rice.edu (mh5.mail.rice.edu [128.42.199.32]) by mail.postgresql.org (Postfix) with ESMTP id CC4511337B93 for ; Thu, 10 Mar 2011 11:09:01 -0400 (AST) Received: from mh5.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh5.mail.rice.edu (Postfix) with ESMTP id D4E1728FA64; Thu, 10 Mar 2011 09:09:01 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh5.mail.rice.edu, auth channel Received: from mh5.mail.rice.edu ([127.0.0.1]) by mh5.mail.rice.edu (mh5.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id UO3F-p7sNXtN; Thu, 10 Mar 2011 09:09:01 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh5.mail.rice.edu (Postfix) with ESMTP id 8CA0228FA60; Thu, 10 Mar 2011 09:09:01 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id D42212D5A6; Thu, 10 Mar 2011 09:09:00 -0600 (CST) Date: Thu, 10 Mar 2011 09:09:00 -0600 From: Kenneth Marshall To: Scott Whitney Cc: pgsql-admin@postgresql.org Subject: Re: pg_clogs hanging around Message-ID: <20110310150900.GA8169@aart.is.rice.edu> References: <20110310140405.GZ8169@aart.is.rice.edu> <5606497.14104.1299766714935.JavaMail.root@zimbra.int.journyx.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5606497.14104.1299766714935.JavaMail.root@zimbra.int.journyx.com> User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/119 X-Sequence-Number: 36991 On Thu, Mar 10, 2011 at 08:18:34AM -0600, Scott Whitney wrote: > > On Thu, Mar 10, 2011 at 07:56:26AM -0600, Scott Whitney wrote: > > > > > > > > If you have hardware problems like that you have way more > > > > problems. You could have corruption (silent) occurring in any of > > > > the other > > > > database files. Good luck. > > > > > > I am, in fact, aware of that, but every single machine ever > > > manufactured will have hardware problems such at this at some point. > > > It stems quite simply from Ohm's Law, one gross over-simplification > > > of which is as simple as "if it's got a resistor in it, it's going > > > to fail at some point", as I'm sure you know. It's merely a matter > > > of whether proactive replacement, backups, standby systems, etc > > > ameliorate that risk. When we had our failure a couple of years ago, > > > it did not. > > > > > > Regardless, my question still stands, and I do, in fact, care about > > > ANY database blocking cleanup of clogs (or anything else). There's > > > this concept of "if this then what else," and if template0 (or > > > anyone else) is blocking that ability to properly clean those up, > > > what else is possibly screwed up in a similar fashion. > > > > > > So, what can I do to resolve this issue? > > > > > > > True, entropy rules. I think that you can use "VACUUM FREEZE" > > to allow the clogs to be cleaned up. > > > I thought I was. I've got -F in there. vacuumdb -a -v -F > Sorry about that, you are. I have never done it from the command-line just from psql. I was thinking F for FULL... :) Cheers, Ken From scott@journyx.com Thu Mar 10 15:18:18 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 630481337B92 for ; Thu, 10 Mar 2011 11:18:18 -0400 (AST) 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 35260-08 for ; Thu, 10 Mar 2011 15:18:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id 6EBC31337B82 for ; Thu, 10 Mar 2011 11:18:07 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id 4102FC4018; Thu, 10 Mar 2011 09:18:07 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id iHA3xGyo9Qla; Thu, 10 Mar 2011 09:18:02 -0600 (CST) Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5]) by mail.int.journyx.com (Postfix) with ESMTP id 846C1C4050; Thu, 10 Mar 2011 09:18:02 -0600 (CST) Date: Thu, 10 Mar 2011 09:18:02 -0600 (CST) From: Scott Whitney Reply-To: Scott Whitney To: Kenneth Marshall Cc: pgsql-admin@postgresql.org, Scott Whitney Message-ID: <31022840.14155.1299770282363.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <30674693.14151.1299770218945.JavaMail.root@zimbra.int.journyx.com> Subject: Re: pg_clogs hanging around MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.100.188] X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0 (Win)/6.0.6_GA_2331.RHEL4) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/120 X-Sequence-Number: 36992 > > > > > If you have hardware problems like that you have way more > > > > > problems. You could have corruption (silent) occurring in any > > > > > of the other > > > > > database files. Good luck. > > > > > > > > I am, in fact, aware of that, but every single machine ever > > > > manufactured will have hardware problems such at this at some > > > > point. It stems quite simply from Ohm's Law, one gross > > > > over-simplification of which is as simple as "if it's got a > > > > resistor in it, it's going > > > > to fail at some point", as I'm sure you know. It's merely a > > > > matter of whether proactive replacement, backups, standby > > > > systems, etc > > > > ameliorate that risk. When we had our failure a couple of years > > > > ago, it did not. > > > > > > > > Regardless, my question still stands, and I do, in fact, care > > > > about ANY database blocking cleanup of clogs (or anything else). > > > > There's this concept of "if this then what else," and if > > > > template0 (or > > > > anyone else) is blocking that ability to properly clean those > > > > up, what else is possibly screwed up in a similar fashion. > > > > > > > > So, what can I do to resolve this issue? > > > > > > > > > > True, entropy rules. I think that you can use "VACUUM FREEZE" > > > to allow the clogs to be cleaned up. > > > > > I thought I was. I've got -F in there. vacuumdb -a -v -F > > > > Sorry about that, you are. I have never done it from the command-line > just from psql. I was thinking F for FULL... :) > This is also interesting. I just allowed connections to template0 for the express purpose of vacuuming it, did a full vac on template0, and that did NOT clear up the hanging clogs. Thanks for the help so far, everyone. I'm going to assuming something is going wrong with my vacuuming on Saturdays (which I can't PROVE until my Saturday window), and take it from there. I'll reply next week with more info if needed. From Kevin.Grittner@wicourts.gov Thu Mar 10 15:30:59 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 56E691337B82 for ; Thu, 10 Mar 2011 11:30:59 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 93026-03 for ; Thu, 10 Mar 2011 15:30:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 42212133616B for ; Thu, 10 Mar 2011 11:30:51 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Thu, 10 Mar 2011 09:30:51 -0600 Message-Id: <4D789A44020000250003B750@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Thu, 10 Mar 2011 09:30:44 -0600 From: "Kevin Grittner" To: "Kenneth Marshall" Cc: "Scott Whitney" , Subject: Re: pg_clogs hanging around References: <30674693.14151.1299770218945.JavaMail.root@zimbra.int.journyx.com> <31022840.14155.1299770282363.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <31022840.14155.1299770282363.JavaMail.root@zimbra.int.journyx.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/121 X-Sequence-Number: 36993 Scott Whitney wrote: > This is also interesting. I just allowed connections to template0 > for the express purpose of vacuuming it, did a full vac on > template0, and that did NOT clear up the hanging clogs. Was it a VACUUM FREEZE followed by a CHECKPOINT? Did you also cover that new database? -Kevin From scott@journyx.com Thu Mar 10 15:48:18 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A953F1337BDD for ; Thu, 10 Mar 2011 11:48:18 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 28637-02 for ; Thu, 10 Mar 2011 15:48:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id D2D8A133655C for ; Thu, 10 Mar 2011 11:48:10 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id 88F34FC012; Thu, 10 Mar 2011 09:48:10 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id AwvpWQLTGr4l; Thu, 10 Mar 2011 09:47:36 -0600 (CST) Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5]) by mail.int.journyx.com (Postfix) with ESMTP id 28BE7FC005; Thu, 10 Mar 2011 09:47:25 -0600 (CST) Date: Thu, 10 Mar 2011 09:47:24 -0600 (CST) From: Scott Whitney Reply-To: Scott Whitney To: Kevin Grittner Cc: Scott Whitney , pgsql-admin@postgresql.org, Kenneth Marshall Message-ID: <7423332.14202.1299772044088.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <27682685.14194.1299771826678.JavaMail.root@zimbra.int.journyx.com> Subject: Re: pg_clogs hanging around MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.100.188] X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0 (Win)/6.0.6_GA_2331.RHEL4) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/122 X-Sequence-Number: 36994 > > This is also interesting. I just allowed connections to template0 > > for the express purpose of vacuuming it, did a full vac on > > template0, and that did NOT clear up the hanging clogs. > > Was it a VACUUM FREEZE followed by a CHECKPOINT? Did you also cover > that new database? Yes, yes and yes, actually. Unless I did it wrong, which is always a strong possibility. Especially early in the morning... pg statement: update pg_database set datallowconn=true where datname='template0'; shell: vacuumdb -f -v -z -F template0 pg statement: checkpoint; pg statement: update pg_database set datallowconn=true where datname='template0'; Like I said, I'm beginning to believe that something is rotten wrt my Saturday vacs... From Kevin.Grittner@wicourts.gov Thu Mar 10 15:57:06 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A3F1C1337B82 for ; Thu, 10 Mar 2011 11:57:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 40261-09 for ; Thu, 10 Mar 2011 15:56:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 79E16133655C for ; Thu, 10 Mar 2011 11:56:58 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Thu, 10 Mar 2011 09:56:58 -0600 Message-Id: <4D78A063020000250003B756@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Thu, 10 Mar 2011 09:56:50 -0600 From: "Kevin Grittner" Cc: "Scott Whitney" , , "Kenneth Marshall" Subject: Re: pg_clogs hanging around References: <27682685.14194.1299771826678.JavaMail.root@zimbra.int.journyx.com> <7423332.14202.1299772044088.JavaMail.root@zimbra.int.journyx.com> In-Reply-To: <7423332.14202.1299772044088.JavaMail.root@zimbra.int.journyx.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.189 tagged_above=-10 required=5 tests=BAYES_00=-1.9, MISSING_HEADERS=1.021, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/123 X-Sequence-Number: 36995 Scott Whitney wrote: > vacuumdb -f -v -z -F template0 VACUUM FULL (the -f option) is almost always a bad idea, for many reasons. I wouldn't be surprised if it somehow messed you up. I would schedule a database REINDEX on any databases where you used the -f option, and then re-evaluate, if you feel compelled to push this issue. I still doubt that this effort will buy you anything in recoverability. Between the hint bits and the frozen xmin in all the tuples, the clog won't be consulted anyway. If the pursuit of this is causing you to do direct updates of system tables and to resort to VACUUM FULL, you're putting your data at risk for no apparent benefit. -Kevin