Thread: WAL and backup recovery

Hi all:
            I've a problem regarding WAL (I think :-)). When recovering
a backup in 7.1.1, the number of files in pg_xlog/ starts to grow,
and eventually exhaust the disk capacity (it ate my 6G partition). I've
the following settings in my postgresql.conf file:

      wal_buffers = 4
      wal_files = 16
      wal_sync_method = fsync
      checkpoint_segments = 1
      checkpoint_timeout = 30

Right now I'm just testing the new engine, in fact I've installed it
yesterday (yes, I know, 7.1.2 is out today :->), so I don't care so much

about the performance of this settings. But, correct me if I
misunderstood the documentation, whit this config the backend will do a
checkpoint every 30 secs, or after the creation of a new segment
(whichever happens first), and if old segments are deleted why is the
file count growing?
Any help or tip would be greatly appreciated. Since now, thanks a lot.


Yours: Dario Brignardello

Dario Brignardello <dbrignar@sinectis.com> writes:
>       checkpoint_segments = 1
>    checkpoint_timeout = 30

Reducing checkpoint_segments and checkpoint_timeout is actually
seriously counterproductive, if your problem is amount of disk space
chewed up by WAL logs during a long transaction (such as bulk load of
a big table).  The WAL log cannot be truncated until the xact commits,
so the checkpoints that happen meanwhile just cause log bloat.  Lots
of it, because each checkpoint causes fresh copying of modified pages
into the WAL log.

Try increasing those numbers, not decreasing them.  Maybe 10/600 or so?

Also, if you are short on disk space for WAL, increasing wal_files isn't
such a great idea either.

            regards, tom lane

On Wed, 23 May 2001, Tom Lane wrote:

> Dario Brignardello <dbrignar@sinectis.com> writes:
> >       checkpoint_segments = 1
> >    checkpoint_timeout = 30
>
> Reducing checkpoint_segments and checkpoint_timeout is actually
> seriously counterproductive, if your problem is amount of disk space
> chewed up by WAL logs during a long transaction (such as bulk load of
> a big table).  The WAL log cannot be truncated until the xact commits,
> so the checkpoints that happen meanwhile just cause log bloat.  Lots
> of it, because each checkpoint causes fresh copying of modified pages
> into the WAL log.

Hi Tom,
    in the manual it mentions that the defaults are checkpoint_segments=3 and checkpoint_timeout=300 and a checkpoint
occursfor whichever of the two comes up first. If I change in postgresql.conf checkpoint_segments=10, will the default
checkpoint_timeout=300be ignored or should I increase that as well. I mean, will checkpoint_timeout be still in effect,
becauseif so, then the increasing only checkpoint_segments is useless. 

thanks in advance,
thalis

>
> Try increasing those numbers, not decreasing them.  Maybe 10/600 or so?
>
> Also, if you are short on disk space for WAL, increasing wal_files isn't
> such a great idea either.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Thanks for your help, its working now :-).
Yours:
Dario Brignardello


Tom Lane wrote:

> Dario Brignardello <dbrignar@sinectis.com> writes:
> >       checkpoint_segments = 1
> >       checkpoint_timeout = 30
>
> Reducing checkpoint_segments and checkpoint_timeout is actually
> seriously counterproductive, if your problem is amount of disk space
> chewed up by WAL logs during a long transaction (such as bulk load of
> a big table).  The WAL log cannot be truncated until the xact commits,
> so the checkpoints that happen meanwhile just cause log bloat.  Lots
> of it, because each checkpoint causes fresh copying of modified pages
> into the WAL log.
>
> Try increasing those numbers, not decreasing them.  Maybe 10/600 or so?
>
> Also, if you are short on disk space for WAL, increasing wal_files isn't
> such a great idea either.
>
>                         regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote in message
news:14951.990634138@sss.pgh.pa.us...
> Dario Brignardello <dbrignar@sinectis.com> writes:
> >       checkpoint_segments = 1
> > checkpoint_timeout = 30
>
> Reducing checkpoint_segments and checkpoint_timeout is actually
> seriously counterproductive, if your problem is amount of disk space
> chewed up by WAL logs during a long transaction (such as bulk load of
> a big table).  The WAL log cannot be truncated until the xact commits,
> so the checkpoints that happen meanwhile just cause log bloat.  Lots
> of it, because each checkpoint causes fresh copying of modified pages
> into the WAL log.
So let me know one thing, does checkpoint cause copying modified pages to
WAL log or does it just flush the modified pages to disk?
I thought WAL writings occur when pages being modified, deleted or inserted,
please correct me if I am wrong...thanx....


Murat



>
> Try increasing those numbers, not decreasing them.  Maybe 10/600 or so?
>
> Also, if you are short on disk space for WAL, increasing wal_files isn't
> such a great idea either.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)