[MDEV-30136] Map innodb_flush_method to new settable Booleans innodb_{log,data}_file_{buffering,write_through} Created: 2022-11-30  Updated: 2024-02-05  Resolved: 2023-01-11

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 11.0.1

Type: Task Priority: Blocker
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: Preview_11.0, performance

Issue Links:
Problem/Incident
causes MDEV-30447 storage/innobase/os/os0file.cc:1028:5... Closed
causes MDEV-33203 storage/innobase/os/os0file.cc doesn'... Closed
Relates
relates to MDEV-30054 debug-no-sync doesnt fully disable sy... Closed
relates to MDEV-30506 Port innodb_{log,data}_file_{bufferin... Open
relates to MDEV-32357 Make innodb_flush_method dynamic Open
relates to MDEV-33379 innodb_log_file_buffering=OFF causes ... In Progress
relates to MDEV-24854 Change innodb_flush_method=O_DIRECT b... Closed
relates to MDEV-28766 MDEV-28111 breaks innodb_flush_log_at... Closed
relates to MDEV-28909 Write performance not scale to NVMe SSD Stalled
relates to MDEV-30507 Document in KB innodb_{log,data}_file... Closed
relates to MDEV-33095 innodb_flush_method=O_DIRECT creates ... Closed

 Description   

Starting with MDEV-28766 in 10.8, O_DIRECT can be enabled on the redo log:

SET GLOBAL innodb_log_file_buffering=OFF;

The parameter innodb_flush_method is confusing and could not be set without restarting the server. Its allowed values are as follows:

  • fsync (0, SRV_FSYNC): the default before MDEV-24854: use file system cache and explicit fdatasync() or fsync()
  • O_DSYNC (1, SRV_O_DSYNC): O_DIRECT on data files, and enable O_DSYNC on the redo log
  • littlesync (2, SRV_LITTLESYNC): like O_DIRECT_NO_FSYNC, but using the file system cache (unsafe!)
  • nosync (3, SRV_NOSYNC): like littlesync, but do not invoke fsync() or fdatasync() on the log file
  • O_DIRECT (4, SRV_O_DIRECT): like fsync but bypassing the file system cache for data files (default)
  • O_DIRECT_NO_FSYNC (5, SRV_O_DIRECT_NO_FSYNC): like O_DIRECT, but do not call fsync() or fdatasync() on the data files (unsafe in some cases; see this comment in MDEV-24854)

Let us deprecate the parameter innodb_flush_method and map it to 4 Boolean parameters that can be changed with SET GLOBAL while the server is running:

  • innodb_log_file_buffering (disable O_DIRECT, added by MDEV-28766 in 10.8.4, 10.9.2)
  • innodb_data_file_buffering (disable O_DIRECT on data files)
  • innodb_log_file_write_through (enable O_DSYNC on the log)
  • innodb_data_file_write_through (enable O_DSYNC on persistent data files)

3 of the above 4 Boolean parameters would be added in this task.

 Comments   
Comment by Marko Mäkelä [ 2022-12-07 ]

The deprecated parameter innodb_flush_method will be mapped to existing or added settable Boolean parameters as follows:

innodb_flush_method innodb_log_file_buffering⁰ innodb_log_file_write_through innodb_data_file_buffering innodb_data_file_write_through
fsync, littlesync, nosync, normal¹ - OFF ON OFF
O_DSYNC OFF ON OFF ON
O_DIRECT, O_DIRECT_NO_FSYNC, unbuffered¹, async_unbuffered¹ - - - -

⁰ innodb_log_file_buffering was added in MDEV-28766 (MariaDB Server 10.8.4).
¹ The options normal, unbuffered, async_unbuffered are available on Microsoft Windows only.

The mark - in the cells means that there will not be any assignment to the parameter.

The default values of the substituting parameters are as follows:

parameter default value
innodb_log_file_buffering OFF (ON if innodb_flush_log_at_trx_commit=2)
innodb_log_file_write_through OFF
innodb_data_file_buffering OFF
innodb_data_file_write_through OFF

Previously, it was only possible to enable write-through for the log but not for the data files.

There calls to fsync() or datasync() operations can be disabled by setting the parameter debug_no_sync (MDEV-30054).

Comment by Marko Mäkelä [ 2022-12-08 ]

The SET GLOBAL logic for modifying the Boolean parameter innodb_log_file_write_through will use the same mechanism that was already tested in MDEV-28766 for innodb_log_file_buffering and in MDEV-27812 for innodb_log_file_size. I would not expect surprises there.

The logic for changing innodb_data_file_buffering and innodb_data_file_write_through is new. We invoke the function fil_space_t::reopen_all(), which will attempt to close and reopen each data file in the configured mode. Already closed files will be skipped. It could theoretically cause serious denial of service. In my tests in an extra connection that was issuing SET GLOBAL while Sysbench was preparing a server or running a workload on RAM disk or fast NVMe storage, it worked rather well. It could be a good idea to test it further with innodb_open_files=10 and a large number of tables or partitions.

Comment by Matthias Leich [ 2022-12-13 ]

origin/bb-10.11-new-innodb-defaults c434f870a346fb11a6b5932dbdf860ed7e2d2f74 2022-12-12T10:05:22+02:00
which contains MDEV-29986, MDEV-19506, MDEV-29694, MDEV-30136, MDEV-29983
performed well in RQG testing. No new problems

Generated at Thu Feb 08 10:13:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.