Postgresql on ZFS

AGLT2 has been running Postgresql on top of ZFS on our head01.aglt2.org (dCache headnode) for more than 1 year. Recently we came across an interesting presentation on Postgresql on ZFS best practices which provided some good details about how to better tune ZFS for use by Postgresql. In the next section we will cover what was changed

AGLT2 head01.aglt2.org Tuning

The head01.aglt2.org node is running dCache 4.2.6-SNAPSHOT, Scientific Linux 6.10 and ZFS 0.7.9. The system has 64 GB of ram and two Intel P3600 NVMe 800GB cards to host the Postgresql 9.5 database.

ZFS setup a mirror of the two NVMe cards and presents a pool of about 744GB for use.

root@head01 dcache]# zpool status pgsql

pool: pgsql state: ONLINE

scan: scrub repaired 0B in 0h2m with 0 errors on Fri May 29 15:33:25 2015

config:
NAME STATE READ WRITE CKSUM

pgsql ONLINE 0 0 0

mirror-0 ONLINE 0 0 0

nvme0n1 ONLINE 0 0 0

nvme1n1 ONLINE 0 0 0

The original relevant ZFS settings were:

[root@head01 dcache]# zfs get atime,compression,primarycache,recordsize pgsql/9.3

NAME PROPERTY VALUE SOURCE

pgsql/9.3 atime on local

pgsql/9.3 compression lz4 local

pgsql/9.3 primarycache all local

pgsql/9.3 recordsize 8K local

We changed the atime to 'off', primarycache to 'metadata' and recordsize to '16K'.

We also change the ZFS modue parameter zfs_txg_timeout which controls how often (in seconds) data is flushed to disk, from its default of 5 to 1:

root@head01 dcache]# cat /sys/module/zfs/parameters/zfs_txg_timeout

5

[root@head01 dcache]# echo 1 > /sys/module/zfs/parameters/zfs_txg_timeout

[root@head01 dcache]# echo 'options zfs zfs_txg_timeout=1' >> /etc/modprobe.d/zfs.conf

When then reconfigured Postgresql to turn off synchronous commits (risking up to 1 second of data loss):

[root@head01 dcache]# su - postgres

-bash-4.1$ psql -c 'ALTER SYSTEM SET synchronous_commit=off'

ALTER SYSTEM

The last change was to set the ZFS logbias to 'throughput' instead of 'latency':

[root@head01 dcache]# zfs set logbias=throughput pgsql/9.3

-- Main.ShawnMcKee - 21 Aug 2018