Daniel Black added a comment - 2022-01-24 03:45 btrfs test 5.15.14-200.fc35.x86_64 $ dd if=/dev/zero of=../btrfs.blk bs=1M count=2K 2048+0 records in 2048+0 records out 2147483648 bytes (2.1 GB, 2.0 GiB) copied, 1.50241 s, 1.4 GB/s   $ sudo losetup --direct-io=on -f ../btrfs.blk   $ sudo mkfs.btrfs /dev/loop6 btrfs-progs v5.15.1 See http://btrfs.wiki.kernel.org for more information.   Performing full device TRIM /dev/loop6 (2.00GiB) ... NOTE: several default settings have changed in version 5.15, please make sure this does not affect your deployments: - DUP for metadata (-m dup) - enabled no-holes (-O no-holes) - enabled free-space-tree (-R free-space-tree)   Label: (null) UUID: c364f0a2-b9a9-4b13-a21a-7639fd896765 Node size: 16384 Sector size: 4096 Filesystem size: 2.00GiB Block group profiles: Data: single 8.00MiB Metadata: DUP 102.38MiB System: DUP 8.00MiB SSD detected: yes Zoned device: no Incompat features: extref, skinny-metadata, no-holes Runtime features: free-space-tree Checksum: crc32c Number of devices: 1 Devices: ID SIZE PATH 1 2.00GiB /dev/loop6   $ sudo mount /dev/loop6 /mnt/   $ sudo chown dan: /mnt/   $ scripts/mysql_install_db --no-defaults --srcdir=$OLDPWD --builddir=$PWD --datadir=/mnt/dd Installing MariaDB/MySQL system tables in '/mnt/dd' ... OK   $ sql/mysqld --no-defaults --skip-networking --datadir=/mnt/dd --verbose 2022-01-24 14:35:49 0 [Note] sql/mysqld (server 10.8.0-MariaDB) starting as process 41741 ... 2022-01-24 14:35:49 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2022-01-24 14:35:49 0 [Note] InnoDB: Number of transaction pools: 1 2022-01-24 14:35:49 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions 2022-01-24 14:35:49 0 [Note] InnoDB: Using liburing 2022-01-24 14:35:49 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB 2022-01-24 14:35:49 0 [Note] InnoDB: Completed initialization of buffer pool 2022-01-24 14:35:49 0 [Note] InnoDB: 128 rollback segments are active. 2022-01-24 14:35:49 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2022-01-24 14:35:49 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ... 2022-01-24 14:35:49 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB. 2022-01-24 14:35:49 0 [Note] InnoDB: 10.8.0 started; log sequence number 42173; transaction id 14 2022-01-24 14:35:49 0 [Note] InnoDB: Loading buffer pool(s) from /mnt/dd/ib_buffer_pool 2022-01-24 14:35:49 0 [Note] Plugin 'FEEDBACK' is disabled. 2022-01-24 14:35:49 0 [Note] InnoDB: Buffer pool(s) load completed at 220124 14:35:49 2022-01-24 14:35:49 0 [Note] sql/mysqld: ready for connections. Version: '10.8.0-MariaDB' socket: '/tmp/mysql.sock' port: 0 Source distribution   $ client/mariadb -S /tmp/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.8.0-MariaDB Source distribution   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [(none)]> show global variables like 'innodb_flush_method%'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | innodb_flush_method | O_DIRECT | +---------------------+----------+ 1 row in set (0.002 sec)   $ echo $SYSBENCH sysbench /usr/share/sysbench/oltp_update_index.lua --mysql-socket=/tmp/mysql.sock --mysql-user=dan --mysql-db=test --percentile=99 --tables=2 --table_size=2000000   $ $SYSBENCH prepare sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)   Creating table 'sbtest1'... Inserting 2000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... Creating table 'sbtest2'... Inserting 2000000 records into 'sbtest2' Creating a secondary index on 'sbtest2'...   $ $SYSBENCH --rand-seed=42 --rand-type=uniform --max-requests=0 --time=60 --report-interval=5 --threads=2 run sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)   Running the test with following options: Number of threads: 2 Report intermediate results every 5 second(s) Initializing random number generator from seed (42).     Initializing worker threads...   Threads started!   [ 5s ] thds: 2 tps: 99.77 qps: 99.77 (r/w/o: 0.00/99.77/0.00) lat (ms,99%): 30.26 err/s: 0.00 reconn/s: 0.00 [ 10s ] thds: 2 tps: 90.14 qps: 90.14 (r/w/o: 0.00/90.14/0.00) lat (ms,99%): 31.94 err/s: 0.00 reconn/s: 0.00 [ 15s ] thds: 2 tps: 98.88 qps: 98.88 (r/w/o: 0.00/98.88/0.00) lat (ms,99%): 48.34 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 2 tps: 62.60 qps: 62.60 (r/w/o: 0.00/62.60/0.00) lat (ms,99%): 89.16 err/s: 0.00 reconn/s: 0.00   [ 25s ] thds: 2 tps: 32.98 qps: 32.98 (r/w/o: 0.00/32.98/0.00) lat (ms,99%): 186.54 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 2 tps: 61.20 qps: 61.20 (r/w/o: 0.00/61.20/0.00) lat (ms,99%): 54.83 err/s: 0.00 reconn/s: 0.00 [ 35s ] thds: 2 tps: 65.19 qps: 65.19 (r/w/o: 0.00/65.19/0.00) lat (ms,99%): 44.98 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 2 tps: 72.01 qps: 72.01 (r/w/o: 0.00/72.01/0.00) lat (ms,99%): 41.85 err/s: 0.00 reconn/s: 0.00 [ 45s ] thds: 2 tps: 65.19 qps: 65.19 (r/w/o: 0.00/65.19/0.00) lat (ms,99%): 40.37 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 2 tps: 54.21 qps: 54.21 (r/w/o: 0.00/54.21/0.00) lat (ms,99%): 211.60 err/s: 0.00 reconn/s: 0.00 [ 55s ] thds: 2 tps: 78.00 qps: 78.00 (r/w/o: 0.00/78.00/0.00) lat (ms,99%): 38.94 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 2 tps: 64.59 qps: 64.59 (r/w/o: 0.00/64.59/0.00) lat (ms,99%): 62.19 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 4226 other: 0 total: 4226 transactions: 4226 (70.39 per sec.) queries: 4226 (70.39 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)   General statistics: total time: 60.0297s total number of events: 4226   Latency (ms): min: 7.73 avg: 28.40 max: 481.22 99th percentile: 95.81 sum: 120025.38   Threads fairness: events (avg/stddev): 2113.0000/0.00 execution time (avg/stddev): 60.0127/0.01

Daniel Black added a comment - 2022-01-24 05:27 btrfs in above test also succeded when wihout directio without direct io $ sudo losetup --direct-io=off -f ../btrfs.blk   $ sudo mount /dev/loop6 /mnt/   $ losetup -a /dev/loop1: []: (/var/lib/snapd/snaps/multipass_5317.snap) /dev/loop6: []: (/home/dan/repos/btrfs.blk) survived reinstall and sysbench prepare and innodb_flush_method=O_DIRECT

Marko Mäkelä added a comment - 2022-03-18 13:55 For MDEV-28111 (MariaDB Server 10.8.3), I tested O_DIRECT writes of the InnoDB redo log ib_logfile0 with and without O_DSYNC on 3 non-FUA devices today: a SATA HDD, SSD, and a PCIe NVMe drive. In my test, O_DSYNC was slower on the HDD, and slightly faster on the SSD and NVMe drives. According to https://lwn.net/Articles/400541/ Linux should work correctly on devices that lack FUA support. The unsafety claim that wlad made matches the situation before 2010: https://linux-scsi.vger.kernel.narkive.com/yNnBRBPn/o-direct-and-barriers

Marko Mäkelä added a comment - 2022-09-01 05:38 MDEV-28766 (starting with MariaDB Server 10.8.4) allows O_DIRECT to be enabled or disabled on the InnoDB write-ahead log file ib_logfile0 on Linux and Microsoft Windows: SET GLOBAL innodb_log_file_buffering= OFF ; SET GLOBAL innodb_log_file_buffering= ON ;

Marko Mäkelä added a comment - 2024-02-27 09:56 At least on bcachefs, the use of fcntl(fd, F_SETFL, O_DIRECT) can lead to data corruption. In MDEV-33379 (to be released as part of MariaDB Server 10.11.8, 11.0.6) the code was refactored so that instead of invoking fcntl(2) , we pass the O_DIRECT flag to the open(2) system call.