[nycbug-talk] zfs/pgsql brain dump

[Charles Sprickman]

Some random thoughts I jotted down during the presentation:

*set the zfs recordsize to 8K on a filesystem (eg: /var/db/pgsql) so zfs blocks match db blocks
*full_page_writes = off is safe on zfs (or any other COW filesystem) and gives some performance gains
*reduce the ARC to leave some RAM for PG, on a 32GB host, I have this in loader.conf: vfs.zfs.arc_max="16G"
*for managing streaming replication, repmgr is great (http://www.repmgr.org/); it simplifies swapping a master/slave and creating new slaves down to a few commands
*pgpool (http://pgpool.net/) is nice for pooling and load balancing over a number of slaves, but the FreeBSD port is very out of date, grab the source, hope a few linuxisms are now fixed
*Someone asked about ZIL on SSDs, this is a very quick and dirty benchmark on two $80 SATA drives and two $150 Intel 320 SSDs for ZIL:  https://ns.morefoo.com/zil/pgbench_w_sys/ - we use this on slaves to save some cash, masters are all SSD.  The main drawback with SSD ZIL is it does nothing for reads.
*SSDs in general, keep spares, if you have no budget for a few dozen SAS drives, having a ton of hot spares is still orders of magnitude cheaper.  Intel 320s are safe as are their new semi-enterprise line (can't remember the model at the moment, ask me if you're curious and I'll dig it up)
*SSD safety and notes on battery/cap backed SSDS: http://blog.2ndquadrant.com/intel_ssd_now_off_the_sherr_sh/
*pgtune is awesome if you need some quick guidance on tuning: https://github.com/gregs1104/pgtune
*Two books that are awesome, particularly Greg Smith's tuning book which has excellent explanations of hardware:  http://www.packtpub.com/postgresql-90-high-performance/book and http://www.packtpub.com/postgresql-9-admin-cookbook/book

Feel free to argue with any of the above. :)

Charles