MySQLのInnoDBの書き込み速度向上について、innodb_flush_log_at_trx_commitやinnodb_doublewriteの設定を変更する方法が紹介されていることも多いです。
紹介とともに設定変更による危険性について触れられているケースが大半ではありますが、先日、危険性を把握しないまま設定されているケースに遭遇しました。
危険性を説明しようと思った時に、実は自分でもそこまでよく理解していないことに気が付いたので、今回は改めてこれらの動作等についてまとめました。
結論
innodb_flush_log_at_trx_commit- 本番環境では原則的に
1にする - クラッシュ時にデータが失われても問題がない場合(例えばやり直しがきくデータ移行のインポート時等)は
0または2にしても問題ない
- 本番環境では原則的に
innodb_doublewrite- クラッシュ時にデータが失われても問題がない場合(例えばやり直しがきくデータ移行のインポート時等)は
0にしても問題ない - ファイルシステム側でページのアトミックな更新に対応しているのであれば
0にしても問題ない- 例えば、
ZFSやFusion-io NVMFS
- 例えば、
RDS Optimized Writesを利用しているのであれば0であっても問題ない
- クラッシュ時にデータが失われても問題がない場合(例えばやり直しがきくデータ移行のインポート時等)は
データ更新の流れ
パラメータの役割に入る前に、どのような流れでデータが更新されているのかを、データの持ち方から確認していきます。
バッファプール
まず、InnoDBにおいては、データ操作はデータファイルを直接参照するのではなく、必ずデータファイルのデータをメモリ上に持ってきた上で行われます。これは、SELECT、UPDATE、DELETE等の既に存在するデータに対する操作はもちろんのこと、まだデータが存在しないINSERT処理も同様です。
InnoDBは行本体がB-treeインデックスの葉の部分に格納されていますので、INSERT処理の場合、その処理によって書き込まれる予定のページをB-treeインデックスをたどり、そのページ自体をメモリ上に展開し、それに対して書き込みます。
このように、データを保存・操作する際に利用するのが、メモリ上のバッファプールと呼ばれる領域です。
バッファプールとファイルデータの差異
さて、全ての操作がバッファプール上で行われるということは、バッファプールのデータとデータファイルのデータで必ず差異が発生してしまうということです。この、まだデータファイルに永続化が完了しておらず、差異が発生しているページのことをダーティページと呼びます。
ダーティページは、定期的にページクリーナと呼ばれるバックグラウンドプロセスによりデータファイルにフラッシュ(書き込み)されます。フラッシュは、バッファプール上のダーティページの割合がinnodb_max_dirty_pages_pct_lwmで設定したパーセンテージを超えた後に開始されます。
したがって、ダーティページが存在する状況というのは決して珍しいわけではありませんが、この状況でMySQLのプロセスが強制的に停止してしまうと、更新内容が消えてしまうということになります。これでは、コミットされたデータが失われないという「永続性」が担保できません。
この対策として、コミットされたデータが失われないようにする、REDOログという仕組みがInnoDBには存在します。
REDOログとログバッファ
REDOログとは、「どのような更新が行われたか」を保存しておくファイルのことです。ただし、これもバッファプールと同じく、まずはメモリ上にあるログバッファという領域で管理されます。
それではバッファプールと同じで意味がないのではないかと思われますが、ログバッファの内容については、「通常」、トランザクションがコミットされた段階でREDOログにフラッシュされます。したがって、「コミットされたが永続化されていない」というケースは「通常」避けられているということです。
これにより、万が一、ダーティページがある状態で意図せぬプロセス停止が発生したとしても、次回の起動時にREDOログからデータを復元することができます。このデータのリカバリ(クラッシュリカバリ)はMySQL側で自動的に行われます。
innodb_flush_log_at_trx_commit
さて、いよいよ1つ目のパラメータinnodb_flush_log_at_trx_commitについてです。上記でわざわざコミットされた段階でREDOログへフラッシュされる挙動を「通常」と記載したのは、これを設定でそのように動作させないこともできるためです。それがinnodb_flush_log_at_trx_commitです。
通常、この値は1であり、これは「コミット時にはログバッファの内容をフラッシュする」というものです。それでは、パフォーマンスを向上するという文脈で語られる、innodb_flush_log_at_trx_commitを0や2に設定するとどうなるのでしょうか。まとめると、次の通りです。
| 値 | 挙動 |
| 1 | ログバッファはコミット時にフラッシュされる |
| 0 | ログバッファは1秒間に1回フラッシュされる |
| 2 | ログバッファはコミット後と1秒間に1回フラッシュされる |
2は分かりにくいですが、1がコミットが成功した時点でフラッシュされているのに対し、2はコミット後にフラッシュを非同期で試みるため、ラグが発生し得るということです。
innodb_flush_log_at_trx_commit = 0 or 2 の使いどころ
前述の通り、innodb_flush_log_at_trx_commitが0だとIO自体が減り、2だとコミット時のIO待ちがなくなるため、パフォーマンス向上につながります。
これは、データ移行のインポート処理時等に有用です。ここで仮にクラッシュしたとしても、またやり直せばいいからです。例えば、レプリカを手動で作成する際の初期データ移行時に設定して時間を短縮する等が考えられます。
しかしながら、本番環境で1以外を選択する場合、クラッシュした時に「コミットしたのにデータがない」という状況が発生してしまう可能性があるため、本当にそういった挙動で問題ないのかをしっかり考えた上で選択する必要があります。
つまり、「処理が早くなるから」という理由だけで選択するのは極めて危険ということです。
ダブルライト
概要
上記のデータ更新時の流れには記載していないのですが、実は、バッファプールからデータファイルにフラッシュする際は、「通常」、2度に分けて書き込まれます。1回目はダブルライトバッファと呼ばれるファイル上の連続した領域で、その後、データファイルに書き込みます。非常に紛らわしいのですが、バッファと言いつつメモリ上ではなくファイル上の領域です。
では、なぜこのような仕組みがとられているのでしょうか。それは、InnoDBのページサイズがファイルシステム側でアトミックに扱えるサイズを超えていることが多いためです。
InnoDBのデフォルトのページサイズは16KBです。一方でext4やXFSのデフォルトは4KBです。例えば、最初の4KBを書き込んだ後にクラッシュした場合、残り12KBは書き込めていないので、データとして整合性がなく、かつ、リカバリもできません。
これを避け、正常にリカバリできるようにする仕組みがダブルライトです。
不整合を避けられる理由
次の2パターンに分けて不整合を避けられる理由を考えてみます。
- ダブルライトバッファに書き込み中にクラッシュ
- ダブルライトバッファからデータファイルに書き込み中にクラッシュ
ダブルライトバッファに書き込み中にクラッシュ
この場合、データファイルにはまだ実際に書き込みが行われていません。したがって、データファイルはダーティページをフラッシュする前の段階で整合性を保っており、ダブルライトを利用していたおかげで破損することが避けられました(本来であればこのタイミングで破損してしまうのをダブルライトバッファ側で吸収した)。
また、コミットされたデータはinnodb_flush_log_at_trx_commitが1なのであれば、前述の通り必ずREDOログに存在しています。なので、このREDOログを利用することにより、正常なデータファイルに対してコミット済みのデータを適用することができます。
ダブルライトバッファからデータファイルに書き込み中にクラッシュ
この場合、ダブルライトバッファ上のページは正常です。したがって、単純にダブルライトバッファの内容をデータファイルに改めて書き込めば正常な状態になります。
innodb_doublewrite
さて、ここで表題の2つ目のパラメータinnodb_doublewriteが出てきます。これを0に設定するか、--skip-innodb-doublewriteオプションを指定してMySQLを起動することで、ここまで説明してきたダブルライトを無効にすることができます。
ダブルライトにより2回書き込みが発生すると言っても、ダブルライトバッファはシーケンシャルな領域なので、データファイルへの書き込みより高速であり、2倍の時間がかかるわけではありませんが、2回書き込みが発生するのを1回に抑えることができるため、パフォーマンスは向上します。
しかしながら、ダーティページのフラッシュ中にクラッシュした場合、データファイルが破損してしまうという大きなリスクがあります。
innodb_doublewrite = 0 の使いどころ
まず、innodb_flush_log_at_trx_commitと同じく、データ移行のインポート時には有用です。クラッシュしても再度実行すればいいためです。
また、前述の通り、innodb_doublewriteが必要な理由はファイルシステム側にありました。したがって、ファイルシステム側でアトミックな書き込みが保証できるのであれば、ダブルライトを無効にしても問題ないということです。
例えば、ファイルシステムとしてFusion-io NVMFSやZFSを利用する場合、ダブルライトを無効にしてファイルシステム側にクラッシュ対策を任せてしまうというものです。なお、Fusion-io NVMFSの場合、自動で0になります。
二重書き込みバッファーがアトミック書き込みをサポートする Fusion-io デバイス上にある場合、二重書き込みバッファーは自動的に無効になり、代わりに Fusion-io アトミック書き込みを使用してデータファイル書き込みが実行されます。
innodb_doublewrite
あとは、RDS Optimized Writesを利用する場合です。これは、rds.optimized_writesパラメータを設定することで利用できる機能であり、利用中の場合はinnodb_doublewriteが0になります。
これらのデータベースは、AWS Nitro System を使用する DB インスタンスクラスで実行されます。これらのシステムのハードウェア構成により、データベースは 16 KiB ページを 1 ステップで確実かつ永続的にデータファイルに直接書き込むことができます。AWS Nitro System により RDS Optimized Writes が可能になります。
RDS Optimized Writes for MySQL による書き込みパフォーマンスの向上
REDOログとバイナリログ(binlog)の違い
InnoDBのREDOログと、MySQLのバイナリログは混同しやすいものですので、バイナリログについても記載しておきます。
| 項目 | REDOログ | バイナリログ |
| スコープ | InnoDB | MySQL全体 |
| 用途 | クラッシュリカバリ | レプリケーション、PITR |
| 保持内容 | 物理的変更内容 | 論理的変更内容 |
| ファイル上書き | される | されない |
用途
REDOログは前述の通りクラッシュリカバリに利用することが目的ですが、バイナリログはレプリケーションやPITR(ポイント・イン・タイム・リカバリ)に利用することが目的です。
REDOログはあくまでInnoDB側で出力するInnoDB向けのログであり、MySQL全体の記録にはならないため、レプリケーション等には利用できません。一方で、バイナリログも後述のように論理的な記録に過ぎないので、クラッシュリカバリの観点だと利用できません。
物理的か論理的か
REDOログが「InnoDB」の変更を「物理的に」記録したものであるのに対して、バイナリログは、「MySQL全体」の変更を「論理的に」記録するものです。
物理的な記録というのは、「このページのこの位置はこういうデータからこういうデータになりました」というものです。一方で論理的な記録というのは、「このテーブルはこのデータからこういうデータになりました」というものです。バイナリログ側では物理的なデータファイルを意識しません。
また、バイナリログは出力形式をSTATEMENT、MIXED、ROWから選べますが、STATEMENTの場合は、「このテーブルにはこういうSQLが実行されました」という記録しか行われません。
ファイルが上書きされるかされないか
ファイルが上書きされるかされないかという点も違いがあります。
REDOログはクラッシュリカバリが主目的であるため、データファイルにダーティページが書き込まれた後は、その情報をずっと保持しておく必要はありません。したがって、不要になった情報は上書きしても問題ないため、REDOログファイルは再利用(以前保存した情報が上書き)されます。
一方でバイナリログは再利用されません。expire_logs_daysやbinlog_expire_logs_secondsで設定した期限を超過するか、手動で消されるまで情報が残り続けます。
最後に
パフォーマンス向上の観点で語られることもあるinnodb_flush_log_at_trx_commitやinnodb_doublewriteは特徴を理解した上で設定変更する場合は有用ですが、安易な設定変更はクラッシュ時のデータ損失のリスクがあります。
とりあえず「なんか早くなるらしい」ということで設定されているケースを見かけて改めて感じましたが、私自身も、気が付かないうちにそういうことをやっているのかもしれないなと思いました。
人の振り見て我が振り直せと言いますが、私自身、そういうことがないよう、パラメータを変更する場合は、しっかり変更の意味を考えた上で対応できるようにしたいです。
コメント