I have recently been dealing with an upgrade from Debian 9 to 10 increasing MariaDB write load to ~20MB/s (from <0.5MB/s) on a near idle server.
Along the way I found that Linux tools to diagnose this situation are pretty bad compared to Windows. It’s nearly impossible to find out what files a process is writing to in MB/s, without turning to semi-commercial tools such as
Sysdig showed ibdata1 being written to at ~0.5MB/s, which was odd given that figure was roughly the write load from before the upgrade. A far cry from the 20MB/s being reported by
htop and my poor SSD’s smart statistics.
The culprit turned out to be BTRFS – specifically compression. For database or other small write workloads, it balloons the size of writes. I was led astray by
btrfs fi defrag – this tool is not truly capable of disabling compression, despite what some posts online may say. I could see in
compsize that a handful of data was still compressed, but never would have guessed that these few MB were all it took to continue to balloon the writes up!
The only approach I could find was
cp -ra on
/var/lib/mysql, delete the old one, and move the copy into its place.