Re: WAL on zfs Settings
Laurent Asorne <[email protected]> Wed, 13 Feb 2019 00:03:11 +0100
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Message-ID | <[email protected]> |
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 [email protected] www.confiance.lu > On 12 Feb 2019, at 17:39, Stephen Froehlich <[email protected]> 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-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 <[email protected]> > Sent: Friday, January 25, 2019 11:18 AM > To: Stephen Froehlich <[email protected]>; [email protected] > 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^® ^ >> >> >> [email protected] <mailto:[email protected]> >> >> Tel: +1 (303) 661-3708 >> >> >> > >