MySQLのパフォーマンス改善（同期タイミング調整）

Posted at 2025-03-04

以前パフォーマンス改善でパーティショニングについて記載しました。今回は、書き込み時のパフォーマンスについて観点を当てて記載します。
過去構築したシステムをAWSのMySQLに移行する際、データ登録の処理が遅延し、パンクする事象があったため、調べた内容になります。

今回記載するsync_binlogの設定や、関連するパラメータ（innodb_flush_log_at_trx_commit など）を適切に調整することで、トランザクションの安全性とパフォーマンスのバランスを取ることができます。

sync_binlogの役割と設定

sync_binlog は、バイナリログ（Binlog）の同期タイミングを制御するパラメータです。ディスク書き込みの頻度が高いほどパフォーマンスは低下します。デフォルトは1になっています。他のパラメータは以下

値	説明	パフォーマンス	データ耐久性
`0`	OSのキャッシュに任せ、明示的な同期を行わない	高速	クラッシュ時にデータ損失のリスクあり
`1`	トランザクションごとにバイナリログを同期	低速	クラッシュ耐性が最も高い
`N (>1)`	N回のトランザクションごとにバイナリログを同期	中程度	データ損失は最大N回のトランザクション分

設定のイメージは以下:

データが損失することを完全に許容しないのであれば1のまま運用することが望ましいですが、最新データの紛失がある程度は許容できるシステムであることと性質上大量のデータが書き込まれる仕組みのため、今回はパフォーマンスを優先し設定を変更しました。

このパラメータは、トランザクションコミット時のログのフラッシュ方法を制御するパラメータです

値	説明	パフォーマンス	データ耐久性
`0`	OSのキャッシュに任せる（1秒ごとにフラッシュ）	高速	クラッシュ時にデータ損失のリスクあり
`1`	トランザクションごとにログをフラッシュ	低速	クラッシュ耐性が最も高い
`2`	コミット時にログを書き込むが、ディスクフラッシュは1秒ごと	中程度	クラッシュ時に1秒分のデータ損失リスクあり

設定のイメージは以下:

1万件のトランザクションで1万件インサートした場合以下の動作となった。　※当時RDSのスペックはだいぶ落としていたはずだが詳細は失念。。。

`sync_binlog`	`innodb_flush_log_at_trx_commit`	所要時間
`1`	`0`	70秒
`10000`	`0`	35秒
`1000`	`0`	38秒
`1000`	`1`	10秒
`1000`	`2`	18秒

当時の事象では上記を1分以内に処理できないと問題が起きるというのが問題だったので、以下の設定にして対応した。
sync_binlog = 1000、innodb_flush_log_at_trx_commit = 2

データの保守性は弱くなるので、安易にsync_binlogをいじることを推奨することではないが、登録系のパフォーマンス改善という部分では考慮が必要な部分かと思い記載しました。