What is “Write Amplification”? Write amplification factor (WAF) is a numerical value that represents the amount of data a solid state storage controller has to write in relation to the amount of data that the host’s flash controller has to write. The numerical value is calculated as a rate by dividing the amount of data written to flash media by the amount of data written by the host or database server.
Nextcloud on Raspberry Pi (SD) is critical when it comes to insertion of many small data or files. MySQL is B-tree based, so is rebalancing the tree all the time for read optimisation, performance. Nominal speed on SD card is ~20 MBytes/s, for random accesses it’s just 1-2 MBytes/s or even lower, when SD cards tends to “full”.
So, MySQL simply is the wrong database technology for Nextcloud on SD card. Nextcloud needs a “flash friendly database”, which uses different algorithms. Best suited are “append only databases” with “skiplist” technology. This algorithm is highly efficient even on slow SD cards with high latency for random accesses.
Next problem is the file system (xfs, ext4) in general. Here i can recommend a “flash friendly” file system, which greatly reduces IOPS. Note: “atime” - flag is modified even when just reading from file system. My recommendation: Mount with “noatime” or better “relatime”.
Have fun!