MySQLのログとかバッファとかの役割をまとめた
MySQLの運用をしていると「バイナリログ…？ Redoログ…？ リレーログ…？ ログ多すぎん？」となることありますよね？ 私はありました。というか今もあります。
何回調べてもやはり忘れることも多いので一回まとめてみました。
REDOログ
MySQLでは全てのデータはバッファプール上で読み書きされるが、バッファプールはメモリ上の領域なので揮発性があり、突然の電源断などでデータが吹き飛んでしまう。
とはいえ、全てのデータを都度ディスクに読み書きしていたらパフォーマンスが悪くなる。
そこで、（デフォルト設定では）トランザクションがコミットされた時点でバッファプールに書き込むと同時にREDOログにも書き込みを実施。REDOログへの書き込みが成功した時点でCOMMIT成功とする。
この時点ではバッファプール上の変更はディスク上のテーブルデータに反映されていないため、サーバやMySQLのプロセスがクラッシュしてしまうとデータが以前の状態に戻ってしまう。
が、再起動時のリカバリプロセスでディスク上の変更前のデータを復元し、そこにREDOログに記録された変更を反映しディスクに書き込むことで、COMMIT成功時点のデータが復元される。
ということでこれがないと上述のとおり、突然サーバが停止した際にCOMMITされたデータの保証がされなくなる。
ACID特性のうちDurabilityのために必須。これを無効にすることもできるがそうすると途中でクラッシュしたサーバは二度と起動しなくなる。
COMMITの度にディスクへの書き込みが必須となるため、当然パフォーマンスの足を引っ張る。
innodb_flush_log_at_trx_commitを1以外の値にすることで、COMMIT毎ではなく1秒毎に書き込むような設定も可能。
その場合、書き込み頻度は減るためパフォーマンスは向上するが、当然データの損失の可能性が有り、ACID特性は担保されない。（この場合「RDBMS使う必要ある？」を考えた方が良い）
UNDOログ
トランザクションで発生した変更の履歴を保存しておくためのログ。
役割としては2つあり、
1つ目はトランザクションがROLLBACKされたときに、UNDOログに保存されたデータを使用して本のデータへと巻き戻す。
2つ目は複数の並列するトランザクションが、COMMIT後のデータを読み取らないように（REPETABLE READを実現するために）UNDOログからCOMMIT前のデータを読み取る。
UNDOログは過去データを読み取る必要がなくなった時点で不要になるので、そうなったUNDOログはパージ処理が走る。
逆に言うと、COMMITもROLLBACKもされない長時間のトランザクションが残り続けるとUNDOログも増大し続ける。
最新のデータを取得するにはUNDOログを辿っていく必要があるため、増大したUNDOログを抱えたデータへのクエリは遅くなる。
MySQLを使っていて何もしていないのに徐々にCPU負荷だけが高い状態になっていたら結構な確率でこれ。（"Rollback Segment History List length"でググると痛い目見た人の戒めが結構引っかかる）
このあたりは下記の記事に超詳しく書かれている。
InnoDBのMVCCのガベージコレクションについて
ログバッファ
REDOログに書き込むためのバッファ。
未COMMITのトランザクションで発生した変更はまずこのメモリ上のバッファに書き込まれて、変更サイズが上限に達するかCOMMITがされた時点でディスクへのフラッシュが行われる。
たしかこれリングバッファで実装されてて、動作を解説したブログを読んだ記憶があるんだけど見当たらない… ひょっとした別の仕組みだったかも。
バイナリログ
レプリケーションのために生成されるデータの変更履歴を記録したログ。
書き込み可能なメイン側のローカルストレージに生成され、レプリカ側がネットワーク経由で読み取りに来る。
Aurora MySQLの場合はクラスタストレージではなく、ローカルストレージに保存するため書き込み頻度によっては保存期間に注意。
無効にするとレプリケーションができなくなるのでクラスタ全体での可用性は落ちるが、メインDBの可用性やデータ耐久性には影響しない。
Aurora MySQLの場合はストレージレイヤがインスタンスで共通のため、複数のリーダーが存在してもバイナリログは不要。
Aurora MySQLではなく通常のRDSでは同期レプリケーションになるため、COMMITが頻繁に発生するワークロードだと結構バイナリログの書き込み待機イベントがキツくなるケースがある。あった。
このケースではAuroraに移行するだけでDB Loadが2/3程度にまで収まってくれた。
リレーログ
バイナリログがレプリカサーバによって読み込まれると、レプリカサーバにその内容がリレーログとして保存される。
最初は「メイン側からバイナリログを読み取って保存するならばそれもバイナリログでいいのでは？」と思っていたが、レプリカ側でもメイン側とは独立して任意のクエリによってデータが書き込まれる可能性があり（設定によっては）バイナリログを生成するので、「メインからコピーしてきた変更差分（ログ）」と「自身が生成した変更差分」でファイルが分かれているものと考えている。
ダブルライトバッファー
OSで管理しているページサイズとMySQLのページサイズの差分のせいで、MySQL的には1回の書き込み（1ページ16kb）命令だけど、OS的には4回の書き込み（4kb * 4）に分割されるケースが有る。
その場合、OSのほうで3回書き込んだ直後に電源断となってしまうと、MySQLは1ページ16kbを書き込んだと認識しているが実際には12kbしか書かれておらず、データの破損が発生してしまう。（Torn Write）
これを避けるために、メモリからディスクに書き込むときは一回ディスクに書き込んだデータをもう一回書き込んでいる。
クラッシュリカバリ処理中に、ディスク上のページデータに破損がないかをチェックサムを元にチェック。破損していた場合はダブルライトバッファー上のデータを読み取り反映する。もしダブルライトバッファー上のデータも破損していた場合はREDOログからの修復に切り替える。
この操作を実施するためのディスク上の領域。
RDSのOprimized WriteはMySQLのページサイズ、ファイルシステムブロック、OSのページサイズを合致させて、Torn Writeが原理上発生しないようにして書き込み回数を減らしてパフォーマンスをアップさせている。
バッファプール
データの読み書きを行うためのバッファ。
単なるキャッシュではなく、書き込みも基本的にここに対して行われて最終的にディスク上に保存される。
バッファ上に存在しないデータに書き込む場合、一度該当のデータをバッファに読み込んでから書き込み処理がされるらしく、UPDATEとかでもDisk Readが増えたりして「？？？」となった記憶がある。
余談:MySQLのデータ書き込みとクラッシュリカバリプロセスのREDOログの使われ方
MySQLを勉強し始めた当初は「REDOログへの書き込みを以てCOMMIT成功ということは、REDOログからディスクへ永続化されるのかな？」と思っていが、どうやら違うらしい。
MySQL InnoDB: Differences between WAL, Double Write Buffer, Log Buffer, Redo Log
そもそも上記の諸々を調査するきっかけになったのが上記のstackoverflowのquestionなのだが、
これによると、バッファプール上に記録されたデータの変更がダブルライトバッファーを経由してディスクに書き込まれるとのこと。つまりREDOログに記録されたデータは使われないらしい。
Writing to the data file is only done by copying modified pages from the buffer pool to the respective page on disk.
REDOログはクラッシュリカバリの際に、ディスクから読み出された未コミットのデータをバッファプール上に展開した後にそのバッファプール上のデータを更新（コミット後の状態に）するのにだけ使われるとのこと。
The redo log file is used only to reproduce modified pages in the buffer pool after a crash. A modified page that was not able to be copied to disk must be recreated by reading the original page from disk into the buffer pool, then applying redo log changes to the page in the buffer pool. Then the modified page can be flushed back to disk.
REDOログはトランザクションによって発生した変更内容を保持しているが、該当のデータ保存されているページ（16kb）のデータすべてを保持しているわけではないため、このような仕組みになっているのだそう。
when the page write operation was interrupted in the middle. So, you'll get a corrupted data, and it will be impossible to repair it from the redo log as there are only changes saved within redo and not a whole page.
余談2:THE LOG IS THE DATABASE
ちなみに
The redo log file is used only to reproduce modified pages in the buffer pool after a crash.
とのことだが、AuroraではむしろこのREDOログ（あるいはWALログ）こそがアーキテクチャの要となっていて、論文にも"THE LOG IS THE DATABASE"と書かれている。
Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases
余談3:この記事を投稿したきっかけ
このquestionの回答者、わかりやすい解説してくれてるなー と思ってProfileを見たらSQLアンチパターンの作者でした。
ということを最近SQLアンチパターンの第2版が出たことで思い出し、メモ書きに放り込んであった記事を引っ張り出して公開してみました。
（ここから時が過ぎたので「です・ます」調になっています）
教訓:ACID特性は高コスト
上記の各種ログやバッファの役割を見ると分かる通り、基本的にこれらの処理はACID特性を担保するために用意されている仕組みとなります。（バイナリログとリレーログはちょっと違うけど）
つまり ACID特性を得ることは基本的には高コストである ということを念頭に置いて、適切なデータストアの選択を行う必要があります。
私がインフラ構成の相談を受けデータストアの選定を行う際、システムで取り扱うデータの種類を聞いた後に「このデータってACID特性必要？」と確認します。
とはいえ、ほとんどのケースではメンバーのスキルセットや使用するフレームワークを考慮してSQLが使用できるRDBMSを選択するのですが。
ちなみに、下記の記事を読んだときは「裏付けについては記載がないけど、まあそうなるだろうなー」という感想でした。7年前の質問の更に数年前の話なら、今はまた状況が違うのかな 🤔
そのMongoDBは、その数年後、問題の多かった自社エンジンを捨てて、Wired TigerというInnoDBそっくりなストレージエンジンを買収し、さらに数年後のつい最近、複数行にまたがる「普通の」トランザクションを実装することを発表しました。その結果起きたことは？PostgreSQLやMySQLと同じ性能になりました。
Discussion