Ok, there was significant IO and IO waits and I tweaked and tested a lot. I also used the mysqltuner script to get ideas about what I still could improve.
Apparently, MySQL updates to the oc_filecache table where the main cause for this.
The only setting which really made a difference was to set
to 0 or 2. Even with this setting, it’s still quite slow, but some orders of a magnitude faster and the IO load caused by MySQL got reduced significantly.
(Addendum: It looks like davfs2 or maybe the specific combination of davfs2 and NextCloud WebDAV has to be blamed for the terrible performance. Using Windows Explorer or Konqueror “webdavs://” URLs, access to and navigation through WebDAV is actually quite fast, comparable to the NextCloud web interface. I didn’t perform any transfer / throughput tests, though.) Not sure what davfs2 is doing there. :-/ But maybe it shouldn’t be recommended as a client in the documentation then…)
Apparently, NextCloud executes many micro-transactions if WebDAV operations are performed, which really hit the DB server. The impact of this probably also has to do with the DB’s backend storage’s characteristics. In my case, the storage is backed by a synced DRBD (replicated/distributed block device) device, which probably is close to the worst case scenario if it comes to fsync latency/performance, as the block device has to wait for confirmation from the mirror before reporting “success”.
As I understand, the drawback of the DB config adjustment I did is the loss of guaranteed ACID integrity in case of a kernel / system crash, which also does not sound like a good solution. For the oc_filecache table alone, this probably would not matter too much, but the innodb_flush_log_at_trx_commit setting affects the whole MariaDB server with all databases running on it, so it really appears to be a big compromise to me…
Are there any other options to optimize oc_filecache performance? I wonder if I’m the only one who experiences those performance issues - or maybe noone actually uses WebDAV access to the files managed by NextCloud? Though this also sounds very unlikely to me, so I would expect configuration hints or howtos already in the official documentation (“What to do to actually get usable WebDAV performance from NextCloud”) or at least in many third-party blogs or the like, but this doesn’t seem to be the case…