I have a ZFS mirror of 2 4TB HDDs with 4KiB block sectors on a FreeBSD 11.1 box (OS on an SSD). On that zpool (iocage) I have a multitude of jails. I noticed over time I was getting very poor I/O performance for MariaDB, particularly with writes. My application using Maria is Nextcloud. I put MariaDB in its own zfs dataset inside the jail so I could change its primary cache to just be metadata only since I read that is supposed to be handled fine by the database itself, and it's just redundant at that point. I proceeded to run ioping on that dataset, and also a directory in the dataset for the root of the jail, and found there was a stark difference in performance.
For iocage/jails/nextcloud/root:
68.4 k requests completed in 2.99 s, 16.7 GiB read, 22.9 k iops, 5.58 GiB/s
generated 68.4 k requests in 3.00 s, 16.7 GiB, 22.8 k iops, 5.56 GiB/s
min/avg/max/mdev = 39.5 us / 43.7 us / 283.0 us / 6.16 us
For iocage/jails/nextcloud/mysql:
1.66 k requests completed in 3.00 s, 415.5 MiB read, 554 iops, 138.5 MiB/s
generated 1.66 k requests in 3.00 s, 415.8 MiB, 553 iops, 138.5 MiB/s
min/avg/max/mdev = 39.3 us / 1.81 ms / 326.3 ms / 11.0 ms
As you can see, the IO is VERY low in comparison, despite being on the same disks. Doing a ZFS get all, the only differences in properties are the mysql dataset has lz4 compression enabled and the primarycache to metadata. Will these changes that dramatically hamper performance or is there a deeper issue here? My slow db writes happened even before I put its contents in a different dataset
For iocage/jails/nextcloud/root:
68.4 k requests completed in 2.99 s, 16.7 GiB read, 22.9 k iops, 5.58 GiB/s
generated 68.4 k requests in 3.00 s, 16.7 GiB, 22.8 k iops, 5.56 GiB/s
min/avg/max/mdev = 39.5 us / 43.7 us / 283.0 us / 6.16 us
For iocage/jails/nextcloud/mysql:
1.66 k requests completed in 3.00 s, 415.5 MiB read, 554 iops, 138.5 MiB/s
generated 1.66 k requests in 3.00 s, 415.8 MiB, 553 iops, 138.5 MiB/s
min/avg/max/mdev = 39.3 us / 1.81 ms / 326.3 ms / 11.0 ms
As you can see, the IO is VERY low in comparison, despite being on the same disks. Doing a ZFS get all, the only differences in properties are the mysql dataset has lz4 compression enabled and the primarycache to metadata. Will these changes that dramatically hamper performance or is there a deeper issue here? My slow db writes happened even before I put its contents in a different dataset