MDEV-32777 DDL operations very slow under macOS

[DaveGosselin-MariaDB]

macOS is a development platform for our product but calls to fcntl(F_FULLFSYNC) are too slow for developer needs. macOS offers at least three different disk flush calls, making tradeoffs between reliability and performance (see analysis notes on this MDEV). On macOS only and as a compromise, we will back out the change to call fcntl(F_FULLFSYNC), sacrificing a full flush in favor of more performant writes.

[CLAassistant]

Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you sign our Contributor License Agreement before we can accept your contribution.
_{You have signed the CLA already but the status is still pending? Let us recheck it.}

[anson1014]

In the description of MDEV-32777, I can see very clearly the times listed to run the attached DDL script in macOS and how it is much slower than other platforms.

Just a suggestion, but perhaps you can run the same script with your changes and then list the time taken to further your case for how this change will be more performant?

[DaveGosselin-MariaDB]

Hi @anson1014 there is a comment on the ticket with results but it's limited to Developers. The relevant part you ask about is:

For the attached DDL SQL script, and the following configurations, see the below performance figures (git sha 70de4075a18c12ed7e55db9a49e66eb6cff308b2).
innodb_flush_log_at_trx_commit=1, F_FULLFSYNC: ~5.0s
innodb_flush_log_at_trx_commit=0, F_FULLFSYNC: ~4.7s
innodb_flush_log_at_trx_commit=1, F_BARRIERFSYNC: ~2.2s
innodb_flush_log_at_trx_commit=0, F_BARRIERFSYNC: ~2.1s
innodb_flush_log_at_trx_commit=1, fsync: ~300ms
innodb_flush_log_at_trx_commit=0, fsync: ~300ms

[anson1014]

Thanks for that, I see now where you mentioned the results on the Jira. Looks good!

[vuvova]

why couldn't you simply set innodb_flush_log_at_trx_commit=0 ? That would be as fast or may be even faster, wouldn't it?

[DaveGosselin-MariaDB]

why couldn't you simply set innodb_flush_log_at_trx_commit=0 ? That would be as fast or may be even faster, wouldn't it?

I attempted that as well. Please see my data and results in comment (restricted to Developers): https://jira.mariadb.org/browse/MDEV-32777?focusedCommentId=277114&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-277114

[vuvova]

I'm uncomfortable with the idea of disabling durability by default. Surely it's not what users expect.

For testing purposes you can use --debug-no-sync like mtr does.

my_sync() could use F_BARRIERFSYNC in some cases (MYF flag could control that), but they appear to be rare and it's too easy to make a mistake here and request a too weak barrier.