[MDEV-24854] Change innodb_flush_method=O_DIRECT by default - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Fix Version/s: 10.6.0
Component/s: Storage Engine - InnoDB
Labels:
- performance

Description

We have innodb_use_native_aio=ON by default since the introduction of that parameter in MariaDB 5.5. However, to really benefit from the setting, the files should be opened in O_DIRECT mode, to bypass the file system cache. In this way, the reads and writes can be submitted with DMA, using the InnoDB buffer pool directly, and no processor cycles need to be used for copying data.

The setting O_DIRECT should be equivalent to the old default innodb_flush_method=fsync in other aspects. Only the file system cache will be bypassed.

Note: innodb_flush_method=O_DIRECT in combination with a tiny innodb_buffer_pool_size may cause a significant performance regression, because we will no longer be able to take advantage of the file system cache of the operating system kernel. The InnoDB buffer pool will completely replace it. Affected users should configure innodb_flush_method=fsync.

This change will not affect Microsoft Windows. The default there is innodb_flush_method=unbuffered, which is roughly equivalent to O_DIRECT.

Attachments

Issue Links

causes

MDEV-33095 innodb_flush_method=O_DIRECT creates excessive errors on Solaris

Closed

relates to

MDEV-26040 os_file_set_size() may not work on O_DIRECT files

Closed

MDEV-27772 Performance regression with default configuration in 10.6

Open

MDEV-28766 MDEV-28111 breaks innodb_flush_log_at_trx_commit=2

Closed

MDEV-28909 Write performance not scale to NVMe SSD

Stalled

MDEV-30136 Map innodb_flush_method to new settable Booleans innodb_{log,data}_file_{buffering,write_through}

Closed

MDEV-30986 Slow full index scan in 10.6 vs 10.5 for the (slow) I/O-bound case

Closed

MDEV-32067 InnoDB linear read ahead had better be logical

Confirmed

MDEV-32357 Make innodb_flush_method dynamic

Closed

MDEV-33379 innodb_log_file_buffering=OFF causes corruption on bcachefs

Closed

MDEV-16526 Overhaul the InnoDB page flushing

Closed

MDEV-23855 InnoDB log checkpointing causes regression for write-heavy OLTP

Closed

MDEV-27900 aio returning partial reads/writes looks like InnoDB: Database page corruption on btrfs with io_uring

Closed

(8 relates to)

Activity

Ascending order - Click to sort in descending order

View 4 older comments

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 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

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-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 - 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.

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.

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2021-02-12 14:49

Updated:: 2024-02-27 09:56

Resolved:: 2021-02-20 10:05

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.