ZFS sub-optimal performance with default setting

[Artem Belevich]

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