On Jul 8, 2009, at 2:50 AM, Yaroslav Tykhiy wrote:
> 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.
Are you sure you weren't hitting swap? IIRC prefetch tries to keep
data (disk blocks?) in memory that it fetched recently. ZFS uses quite
a bit of memory, so if you distributed all your memory to be used by
just postgres and disk cache then you didn't leave enough space for
the prefetch data and _something_ will be moved to swap.
If you're running FreeBSD i386 then ZFS requires some careful tuning
due to the limits a 32-bit OS puts on memory. I recall ZFS not being
very stable on i386 a while ago for those reasons, which has by now
been fixed as far as possible, but it's not ideal (and it likely never
will be).
You'll probably want to ask about this on the FreeBSD mailing lists as
well, they'll know much better than I do ;)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a54776e10131807247821!