Comments on: MySQL/ZFS Performance Update

By: Kamil

Kamil — Fri, 09 Dec 2022 13:22:38 +0000

Thank you for publishing your results.

I think that to be more fair you should reduce innodb buffer by 8GB in case of ZFS (to use same amount of RAM).

I have performed similar tests with 36GB database:

1GB innodb buffer + 4GB ARC on ZFS (about 5GB RAM total)5GB innodb buffer for ext4 (about 5GB RAM total)I removed skip-log-bin in my configurations, because I need it, and my server has 8k recordsize set in ZFS on whole ZFS pool (I don’t remember why).

Resuts for ZFS are still much better than EXT4 (zfs is faster by 50%) and I have no issues with latency on ZFS at all, actually it has lower latency than setup with ext4.

And the compression on ZFS is outstanding. My database uses only ~50% of space on ZFS.

By: Ivan Baldo

Ivan Baldo — Mon, 03 Jan 2022 16:29:26 +0000

In reply to Yves Trudeau. But what happens with files modification times? Isn't lazytime useful for that? Thanks!

By: Eric Robinson

Eric Robinson — Sun, 02 Jan 2022 03:18:40 +0000

Yves, here’s a question for you. We’re using ZFS zvols as the backing devices for DRBD volumes, The filesystems we create on top of DRBD could be either ZFS, EXT4, or XFS. I’m thinking ZFS on top of DRBD is not the best idea because ZFS normally wants to talk directly to the drives, so I guess DRBD would defeat that. That leaves EXT4 or XFS.If we go with one of those, which of your sample my.cnf configs would we use for testing?

By: Eric Robinson

Eric Robinson — Wed, 15 Dec 2021 12:51:34 +0000

In reply to Yves Trudeau. Hi Yves, I ran the sysbench test on ZFS at 2500 scale. Resulting graph is here: https://www.dropbox.com/s/2mblcsk0hg6f0bb/zfs-at-scale.png?dl=0 The numbers are not terribly different from the test at 200 scale, about 70 TPS less on average, but I'm not sure what that means. By the way, did you catch my original statement that there will be 100+ separate instances of MySQL on the server, not just 100+ databases? Only a portion of the server's total resources will be allocated to each instance. Obviously the conventional wisdom of 70% server RAM for the buffer pool is off the table.

By: Yves Trudeau

Yves Trudeau — Mon, 13 Dec 2021 17:28:30 +0000

yes

By: Eric Robinson

Eric Robinson — Mon, 13 Dec 2021 17:24:29 +0000

In reply to Yves Trudeau. You mean the sysbench --scale parameter?

By: Yves Trudeau

Yves Trudeau — Mon, 13 Dec 2021 17:09:47 +0000

In reply to Eric Robinson. 100+ instances with an average size of above 20GB will certainly fits within the sweet spot of ZFS. If you want to validate the configuration you should retry the benchmark with a size of 2000 or maybe even 2500 (instead of 200). That will give you a better idea.

By: Eric Robinson

Eric Robinson — Mon, 13 Dec 2021 16:58:02 +0000

In reply to Yves Trudeau. I performed a default install of percona-server-server.x86_64 8.0.26-16.1.el8 and made no changes beyond the ones in your procedure. Regarding the ARC stats, I see.. c 4 125549541504 cached_only_in_progress 4 0 c_max 4 540636465152 c_min 4 33789779072 size 124531475032 > ZFS would start to show its value with a dataset size greater than the memory size. Ultimately, that describes the situation I will have. The server will be home to 100+ separate instances of MySQL with customized configurations, none of which will enjoy the benefit of the server's full resources. The databases will range in size from 10 GB to 400 GB, most around 20-25. GB. All instances will run with buffer pools that are as small as is feasible.

By: Yves Trudeau

Yves Trudeau — Mon, 13 Dec 2021 16:32:33 +0000

In reply to Eric Robinson. That is very interesting, Given the 1TB of RAM, and the 189GB of uncompressed data, all the dataset fits in memory, either in the buffer pool or in the file cache. In such a case, I am not surprised ext4 outperforms ZFS. The dataset is quickly cached so the amount of reads drops leaving only the writes as a limiting factor. Writes are more expensive in ZFS because of compression. ZFS would start to show its value with a dataset size greater than the memory size. Were you using an ACID configuration? What size have you used for the ARC with 1TB of RAM?

By: Eric Robinson

Eric Robinson — Mon, 13 Dec 2021 14:20:24 +0000

Clearly the glitch was was with me, not the forum. 🙂

By: Eric Robinson

Eric Robinson — Mon, 13 Dec 2021 14:18:53 +0000

In reply to Eric Robinson. If the link does not work when clicking it, try pasting it into a browser manually. That worked for me.

By: Eric Robinson

Eric Robinson — Mon, 13 Dec 2021 01:20:36 +0000

Hi Yves, I repeated your test on local hardware and compared the results to Ext4 with InnoDB table compression. See the following graph.

https://www.dropbox.com/s/e6si0fm9i5a9d5o/zfs-vs-ext4.png?dl=0

I believe I followed your steps exactly. The test system is a Dell PowerEdge R640 with 48 cores, 1 TB RAM, and 6 x Dell 3.2 TB Enterprise NVME drives. On the ZFS test, all 6 drives are in one zpool but I created separate zfs filesystems for data and logs, per your instructions. For the Ext4 test, I created a single LVM RAID array with 6 stripes (4K block size), but the the data and logs are on the same LV. Even so, Ext4 dramatically outperformed ZFS.

The compression ratio on ZFS was much better. The test database, which is 189 GB uncompressed, ended up being 92 GB with InnoDB table compression, but only 59 GB on ZFS.

Something to note is that IOWAIT averaged about 2% during the Ext4 test, but was around 0-0.5% on the ZFS test.
Another interesting finding is that performance was only slightly impacted when I used a much smaller buffer pool. With an 8 GB pool (versus 26 GB), average performance on ZFS only dropped by about 100 TPS. I have not tried that with Ext4 yet.

These results are disappointing because I really want to use ZFS, but now I am not sure I can justify it. It is possible that I missed something. You’re welcome to examine the test system if you are interested.

By: Dan

Dan — Fri, 27 Aug 2021 18:19:38 +0000

In reply to Yves Trudeau. Here is a recent benchark with bttrfs, xfs, ext4 and f2fs. It also includes some basic mariadb benchmarks, but notice that this site is known to use default settings, they don't do any finetuning, so probably btrfs have CoW enable for the mariadb tests. https://www.phoronix.com/scan.php?page=news_item&px=Linux-5.14-File-Systems

By: Alex Samorukov

Alex Samorukov — Wed, 21 Jul 2021 19:19:06 +0000

Thank you, great article. Just curious, did you ever tried to repeat same test on FreeBSD? They now sharing OpenZFS code, so kernel will be only difference. I can try to reproduce that…

By: Yves Trudeau

Yves Trudeau — Wed, 21 Jul 2021 16:23:38 +0000

In reply to Domas Mituzas. Why is that weird? In most common cases, ext4 and xfs behave similarly, especially in a cloud based environment where the iops are limited to a few 1000s.

By: Domas Mituzas

Domas Mituzas — Tue, 20 Jul 2021 21:16:41 +0000

People are using ext4 for databases? weird!

By: Yves Trudeau

Yves Trudeau — Fri, 16 Jul 2021 20:30:37 +0000

In reply to Dan. I'll look into btrfs in a near future.

By: Dan

Dan — Wed, 14 Jul 2021 00:59:29 +0000

In reply to Yves Trudeau. btrfs improved a lot and usually only most advance features like raid5/6 are still broken and others need more fine-tune, but unless you need some advance features, usage is stable and with good performance (usually a little slower, some times lot slower, other even faster than ext4) check https://btrfs.wiki.kernel.org/index.php/Status#Overview notice that nodatacow may be required to avoid over fragmentation of the DB. i remember that some sqlite optimizations where made, dunno if also apply to mysql/mariadb

By: Yves Trudeau

Yves Trudeau — Tue, 13 Jul 2021 17:53:05 +0000

In reply to Ivan Baldo. ext4 was mounted with the noatime option, which is commonly used with MySQL.

By: Yves Trudeau

Yves Trudeau — Tue, 13 Jul 2021 17:50:54 +0000

In reply to Camille Huot (@CamilleHuot). If the storage is very low, the compression/uncompression overhead of ZFS will likely become the bottleneck however, even if a bit slower, ZFS will store much more data. Another thing to add to the todo list.