ZFS prefetch considered evil?

Hi All,

I have a mid-size database (~300G) used as an email store and running
on a FreeBSD + ZFS combo. Its PG_DATA is on ZFS whilst xlog goes to a
different FFS disk. ZFS prefetch was enabled by default and disk time
on PG_DATA was near 100% all the time with transfer rates heavily
biased to read: ~50-100M/s read vs ~2-5M/s write. A former
researcher, I was going to set up disk performance monitoring to
collect some history and see if disabling prefetch would have any
effect, but today I had to find out the difference the hard way.
Sorry, but that's why the numbers I can provide are quite approximate.

Due to a peak in user activity the server just melted down, with mail
data queries taking minutes to execute. As the last resort, I
rebooted the server with ZFS prefetch disabled -- it couldn't be
disabled at run time in FreeBSD. Now IMAP feels much more responsive;
transfer rates on PG_DATA are mostly <10M/s read and 1-2M/s write; and
disk time stays way below 100% unless a bunch of email is being
inserted.

My conclusion is that although ZFS prefetch is supposed to be adaptive
and handle random access more or less OK, in reality there is plenty
of room for improvement, so to speak, and for now Postgresql
performance can benefit from its staying just disabled. The same may
apply to other database systems as well.

Thanks,
Yar