Diagnosing (seemingly) slow writes to mariadb

[stratacast1]

At the moment I am running FreeBSD 11.1, and using FreeBSD jails on 7200rpm 4KiB block drives. The HDDs are in a mirrored ZFS pool. The database in question is a MariaDB 10.2 database, nginx web server, php 7.1 and the software on top is Nextcloud. I've asked this on the forums and their GH, but they seem to be at a loss. I wish I paid more attention to when my slow write issue occurred, but it ONLY happens with small files (20MB of small files can take 10 minutes!!). Some of the changes that occurred around until I recognized the issue was I changed from Apache to nginx, mariadb 10.1 to 10.2, and changed from 512B block drives to 4KiB block drives. When I changed drives I had to rewrite all my old drive contents to my new drives I think via resilvering (dead drives and changing to 4KiB unintentionally).

Is there a good way that I can test to see where the bottleneck is? What's odd is this only occurs from the web interface. Uploading the same small files is fine from the desktop sync client. I can supply configs if you like

 

[SirDice]

MySQL/MariaDB use their own caching, ZFS also uses caching and this typically interferes. That would be my prime suspect. There are some settings specific for MySQL/MariaDB you need to do on ZFS: https://wiki.freebsd.org/ZFSTuningGuide#MySQL

Also make sure your MariaDB caches and buffers are big enough but not too big. Too large caches causes more overhead to manage them which results in a degradation of the performance. Definitely have a look with databases/mysqltuner for example and see if everything is in check. Change some parameters based on the output, let MySQL/MariaDB run for a couple of days then check again.