最近在幫同事調校 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 先天上的差異而造成一些性能上的影響，但仍希望再進行最佳化系統上的資源與設定, 後來查了一下官方文檔並找了幾個參數與實測，這篇就是為了記錄測試及性能比較.
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.
innodb_flush_method options for Unix-like systems include:
fsync()system call to flush both the data and log files.
fsyncis the default setting.
O_SYNCto open and flush the log files, and
fsync()to flush the data files.
InnoDBdoes not use
O_DSYNCdirectly 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.
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_DIRECTduring 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_DIRECTinstead.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
- 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
InnoDBactivities that cause logs to be flushed independently of the
innodb_flush_log_at_trx_commitsetting, 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
1 ... 2700, with a default value of 1). However, any mysqld process crash can erase up to
Nseconds of transactions.
- DDL changes and other internal
InnoDBactivities flush the log independently of the
InnoDBcrash recovery works regardless of the
innodb_flush_log_at_trx_commitsetting. Transactions are either applied entirely or erased entirely.
對比了一下確實和官方的說法及目前我們數據的行為一致，目前調整為 fsync / trx=0