pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_clog not getting cleared
4+ messages / 3 participants
[nested] [flat]
* pg_clog not getting cleared
@ 2010-12-21 20:24 Scott Whitney <scott@journyx.com>
2010-12-21 21:21 ` Re: pg_clog not getting cleared Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Whitney @ 2010-12-21 20:24 UTC (permalink / raw)
To: pgsql-admin
Hello. I posted this once before, but I'm encountering it again.
Each Saturday, I run: vacuumdb -a -v. I have autovac on all the time.
However, my pg_clog directory lists clog files going back to July.
This is pg 8.4.4 on Linux (CentOS 5.5).
I know this isn't a whole lot of information at this time. What more would be useful in finding out what's failing and where?
I seem to recall that doing vacuumdb -f -a -v resolved it, which would seem to point to a database not being vacuumed, but that's sort of the point of the -a switch, right? I'm running this as the "postgres" user himself.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_clog not getting cleared
2010-12-21 20:24 pg_clog not getting cleared Scott Whitney <scott@journyx.com>
@ 2010-12-21 21:21 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2010-12-21 21:21 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; pgsql-admin
Scott Whitney <scott@journyx.com> wrote:
> Each Saturday, I run: vacuumdb -a -v. I have autovac on all the
> time.
>
> However, my pg_clog directory lists clog files going back to
> July.
>
> This is pg 8.4.4 on Linux (CentOS 5.5).
>
> I know this isn't a whole lot of information at this time. What
> more would be useful in finding out what's failing and where?
Nothing is failing -- clog is needed to track what has committed
until transactions are "frozen".
> I seem to recall that doing vacuumdb -f -a -v resolved it
Are you sure that wasn't -F rather than -f ? The uppercase switch
freezes tuples, which would allow clog to be cleaned up. This
shouldn't normally be needed, unless you have some other reason to
need aggressive freezing.
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_clog not getting cleared
@ 2010-12-22 21:12 Scott Whitney <scott@journyx.com>
2010-12-22 21:38 ` Re: pg_clog not getting cleared Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Whitney @ 2010-12-22 21:12 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Scott Whitney <swhitney@journyx.com>; pgsql-admin
I'm certain it was -f, not -F, since it's still in the script I use, albeit commented out.
I understand the purpose of the clogs, but I would think that the transactions would have been frozen on all dbs (I've got about 300 in my cluster) by now. My logs go back to July 13th which, I think, is when the server was last restarted.
I'll try the -F on Sat and see if that resolves the issue. I'll also have logs available at that time, assuming all goes well.
Thanks for the advice.
Scott Whitney <scott@journyx.com> wrote:
> Each Saturday, I run: vacuumdb -a -v. I have autovac on all the
> time.
>
> However, my pg_clog directory lists clog files going back to
> July.
>
> This is pg 8.4.4 on Linux (CentOS 5.5).
>
> I know this isn't a whole lot of information at this time. What
> more would be useful in finding out what's failing and where?
Nothing is failing -- clog is needed to track what has committed
until transactions are "frozen".
> I seem to recall that doing vacuumdb -f -a -v resolved it
Are you sure that wasn't -F rather than -f ? The uppercase switch
freezes tuples, which would allow clog to be cleaned up. This
shouldn't normally be needed, unless you have some other reason to
need aggressive freezing.
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_clog not getting cleared
2010-12-22 21:12 Re: pg_clog not getting cleared Scott Whitney <scott@journyx.com>
@ 2010-12-22 21:38 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-12-22 21:38 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
Scott Whitney <scott@journyx.com> writes:
> I understand the purpose of the clogs, but I would think that the transactions would have been frozen on all dbs (I've got about 300 in my cluster) by now. My logs go back to July 13th which, I think, is when the server was last restarted.
It doesn't really try to remove clog entries that are younger than
vacuum_freeze_table_age (see also vacuum_freeze_min_age). At two bits
per transaction, the general feeling is that eating the disk space is
better than forcing full-database vacuums more often. But if you're
hot to have the space released sooner, those are the knobs to frob.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-22 21:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-21 20:24 pg_clog not getting cleared Scott Whitney <scott@journyx.com>
2010-12-21 21:21 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-22 21:12 Re: pg_clog not getting cleared Scott Whitney <scott@journyx.com>
2010-12-22 21:38 ` Tom Lane <tgl@sss.pgh.pa.us>
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