Comments on: Taking a Look at BTRFS for MySQL

By: Ghislain

Ghislain — Fri, 20 Jan 2023 14:05:25 +0000

In reply to Yves Trudeau. i agree on that :) would be interesting to see how kernel 6.1 works on this as it has async by default and had several improvements in perf. ubuntu package are ok now https://kernel.ubuntu.com/~kernel-ppa/mainline/v6.1.7/

By: Ghislain

Ghislain — Fri, 20 Jan 2023 13:54:25 +0000

In reply to bachtiar. modern filesystem say they get rid of partition and those old paradigm. You cannot then tell that for this you need ext4, for that xfs, and for static file only btrfs. no one will want to manage machines with 10 partition, one formated for each use, modern FS should work for most of them if not all :) and i agree that snapshot is a must.

By: Yves Trudeau

Yves Trudeau — Fri, 20 Jan 2023 13:47:42 +0000

In reply to Rudi Servo. Without COW and compression, why would anyone use Btrfs with a database, those are the strong selling points for such workload. Since Btrfs is still under heavy development, I'll certainly revisit in the future but I'll enable COW and compression. I'll keep your proposed settings for then.

By: Yves Trudeau

Yves Trudeau — Fri, 20 Jan 2023 13:40:00 +0000

In reply to bachtiar. COW shouldn't be an issue with databases, ZFS performs well with databases. Maybe the COW implementation of Btrfs needs improvements. Snapshots are quite an important feature for database so COW has to be enabled.

By: bachtiar

bachtiar — Thu, 19 Jan 2023 15:31:43 +0000

I’m surprised that the author did not mention the elephant in the room. Btrfs is a copy-on-write filesystem. As such, it perfectly suited for snapshots and deduplication, but not at all for database workloads. If you are using btrfs it for databases, it is essential that you turn copy-on-write off.

By: Rudi Servo

Rudi Servo — Sun, 11 Sep 2022 11:35:04 +0000

There are a number of things that you might test in btrfs.
newer kernels or ubuntu 22.04.
space_cache=v2 (has to be used the 1st time with clear_cache)
compress=zstd
ssd, discard=async (do not know if this has any influence in AWS)
autodefrag

You can also turn off COW on a folder with chattr +C , it needs to be empty and compression will not work.
autodefrag seems to have a regression on kernel 5.16, fixed on 5.17.

By: MrCal

MrCal — Wed, 26 Jan 2022 00:24:18 +0000

In reply to Josef Bacik. Is integrity (bit-rot protection) not of any concern in regards to MySQL? That would be my most important reason to run btrfs with MySQL.Never ever had integrity issues with legacy storage (ext4) myself, but I'm getting more and more influenced by what I read about how often other people experience bit-rot issues and that they are being fixed by btrfs

By: Yves Trudeau

Yves Trudeau — Fri, 21 Jan 2022 16:52:15 +0000

In reply to Lukey. for btrfs and zfs I used: innodb_flush_method fsync

By: Lukey

Lukey — Thu, 20 Jan 2022 16:41:14 +0000

In reply to Yves Trudeau. Also, you should disable direct-io(/use buffered io) in mysql for btrfs. This way btrfs will actually compress writes and you should get better performance. ZFS just ignores direct-io btw.

By: ghislain

ghislain — Fri, 07 Jan 2022 08:20:34 +0000

In reply to Josef Bacik. well when you design a filesystem to make all integrated with no funcky partitions anymore, no raid blind system and complete ease of use of the storage compare to legacy systemes previously made of 3/4 moving parts working badly together ( lvm on mdam on ext4 ?) but in the end you have to manage fixed partition with different filesystems and one btrfs one xfs over mdadm you completly loose the purpose to have btrfs or ZFS in the first place :) Of course at meta size you dont have the issue because your mysql will be on another machine or such but for smaller player with lamp on one machine it a real bummer not to be able to use btrfs everywhere :) anyway thanks for all the work you put on btrfs and i hope you will be able to make progress on this part !

By: Yves Trudeau

Yves Trudeau — Thu, 06 Jan 2022 13:56:45 +0000

Thanks Joseph, I’ll check your presentations.

By: Yves Trudeau

Yves Trudeau — Thu, 06 Jan 2022 13:52:36 +0000

In reply to Ghislain. I'll try without autodefrag but I am afraid it will make the compression ratio worse.

By: Josef Bacik

Josef Bacik — Wed, 05 Jan 2022 19:12:15 +0000

Random over-write workloads suck on Btrfs. If you’ve ever seen me give talks about Btrfs it’s the first thing I talk about. We specifically don’t use Btrfs for our mysql workloads inside of Meta. Things like RocksDB work significantly better for Btrfs, so MyRocks would probably perform much better, but a lot of people don’t use that. But generally, yes random write workloads are a weakness of Btrfs, we are aware, and we generally recommend using something else if that’s what your workload requires.

You can do things to mitigate the pain. Firstly using “-o autodefrag” is going to lead to sadness for mysql, because basically every write is going to trigger a defrag operation, which is going to slow you way down. Secondly you can use “chattr +C /var/lib/mysql” to disable COW for data files. We recommend this for things like virt images (this is what Fedora does for it’s qemu images directory if you have btrfs), and for people that really want to use mysql on btrfs. However if you take a snapshot it’ll still trigger a COW on the first write after the snapshot, so you can still induce the pain of COW despite turning it off for specific files.

Eventually we will get around to these workloads and see if we can improve them, but generally speaking if you are running mysql you don’t care about the fancy things that Btrfs brings you, you just want the file system to get out of the way. xfs and ext4 are perfect for that, so generally we recommend using that instead of btrfs for that style of workload.

By: Ghislain

Ghislain — Wed, 05 Jan 2022 06:45:45 +0000

In reply to Ghislain. from the doc autodefrag should not be used for databases: autodefrag noautodefrag (since: 3.0, default: off) Enable automatic file defragmentation. When enabled, small random writes into files (in a range of tens of kilobytes, currently it’s 64K) are detected and queued up for the defragmentation process. Not well suited for large database workloads. The read latency may increase due to reading the adjacent blocks that make up the range for defragmentation, successive write will merge the blocks in the new location.

By: Ghislain

Ghislain — Tue, 04 Jan 2022 14:39:18 +0000

thanks a lot for this test.

you should use a better hash algorithm as crc32 is not recommended.

mkfs.btrfs –checksum xxhash

But i dont think it will change a lot of thing. I dont think autodefrag is a good idea as it seems it will launch an action at each write. We use here a btrfs balance cron for this in the btrfs-tools package.

For the kernel, perhaps you could try after installing

linux-image-5.14.0-1011-oem linux-modules-5.14.0-1011-oem

as its in the ubuntu repo

best regards
ghislain