innodb_flush_method 的参数设置

红太狼 2023-02-21 09:25 4阅读 0赞

os: centos 7.4.1708  
db: mysql 8.0.20

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i mysql80
    mysql-community-client.x86_64              8.0.20-1.el7                @mysql80-community
    mysql-community-common.x86_64              8.0.20-1.el7                @mysql80-community
    mysql-community-devel.x86_64               8.0.20-1.el7                @mysql80-community
    mysql-community-libs.x86_64                8.0.20-1.el7                @mysql80-community
    mysql-community-libs-compat.x86_64         8.0.20-1.el7                @mysql80-community
    mysql-community-server.x86_64              8.0.20-1.el7                @mysql80-community
    mysql-community-test.x86_64                8.0.20-1.el7                @mysql80-community
    mysql80-community-release.noarch           el7-3                       installed
    
    # mysql -e "select version();"
    +-----------+
    | version() |
    +-----------+
    | 8.0.20    |
    +-----------+

# innodb\_flush\_method #

lower\_case\_table\_names 默认为0，当设置为 1，重启 mysql 报错

The innodb\_flush\_method options for Unix-like systems include:

**fsync or 0**: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.

**O\_DSYNC or 1**: InnoDB uses O\_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O\_DSYNC directly because there have been problems with it on many varieties of Unix.

**littlesync or 2**: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

**nosync or 3**: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

**O\_DIRECT or 4**: InnoDB uses O\_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.

**O\_DIRECT\_NO\_FSYNC**: InnoDB uses O\_DIRECT during flushing I/O, but skips the fsync() system call after each write operation.

Prior to MySQL 8.0.14, this setting is not suitable for file systems such as XFS and EXT4, which require an fsync() system call to synchronize file system metadata changes. If you are not sure whether your file system requires an fsync() system call to synchronize file system metadata changes, use O\_DIRECT instead.

As of MySQL 8.0.14, fsync() is called after creating a new file, after increasing file size, and after closing a file, to ensure that file system metadata changes are synchronized. The fsync() system call is still skipped after each write operation.

Data loss is possible if redo log files and data files reside on different storage devices, and a crash occurs before data file writes are flushed from a device cache that is not battery-backed. If you use or intend to use different storage devices for redo log files and data files, and your data files reside on a device with a cache that is not battery-backed, use O\_DIRECT instead.

参考:  
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html\#sysvar\_innodb\_flush\_method