ZFS sub-optimal performance with default setting

[Charles Sprickman]

On Jan 9, 2013, at 8:37 PM, Artem Belevich wrote:

> On Wed, Jan 9, 2013 at 6:31 AM, Patrick Dung <patrick_dkt at yahoo.com.hk> wrote:
>> Hi freebsd-fs!
>> 
>> I have my the original question in:
>> http://archives.postgresql.org/pgsql-performance/2013-01/msg00044.php
>> But later it was found out the bottleneck seems to be the ZFS with out a fast ZIL.
>> Please give some advise, thanks.
> 
> For database storage on ZFS it may be necessary to change ZFS record
> size to match database page size. At least that's what one of the
> things Oracle recommends for oracle database:
> http://www.oracle.com/technetwork/server-storage/solaris10/config-solaris-zfs-wp-167894.pdf
> 
> You may also check if disabling prefetching (via
> vfs.zfs.prefetch_disable=1 tunable in loader.conf) helps your
> workload.

PostgreSQL has a ton of tunables, and the performance list is a good place to start.  The archives there are full of information on zfs.

A few things off the top of my head:

-set recordsize to 8k on the pg dataset (this has to be done before you write data)
-set "full_page_writes = off" (safe on zfs, not necessarily so on ufs)
-leave RAM available for PG by limiting the max ARC size in loader.conf, tell PG how much it has left after ARC plus some slop with "effective_cache_size" (for example, if you have 64GB of RAM, maybe limit ARC to 32GB, then set effective_cache_size to 30GB or so)
-turn off atime updates

As far as general PG setup, pgtune will put you in a better place than the default config:

https://github.com/gregs1104/pgtune

Actually the stock config file is really terrible, if you haven't touched it, you're almost guaranteed to have lousy performance.

Lastly, this book is amazing - there's lots of general information that's quite useful outside of PG and general db tuning:

http://www.2ndquadrant.com/en/postgresql-90-high-performance/

If you have $200 or so laying around, slap in two Intel 320 SSDs (these survive power-loss without corruption), and make a mirrored ZIL for zfs.  That will up your TPS on write-heavy loads into at least the 20K realm.

Charles

> 
> --Artem
> _______________________________________________
> freebsd-fs at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-fs
> To unsubscribe, send any mail to "freebsd-fs-unsubscribe at freebsd.org"