Thread: Understanding wal segments
Hi List; I'm wanting to better understand wal / checkpoint segments. I have a cluster which has checkpoint_segments set to 32 and wal_buffers set to 24. I see consistently 66 files in pg_xlog and I've recently implemented wal archiving and so far there are 41 segments/files in the wal archive dir. What's the relationship between wal_buffers and checkpoint_segments? even though the max distance between checkpoints is 32 segments, is there something that controls the number of files/segments in the pg_xlog dir ? Thanks in advance /Kevin
Kevin Kempter <kevin@kevinkempterllc.com> writes:
> What's the relationship between wal_buffers and checkpoint_segments?
None particularly.
> even
> though the max distance between checkpoints is 32 segments, is there
> something that controls the number of files/segments in the pg_xlog dir ?
The system is designed to settle at 2*checkpoint_segments+1 files in
pg_xlog, assuming that you have activity spikes sufficient to drive it
up to that level at all.
regards, tom lane
On Thursday 01 November 2007 22:38:02 Tom Lane wrote: > Kevin Kempter <kevin@kevinkempterllc.com> writes: > > What's the relationship between wal_buffers and checkpoint_segments? > > None particularly. > > > even > > though the max distance between checkpoints is 32 segments, is there > > something that controls the number of files/segments in the pg_xlog dir ? > > The system is designed to settle at 2*checkpoint_segments+1 files in > pg_xlog, assuming that you have activity spikes sufficient to drive it > up to that level at all. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq makes sense. our system cetrtianly has the activity needed to push it to that level. I turned on wal archivint 1hr & 10min ago and I already have 135 wal segments in my archive directory. I'm running a file system backup, I did a pg_start_backup and now I'm running the tar. Will this affect the transaction activity? I thought it would not. Also I suspect after each filesystem backup I should remove the older archived segments since the file system backup makes them irrelevant. Is this accurate? Thx /Kevin
Kevin Kempter <kevin@kevinkempterllc.com> writes:
> Also I suspect after each filesystem backup I should remove the older
> archived segments since the file system backup makes them
> irrelevant. Is this accurate?
Depends what your goals are. If you only want to be able to recover
the latest database state then you only need archived WAL segments
back to the start of the latest filesystem backup. If you are
interested in the "PITR" aspect of it --- being able to revert back
to last Tuesday before some bad guy injected bad data, say --- then
you'll want to keep older filesystem backups too, and a continuous
string of archived WAL files covering the period of interest.
regards, tom lane
On Thursday 01 November 2007 23:38:48 Tom Lane wrote: > Kevin Kempter <kevin@kevinkempterllc.com> writes: > > Also I suspect after each filesystem backup I should remove the older > > archived segments since the file system backup makes them > > irrelevant. Is this accurate? > > Depends what your goals are. If you only want to be able to recover > the latest database state then you only need archived WAL segments > back to the start of the latest filesystem backup. If you are > interested in the "PITR" aspect of it --- being able to revert back > to last Tuesday before some bad guy injected bad data, say --- then > you'll want to keep older filesystem backups too, and a continuous > string of archived WAL files covering the period of interest. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster Makes sense. Thanks. One more question. I see a fair amount of file changed warnings in the tar output. Like this: tar: ./data/base/82097725/4242350944: file changed as we read it ./data/base/82097725/143763179 ./data/base/82097725/3587685114 ./data/base/82097725/3587684991 ./data/base/82097725/3587685046 ./data/base/82097725/3587685003 ./data/base/82097725/3588484372 tar: ./data/base/82097725/3588484372: file changed as we read it ./data/base/82097725/3587685172 ./data/base/82097725/10733 ./data/base/82097725/3588482971 ./data/base/82097725/4082992388 ./data/base/82097725/4242349291.4 tar: ./data/base/82097725/4242349291.4: file changed as we read it Is this an issue or does postgres comensate for this since it knows I'm running a backup (i've run a pg_start_backup) ? Thx /Kevin
Kevin Kempter <kevin@kevinkempterllc.com> writes:
> One more question. I see a fair amount of file changed warnings in the tar
> output. Like this:
> tar: ./data/base/82097725/4242350944: file changed as we read it
Yeah, GNU tar complains about that. It's not a problem, other than the
risk that a boatload of such warnings could obscure real problems ...
regards, tom lane
On Thursday 01 November 2007 23:52:00 Tom Lane wrote: > Kevin Kempter <kevin@kevinkempterllc.com> writes: > > One more question. I see a fair amount of file changed warnings in the > > tar output. Like this: > > > > tar: ./data/base/82097725/4242350944: file changed as we read it > > Yeah, GNU tar complains about that. It's not a problem, other than the > risk that a boatload of such warnings could obscure real problems ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match great. Thanks again. /Kevin