MySQLのアーキテクチャ
MySQLは、主に以下の３つのlayerから構成されています。
- connection layer：クライアント接続、権限認証、セキュリティチェックなどを処理
- SQL layer(Server層とも呼ばている)：SQLの解析、分析、チューニング、実行、キャッシュなどの機能を提供
- ストレージエンジン層：
- テーブルにデータを書き込んだり読み出したりする機能を提供
- MyISAM、InnoDB、Archiveなどが存在
- テーブル毎にストレージエンジンを分けられる(つまり、ストレージエンジンはテーブル単位です。テーブル作成する時に明示的にストレージエンジンを指定しない場合はデフォルトのストレージエンジン(InnoDB)が利用される
ストレージエンジンの詳細の種類は以下の記事をご参考ください。
トランザクション機能はMySQLのServer層ではなく、ストレージエンジンが実装しております。
MySQLでトランザクションをサポートしているストレージエンジンはInnoDBです。
InnoDBは最も広く使われているので、これ以降のストレージエンジンは全部InnoDBを指しています。
MySQLでトランザクションの実行フロー
redo log
redo logとは
redo logは、InnoDBエンジンが出力するログです。つまり、InnoDB以外のストレージエンジンはそもそもredo logが存在していません。
MySQLのデータはディスクに保存され、読み取りと書き込みのたびにディスクI/O操作が必要なため、同時にたくさんのリクエストを処理する時にパフォーマンスが低下してしまいます。 そのため、MySQLではバッファプールというキャッシュを導入し、ディスク上のデータ(pageという単位)をメモリ上にキャッシュし、データベースへのディスクI/Oの負担を軽減することを図っています。
データベースからデータを読み出す時は、まずキャッシュから読み出し、キャッシュにない場合はディスクから読み出してキャッシュに入れます。データベースにデータを書き込む時は、先にキャッシュに書き込みます。このときキャッシュ内のデータpageが変更され、このデータページをダーティページと呼び、バッファプールでデータが変更された後、設定した更新ポリシーに従って定期的にディスクにフラッシュする、ダーティページフラッシングという処理を行っている。
redo logは、WAL（Write-Ahead Logging）技術を使っています。ディスクI/Oに時間がかかるため、InnoDBエンジンがメモリ上でレコードを更新し、redo log bufferに記録したら、レコード更新操作が完了とします。
別の専用スレッドが適当のタイミングでredo log bufferの内容をディスクにあるredo logファイルに書き出します。
この時点では、REDOログのトランザクションの状態はprepareであり、まだ正常にコミットされていません。
redo logのサイズについて
InnoDBのRedo logはサイズが固定されています。例えば4つのファイルとして構成し、各ファイルのサイズを1GBとすると、合計4GBの操作を記録することができる。最初から始めて、使い切ったら最初に戻るということです。
redo logはデータpageへの変更を記録するため、Buffer Poolがデータページをディスクにフラッシュした場合、redo log中のdata pageが無効になり、新しいログは無効になったデータpage上書きすることになります。
redo logが空き容量が一杯になった場合、メモリ上のデータページがディスクにフラッシュされていたことを確認してからredo log中の古いログを削除することになります。 古いログを消去している間は、新しい更新のリクエストを受け付けず、MySQLのパフォーマンスは低下してしまいます。 ですので、並行処理が多いworkloadでは、Redoログのファイルサイズを適正に設定しておくことがとても重要です。
redo logの用途→crash-safe
ダーティページフラッシングがまだ終了していないのに、何らかの理由でMySQLがダウンして再起動した場合、Buffer Pool内の変更されたデータのディスクへのフラッシュが間に合わず、データが消えてしまい、トランザクションの永続性を保証できなくなります。
この問題を解決するために、redo logが導入されました。REDOログは、その名の通りRedoに焦点を当てたものです。 これは、特定の行や列がどのように変更されたかではなく、データベースの各pageに加えられた変更を記録し、トランザクションコミット後にデータの物理ページを回復するために使用され、最後のコミット位置までしか回復できません。
MySQLがダウンして再起動してしまった時は、システムが自動的にredo logをチェックし、ディスクに書き込まれていないデータを redo logからMySQLにリストアすることによって、Innodb エンジンのcrash-safeを実現しています。
MySQL起動する時に、前回正常終了したか異常終了したかにかかわらず、起動時に必ずリカバリ操作を行います。
binlog
- binlogは、Server層が出力するログです。つまり、
どんなストレージエンジンを使ったとしても、mysqlはかならずbinlogログを出力することになります。 - binlogは、データベースに対するすべてのDDL(tableの作成、更新、削除など)およびDML操作(recordのinsert,update,delete)を記録します。つまり、SELECTやSHOWなど、データそのものを変更しない操作は記録されない。
- binlogはバイナリ形式でディスクに保存される論理ログです。
問題となるクエリーを特定するなどのためにすべてのステートメントをログに記録するには、general_logという一般クエリーログを使用します。
binlogの用途
primary instaceからstand-by instanceやread-only instanceへのデータレプリケーション
primary(マスター)側でBinlogを有効にしてstand-by、あるいはread-only側に送信し、そこでBinlogを再生(binlog中のクエリを再実行)することでデータのレプリケーションを実現しています。
ポイントインタイムリカバリ
誤って削除したデータを復元したり、特定のタイミングの状態に戻したいしたりする場合、binlogを利用します。
データベースのバックアップをベースに復元し、バックアップ時点から指定時点の間のbinlogを適用することで、指定時点のデータを復元できます。
redo logとbinlogの違い
| redo log | binlog | |
|---|---|---|
| 物理/論理 | 物理ログ | 論理ログ |
| 実現のlayer | 1. innodb database engineのログ 2. InnoDB以外のストレージエンジンはそもそもredo logは存在しません |
1. SQL layer(サーバー層)のログ 2. どんなストレージエンジンを使ったとしてもかならずbinlogログを出力 |
| 記録方式 | ラウンドロビン方式で書き込まれ、最後に到達すると最初に戻ってログを書き込む | ファイルサイズが指定値より大きい場合、後続のログは新しいファイルに記録 |
| 利用シーン | crash-safe | 1. replication(master instanceからstand-by or read-only instanceへのデータ同期) 2. point-in-time recovery |
| diskに書き込むタイミング | transaction開始する時 | transaction commitする時 |