RE: WAL on zfs Settings

Hi Adam,

Sorry this got sent to my Spam folder and I just happened to check it this morning.

Start with this slide deck ... https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices

Sean advocates small block sizes and turning off all error checking in the database, but I don't expect you to re-init
yourdatabase to make the latter happen.  For a typical database workload with lots of small writes, having
"recordsize=16K"for the zfs volume in question might be quite helpful.  For my application, I have found that it
doesn'thelp much, nor does it hurt other than it hinders compression some. 

The big helper you can do now is that you can safely set "full_page_writes = off" & "wal_compression = off" for the WAL
(usuallyabout a 30-50% bump in write speed) as ZFS's copy-on-write scheme makes corruption impossible, and lz4 is
betterthan what Postgres uses internally. 

The other big helper is having a nice, fast ZIL SLOG.

--Stephen



-----Original Message-----
From: Adam Jensen <hanzer@riseup.net>
Sent: Friday, January 25, 2019 11:18 AM
To: Stephen Froehlich <s.froehlich@cablelabs.com>; pgsql-novice@lists.postgresql.org
Subject: Re: WAL on zfs Settings

Hi, Stephen.

I also use PostgreSQL with a ZFS filesystem (FreeBSD). Will you describe your ZFS setup and the relevant parts of your
postgresql.conf?


On 1/4/19 8:24 AM, Stephen Froehlich wrote:
> I am rebuilding my database server, and one of the novice mistakes I
> made the first time around is not creating distinct zfs filesystems
> for different tablespaces and of course the WAL. The database is for
> analysis work, which often means large data load-ins.  (Backups are
> purely occasional pg_dumps.)
>
>  
>
> My question is what are the optimal zfs settings for the WAL
> filesystem (block size, who does compression, etc.)?  The underlying
> hardware will be 3 NVME SSDs in a raidz configuration.  (I do already
> know that I can turn off full_page_writes for WAL on zfs.)
>
>  
>
> Thanks,
>
> Stephen
>
>  
>
> ----------------------------------------------------------------------
> --
>
> Stephen Froehlich
> Sr. Strategist, *Cable*Labs^® ^
>
>
> s.froehlich@cablelabs.com <mailto:s.froehlich@cablelabs.com>
>
> Tel: +1 (303) 661-3708
>
>  
>