innodb_flush_log_at_trx_commit=1 和刷新到磁碟的現實

我在各種伺服器上使用 MySQL 5.5 的預設設置，我有下表：

create table test._perf (
   name varchar(255) not null
) engine=InnoDB;

我有一個 .sql 腳本，其中包含 1000 行：

insert into test._perf values('abc');

我像這樣執行它：

time mysql -u root < 1000-inserts.sql

由於它在自動送出上執行，因此會發生 1000 個事務，一個接一個。

預設設置innodb_flush_log_at_trx_commit為 1，表示完整的 ACID。（文件：https ://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit ）

該文件還指出：

許多作業系統和一些磁碟硬體欺騙了刷新到磁碟操作。他們可能會告訴 mysqld 已經發生了刷新，即使它還沒有發生。那麼即使設置為 1 也不能保證事務的持久性……

我正在測試 1000-inserts.sql 腳本的所有伺服器都是現代的（“快速”），但它們之間有很大不同（metal vs vm，windows vs linux，ext4 vs other filesystems，busy vs idle），但結果是始終一致並屬於以下兩類之一：

• 1000 次插入在 0.2-1 秒內完成（快速）
• 1000 個刀片在 20-55 秒內完成（慢）

在“慢”伺服器中設置innodb_flush_log_at_trx_commit為2使它們“快”。

我想知道以下問題的答案：

1. 我可以假設具有“慢”行為的伺服器是真正實現的正確和正確的伺服器innodb_flush_log_at_trx_commit=1嗎？
2. 我可以假設具有“快速”行為的伺服器是錯誤的並且是文件中解釋的行為嗎？（許多作業系統和一些磁碟硬體欺騙了刷新到磁碟的操作。他們可能會告訴 mysqld 已經發生了刷新，即使它沒有發生。）

很明顯，innodb_flush_log_at_trx_commit 的值為 2 比 1 具有更好的性能。

由於送出操作相當依賴於底層系統，因此即使是作業系統愚弄也是每次送出都需要採取的額外步驟。

對於值 1，刷新到磁碟操作必然會在每個事務中發生，而對於 2，它每秒發生一次（取決於 OS 調度）

1. 奧普恩。是“慢”的，因為您確實確保了數據的持久性和一致性。
2. 奧普恩。是“快速”，因為您做的工作更少（磁碟操作）。即使是“謊言”，mysqld 也會多走一步。

對於 innodb_flush_log_at_trx_commit=1：

for every transaction {
do-log-write
do-disk-commit (actual / fake )
}

對於 innodb_flush_log_at_trx_commit=2：

for every transaction {
do-log-write
}
for every second {
do-disk-commit (actual / fake )
}

您執行的測試還包含多個用於比較的參數。您可能還想使用 sync_binlog 和 flush_method 更改進行測試。主要考慮變數優化以獲得更好的性能。

感謝有趣的研究。

“快速”的可能是由於

• 在驅動器和/或控制器中寫入記憶體。但它會在停電時倖存下來嗎？
• SSD 而不是旋轉驅動器。這可能是安全的。但製造商可能正在玩不安全的遊戲。
• 具有“電池支持的寫入記憶體”的 RAID 控制器——這是誠實的。也就是說，它既“快速”又“安全”。

還有兩件事要看：

• innodb_flush_method; 通常推薦O_DIRECT. （稍微影響速度；不安全。）
• innodb_doublewrite- 避免“撕裂的頁面”。 ON更安全。 OFF在 FusionIO SSD 上是安全的，因為它們保證 16KB 寫入的原子性。

引用自：https://dba.stackexchange.com/questions/117370