Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cgGDa-0007GO-1s for pgsql-admin@arkaria.postgresql.org; Tue, 21 Feb 2017 19:35:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cgGDZ-0008HJ-Kr for pgsql-admin@arkaria.postgresql.org; Tue, 21 Feb 2017 19:35:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cgGBp-00068t-Ol for pgsql-admin@postgresql.org; Tue, 21 Feb 2017 19:33:33 +0000 Received: from omr1.cc.ipv6.vt.edu ([2607:b400:92:8300:0:c6:2117:b0e] helo=omr1.cc.vt.edu) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cgGBl-00061r-Qz for pgsql-admin@postgresql.org; Tue, 21 Feb 2017 19:33:32 +0000 Received: from mr2.cc.vt.edu (mr2.cc.ipv6.vt.edu [IPv6:2607:b400:92:8400:0:90:e077:bf22]) by omr1.cc.vt.edu (8.14.4/8.14.4) with ESMTP id v1LJXRQ8017481 for ; Tue, 21 Feb 2017 14:33:27 -0500 Received: from mail-qt0-f199.google.com (mail-qt0-f199.google.com [209.85.216.199]) by mr2.cc.vt.edu (8.14.7/8.14.7) with ESMTP id v1LJXLOw014838 for ; Tue, 21 Feb 2017 14:33:26 -0500 Received: by mail-qt0-f199.google.com with SMTP id n37so10785298qtb.7 for ; Tue, 21 Feb 2017 11:33:26 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to; bh=RPGD2ozNJKoMXVJTR9WbJo2rr9mZVWJMbckUZvAq4nQ=; b=KP9jdfkLpxYOW+x40aSyPaiJZG/qhC0L+Q8oOxFQJG91HA84vwV+t8N1VgU9T4TOe4 VzDGQZvOChrbL7PmHzVHxOTCjua1LPcNdH7oBfU9qurkSx1fZqnAAK6BxnZjlhkcTvOA WGCM3rXvTOr2Pwv0adbgywS2Qo122sqtoFu8iy3+BClNQ364RmhT8i5J9Qj/CMYBZsxZ u4CHHBwyil8/KXZ4D3g7Qrl+MxAgKzPx4GseFBrRxeGVu1VsYS5T6mpLW/KphMXikf4B tFK1xAMgtI7T5QelGEzQN683WMdGOWV578lPrTdz+ozRi9CoKWKj0C9pKWPG6k/Z11sm HvbA== X-Gm-Message-State: AMke39k9WKfuwgOP6U4QIffnPlAjrRiUi2/RZb73JifsB8oxfVWlcjK12dGJAmuUJtZDoSCVOIy0b7KTDXvTATuYb0b+aWaPA97isZcvkenUF3pKB7zB0YAvSXPscW9c7L/1HPW0inhnbWlpJWk3NFCJq/xsID1zSCzBVAQX0PJqQCPrBR+LoAJF05E+kyND4M4fjlc= X-Received: by 10.55.3.7 with SMTP id 7mr13991872qkd.299.1487705600859; Tue, 21 Feb 2017 11:33:20 -0800 (PST) X-Received: by 10.55.3.7 with SMTP id 7mr13991844qkd.299.1487705600567; Tue, 21 Feb 2017 11:33:20 -0800 (PST) Received: from stell.cns.vt.edu ([2001:468:c80:2102:15f0:2776:7ab8:9120]) by smtp.gmail.com with ESMTPSA id h124sm12732192qke.8.2017.02.21.11.33.19 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 21 Feb 2017 11:33:20 -0800 (PST) Subject: Re: WAL archive space planning? To: Keith References: <274927ff-1e85-2ac7-a5fc-68dd033c5126@vt.edu> Cc: Steven Chang , pgsql-admin From: Ray Stell Message-ID: Date: Tue, 21 Feb 2017 14:33:19 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:45.0) Gecko/20100101 Thunderbird/45.7.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------162673C76932183A0B176804" X-Spam-Status: No, score=1.0 required=5.0 tests=HTML_MESSAGE, RCVD_IN_DNSWL_NONE,RCVD_IN_MSPIKE_H3,RCVD_IN_MSPIKE_WL,SPF_SOFTFAIL autolearn=disabled version=3.4.0 X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on mr2.cc.vt.edu X-Pg-Spam-Score: -3.7 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------162673C76932183A0B176804 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 2/21/17 12:26 PM, Keith wrote: > > > On Tue, Feb 21, 2017 at 12:22 PM, Ray Stell > 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 >> >> >> 2017-02-18 2:30 GMT+08:00 Ray Stell > >: >> >> 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 >> ) >> To make changes to your subscription: >> 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. --------------162673C76932183A0B176804 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 2/21/17 12:26 PM, Keith wrote:



On Tue, Feb 21, 2017 at 12:22 PM, Ray Stell <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.

2017-02-18 2:30 GMT+08:00 Ray Stell <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)
To make changes to your subscription:
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.



--------------162673C76932183A0B176804--