[nycbug-talk] zfs/pgsql brain dump

[Pete Wright]

On 09/04/13 16:55, Charles Sprickman wrote:
> Some random thoughts I jotted down during the presentation:
>
thanks charles - really sad i couldn't make this meeting today :/

> *set the zfs recordsize to 8K on a filesystem (eg: /var/db/pgsql) so zfs blocks match db blocks

fantastic advice.  was recently doing some burn-in testing and was 
caught by zfs defaul recordsize being 128k blocks, adjusting my tests to 
use 128k blocks more than doubled my aggregate throughput.

lesson: blocksizes do matter!

> *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.

+1 on the intl 320 SSD cards

> *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
>
+1 on Greg's high perf psql book!

=pete


-- 
Pete Wright
pete at nomadlogic.org
twitter => @nomadlogicLA