Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30136

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

Details

    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.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30447 storage/innobase/os/os0file.cc:1028:58: error: use of undeclared identifier 'O_DIRECT'

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33203 storage/innobase/os/os0file.cc doesn't compile on Solaris

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30054 debug-no-sync doesnt fully disable sync calls

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-30506 Port innodb_{log,data}_file_{buffering,write_through} usage to MariaBackup

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Task - A task that needs to be done. MDEV-32357 Make innodb_flush_method dynamic

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33379 innodb_log_file_buffering=OFF causes corruption on bcachefs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-24854 Change innodb_flush_method=O_DIRECT by default

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28766 MDEV-28111 breaks innodb_flush_log_at_trx_commit=2

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28909 Write performance not scale to NVMe SSD

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-30507 Document in KB innodb_{log,data}_file_{buffering,write_through}

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33095 innodb_flush_method=O_DIRECT creates excessive errors on Solaris

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33545 Perf regression from removing innodb_flush_method=O_DIRECT_NO_FSYNC

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33894 MariaDB does unexpected storage read IO for the redo log

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä added a comment - - edited

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

            marko Marko Mäkelä added a comment - - edited 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 ).
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            marko Marko Mäkelä added a comment -

            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.

            marko Marko Mäkelä added a comment - 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.
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Matthias Leich [ mleich ]
            mleich Matthias Leich added a comment -

            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

            mleich Matthias Leich added a comment - 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
            mleich Matthias Leich made changes -
            Assignee Matthias Leich [ mleich ] Marko Mäkelä [ marko ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            Fix Version/s 11.0.1 [ 28548 ]
            Fix Version/s 11.0 [ 28320 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            danblack Daniel Black made changes -
            greenman Ian Gilfillan made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels performance Preview_11.0 performance
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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