Обсуждение: pg_clogs hanging around

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

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
recreatesome customer data after bringing up the previous backup. So, I'd rather have them not there, because, well, if
thereare 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,
andthat didn't seem to help.  

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
recreatesome customer data after bringing up the previous backup. So, I'd rather have them not there, because, well, if
thereare 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,
andthat 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

> > 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
somepoint. It stems quite simply from Ohm's Law, one gross over-simplification of which is as simple as "if it's got a
resistorin 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
properlyclean those up, what else is possibly screwed up in a similar fashion. 

So, what can I do to resolve this issue?

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
somepoint. It stems quite simply from Ohm's Law, one gross over-simplification of which is as simple as "if it's got a
resistorin 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
anythingelse). There's this concept of "if this then what else," and if template0 (or anyone else) is blocking that
abilityto 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

> 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

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

> > > > > 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
vacon 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
(whichI can't PROVE until my Saturday window), and take it from there. 

I'll reply next week with more info if needed.

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

> > 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...

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