MacOS 13.6, m1 Macbook Pro. Homebrew binaries

Possibly relevant : https://mjtsai.com/blog/2022/02/17/apple-ssd-benchmarks-and-f_fullsync/
1. fsync flushes file system cache, but not flush disk cache, on macOS. Hence, it does not guarantee durability, on macOS. Elsewhere, it flushes both filesystem and disk cache, and does garantee durability
2. The syscall that corresponds to fsync() on macOS, is fcntl(F_FULLSYNC). It is potentially much slower on macOS, than fsync anywhere else, on unknown reasons.
3. MariaDB is using fcntl(F_FULLSYNC), albeit inconsistently - e.g not inside Innodb. But it is still used in mysys, e.g for writing frm files and such . MySQL used to use it,too, in the past
4. MySQL gave up on pedantic durability on macOS, and uses non-durabile safe fsync since 10.8 https://github.com/mysql/mysql-server/commit/3cb16e9c3879d1790159d2856cb7aa0bd3d201fd

5. Since nobody is using macOS as production platform, maybe MariaDB can do that, too

The F_FULLFSYNC was actually being used in InnoDB back in 2005 (see the original commit, fixup 1 and fixup 2). I think that around that time, MySQL and InnoDB were being used as an embedded database in some proprietary products that ran on the platform.

The last traces of InnoDB F_FULLSYNC were removed in MySQL 5.7 in 2013. It looks like the code may have been effectively disabled already when MySQL moved to the CMake build system; the preprocessor symbol HAVE_DARWIN_THREADS was not defined even in MySQL 5.5 as far as I can tell.

MDEV-24105 recommends to "restore F_FULLSYNC to full glory", and in light of this one, I'm unsure.

I was drawn to this ticket initially by Marko during a November 8, 2023 slack conversation in the #engineering channel, regarding slow test performance on macOS. This ticket isn't necessarily directly related to slow test performance, but may impact it to some degree, meriting exploration. Correspondingly, I created TODO-4441 to track macOS test support. For the present issue, changing my_sync as MySQL did in https://github.com/mysql/mysql-server/commit/3cb16e9c3879d1790159d2856cb7aa0bd3d201fd makes the attached script of DDL run in about 400ms on my M1 macbook pro running macOS 13.6.3. Without this change, it takes about 5s; both measurements are consistent with the reporter's observation.
Now, with respect to our automated test suites, disabling fsync didn't have a clearly measurable speed improvement at least as indicated by running the main suite. Tests running on macOS with and with fsync disabled ran in essentially the same amount of time as the tests run with fsync enabled. Surprisingly, when compared to running an aarch64 VM on the same hardware and running under macOS, many tests ran in roughly the same time, but also many tests (like binlog_encryption) ran nearly 2x faster under the Linux VM than under macOS bare metal. I think fsync may play a smaller role in these cases than we realize. For example, tests like main.lock_sync, binlog_encryption.multisource, binlog_encryption.encryption_combo, ran nearly twice as fast and in one extreme case, main.show_explain, runs in 71s on macOS and 5s on Linux.
In the case of main.show_explain, the time is taken by extra long waits due to an improper use of wait_condition.inc, which has a prerequisite that the test set the $wait_condition variable before sourcing wait_condition.inc so that the latter has some criteria to check while waiting and before continuing. The test main.show_explain does not set this value, and that may explain why macOS is slower as perhaps that wait takes longer to clear, but why not the same behavior on Linux? I will investigate that further, but it's very likely a separate issue worthy of its own ticket.
When testing both under macOS and Linux at git sha 9e76d94ef0f3f1a8d222a23c7922f806d00777b5 (tip of 11.3 at time of testing), I used the following build flags. I didn't use 11.4's mtr as it's totally broken; I filed MDEV-33060 to capture this problem.

-DCMAKE_BUILD_TYPE=Debug \
-DCMAKE_EXPORT_COMPILE_COMMANDS=ON \
-DMYSQL_MAINTAINER_MODE=OFF \
-DWno-deprecated-declarations=1 \
-DWITH_DEVELOPER_ENTITLEMENTS=ON

Arguments to mtr under both environments (note no use of ramdisk as, while macOS supports ramdisk, macOS does not support an implementation of /dev/shm for ramdisk):

--force --max-test-fail=0 --parallel=auto --suite=main

Gosselin, I would expect some tests to run slower if they didn’t set innodb_flush_log_at_trx_commit=0 or if we set sync_binlog=1 by default (MDEV-16589). Very few tests exercise group commit; in fact, the majority of tests use a single connection of the single-threaded test client, so we should not see any group commit optimizations in action.

Did you try to create a directory on RAM disk and make mysql-test/var a symbolic link that points there? In mysql-test/mariadb-test-run.pl you could search for tmpfs_locations to find the defaults and some related parameters.

Did you experiment with F_BARRIERFSYNC, which is mentioned in MDEV-24105?

Hi marko, thank you for the flags, I will experiment with those. I successfully made a RAMdisk on macOS a few weeks back and tested with that and it was still much slower than linux; I don't have exact numbers but it wasn't competitive with Linux. I did not experiment with F_BARRIERSYNC, I will try that and see if it's viable in lieu of following MySQL's path as mentioned earlier. Thank you!