Subject: Re: WAL archive space planning?
To: Steven Chang <stevenchang1213@gmail.com>
References: <f757ab05-c6d8-07f4-dac7-4394dde2bf85@vt.edu>
 <CAEJt7k0udN2-FGMsY3Su23uBLNmPsdFUS_4SFMtKq8ja4gzznw@mail.gmail.com>
Cc: pgsql-admin@postgresql.org
From: Ray Stell <stellr@vt.edu>
Message-ID: <274927ff-1e85-2ac7-a5fc-68dd033c5126@vt.edu>
Date: Tue, 21 Feb 2017 12:22:33 -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: 
 <CAEJt7k0udN2-FGMsY3Su23uBLNmPsdFUS_4SFMtKq8ja4gzznw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------7EA1AD9C1272CA4BB822838E"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------7EA1AD9C1272CA4BB822838E
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

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.

--------------7EA1AD9C1272CA4BB822838E
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <p>On 2/21/17 12:09 AM, Steven Chang wrote:<br>
    </p>
    <blockquote
cite="mid:CAEJt7k0udN2-FGMsY3Su23uBLNmPsdFUS_4SFMtKq8ja4gzznw@mail.gmail.com"
      type="cite">
      <div dir="ltr"><span style="font-size:17.5px">check this, his 2nd
          part introduces Stream Replication Implementation and tell you
          px_log retention and wal archive related parameters.</span>
        <div style="font-size:17.5px"><a moz-do-not-send="true"
href="https://www.itenlight.com/blog/2016/05/19/PostgreSQL+HA+with+pgpool-II+-+Part+2"
            target="_blank">https://www.itenlight.com/<wbr>blog/2016/05/19/PostgreSQL+HA+<wbr>with+pgpool-II+-+Part+2</a></div>
      </div>
      <div class="gmail_extra"><br>
        <div class="gmail_quote">2017-02-18 2:30 GMT+08:00 Ray Stell <span
            dir="ltr">&lt;<a moz-do-not-send="true"
              href="mailto:stellr@vt.edu" target="_blank">stellr@vt.edu</a>&gt;</span>:<br>
          <blockquote class="gmail_quote" style="margin:0 0 0
            .8ex;border-left:1px #ccc solid;padding-left:1ex">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.<br>
            <br>
            When I turned on the archive command for a few minutes,
            pg_xlog dir contained these files for the time period:<br>
            <br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:47
            00000001000023AC0000007F<br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:45
            00000001000023AC0000007E<br>
            <br>
            The archive command wrote 126, 16MB files:<br>
            <br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:47
            000000010000237700000056<br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:47
            000000010000237700000055<br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:47
            000000010000237700000054<br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:47
            000000010000237700000053<br>
            ...<br>
            <br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:45
            0000000100002376000000DC<br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:45
            0000000100002376000000DB<br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:45
            0000000100002376000000DA<br>
            -rw------- 1 postgres postgres 16777216 Feb 17 06:45
            0000000100002376000000D9<br>
            <br>
            On servers that are not nearly as busy, I observe a
            one-to-one relationship between these files/rates.<br>
            <br>
            Is there a good WAL archive space planning guide?<br>
            <br>
            Is the way to collect planning data for this to turn on
            wal_debug?<br>
            <br>
            TIA!<span class="HOEnZb"><font color="#888888"><br>
                <br>
                <br>
                <br>
                <br>
                -- <br>
                Sent via pgsql-admin mailing list (<a
                  moz-do-not-send="true"
                  href="mailto:pgsql-admin@postgresql.org"
                  target="_blank">pgsql-admin@postgresql.org</a>)<br>
                To make changes to your subscription:<br>
                <a moz-do-not-send="true"
                  href="http://www.postgresql.org/mailpref/pgsql-admin"
                  rel="noreferrer" target="_blank">http://www.postgresql.org/mail<wbr>pref/pgsql-admin</a><br>
              </font></span></blockquote>
        </div>
        <br>
      </div>
    </blockquote>
    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.   <br>
  </body>
</html>

--------------7EA1AD9C1272CA4BB822838E--