Re: WAL segments (names) not in a sequence

On Fri, May 24, 2013 at 3:24 PM, Amit Langote <amitlangote09(at)gmail(dot)com>wrote:

> On Sat, May 25, 2013 at 3:08 AM, German Becker <german(dot)becker(at)gmail(dot)com>
> wrote:
> > Thanks Amit, I understand now. Is there a way to know/predict how many
> > prealocated segments will there be in a certain moment? What does it
> deppend
> > on?
>
> Upthread, Fujii Masao-san suggested what might have happened that
> caused these pre-allocated segments to be created. To quote him:
>
> "WAL recycling is performed by checkpoint. Checkpoint always checks
> whether there are WAL files no longer required for crash recovery,
> IOW, WAL files which were generated before the prior checkpoint
> happened, and then if they are found, checkpoint tries to recycle
> them."
>
> Reading here would also help:
> http://www.postgresql.org/docs/9.1/static/wal-configuration.html
>
> If you are still using the same values as during this observation,
> could you provide values for these postgresql.conf parameters:
> checkpoint_segments, checkpoint_timeout, wal_keep_segments?
>
>
> --
> Amit Langote
>

Amit,

Frist, thanks for your help and your interest. I (think) I understand how
checkpoint /wal segment work. What I didn't understand from the
documentation is the possibility of segments being prealocated. I thought
that the WAL segment with the higher sequence number is the one being
written at present time, as opposed to a segment allocated to be written in
the future. If you could clarify this point to me, that would be great.

Here are the parameters related to checkpoint

For "restore" config
checkpoint_segments = 256 # in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 -
1.0
#checkpoint_warning = 30s

#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables

For "production" config:
checkpoint_segments = 256 # in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.7 # checkpoint target duration, 0.0 -
1.0
wal_keep_segments = 256 # in logfile segments, 16MB each; 0 disables

Thanks again,

Germán