Skip to content

Instantly share code, notes, and snippets.

@artizirk
Last active September 11, 2021 17:53
Show Gist options
  • Save artizirk/e144065165b07dff1accc608c7e83f5a to your computer and use it in GitHub Desktop.
Save artizirk/e144065165b07dff1accc608c7e83f5a to your computer and use it in GitHub Desktop.
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
Copy link
Author

artizirk commented Nov 10, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment