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 your database 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't help 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 (usually about a 30-50% bump in write speed) as ZFS's copy-on-write scheme makes corruption impossible, and lz4 is better than what Postgres uses internally.

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

--Stephen

-----Original Message-----
From: Adam Jensen <hanzer(at)riseup(dot)net>
Sent: Friday, January 25, 2019 11:18 AM
To: Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com>; pgsql-novice(at)lists(dot)postgresql(dot)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(dot)froehlich(at)cablelabs(dot)com <mailto:s(dot)froehlich(at)cablelabs(dot)com>
>
> Tel: +1 (303) 661-3708
>
>  
>