ZFS sub-optimal performance with default setting

[Patrick Dung]

Hi Artem,

Thanks for reply.

I have tried some tests, good and bad result is in below..........

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! 
Regards,
Patrick

--- On Thu, 1/10/13, Artem Belevich <art at freebsd.org> wrote:

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

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.

--Artem