ZFS sub-optimal performance with default setting

[Patrick Dung]

I have tried some tests, good and bad result is in below..........
I am sure there is some bottleneck, and the root cause is still unknown.

logbias=throughput, primarycache=all, sync=standard
Result: SQL
Key     Value     Time     Comment
Insert Time:     10000     28 s :-(     Should not take more than 5's on an average system.
Update Time:     10000     29 s :-(     Should not take more than 9's on an average system.
Select Time:     10000     8 s :-(     Should not take more than 6's on an average system.
Delete Time:     10000     24 s :-(     Should not take more than 5's on an average system. 

logbias=latency, primarycache=all,
 sync=standard
Result: SQL
Key     Value     Time     Comment
Insert Time:     10000     9 s :-(     Should not take more than 5's on an average system.
Update Time:     10000     10 s :-(     Should not take more than 9's on an average system.
Select Time:     10000     4 s :-)     Looks fine!
Delete Time:     10000     8 s :-(     Should not take more than 5's on an average system. 

logbias=latency, primarycache=all, sync=disabled
Result: SQL
Key     Value     Time     Comment
Insert Time:     10000     3 s :-)     Looks fine!
Update Time:     10000     3 s :-)   
  Looks fine!
Select Time:     10000     3 s :-)     Looks fine!
Delete Time:     10000     3 s :-)     Looks fine! 

Thanks,
Patrick

--- On Thu, 1/10/13, Charles Sprickman <spork at bway.net> wrote:

From: Charles Sprickman <spork at bway.net>
Subject: Re: ZFS sub-optimal performance with default setting
To: "Artem Belevich" <art at freebsd.org>
Cc: "Patrick Dung" <patrick_dkt at yahoo.com.hk>, "freebsd-fs" <freebsd-fs at freebsd.org>
Date: Thursday, January 10, 2013, 10:03 AM

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"