innodb_flush_method 的参数设置

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 报错

# mysql

mysql> show global variables like '%innodb_flush_method%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.01 sec)

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

展开阅读全文
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值