From: Ray Stell <stellr@vt.edu>
To: Keith <keith@keithf4.com>
Cc: Steven Chang <stevenchang1213@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: WAL archive space planning?
Date: Tue, 21 Feb 2017 14:33:19 -0500
Message-ID: <b9b322c6-308b-65bf-22dd-776bee44efd9@vt.edu> (raw)
In-Reply-To: <CAHw75vs+oAnOhGhmiSUS7AahHZDLg-U4Dv7wqJiXncBkoed-gA@mail.gmail.com>
References: <f757ab05-c6d8-07f4-dac7-4394dde2bf85@vt.edu>
<CAEJt7k0udN2-FGMsY3Su23uBLNmPsdFUS_4SFMtKq8ja4gzznw@mail.gmail.com>
<274927ff-1e85-2ac7-a5fc-68dd033c5126@vt.edu>
<CAHw75vs+oAnOhGhmiSUS7AahHZDLg-U4Dv7wqJiXncBkoed-gA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 2/21/17 12:26 PM, Keith wrote:
>
>
> On Tue, Feb 21, 2017 at 12:22 PM, Ray Stell <stellr@vt.edu
> <mailto:stellr@vt.edu>> wrote:
>
> 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.
>
>
>
> Ray,
>
> Did you see my previous response about the archive_timeout setting?
Lost your post somehow, but I see it in the list archive. Thanks, Keith.
There was a 30 minute timeout set, but I would not think that would
increase the archive file generation as observed, does it?
" When this parameter is greater than zero, the server will switch to a
new segment file whenever this many seconds have elapsed since the last
segment file switch, and there has been any database activity, including
a single checkpoint. (Increasing checkpoint_timeout will reduce
unnecessary checkpoints on an idle system.) "
Your post seems to indicate having a non-zero value might increase the
number of archive files, is that so? Maybe it is in milliseconds which
might make sense with the observed archive rate, but the doc says
seconds and it was set to 1800. I can set it to 0 and see if it changes
things, but I'm a little confused.
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, keith@keithf4.com, stevenchang1213@gmail.com
Subject: Re: WAL archive space planning?
In-Reply-To: <b9b322c6-308b-65bf-22dd-776bee44efd9@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