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

Make innodb_flush_method dynamic

Details

    Description

      The new default value of innodb_flush_method, O_DIRECT, is not the best for some kinds of load, like queries requiring large scans (see MDEV-30986) in 10.6, while it's the best for small data changes. That is, depending on the instance role, the optimal value may be different.

      Now consider the case when instance changes the role (like replica promoted to master after the crash of current master, or due to maintenance needed). It would really help to have a way to change this setting without a restart (and further warming up etc), to provide better HA, while keeping the best performance for the new role.

      It will also help to speed up and simplify testing the impact of different innodb_flush_method values on the load, adapt on the fly etc. We let buffer pool to be resized without a restart, but can not control this aspect of writing it's content to disk. This is not logical, hence this request.

      Attachments

        Issue Links

          relates to

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

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-30136 Map innodb_flush_method to new settable Booleans innodb_{log,data}_file_{buffering,write_through}

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I do not think we can allow innodb_flush_method to be changed to any allowed value while the server is running. Most values, yes, but not all of them. Let us review how this was refactored in MDEV-30136:

            innodb_flush_method innodb_data_file_buffering innodb_data_file_write_through
            fsync, littlesync, nosync, normal¹ ON OFF
            O_DSYNC 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 two columns of parameters related to log writes have been omitted, because would depend on the file format change MDEV-14425 and are therefore not applicable to MariaDB Server 10.6.

            Because we do not want to change the meaning of some values in a GA release, I believe that we must disallow SET_GLOBAL innodb_flush_method=…; to or from the values:

            • littlesync
            • nosync
            • O_DIRECT_NO_FSYNC
              In MDEV-30136 the meanings of these parameters were revised (together with deprecating the parameter innodb_flush_method). That was acceptable in a new major release but not within a GA release.

            The revised table of allowed dynamic values of innodb_flush_method and their impact on the persistent InnoDB data files would be as follows:

            innodb_flush_method O_DIRECT O_DSYNC
            fsync, normal¹ OFF OFF
            O_DSYNC ON ON
            O_DIRECT, unbuffered¹, async_unbuffered¹ ON OFF

            ¹ The options normal, unbuffered, async_unbuffered are available on Microsoft Windows only.

            marko Marko Mäkelä added a comment - I do not think we can allow innodb_flush_method to be changed to any allowed value while the server is running. Most values, yes, but not all of them. Let us review how this was refactored in MDEV-30136 : innodb_flush_method innodb_data_file_buffering innodb_data_file_write_through fsync , littlesync , nosync , normal ¹ ON OFF O_DSYNC 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 two columns of parameters related to log writes have been omitted, because would depend on the file format change MDEV-14425 and are therefore not applicable to MariaDB Server 10.6. Because we do not want to change the meaning of some values in a GA release, I believe that we must disallow SET_GLOBAL innodb_flush_method=…; to or from the values: littlesync nosync O_DIRECT_NO_FSYNC In MDEV-30136 the meanings of these parameters were revised (together with deprecating the parameter innodb_flush_method ). That was acceptable in a new major release but not within a GA release. The revised table of allowed dynamic values of innodb_flush_method and their impact on the persistent InnoDB data files would be as follows: innodb_flush_method O_DIRECT O_DSYNC fsync , normal ¹ OFF OFF O_DSYNC ON ON O_DIRECT , unbuffered ¹, async_unbuffered ¹ ON OFF ¹ The options normal , unbuffered , async_unbuffered are available on Microsoft Windows only.
            marko Marko Mäkelä added a comment -

            With further development such as MDEV-33545 this would become less and less feasible to implement.

            marko Marko Mäkelä added a comment - With further development such as MDEV-33545 this would become less and less feasible to implement.

            People

              marko Marko Mäkelä
              valerii Valerii Kravchuk
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.