Bug #71521 Manual does not list all valid values for innodb_flush_method

Submitted: 30 Jan 2014 8:43 Modified: 30 Jan 2014 12:34
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb_flush_method, missing manual

[30 Jan 2014 8:43] Valeriy Kravchuk
Description:
This is related to Bug #71519 and Bug #71513. Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_method) lists the following values as acceptable for the innodb_flush_method variable on various Unix OSes:

fdatasync
O_DSYNC
O_DIRECT
O_DIRECT_NO_FSYNC

and says the following about Windows:

"This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed."

At the same time, in the source code of MySQL 5.6 (in the storage/innobase/srv/srv0start.cc file) I see:

...
#ifndef __WIN__
        } else if (0 == ut_strcmp(srv_file_flush_method_str, "fsync")) {
                srv_unix_file_flush_method = SRV_UNIX_FSYNC;

        } else if (0 == ut_strcmp(srv_file_flush_method_str, "O_DSYNC")) {
                srv_unix_file_flush_method = SRV_UNIX_O_DSYNC;

        } else if (0 == ut_strcmp(srv_file_flush_method_str, "O_DIRECT")) {
                srv_unix_file_flush_method = SRV_UNIX_O_DIRECT;

        } else if (0 == ut_strcmp(srv_file_flush_method_str, "O_DIRECT_NO_FSYNC")) {
                srv_unix_file_flush_method = SRV_UNIX_O_DIRECT_NO_FSYNC;

        } else if (0 == ut_strcmp(srv_file_flush_method_str, "littlesync")) {
                srv_unix_file_flush_method = SRV_UNIX_LITTLESYNC;

        } else if (0 == ut_strcmp(srv_file_flush_method_str, "nosync")) {
                srv_unix_file_flush_method = SRV_UNIX_NOSYNC;
#else
        } else if (0 == ut_strcmp(srv_file_flush_method_str, "normal")) {
                srv_win_file_flush_method = SRV_WIN_IO_NORMAL;
                srv_use_native_aio = FALSE;

        } else if (0 == ut_strcmp(srv_file_flush_method_str, "unbuffered")) {
                srv_win_file_flush_method = SRV_WIN_IO_UNBUFFERED;
                srv_use_native_aio = FALSE;

        } else if (0 == ut_strcmp(srv_file_flush_method_str,
                                  "async_unbuffered")) {
                srv_win_file_flush_method = SRV_WIN_IO_UNBUFFERED;
#endif /* __WIN__ */
        } else {
                ib_logf(IB_LOG_LEVEL_ERROR,
                        "Unrecognized value %s for innodb_flush_method",
                        srv_file_flush_method_str);
                return(DB_ERROR);
        }
...

That's why it seems there are more acceptable values than manual lists, on both Unix and Windows.

How to repeat:
Check all values that are accepted in the source code for this variable and compare to what manual says. Alternatively, try to set innodb_flush_method to, say, nosync on Linux and check what happens:

openxs@ao756:~/dbs/5.6$ bin/mysqld_safe --no-defaults --innodb_flush_method=nosync &
[1] 31972
openxs@ao756:~/dbs/5.6$ 140130 10:40:54 mysqld_safe Logging to '/home/openxs/dbs/5.6/data/ao756.err'.
140130 10:40:54 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/5.6/data

openxs@ao756:~/dbs/5.6$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15-debug MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'innodb_flush%';
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| innodb_flush_log_at_timeout    | 1      |
| innodb_flush_log_at_trx_commit | 1      |
| innodb_flush_method            | nosync |
| innodb_flush_neighbors         | 1      |
| innodb_flushing_avg_loops      | 30     |
+--------------------------------+--------+
5 rows in set (0,01 sec)

So, how comes I see nosync above when manual does not list it?

Suggested fix:
Fix the manual to list and explain the real influence of all values for innodb_flush_method that MySQL server accepts.
[30 Jan 2014 9:02] Shane Bester
did you see http://bugs.mysql.com/bug.php?id=26588 :)
[30 Jan 2014 12:34] MySQL Verification Team
Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[30 Jan 2014 12:44] MySQL Verification Team
let's check if these extra values are really needed these days, if not then time to clean up those..
[22 Mar 2017 12:45] Shane Bester
fyi
https://bugs.mysql.com/bug.php?id=26588
(innodb_flush_method, nosync and littlesync undocumented)
[5 Apr 2017 11:45] Daniel Price
Posted by developer:
 
littlesync and nosync options are now documented:
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_method

Bug status is changed to "Code/Hardware Bug" for evaluation of innodb_flush_method options.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.