mysql 5.7.x innodb_flush_method / innodb_flush_log_at_trx_commit 性能影響比較

最近在幫同事調校 mysql, 他原有的環境是位於Azure 上的 VM mysql 5.0.12 主要是 MyISAM 的資料格式,因為版本有點久遠,也希望改善上頭的性能,所以做了遷移至新的 VM上並使用 MySQL 5.7.28 做為主要的 database 版本,也將大部份 table 轉換為 InnoDB 並調整 memory 以滿足目前使用,但同事反應遷移後性能改善確實明顯,穩定度也增加,但計算獎金時感覺在insert/update/delete 較以往來的慢,後來找機會進行 profile 一下,檢查了一些 slow query 的語法,發現有些語法確實較舊版本來的慢,判斷這應是 MyISAM 及 InnoDB 先天上的差異而造成一些性能上的影響,但仍希望再進行最佳化系統上的資源與設定, 後來查了一下官方文檔並找了幾個參數與實測,這篇就是為了記錄測試及性能比較.

參考資源:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method

If innodb_flush_method is set to NULL on a Unix-like system, the fsync option is used by default. If innodb_flush_method is set to NULL on Windows, the async_unbuffered option is used by default.

The innodb_flush_method options for Unix-like systems include:

  • fsyncInnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.
  • O_DSYNCInnoDB 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: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
  • nosync: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
  • O_DIRECTInnoDB 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_FSYNCInnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call after each write operation.Prior to MySQL 5.7.25, 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 5.7.25, 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.On storage devices with cache, data loss is possible if data files and redo log files reside on different storage devices, and a crash occurs before data file writes are flushed from the device cache. If you use or intend to use different storage devices for redo logs and data files, use O_DIRECT instead.

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit 

innodb_flush_log_at_trx_commit

  • The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
  • With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
  • With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
  • For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal InnoDB activities that cause logs to be flushed independently of the innodb_flush_log_at_trx_commit setting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.
  • Log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows you to set log flushing frequency to N seconds (where N is 1 ... 2700, with a default value of 1). However, any mysqld process crash can erase up to N seconds of transactions.
  • DDL changes and other internal InnoDB activities flush the log independently of the innodb_flush_log_at_trx_commit setting.
  • InnoDB crash recovery works regardless of the innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.

 

Compare_InnoDB_Flush_Method

對比了一下確實和官方的說法及目前我們數據的行為一致,目前調整為 fsync / trx=0

 

 

Author: jerryw1974

learning and focus on computer science, cloud infrastructure, virtualization and information security, technical, networking,platform system and cyber-security related topic.