Hacker News Reader

Comment by nikita2206 | Hacker News Reader

Are you finding the benefits and features of ZFS worth the perf penalty? Or maybe even its compression makes it overall a faster alternative to say ext4 for a database? I’ve considered in the past running a DB in AWS with direct nvme drive used for SLOG and for L2ARC cache while the main storage would have been a mounted EBS volume, I haven’t tried it but am still wondering if it’s a better alternative to running a DB on top of bare EBS in Amazon (it should be a lot cheaper than say io2 type volume with provisioned IOPS)

View on HN

I compared ZFS to XFS on a number of large database deployments and XFS was not uniformly faster. All databases were on fancy hardware with a bunch of U.2 SSDs, which is theoretically where XFS would be a big advantage. Postgres’ speed was very similar in both cases. Upsides of ZFS included vastly lower disk usage (with huge gains on poorly normalized databases) and also way easier/faster offsite backup than the non-ZFS Postgres backup strategy.

Faster than pgbackrest? I'd love to see the numbers you all are getting for zfs based backups/restores.

I think they utilized ZFS’ snapshotting feature which is most likely better than any DB’s backup implementation.

It’s essentially impossible to beat ZFS incremental snapshot send. It was literally like 100x faster than rsync (about 10x more bandwidth efficient and 10x higher bandwidth) when I was doing these tests.

With cloud network-based storage like EBS or pd-ssd, I doubt you will notice any IOPS-related perf penalty when running a RDBMS on top of ZFS. Assuming a mixed read/write workload, you will likely hit the disk write throughput limit first, as ZFS just writes ... a lot more, due to how blocks are stored internally: https://github.com/openzfs/zfs/issues/6584#issuecomment-3848...

On an innodb database, I get about 3x compress ratio with ZSTD compression, and ZFS still has to write about 2x more than EXT4.

There just isn't much of a perf penalty. It uses more CPU, yes, but with today's highly-threaded CPUs I can hardly tell there's a cost.

For any data you want to keep, I'd say it's well worth the performance cost. Even for data you don't want to keep, so long as it isn't absolutely performance-critical I find it still worth the cost.

> There just isn't much of a perf penalty. It uses more CPU, yes

Not in my (although small) experience with ZFS vs. databases; the bottleneck is the disk (although it's correct that ZFS is quite CPU-intensive).

Databases have their logging strategies, and ZFS has its own too. So the expense of a write operation is (in principle) doubled.

AFAIK, the typical strategy to mitigate the ZFS overhead is to use a log on a separate, fast storage ("SLOG"). On the db side, in MySQL, it's possible to disable the doublewrite buffer (which is another form of overhead, which, in theory, ZFS should avoid), but nobody really knows if that is 100% reliable or not.

> On the db side, in MySQL, it's possible to disable the doublewrite buffer (which is another form of overhead, which, in theory, ZFS should avoid), but nobody really knows if that is 100% reliable or not.

It is. Actually, that's the best thing about running MySQL on ZFS. The doublewrite buffer is not just another form of overhead, the doublewrite buffer is *the bottleneck* for any workload with moderate amount of write (until it got revamped in 8.0: https://dev.mysql.com/worklog/task/?id=5655).

> It is. Actually, that's the best thing about running MySQL on ZFS. The doublewrite buffer is not just another form of overhead

There has been an interesting discussion on the MariaDB mailing list, questioning this, called "Is disabling doublewrite safe on ZFS?"¹.

The last post has been from the InnoDB lead, saying²:

> I believe that it is technically possible for a copy-on-write filesystem like ZFS to support atomic writes, but for that to be possible in practice, the interfaces inside the kernel must be implemented in an appropriate way.

It seems even he is not 100% sure on this topic.

¹=https://lists.launchpad.net/maria-discuss/msg05205.html

²=https://lists.launchpad.net/maria-discuss/msg05219.html

It doesn't sound like the guys understand how ZFS CoW works? So let's say the machine crashed after InnoDB had written 4KB out of a 16KB data file page to the filesystem. Due to CoW, the old data file page would still be there, unmodified. In contrast, on XFS or EXT4, without the doublewrite buffer, the data file page would be corrupted.

I have a lot of respect for Marko Mäkelä, as he seems like the only one who tries to steer InnoDB away from an evolution dead end. For example, in https://jira.mariadb.org/browse/MDEV-24449, he fixed a corruption bug that had existed since the very first InnoDB commit. At the same time, he also tries to simplify the internals, unlike Oracle who just churns out features along with regressions.

But there are always more corruption bugs within InnoDB. I am not even sure the log checkpoint mechanism is sound, where it does a fsync of the data files first, before getting the checkpoint position from memory, which may have moved forward after the fsync. This behavior also exists since the very first InnoDB commit.

Thanks for the information!

> he also tries to simplify the internals, unlike Oracle who just churns out features along with regressions.

Sadly, very true. I don't know how it was on 5.7 and before, however, at least since v8.0, even patch updates have an alarm probability of breaking existing installations, due to either bugs, or subtle changes in existing functionality.

I did something similar to this and saw a significant performance boost. It was OLAP though - so very read heavy and few to no random writes.

Reply on news.ycombinator.com