Обсуждение: 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