Re: Postgresql 9.4 and ZFS?

Hi,

On 09/30/2015 12:21 AM, Patric Bechtel wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi Benjamin,
>
> if you're using compression, forget about that. You need to
> synchronize the ashift value to the internal rowsize of you SSD,
> that's it. Make sure your SSD doesn't lie to you regarding writing
> blocks and their respective order. In that case you might even choose
> to set sync=disabled. Also, set atime=off and relatime=on. For faster
> snapshot transfers, you might like to set the checksum algo to
> SHA256.

What is "SSD rowsize". Do you mean size of the internal pages?

FWIW I've been doing extensive benchmarking of ZFS (on Linux), including
tests of different ashift values, and I see pretty much no difference
between ashift=12 and ashift=13 (4k vs 8k).

To show some numbers, these are pgbench results with 16 clients:

type scale ashift=12 ashift=13 rsize=8k logbias
----------------------------------------------------------------
ro small 53097 53159 53696 53221
ro medium 42869 43112 47039 46952
ro large 3127 3108 27736 28027
rw small 6593 6301 6384 6753
rw medium 1902 1890 4639 5034
rw large 561 554 2168 2585

small=150MB, medium=2GB, large=16GB (on a machine with 8GB of RAM)

The tests are "adding" the features, i.e. the columns are actually:

* ashift=12
* ashift=13
* ashift=13 + recordsize=8kB
* ashift=13 + recordsize=8kB + logbias=throughput

I've also done a few runs with compression, but that reduces the
performance a bit (understandably).

>
> As always, put zfs.conf into /etc/modprobe.d with
>
> options spl spl_kmem_cache_slab_limit=16384
> options zfs zfs_arc_max=8589934592
>
> you might want to adjust the zfs_arc_max value to your liking. Don't
> set it to more than 1/3 ofyour RAM, just saying.

Why? My understanding is that ARC cache is ~ page cache, although
implemented differently and not as tightly integrated with the kernel,
but it should release the memory when needed and such. Perhaps not
letting it to use all the RAM is a good idea, but 1/3 seems a bit too
aggressive?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services