MySQL 的 binlog 對效能的影響

Percona 的 CTO Vadim Tkachenko 在比較 InnoDB 與 MyRocks 時意外發現了 binlog 會影響不少效能穩定性,再加上 MySQL 8.0 有改變 binlog 相關的預設值,所以他後續花了不少時間測試,寫了兩篇關於 binlog 對於 MySQL 效能的影響:「How Binary Logs (and Filesystems) Affect MySQL Performance」與「How Binary Logs Affect MySQL 8.0 Performance」。

第一篇是想要知道在 Percona Server 5.7 上開 binlog 的影響,做出來後可以看到明顯的效能波動 (因為 binlog 導致 flush 時效能暴跌):

而其中的 workaround 就是調整 flush 參數,讓他比較頻繁的小量寫入,而不是突然要寫很多資料。這樣一來對平均效能也許比較差,但對前端應用衝擊會比較小:

在測試可以看到 sync_binlog=1000 是個妥協點。各單位要自己去找出適合的數字:

The strict setting also comes with noted performance penalties. I will also test sync_binlog=1000 and sync_binlog=10000, which means perform synchronous writes of binary logs every 1000 and 10000 transactions, respectively.

另外有測試 ext4 與 XFS 是否有影響,就測試的結果看起來 ext4 比 XFS 好一些,但差距有限:

第二篇則是拿 MySQL 8.0 與 Percona Server 5.7 比較,可以發現在 MySQL 8.0 開啟 binlog 時有時會有不少的效能損失:

It seems that binary logs have quite an effect MySQL 8.0, and we see up to a 30% performance penalty as opposed to the 13% for Percona Server for MySQL 5.7.

雖是推銷一下自家產品在這塊還不錯 XD

InnoDB redo log 大小對效能的影響

在「Benchmark(et)ing with InnoDB redo log size」這邊看到在討論 InnoDB redo log 的大小對效能的影響 (也就是 innodb_log_file_sizeinnodb_log_files_in_group)。

開頭就有先提到重點,在新版 MySQL 裡,幾乎所有的情況比較大的 redo log 有比較好的效能 (平均值):

tl;dr - conclusions specific to my test

  1. A larger redo log improves throughput
  2. A larger redo log helps more with slower storage than with faster storage because page writeback is more of a bottleneck with slower storage and a larger redo log reduces writeback.
  3. A larger redo log can help more when the working set is cached because there are no stalls from storage reads and storage writes are more likely to be a bottleneck.
  4. InnoDB in MySQL 5.7.17 is much faster than 5.6.35 in all cases except IO-bound + fast SSD

可以看出來平均效能的提昇很顯著,不管是增加 redo log 大小還是升級到 5.7:

但作者也遇到了奇怪的效能問題。雖然平均效能提昇得很顯著,但隨著加入資料的增加,效能的 degradation 其實很嚴重,在原來的網頁上可以看到這些資訊。

The results above show average throughput and that hides a lot of interesting behavior. We expect throughput over time to not suffer from variance -- for both InnoDB and for MyRocks. For many of the results below there is a lot of variance (jitter).

所以也許現階段先加大就好 (至少寫入的效能會提昇),不需要把這個特性當作升級 MySQL 的理由。