pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PG 8.1.4 not clearing pg_clog
3+ messages / 2 participants
[nested] [flat]

* PG 8.1.4 not clearing pg_clog
@ 2007-10-09 17:19  Scott Whitney <swhitney@journyx.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Scott Whitney @ 2007-10-09 17:19 UTC (permalink / raw)
  To: pgsql-admin

I've seen several posts on this issue in the past, but none seems to address
my situation.

In my pg_clog directory, I have 225 files dating back to August 8th, when I
installed this PostgreSQL server.
In my pg_xlog directory, I have 128 files dating back to October 8th.

Autovacuum is on. Settings are as follows:
autovacuum = on                 # enable autovacuum subprocess?
autovacuum_naptime = 3          # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400       # min # of tuple updates before
                                        # vacuum
autovacuum_analyze_threshold = 200      # min # of tuple updates before
                                        # analyze
autovacuum_vacuum_scale_factor = 0.2    # fraction of rel size before
                                        # vacuum
autovacuum_analyze_scale_factor = 0.1   # fraction of rel size before
                                        # analyze


Using:
SELECT datname, age(datfrozenxid) FROM pg_database order by age;

The ages of my frozenxids are in a range from:

6/9/1977 (this is strange..I have 4 dbs listing that, including template0
and 2 of my own. The difference of those databases is that those 3 (not
including template0) are owned by a different user.

Then a range from 1/11/2004 through 6/2/2004. There are 217 of these.

Specifically, some examples are:
      datname      |    age     
-------------------+------------
template0         |  234824829
postgres          | 1073922288
template1         | 1073923008

Which I assume to be epoch dates and thusly converted.

If "today" is Saturday, a cronjob runs this command runs on each database:
PGCMD = 'vacuumdb -f -v -z %s'

where %s is each database in the system. I don't have the log at the moment
to prove it ran Saturday, but, yeah it does.

So...

I'm performing a full vacuum on each database in the system every Saturday.
My pg_clog files date back to August 8th.

What am I doing wrong?


Thanks,

Scott Whitney
Journyx, Inc.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* PG 8.1.4 not clearing pg_clog
@ 2007-10-10 16:49  Scott Whitney <swhitney@journyx.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Scott Whitney @ 2007-10-10 16:49 UTC (permalink / raw)
  To: pgsql-admin

Sorry if this one is a repost, folks. I didn't see it come to my box or show
up on the archive on the web. 

I've seen several posts on this issue in the past, but none seems to address
my situation.

In my pg_clog directory, I have 225 files dating back to August 8th, when I
installed this PostgreSQL server.
In my pg_xlog directory, I have 128 files dating back to October 8th.

Autovacuum is on. Settings are as follows:
autovacuum = on                 # enable autovacuum subprocess?
autovacuum_naptime = 3          # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400       # min # of tuple updates before
                                        # vacuum
autovacuum_analyze_threshold = 200      # min # of tuple updates before
                                        # analyze
autovacuum_vacuum_scale_factor = 0.2    # fraction of rel size before
                                        # vacuum
autovacuum_analyze_scale_factor = 0.1   # fraction of rel size before
                                        # analyze


Using:
SELECT datname, age(datfrozenxid) FROM pg_database order by age;

The ages of my frozenxids are in a range from:

6/9/1977 (this is strange..I have 4 dbs listing that, including template0
and 2 of my own. The difference of those databases is that those 3 (not
including template0) are owned by a different user.

Then a range from 1/11/2004 through 6/2/2004. There are 217 of these.

Specifically, some examples are:
      datname      |    age     
-------------------+------------
template0         |  234824829
postgres          | 1073922288
template1         | 1073923008

Which I assume to be epoch dates and thusly converted.

If "today" is Saturday, a cronjob runs this command runs on each database:
PGCMD = 'vacuumdb -f -v -z %s'

where %s is each database in the system. I don't have the log at the moment
to prove it ran Saturday, but, yeah it does.

So...

I'm performing a full vacuum on each database in the system every Saturday.
My pg_clog files date back to August 8th.

What am I doing wrong?


Thanks,

Scott Whitney
Journyx, Inc.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PG 8.1.4 not clearing pg_clog
@ 2007-10-10 17:39  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Scott Whitney <swhitney@journyx.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2007-10-10 17:39 UTC (permalink / raw)
  To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin

"Scott Whitney" <swhitney@journyx.com> writes:
> Which I assume to be epoch dates and thusly converted.

This assumption is incorrect, which likely accounts for your confusion.
XID numbers have nothing to do with any external reality, and age()
results even less.

> If "today" is Saturday, a cronjob runs this command runs on each database:
> PGCMD = 'vacuumdb -f -v -z %s'

Seems like you should use the -a option instead of assuming that you
know which databases need to be vacuumed.  You haven't shown us a
smoking gun here, but the failure to truncate pg_clog suggests that some
database is getting missed.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-10-10 17:39 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-09 17:19 PG 8.1.4 not clearing pg_clog Scott Whitney <swhitney@journyx.com>
2007-10-10 16:49 PG 8.1.4 not clearing pg_clog Scott Whitney <swhitney@journyx.com>
2007-10-10 17:39 ` 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