Re: WAL archive space planning?

On Fri, Feb 17, 2017 at 1:30 PM, Ray Stell <stellr(at)vt(dot)edu> wrote:

> I was "planning" to turn on WAL archiving on a postgresql 9.4.11 server
> that currently is running with "wal_level=hot_standby" and streaming to a
> standby. I thought there would be a relationship between the rate of
> pg_xlog files and archive generation. When I turned up the
> archive_command/mode I found the scale of the archive target was wrong as I
> had based it on the pg_xlog file creation rate.
>
> When I turned on the archive command for a few minutes, pg_xlog dir
> contained these files for the time period:
>
> -rw------- 1 postgres postgres 16777216 Feb 17 06:47
> 00000001000023AC0000007F
> -rw------- 1 postgres postgres 16777216 Feb 17 06:45
> 00000001000023AC0000007E
>
> The archive command wrote 126, 16MB files:
>
> -rw------- 1 postgres postgres 16777216 Feb 17 06:47
> 000000010000237700000056
> -rw------- 1 postgres postgres 16777216 Feb 17 06:47
> 000000010000237700000055
> -rw------- 1 postgres postgres 16777216 Feb 17 06:47
> 000000010000237700000054
> -rw------- 1 postgres postgres 16777216 Feb 17 06:47
> 000000010000237700000053
> ...
>
> -rw------- 1 postgres postgres 16777216 Feb 17 06:45
> 0000000100002376000000DC
> -rw------- 1 postgres postgres 16777216 Feb 17 06:45
> 0000000100002376000000DB
> -rw------- 1 postgres postgres 16777216 Feb 17 06:45
> 0000000100002376000000DA
> -rw------- 1 postgres postgres 16777216 Feb 17 06:45
> 0000000100002376000000D9
>
> On servers that are not nearly as busy, I observe a one-to-one
> relationship between these files/rates.
>
> Is there a good WAL archive space planning guide?
>
> Is the way to collect planning data for this to turn on wal_debug?
>
> TIA!
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

Did you have an archive_timeout value set? This will cause the
archive_command to run and create a WAL file at that time interval, no
matter how little write activity there may be. These will always be 16MB as
well, but if there's no activity, they will compress down quite
significantly.

If you disable the timeout, I believe you will then get the matched rate of
pg_xlog generation for your archive command.

Keith