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.
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 collectd, 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.
Leave a Reply