Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.60.0.1.1\))
Subject: Re: WAL & ZFS
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <4fe1d719-4f08-7b10-90c3-43514453757d@gmail.com>
Date: Thu, 31 Mar 2022 20:54:11 -0600
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <FB7F0E59-578C-4BF5-8049-F746F7ACAF6E@elevated-dev.com>
References: <FC215502-166A-49DB-B3DA-D08727A34C0B@elevated-dev.com>
 <4fe1d719-4f08-7b10-90c3-43514453757d@gmail.com>
To: Mladen Gogala <gogala.mladen@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/FB7F0E59-578C-4BF5-8049-F746F7ACAF6E%40elevated-dev.com>
Precedence: bulk

> I would consider Linux ZFS only for toy databases that do not hold any =
serious data.

We have tested performance extensively, and ZFS performance is fine for =
our database (5TB, something like 100GB/day data inserted and deleted). =
I would consider hardly anything else for serious data, since hardly =
anything else deals properly with bit rot.

A few comments on the articles:

> Phoronix has tested ZFS against Ext3, Ext4 and XFS. ZFS was =
consistently performing worse than all other file systems. Here is the =
test with Oracle:
>=20
> https://blog.docbert.org/oracle-on-zfs/
>=20
> Here are several articles that caution against ZFS:
>=20
> =
https://forums.servethehome.com/index.php?threads/very-slow-zfs-raidz2-per=
formance-on-truenas-12.33094/

One person opens the thread saying their performance is terrible. Others =
say their performance is OK and give tuning hints. OP says "Block size =
has been settled to 128Kb, I just use it for too much everything to go =
too big or too small. so here we sit." Which, wow. You can create as =
many ZFS file systems as you want on a pool, with different recordsizes. =
Seriously, create a file system for your database configured for your =
database.=20

> =
https://serverfault.com/questions/791154/zfs-good-read-but-poor-write-spee=
ds

Post is 5 years old, well before lz4 compression was in OpenZFS, and lz4 =
is key to getting decent DB write performance. Also note that poster was =
testing against an iSCSCI block device with no idea whatsoever about the =
actual disk configuration behind it.

> =
https://www.phoronix.com/scan.php?page=3Darticle&item=3Dubuntu1910-ext4-zf=
s&num=3D3

This shows ZFS being slower for SQLite, faster for RocksDB, no tests of =
PostgreSQL. Also, out-of-the-box config with no attempt to tune for =
database use.

> And finally, this: https://storytime.ivysaur.me/posts/why-not-zfs/


Wow. This is an absolutely terrible post. It is filled with so much =
misinformation it would take an entire long post to deconstruct all the =
errors and biases.