Re: WAL on zfs Settings - Mailing list pgsql-novice

How do I get out of this list?

Avec mes meilleures salutations / Bescht Gréiss / Mit freundlichen Grüssen /  Kind regards

Laurent Asorne
Agence Immobilière Abby TOUSSAINT
32 81 73 41
laurent@confiance.lu
www.confiance.lu

> On 12 Feb 2019, at 17:39, Stephen Froehlich <s.froehlich@cablelabs.com> wrote:
>
> 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-inityour 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'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(usually about 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
yourpostgresql.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
>>
>>
>>
>
>