[zfs-discuss] zfs databse optimization

Fajar A. Nugraha list at fajar.net
Sat Dec 31 03:02:02 EST 2016

On Sat, Dec 31, 2016 at 3:35 AM, Richard Elling via zfs-discuss <
zfs-discuss at list.zfsonlinux.org> wrote:

> FWIW, not much has changed in the fundamentals since we published detailed
> results from the PAE org
> at Sun. The best practices remain pertinent. YMMV.
> http://assets.en.oreilly.com/1/event/21/Optimizing%20MySQL%
> 20Performance%20with%20ZFS%20Presentation.pdf
> https://blogs.oracle.com/video/entry/optimizing_mysql_performance_with_zfs
Innodb doublewrite has slipped under my radar so far. Thanks for the tip,
Gordan, Richard.

> On additional comment, based on years of looking at workloads and
> recordsize.
> The penalty of RMW can be less than the gains from using larger blocks.
> Avoid
> the extremes and you can often find that a range of
> recordsize/volblocksize can
> be a good pick. For example, with 8k nominal blocksize workloads, 4k
> physical
> disks, and compression, you might find 16k recordsize works best. A good
> rule
> of thumb is to avoid the extremes (512, 128k) and work with the middle
> (8k, 16k, 32k)

Personally, if the application doesn't need foreign key support, I simply
toss innodb aside and use tokudb storage engine instead (available on
mariadb and percona variant). The main difference of tokudb vs innodb, from
zfs tuning perspective, is recordsize and compression: since
tokudb_block_size is 4MB (uncompressed), and it has its own compression
(zlib by default), simply use the default zfs recordsize (128k) and
compression (off).

I haven't tested using zfs large block size support though (e.g. 1MB).
Something interesting to try next time.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.zfsonlinux.org/pipermail/zfs-discuss/attachments/20161231/87db44f4/attachment.html>

More information about the zfs-discuss mailing list