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

On windows 11, difference of innodb_flush_method=O_DIRECT vs. innodb_flush_method=unbuffered

Details

    Description

      Questioning to highlight the issue to fix better
      On the Windows 11 22H2, again, on the Windows latest version
      Those two configuration working for main DB engine without error mesages.

      • innodb_flush_method=O_DIRECT
      • innodb_flush_method=unbuffered

      Thing is that the option innodb_flush_method=unbuffered seems may make more delay when IO intensive situation while innodb_flush_method=O_DIRECT working with not so much of delay or waiting.
      innodb_flush_method=unbuffered makes obvious waiting.
      Please look in to it what makes difference since innodb_flush_method=O_DIRECT makes NO error on the Windows.

      Attachments

        Issue Links

          Activity

            wlad Vladislav Vaintroub added a comment - - edited

            Surprisingly, there is no difference between O_DIRECT and unbuffered in the recent versions of MariaDB.
            Originally, on Unixen O_DIRECT means that data files are not cached, but the redo log is cached.

            On Windows, neither data files, nor redo log is cached in this case (unless innodb_flush_log_at_trx_commit=2), which makes O_DIRECT method equal to unbuffered/async_unbuffered.

            Having, caching redo log in the file system makes no sense anyway, as every write is followed by flush.

            wlad Vladislav Vaintroub added a comment - - edited Surprisingly, there is no difference between O_DIRECT and unbuffered in the recent versions of MariaDB. Originally, on Unixen O_DIRECT means that data files are not cached, but the redo log is cached. On Windows, neither data files, nor redo log is cached in this case (unless innodb_flush_log_at_trx_commit=2), which makes O_DIRECT method equal to unbuffered/async_unbuffered. Having, caching redo log in the file system makes no sense anyway, as every write is followed by flush.
            npdmailing@gmail.com Mitchell Lee added a comment -

            Fun part is no one pointing and noticed and ready to apology since behavior of the configuration not matched as the online manual. Interesting.

            In short.
            1. On the Windows, O_DIRECT not allowed to use. Check the manual. Fix the manual or fix the code.
            Plus, if you going the stay with the O_DIRECT code on the Windows, highlight which of configuration is same as O_RIECT (unbuffered or so)
            2. Suprisingly, in the view of the full mariadb execution binary set, there is no consistance for 'innodb_flush_method' config. Each execution binary play separately that looks like amature open source guys made. What happened? Are u guys satisfy with above comments with close statement? Seriously?
            3. I'm using 'innodb_flush_log_at_trx_commit=2' as u mentioned. Beside arguments whether going to fix or massage codes, dose it make difference in performance wise?

            npdmailing@gmail.com Mitchell Lee added a comment - Fun part is no one pointing and noticed and ready to apology since behavior of the configuration not matched as the online manual. Interesting. In short. 1. On the Windows, O_DIRECT not allowed to use. Check the manual. Fix the manual or fix the code. Plus, if you going the stay with the O_DIRECT code on the Windows, highlight which of configuration is same as O_RIECT (unbuffered or so) 2. Suprisingly, in the view of the full mariadb execution binary set, there is no consistance for 'innodb_flush_method' config. Each execution binary play separately that looks like amature open source guys made. What happened? Are u guys satisfy with above comments with close statement? Seriously? 3. I'm using 'innodb_flush_log_at_trx_commit=2' as u mentioned. Beside arguments whether going to fix or massage codes, dose it make difference in performance wise?
            wlad Vladislav Vaintroub added a comment - - edited

            1. I checked the manual . O_DIRECT is allowed to use (Quoting https://mariadb.com/kb/en/innodb-system-variables/#innodb_flush_method,
            "In MariaDB, Windows recognises and correctly handles the Unix methods"

            2. If you translate how O_DIRECT is documented (see the link above) into OS-neutral language, following will come out
            a. Data files are not cached in the OS/filesystem cache
            b. Both data files and redo log are flushed, for persistence.
            c. There is no word about whether redo log is cached in filesystem or not, with default innodb_flush_log_at_trx_commit=1. It is an implementation detail, and it happened to work unoptimized on Linux (there, until very recently, nobody could figure out how to use O_DIRECT on redo log safely on 4K disks). There is no reason to populate filesystem cache with data if it is not going to be combined , but rather flushed directly after each write.

            I conclude, the documentation is consistent, and O_DIRECT can be the same as async_unbuffered, or unbuffered

            3. innodb_flush_log_at_trx_commit=2 indeed implies caching in file system. Unlike innodb_flush_log_at_trx_commit=1, the data can be combined before it is flushed.

            Now that your questions are hopefully all answered, I'd like to point out, that JIRA is not a place to ask questions. https://mariadb.zulipchat.com/ is.

            wlad Vladislav Vaintroub added a comment - - edited 1. I checked the manual . O_DIRECT is allowed to use (Quoting https://mariadb.com/kb/en/innodb-system-variables/#innodb_flush_method , "In MariaDB, Windows recognises and correctly handles the Unix methods" 2. If you translate how O_DIRECT is documented (see the link above) into OS-neutral language, following will come out a. Data files are not cached in the OS/filesystem cache b. Both data files and redo log are flushed, for persistence. c. There is no word about whether redo log is cached in filesystem or not, with default innodb_flush_log_at_trx_commit=1. It is an implementation detail, and it happened to work unoptimized on Linux (there, until very recently, nobody could figure out how to use O_DIRECT on redo log safely on 4K disks). There is no reason to populate filesystem cache with data if it is not going to be combined , but rather flushed directly after each write. I conclude, the documentation is consistent, and O_DIRECT can be the same as async_unbuffered, or unbuffered 3. innodb_flush_log_at_trx_commit=2 indeed implies caching in file system. Unlike innodb_flush_log_at_trx_commit=1, the data can be combined before it is flushed. Now that your questions are hopefully all answered, I'd like to point out, that JIRA is not a place to ask questions. https://mariadb.zulipchat.com/ is.
            npdmailing@gmail.com Mitchell Lee added a comment -

            Thanks for your effort for that taken as question. But, in short, it was kinda claiming as an issue.
            That I doublely confirmed, on the Windows,
            the mariadb has no option+s+ are available, but actually one option only works internally.

            npdmailing@gmail.com Mitchell Lee added a comment - Thanks for your effort for that taken as question. But, in short, it was kinda claiming as an issue. That I doublely confirmed, on the Windows, the mariadb has no option+s+ are available, but actually one option only works internally.

            People

              wlad Vladislav Vaintroub
              npdmailing@gmail.com Mitchell Lee
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.