Fix for constant disk thrashing issue while uploading

ℹ️ Support
, , ,
1

Hello,

I’ve previously opened a support therad about upload speeds being very slow and causing a lot of disk thrashing, here. I’ve previously scoured the internet trying to find a solution but couldn’t figure it out. I never really got back to resolving it until very recently. With the help of Claude I was able to figure out the problem and find a solution. The original thread has been locked due to inactivity, so I am posting this thread so others may find it helpful, and also to get any feedback from other sysadmins to see if there’s something I may have missed.

It turns out that the core issue is that I’m running mariadb on a btrfs partition with CoW and checksumming enabled. This configuration is particularly problematic, because mariadb requires frequent syncing to disk to remain ACID compliant and to guard against corruption in case of power loss, and btrfs with my configuration in particular introduces a lot of latency for each disk write commit. If I upload a lot of small files, this results in a lot of logs and database updates per file, which means probably hundreds or thousands of commits. Btrfs with this configuration is particularly slow at commiting changes to disk due to its checksumming and CoW nature. As a result, each file took several seconds to commit, which meant uploading a lot of files becomes unbearably slow. Btrfs is not the right filesystem to use with a database apparently, but it’s too late for me to reformat.

To fix this without reformatting, in the mariadb configuration, I’ve set innodb_flush_log_at_trx_commit to 2. This makes it so that mariadb would commit every second, instead of every transaction. I have a UPS, so any issue caused by power loss should be unlikely. I am also a home user, so my requirements aren’t as stringent as a business. This has made a night and day difference for me. Apart from uploads, nextcloud in general has felt much faster and responsive than before as a result and it made a big difference for me.

In my case, I’m using docker, and I’ve set things up using Marius’s guide to hosting nextcloud on Synology. So to do this, I added --innodb-flush-log-at-trx-commit=2 at the end of the command: line in my docker compose file.

Of course the real fix is to put the database somewhere else, something not btrfs based. Or maybe to put it on an SSD. But this should do for now.

I hope this helps people.

2

I have a spare SSD lying arround.thinking of moving my database there now. What filesystem would you recommend for that? Ext4?

3

BTW, Gemini suggests to disable CoW for the db directory

"If you must use Btrfs, you must disable CoW on the database directory using chattr +C /var/lib/mysql"

Where the path should be your db dir.

And "No matter which filesystem you choose, you can significantly boost MariaDB performance by adjusting the mount options in your /etc/fstab:

• Use noatime: This prevents Linux from writing a timestamp every time a database file is read, eliminating unnecessary disk writes.

• Handle Write Barriers with Caution: Disabling barriers (barrier=0 on ext4 or nobarrier on older XFS) speeds up writes. However, only do this if your server has a hardware RAID controller with a Battery-Backed Unit (BBU/NVRAM) or an enterprise UPS. Otherwise, a power failure can corrupt your database."

4

If it’s an SSD, you probably won’t have the same problem I did, even if you used btrfs. But I don’t know. In my case, the btrfs filesystem was on a Synology Hybrid RAID with 5400rpm magnetic disks.. so naturally it’s going to be really slow for random read/writes compared to an SSD.

Having said that, ext4 would probably be better than btrfs for a database anyway, because ext4 is not CoW and I think this is the main issue behind the problem.

I did get this suggestion by claude as well but I couldn’t disable it. I would have to move the database to a new folder, which I didn’t want to do. Disabling CoW would probably be the better, cleaner solution if I was able to do it.

As with everything, you would know your system better so use your judgement. I just posted this in the hopes that someone would find it as a useful hint.

5

To me it (at least seemed to) disabled CoW for the db directory without issues, I did not find time to check if there was any effect thou. In my case everything is on a single USB HDD, no raid yet, but I plan to set up a raid1 with an additional HDD.