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] MySQL Verification Team
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] MySQL Verification Team
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.