Thanks @Yves. 🙂

You say “to speed MySQL on ZFS drastically” but all you are doing is creating more “double writes”.

ZFS has a flag ‘sync’ that can be set to sync=disabled thus completely bypassing ZIL – yes don’t do this in production

from: http://milek.blogspot.com/2010/05/zfs-synchronous-vs-asynchronous-io.html

Synchronous requests are disabled. File system transactions
only commit to stable storage on the next DMU transaction group
commit which can be many seconds. This option gives the
highest performance. However, it is very dangerous as ZFS
is ignoring the synchronous transaction demands of
applications such as databases or NFS.

Also ARC (level one) is in RAM why in the world would you make a L2ARC in shared memory which is RAM if you don’t overflow and if you do overflow you need a disk backing store, just increase the size of ARC why have double caching with LRU logic running?. Lets just say you want to use 2/3 of your RAM in a 32GB system then to get 24GB just alter options zfs zfs_arc_max=25769803776

from: https://www.zfsbuild.com/2010/04/15/explanation-of-arc-and-l2arc/

As a general rule, you want to install as much RAM
into the server as you can to make the ARC as big as
possible. At some point, adding more memory is just
cost prohibitive. That is where the L2ARC becomes
important. The L2ARC is the second level adaptive
replacement cache. The L2ARC is often called “cache
drives” in the ZFS systems.

So you want more performance (at the expense of data reliability) just set sync=disabled and then use more ram for L2ARC. The addition of shared memory images as a slog and a l2arc device while interesting isn’t practical for hitting the highest benchmark scores.

However I grant that what you did might be very useful for looking at use patterns of the devices when such devices don’t really exist.

On other comment I have yet to see any real benchmark of disk performance throughput using gzip beet lz4, yes gzip can compress more but the overhead of the gzip algorithm is much more than lz4 and can really create a bottleneck.

I wonder why you guys use gzip instead of lz4 for ZFS compression. Also for pool without SLOG, I found logbias=throughput has higher performance.

Looking forward for MySQL best practices on Linux VM running on top of ZFS ZVOL.

Modern CPU optimized CRC32 checksums are pretty cheap and I do not think it is worth to disable them (unlike Doublewrite which is expensive)

# mysql -e ‘flush tables with read lock;show master status;! zfs snapshot -r mysqldata/mysql@my_first_snapshot’

Do you call shell command from mysql client? It does not work for me (I use MariaDB).

I understand that MySQL connection can not be closed to keep the read lock, but I don’t know how to do it on shell while calling zfs command to create snapshot.