pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_clogs hanging around
12+ messages / 3 participants
[nested] [flat]
* pg_clogs hanging around
@ 2011-03-09 19:49 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2011-03-09 19:49 UTC (permalink / raw)
To: pgsql-admin
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?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-09 20:35 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 12+ messages in thread
From: Kevin Grittner @ 2011-03-09 20:35 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; pgsql-admin
Scott Whitney <scott@journyx.com> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 13:01 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2011-03-10 13:01 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 13:47 Kenneth Marshall <ktm@rice.edu>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 12+ messages in thread
From: Kenneth Marshall @ 2011-03-10 13:47 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 13:56 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2011-03-10 13:56 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-admin@postgresql.org, Scott Whitney <swhitney@journyx.com>
> > 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?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 14:04 Kenneth Marshall <ktm@rice.edu>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: Kenneth Marshall @ 2011-03-10 14:04 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 14:18 Scott Whitney <scott@journyx.com>
parent: Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2011-03-10 14:18 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-admin@postgresql.org, Scott Whitney <swhitney@journyx.com>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 15:09 Kenneth Marshall <ktm@rice.edu>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 12+ messages in thread
From: Kenneth Marshall @ 2011-03-10 15:09 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 15:18 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2011-03-10 15:18 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-admin@postgresql.org, Scott Whitney <swhitney@journyx.com>
> > > > > 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 15:30 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 12+ messages in thread
From: Kevin Grittner @ 2011-03-10 15:30 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: Scott Whitney <swhitney@journyx.com>; pgsql-admin
Scott Whitney <scott@journyx.com> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 15:47 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2011-03-10 15:47 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Scott Whitney <swhitney@journyx.com>; pgsql-admin@postgresql.org, Kenneth Marshall <ktm@rice.edu>
> > 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...
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_clogs hanging around
@ 2011-03-10 15:56 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 12+ messages in thread
From: Kevin Grittner @ 2011-03-10 15:56 UTC (permalink / raw)
To: ; +Cc: Scott Whitney <swhitney@journyx.com>; pgsql-admin; Kenneth Marshall <ktm@rice.edu>
Scott Whitney <scott@journyx.com> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2011-03-10 15:56 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-09 19:49 pg_clogs hanging around Scott Whitney <scott@journyx.com>
2011-03-09 20:35 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-03-10 13:01 Re: pg_clogs hanging around Scott Whitney <scott@journyx.com>
2011-03-10 13:47 ` Kenneth Marshall <ktm@rice.edu>
2011-03-10 13:56 Re: pg_clogs hanging around Scott Whitney <scott@journyx.com>
2011-03-10 14:04 ` Kenneth Marshall <ktm@rice.edu>
2011-03-10 14:18 ` Scott Whitney <scott@journyx.com>
2011-03-10 15:09 ` Kenneth Marshall <ktm@rice.edu>
2011-03-10 15:18 Re: pg_clogs hanging around Scott Whitney <scott@journyx.com>
2011-03-10 15:30 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-03-10 15:47 Re: pg_clogs hanging around Scott Whitney <scott@journyx.com>
2011-03-10 15:56 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox