Jure Sah <dustwolfy@gmail.com> writes:
It would appear that on a typical webserver, the majority of disk i/o belongs to the MariaDB database process. It would appear it's mostly waiting on fsync(), which to my understanding is executed at least once per commit.
Group commit can amortise this if there are multiple commits in parallel, but essentially yes.
I've also noticed in the documentation that the options to control fsync usage are even more limited than in the MySQL server. They are also very strongly argued against. Considering the point that InnoDB is considered to be in an inconsistent state in any event, so long as the server is not cleanly stopped, is there really justification for such strong opposition here?
Usually you can just set --sync-binlog=0 --innodb-flush-log-at-trx-commit=2 and most fsync should be gone.
I understand that this is extensively researched in the documentation and it has to do with the recovery of data in case of an unexpected server reboot.
InnoDB should recover safely in any case. But if binlog is enabled, the binlog is likely to become out of sync with the data inside innodb (in case of kernel crash/power outage. Mariadb process crash by itself is not enough to break recovery). So if the server is a replication master, slaves might need to be rebuilt. Whatever is argued in one place or another, the better approach is to read docs on what each option actually does, and make your own trade-off, in this case between performance and recoverability. Which is exactly what you did, concluding that running without fsync is the right choice in your setup. Hope this helps, - Kristian.