Content-Type: multipart/alternative;
 boundary="------------mUywwr8g1ZD3cE0EGPb6BZ0R"
Message-ID: <1dd2bcec-c145-b65b-21b8-0e391b05e8e4@gmail.com>
Date: Thu, 31 Mar 2022 19:09:24 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Subject: Re: WAL & ZFS
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <FC215502-166A-49DB-B3DA-D08727A34C0B@elevated-dev.com>
 <43EEBE2E-4C7F-4F35-99BF-4DA8B5D3828A@crazybean.net>
 <35c98a4b-f7a1-a896-4484-582bbaaffb5b@jakobs.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: <35c98a4b-f7a1-a896-4484-582bbaaffb5b@jakobs.com>
Archived-At: 
 <https://www.postgresql.org/message-id/1dd2bcec-c145-b65b-21b8-0e391b05e8e4%40gmail.com>
Precedence: bulk

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

On 3/31/22 18:47, Holger Jakobs wrote:
> The WAL is a journal itself and doesn't need another journal for 
> safety. Therefore, a common recommendation is using ext2 (which has no 
> journal) for the WAL partition.
>
> Is this correct?
>
> Am 31.03.22 um 23:32 schrieb Rui DeSousa:
>> I would recommend a separate pg_wal filesystem with the record size 
>> to match the WAL page size; in my case 16k.  I have keep the default 
>> record size at 128k for the data volume and that configuration has 
>> worked well for supporting large DSS while using 16k data blocks.
>>
>>> On Mar 30, 2022, at 5:32 PM, Scott Ribe 
>>> <scott_ribe@elevated-dev.com> wrote:
>>>
>>> I've read all the info I could find re running PG on ZFS: turn off 
>>> full page writes, turn on lz4, tweak recordsize so as to take 
>>> advantage of compression, etc. One thing I haven't seen is whether a 
>>> separate volume for WAL would benefit from a larger recordsize. Or 
>>> any other tweaks???
>>>
>>> -- 
>>> Scott Ribe
>>> scott_ribe@elevated-dev.com
>>> https://www.linkedin.com/in/scottribe/
>>>
>>>
>>>
>>>
>>>
>>
>>
Journallng file systems journal file operations like open, close, 
create, extend, rename or delete, not file block operations. File blocks 
are not protected by file system journalling, just the inodes. The file 
system journal prevents you from losing files in case of sudden machine 
crash, like when the machine loses power. It has nothing to do with the 
block change journaling, which is the role of WAL files. WAL files 
record the changes to database blocks. With ext2, it would be possible 
to lose a WAL log in case of a sudden crash and that might prevent the 
cluster recovery. File system journaling has nothing to do with WAL 
logs. I would strongly advise against using ext2 for WAL logs.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------mUywwr8g1ZD3cE0EGPb6BZ0R
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 3/31/22 18:47, Holger Jakobs wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:35c98a4b-f7a1-a896-4484-582bbaaffb5b@jakobs.com">The WAL
      is a journal itself and doesn't need another journal for safety.
      Therefore, a common recommendation is using ext2 (which has no
      journal) for the WAL partition.
      <br>
      <br>
      Is this correct?
      <br>
      <br>
      Am 31.03.22 um 23:32 schrieb Rui DeSousa:
      <br>
      <blockquote type="cite">I would recommend a separate pg_wal
        filesystem with the record size to match the WAL page size; in
        my case 16k.  I have keep the default record size at 128k for
        the data volume and that configuration has worked well for
        supporting large DSS while using 16k data blocks.
        <br>
        <br>
        <blockquote type="cite">On Mar 30, 2022, at 5:32 PM, Scott Ribe
          <a class="moz-txt-link-rfc2396E" href="mailto:scott_ribe@elevated-dev.com">&lt;scott_ribe@elevated-dev.com&gt;</a> wrote:
          <br>
          <br>
          I've read all the info I could find re running PG on ZFS: turn
          off full page writes, turn on lz4, tweak recordsize so as to
          take advantage of compression, etc. One thing I haven't seen
          is whether a separate volume for WAL would benefit from a
          larger recordsize. Or any other tweaks???
          <br>
          <br>
          --
          <br>
          Scott Ribe
          <br>
          <a class="moz-txt-link-abbreviated" href="mailto:scott_ribe@elevated-dev.com">scott_ribe@elevated-dev.com</a>
          <br>
          <a class="moz-txt-link-freetext" href="https://www.linkedin.com/in/scottribe/">https://www.linkedin.com/in/scottribe/</a>
          <br>
          <br>
          <br>
          <br>
          <br>
          <br>
        </blockquote>
        <br>
        <br>
      </blockquote>
    </blockquote>
    <p>Journallng file systems journal file operations like open, close,
      create, extend, rename or delete, not file block operations. File
      blocks are not protected by file system journalling, just the
      inodes. The file system journal prevents you from losing files in
      case of sudden machine crash, like when the machine loses power.
      It has nothing to do with the block change journaling, which is
      the role of WAL files. WAL files record the changes to database
      blocks. With ext2, it would be possible to lose a WAL log in case
      of a sudden crash and that might prevent the cluster recovery.
      File system journaling has nothing to do with WAL logs. I would
      strongly advise against using ext2 for WAL logs.</p>
    <p>Regards<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------mUywwr8g1ZD3cE0EGPb6BZ0R--