Skip to content

Instantly share code, notes, and snippets.

@artizirk

artizirk/postgresql_zfs.md

Last active Apr 23, 2020
Embed
What would you like to do?
Postgresql ZFS optimize https://wiki.wut.ee/sysadmin/postgresql

PostgreSQL optimize

ZFS Config

atime = off  # Recoding access time on file open is stupid
relatime = on  # writing access time on file write is fine because we are writing anyway.
compression = lz4  # Its faster to compress/decompress on the CPU than it is to wait for the data from HDD/SSD  
recordsize = 128K  # postgres native 8k will give give horrible compression ratio, default 128k is fine
primarycache = metadata  # maybe?

Each postgres database cluster gets its own dataset

Postgres config

https://pgtune.leopard.in.ua/#/

And also

full_page_writes = false

This disables double writes for data because ZFS is always consistent, ~2x insert perf boost

Optional

Set zfs commit time to 1 second

echo 1 > /sys/module/zfs/parameters/zfs_txg_timeout
echo 'options zfs zfs_txg_timeout=1' >> /etc/modprobe.d/zfs.conf

And disable postgres synchronous_commit

synchronous_commit = off

This should give ~20x perf boost but in case of system chrash you will lose up to 1 second of data Data will still always be consistent thanks to ZFS

Links

Other

@artizirk

This comment has been minimized.

Copy link
Owner Author

@artizirk artizirk commented Nov 10, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.