環境
- MySQL 8.0
やりたいこと
MySQLで多少リスクを取ってでもパフォーマンスを一時的に向上させたい。
あなたが理解してリスクを承知している前提で、以下の SET GLOBAL で変更可能な設定を調べてみた。
設定
innodb_flush_log_at_trx_commit(トランザクションログの書き込み設定)
1がデフォルトで、コミットごとにredo logをディスクへflushする。耐久性は高いが、そのぶんI/O待ちが増えやすい2にするとコミットごとのwriteは行うがflushは毎回やらなくなるので、書き込み性能が上がりやすい- OSやホストごと落ちると、直近1秒前後のトランザクションが失われる可能性がある
- アプリの再実行やデータ再投入が簡単なバッチ処理では割り切りやすいが、通常運用で常時有効にする設定ではない
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
sync_binlog(バイナリログの同期設定)
1だとトランザクションごとにbinary logをディスクへ同期する。レプリケーションや障害復旧の観点では安全寄りの設定である0にするとMySQLがOS任せでbinary logを書き出すため、fsync()の回数が減って更新性能が上がりやすい- その代わりクラッシュ時にbinary logの末尾が失われたり壊れたりする可能性がある
- binary logベースでレプリケーションしている環境や、point-in-time recoveryに依存している環境ではかなり危険である
SET GLOBAL sync_binlog = 0;
unique_checks(ユニーク制約を外す)
- セカンダリインデックスの
UNIQUE制約チェックを一時的に緩める設定で、大量インポート時のI/Oを減らせることがある - 特にダンプのリストアやバッチ投入のように、投入データに重複がないと分かっているケースでは効きやすい
InnoDBが change buffer を使いやすくなるので、セカンダリインデックス更新が多いテーブルほど効果が出やすい- ただし重複データが混ざっていても安全にしてくれる設定ではないので、投入データの正しさを自分で担保する必要がある
- 常時有効にするものではなく、一括投入のセッションだけで使って終わったら戻す前提の設定である
SET unique_checks=0;
indexまるごと削除すると高速化するらしい
SET GLOBALじゃななくてもいいから他のパラメーター
innodb_doublewrite
- InnoDBは通常、ページ破損を避けるためにdoublewrite bufferを経由してからデータファイルへ書き込む
- これを無効化すると余分な書き込みが減るので、特に書き込みが多いワークロードでは効くことがある
- ただし電源断やカーネルパニックのタイミング次第では、partial page writeが発生してデータページが壊れうる
- 一時的なベンチマーク用途なら候補になるが、業務データを載せた本番環境では基本的に触らないほうがよい
innodb_doublewrite = 0
その他
# SSDの場合の例 innodb_io_capacity = 1000 innodb_io_capacity_max = 2000
