From: Ray Stell <stellr@vt.edu>
To: Steven Chang <stevenchang1213@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: WAL archive space planning?
Date: Tue, 21 Feb 2017 12:22:33 -0500
Message-ID: <274927ff-1e85-2ac7-a5fc-68dd033c5126@vt.edu> (raw)
In-Reply-To: <CAEJt7k0udN2-FGMsY3Su23uBLNmPsdFUS_4SFMtKq8ja4gzznw@mail.gmail.com>
References: <f757ab05-c6d8-07f4-dac7-4394dde2bf85@vt.edu>
<CAEJt7k0udN2-FGMsY3Su23uBLNmPsdFUS_4SFMtKq8ja4gzznw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 2/21/17 12:09 AM, Steven Chang wrote:
> check this, his 2nd part introduces Stream Replication Implementation
> and tell you px_log retention and wal archive related parameters.
> https://www.itenlight.com/blog/2016/05/19/PostgreSQL+HA+with+pgpool-II+-+Part+2
> <https://www.itenlight.com/blog/2016/05/19/PostgreSQL+HA+with+pgpool-II+-+Part+2;
>
> 2017-02-18 2:30 GMT+08:00 Ray Stell <stellr@vt.edu
> <mailto:stellr@vt.edu>>:
>
> 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@postgresql.org
> <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
> <http://www.postgresql.org/mailpref/pgsql-admin;
>
>
I'm interested in why I might observed a dramatic difference between
pg_xlog files and the archive target files. Other dbs I see have them
pretty much one-to-one, but not in this case here. What might cause the
large variation? I got only a few pg_xlogs files each minute and 42
archive files/min.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: stellr@vt.edu, stevenchang1213@gmail.com
Subject: Re: WAL archive space planning?
In-Reply-To: <274927ff-1e85-2ac7-a5fc-68dd033c5126@vt.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox