maria-discuss team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Jure Sah <dustwolfy@xxxxxxxxx> 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.