トランザクションはACID特性を満たすと言われている。 そのうちA(Atomicity)はトランザクション内の操作をAll or Nothingとなるよう保証し、トランザクションが中途半端に実行されて(アプリケーションレベルから見た)データの整合性が失われることを防ぐ特性。またD(Durability)とはシステム運用中に起こる様々な障害からデータを守る(整合性を保つ)特性。
これらの特性を満たすためのDBMSの古典的なテクニックがすごく面白いので、それに関するMySQL(主にInnoDB)のパラメータ・パフォーマンスにどのような影響を及ぼすかを調べた(*'ω'*)
なお紹介している技術は基本的に教科書に書かれていた技術で、実際にInnoDBに実装されているアルゴリズムとは異なることがある(とはいえベースにはなっている)
参考
障害の種類
- トランザクション障害: トランザクションがabortしたときに発生
- システム障害: 例えばシステムの電源が落ちたなどの理由で揮発性ストレージ上のデータが消失
- メディア障害: 永続ストレージの一部分が破損し、永続ストレージ上のデータが消失
これらの障害から回復するためにDBMSには様々な機能が実装されている。 ここで「回復する」とは、障害からのシステム再起動時に以下の状態に復元することを指す。
DBMSの基本構成
DBMSにおけるストレージは以下の図のような構成。
データの読み書きの際は、まずはメモリ上のキャッシュ(innodb buffer pool)にを介して行われ、キャッシュに書かれたデータは非同期的にディスクにflushされる。これによりパフォーマンスが向上するが、メモリ上に存在しディスクにはまだ書き込まれていないページは(システム)障害発生時には消失してしまう。
障害発生後の再起動時にコミットしたデータを回復するためにはどうすれば良いだろうか。 考えられる単純な方法としてはCOMMIT時にトランザクションの操作の結果をディスクに書き込むことであるが、これはトランザクションがCOMMITされるたびにディスクへのランダムIOが発生することになり効率が悪い。
ランダムIOによるパフォーマンスの低下を回避しつつコミットしたトランザクションの操作の内容をディスクに永続化するための機能(?)としてトランザクションログがある。
トランザクションログについて説明する前にキャッシュ(DBバッファ)について軽く触れておく。
データベースバッファ
概要
データベースへの読み書きはデータベースバッファを経由して行われる。バッファのサイズが大きければ大きいほど、多くのデータをバッファに置きIOの頻度を減らしてパフォーマンスを向上させることができる。
またバッファ上での同一ページへの更新は、ディスクへのflush時に一つ一つ実行されるのではなく、更新の最終的な結果にまとめられてディスクにflushされる。これによりディスクへの書き込みを減らすことができる。このようなテクニックをwrite combiningという。
データベースバッファのflushはDBMSが良い感じのアルゴリズムによって管理しており、InnoDBではLRUによるバッファ管理がされている。
関連するMySQLパラメータ
innodb_buffer_pool_size
InnoDBがテーブル・インデックス・データをキャッシュするための領域のサイズ。
innodb_buffer_pool_instances
innodb_buffer_pool_sizeをいくつに分割するか。バッファプールサイズが巨大な場合はインスタンスの数を増やすことでバッファプールへの読み書きの競合が抑えられ同時実行性が向上する場合がある。innodb_buffer_pool_sizeが1G以上に設定された場合のみ有効。
innodb_lru_scan_depth
- InnoDBはflush対象のダーティページを見つけるために毎秒LRUリストを探索する。
innodb_lru_scan_depthは各buffer pool instanceのLRUリストをどのくらいのページ数探索するか。 - 最大
innodb_buffer_pool_instances * innodb_lru_scan_depthだけ一度にflushされる可能性があるので、それを考慮してinnodb_io_capacityも大きくする必要がありそう。
innodb_flush_neighbors
- バッファプールからページをflushする際に、同一エクステント(ページグループ)内のダーティページを一緒にflushするかどうか
- デフォルトではバッファプール内の同一エクステント内の連続するダーティページがフラッシュされるようになる。
- HDDでは個々のタイミングでページをflushするより、ヘッドが近くにあるときについでにご近所さんもflushした方がヘッドのシークを抑えることができパフォーマンスの向上につながる。
- 一方SSDを使っている場合はシーク時間は重要な要素ではないため、このオプションを無効にし不要なIOを抑えることでパフォーマンスを向上させることができる。
innodb_flush_method
データ/(後述する)ログファイルをディスクにどのような方法で書き込むか。デフォルトでは fsync() を使うが、 O_DIRECT を指定することでOSのページキャッシュをすっとばして直接書き込むことができる(DBMSが独自にキャッシュを持っているためdirectIOによるパフォーマンスの劣化はそこまではなく、ページキャッシュを抑えてメモリの節約ができる)
トランザクションログ
概要
ログはデータベースに適用された更新を表現するレコードの列を持つ。そのレコードには意味的には以下の要素を持つ。
- 更新したページのアドレス
- 更新を実行していたトランザクションID
- 作用(READしたとかWRITEしたとかCOMMITしたとかABORTしたとか)
- ページに書き込まれた後の値(REDO情報)
- ページに書き込まれる前の値(UNDO情報)
各トランザクションがCOMMIT時にはログを永続化していることが保証されていれば、ログを用いて障害発生前にCOMMITしたデータを復元することができる。
またログを書き込むディスクと、データページを書き込むディスクを分けておけば、両方のメディアで同時に障害が発生した場合はどうしようもないけど、どちらか片方だけでのメディア障害に対するリスクを抑えることができる。
ページをCOMMIT時にディスクに書き込むのは遅いとか言いつつ、ログもディスクに書き込んどるやんけと思うかもしれないが、ログは操作を時系列順に書き込むだけで良いためシーケンシャルに書き込むことができ、ヘッドのシークによる書き込み性能の低下はランダムIOと比べて少ない。
WAL
キャッシュ上のページがいつflushされるかはDBMSによって管理されている。 DBMSが「このページもうflushするで〜〜〜」となったときに即座にflushして良いかというと良くない。
ページのflushは非同期的に実行されるため、COMMITしていない(ログに永続化されていない)ページが、ディスクに書き込まれてしまうかもしれない。 この状態でシステム障害が起きてしまうと、未COMMITのトランザクションの操作内容がディスクには書き込まれているが、ログにはそのトランザクションに関する情報が何も残っておらず、リカバリ時に未COMMITのデータをUNDOすることもできず、整合性が崩れてしまうかもれない。
これを解決するためには WAL(Write Ahead Logging) というプロトコルにのっとってページ/ログのディスクへの永続化を行う。 WAL は、「ページをflushする前に、ログに書こうね」という決まり。
ログのフォーマット
物理ログ
各ログレコードが操作前/操作後のページを丸っと持っておくフォーマット。
これは単純だしリカバリ時も丸っと置き換えるだけなのでリカバリ操作が冪等だが、ログの肥大化を招く。さらにシステムがレコードレベルロックを行なっている場合は、この方法はうまく動作しない。
例えば同一ページ上にあるレコードAとレコードBを考える。trxAがレコードAを更新し、trxBがレコードBを更新した後にtrxAをabortする(その後trxBがCOMMITする)と、この方法ではページごと元の状態に戻すため、abortしていないはずのtrxBによる変更まで巻き戻ってしまう。
論理ログ
各ログレコードが、レコードに対する論理的な操作を持つフォーマット。
これによりログの肥大化、レコードレベルロックを行なっている場合でもうまくいく。 ただし複数のページにまたがる操作が行われたとき、それらのページが一斉にflushされないとログの効力が失われてしまう。
また、このようなログによるリカバリは冪等な操作ではないため、単純にチェックポイントレコードからリカバリを始めるということができなくなる。
物理論理ログ
各ログレコードが、レコードに対する操作ではなくページに対する操作を持つ。
ページを丸っと持つわけではないのでログの肥大化を抑えられるし、複数ページにまたがる操作が行われた場合はそれらのページに対する操作のログをページの数だけ追加すればよい。
リカバリ操作が冪等でないという問題が残っており、これを解決するためにはログレコードにIDを振るなどがあるが、後で書くチェックポイント処理の項目で説明する。
関連するパラメータ
innodb_log_file_size
- その名の通りログファイルのサイズを設定する。
- (InnoDBの)ログは循環的に書き込まれる固定サイズのファイルで、末尾まで書き込まれていっぱいになると、先頭から上書きしていく。先頭から上書きしていくときに、その先頭のログレコードに対するページがダーティページな場合、ログが失われてしまい障害発生時にリカバリができなくなってしまう。
- そのためログの使用率(現在のLSNとチェックポイントレコードの位置、ログファイルサイズから計算)(LSN,チェックポイントについては後述)が大きくなってくると、バッファプールに余裕があってもダーティページのディスクへのflushをDBMSが推し進めてくる。
- そのため、
innodb_buffer_pool_sizeを増やしたときは、innodb_log_file_sizeも大きくしないと、データをメモリ上に多く保持してパフォーマンスを向上させることが難しくなってしまう。 - また、一般的にログファイルが大きいとそれだけ多くのダーティページを持つことができるため、システムのリカバリに時間がかかるようになる。
innodb_log_files_in_group
- デフォルトでは2。ログファイルの数を指定する。REDOログのサイズの合計は
innodb_log_file_size x innodb_log_files_in_groupとなる。 - このパラメータを使ってREDOログを大きくしつつも個々のファイルサイズは小さく抑えるとかもできる。
ログバッファ/ログファイルへの書き込み
概要
トランザクション実行中のログレコードはメモリ上のログバッファにたまっていき、トランザクションがCOMMITされる前にログはディスクに書き込まれる。
group commit
COMMITする度にディスクに書き込んでいては、ディスクへの書き込みが多くなってしまう。ディスクへの書き込みはできるだけ少なくしてパフォーマンスを向上させたい。group commit は(短時間内の)いくつかのトランザクションによるCOMMITをまとめてディスクに書き込むテクニック
どれくらいのCOMMITをまとめてログに書き込むかはわからないが、InnoDBでは特に何も設定せずともgroup commitの恩恵を受けられるみたい(innodb_flush_log_at_trx_commit=1 にしててもグループコミットの恩恵は受けられるってことで良いのかな)
https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-group_commit.html
関連するパラメータ
innodb_log_buffer_size
文字通りログバッファのサイズを設定する、ログバッファが大きいと大規模なトランザクションを効率よくさばくことができるようになる。
innodb_flush_log_at_trx_commit
1: トランザクションコミットのたびにログバッファの内容がログファイルに書き込まれ、ログバッファにある(ページへの操作)がディスクにflushされる。(デフォルト)0: 約1秒ごとに1回、ログバッファの内容がログファイルに書き込まれ、ログバッファにある操作がディスクにflushされる。ディスクへの書き込み頻度が減り、パフォーマンスの向上が見込めますが、COMMITした値がログに書き込まれているとは限らなくなってしまうため、システムクラッシュ時に最大一秒間のトランザクションの操作内容が失われてしまう。2: トランザクションコミットのたびにログバッファの内容がログファイルに書き込まれるが、テーブルスペースへのflushは約1秒間に1度実行される。0と同様にパフォーマンスの向上が見込めるがトランザクションは最大1秒間失われるリスクがある。
チェックポイント処理
概要
チェックポイント処理はログによるシステムのリカバリにかかる時間を短縮するためのテクニック。
ログファイルとバッファプールのサイズを大きくするとそれだけ多くのダーティページを持つことができるが、ダーティページが多いとそれだけシステム障害発生時にログに示された多くの処理を行いシステムを復旧させることになり、システムのリカバリに時間がかかるようになる。
チェックポイント処理は定期的にメモリ内のページをディスクに書き込み(そのとき特別なチェックポイントレコードをログに書き込む)、リカバリ時には最後のチェックポイント処理より後のログのみを読めばよいということにしてリカバリにかかる時間を短くする。
sharp checkpoint (単純なチェックポイント処理)
最も単純なチェックポイント処理の手続きは以下のような感じ
この単純なチェックポイント処理は(ダーティページをディスクにすべて書き出すまで新しいシステムを止めることになるので)大きなパフォーマンスの低下につながる。
fuzzy checkpoint
単純なチェックポイント処理の軽量(非同期)バージョン。以下のような手続き
ファジーチェックポイントは前のファジーチェックポイント処理によるダーティページのディスクへの書き込みが全て終了しないと開始することはできない
- 実行中のトランザクションを一時停止/新規トランザクションの開始を抑制
- ダーティページのリストを作るためにバッファをスキャン
- チェックポイントレコードを書く
- トランザクションを再開/新規トランザクションの開始を許す
- 上の工程で作ったダーティページのリストを少しずつディスクに書き込んでいく
ファジーチェックポイントでは、トランザクションの開始を抑制するのはダーティページの一覧を知るためにバッファをスキャンしてチェックポイントレコードを書いている間だけで済む。
単純なチェックポイント処理では、リカバリ時には一番最後のチェックポイントレコード以降のログからリカバリすればよかったが、ファジーチェックポイントではチェックポイントレコードを書いた後に非同期的にダーティページがディスクに書き出されるため、最後のレコード以降のログからリカバリしてしまうと、最後から二番目より後で実行されたトランザクションによる操作が失われてしまうかもしれない。
そこでリカバリ時は最後から二番目のチェックポイントレコード以降のログからリカバリ処理を実行することで漏れなくリカバリすることができる。(ファジーチェックポイントが開始する時、以前のファジーチェックポイントによるフラッシュは終了しているから)
ファジーチェックポイントでは非同期的にいい感じに少しずつページををディスクにflushしていく。InnoDBでは「いい感じに」flushしていくテクニックとして adaptive flushing と呼ばれるテクニックが実装されている。
adaptive flushing ではダーティページの割合や、ログの割合と innodb_io_capacity の値に応じて動的にフラッシュする量を決定するそうだが、正直まだよくわかってない...
InnoDBでは以下の状況になったときに sharp checkpoint が発生する
- バッファプール内のダーティページの割合が
innodb_max_dirty_pages_pctを超えたとき - ログファイルの使用率が大きくなってきたとき
https://dev.mysql.com/doc/refman/5.6/ja/innodb-performance-adaptive_flushing.html
Log Sequence Number
(物理)論理ログによるリカバリ操作が冪等でないということをトランザクションログの項で書いた。
これを解決するためには、リカバリ時に更新レコードをページに適用してもよいのかどうかを認識する必要がある。
そのために、各(ディスクに書かれた)ログレコードにに単調増加するID(Log Sequence Number)(LSN)を振っておく。各ページはそのページに適用した最新のログのLSNを持っておき、リカバリ時には
ページが持つLSN >= ログレコードのLSN ならばそのログレコードによる更新は既にページに反映されており再実行する必要がないということがわかる。
InnoDBではLSNはlog bufferへの更新が行われたバイト数の合計が採用されているようだ。 SHOW ENGINE INNODB STATUS の [LOG] セクションで確認できる。
LSNによる冪等性の確保は便利だがUNDO操作が複雑になる。というのもAbortにより更新操作を取り消すと、そのページに対する最新のログレコードが(LSNが)分からなくなってしまうから。
なんとなく取り消し操作以前のページに対する操作のログレコード(のLSN)を当てがってやれば良さそうに感じるけれど、以前のログレコードをページに割り当てるということはそのログの操作がまだ反映されていないということになり、おかしくなってしまう。
解決策としては取り消し操作自体をログレコードに残してやればよい (git revert みたいな) abortによる取り消し操作したときにログの歴史を改変するといろいろ大変(LSNの仕組みが破綻)だから、取り消し操作をしたよ〜っていうのをログに書いておけば、歴史を改変しなくてもリカバリ時に取り消し操作を適用できる。
関連するパラメータ
innodb_io_capacity
- バックグランドで実行されるIO操作の帯域上限
innodb_io_capacityの値はinnodb_io_capacity_maxで定義された最大値まで、100 以上の任意の数値に設定できる- システムが1秒あたりに実行できるIO操作の数に設定するのが理想のよう
- この値があまりに大きすぎるとファジーチェックポイントのバックグランドでのflushが一瞬で終わってしまいキャッシュを利用する恩恵が薄くなってしまう
- 大きすぎるとwrite combiningが効かない
innodb_max_dirty_pages_pct
バッファプール全体に対するダーティページの比率の上限。これを越えるとInnoDBは強制的に(sharp?)チェックポイント処理を行う。
innodb_adaptive_flushing
adaptive flushing を用いてページのフラッシュを行う。
innodb_adaptive_flushing_lwm
ログの割合がこの値を越えると、 innodb_adaptive_flushing を設定していなくとも adaptive flushing によるディスク書き込みが行われるようになる。
その他
InnoDBのロールバックセグメント
いわゆるUNDOログの格納場所、InnoDBではトランザクションログにはREDO情報のみを持ち、UNDO情報はこのロールバックセグメントに持つようです。 ロールバックセグメントは(デフォルトでは)テーブルスペース上に領域が確保され、UNDOログはページへの更新と同様にバッファプールにキャッシュされる。
ロールバックセグメントは基本的にトランザクションのUNDOのための仕組みだが、同時にMVCCのための仕組みでもある。
各行はUNDOログへのポイントを持ち、各UNDOログはさらに古いUNDOログへのポインタを持っている。他のトランザクションが古いデータを読むときはそのトランザクションの開始時刻からどのUNDOログを読むべきかがわかる。
innodb_purge_threads
InnoDBでは行が削除されたとき、テーブルスペースから即座に消されるわけではなくて行が削除されたことを示すマークがつけられる。これはMVCCによってその行が削除される前のデータ(UNDOログ)を参照するトランザクションが存在するかもしれないためである。
削除マークがつけられた行はその行を参照するトランザクションがなくなった時点で削除可能になり、バックグランドで削除処理が行われる(パージ処理)
innodb_purge_threads はこのパージ処理を行うスレッドの数を指定する。更新処理の多い環境で History list length (undoログの長さ)が多い場合に大きくすると良いみたい。
(history list lenght は SHOW ENGINE INNODB STATUS で確認できる)
ダブルライトバッファ
- InnoDBではダーティページをテーブルスペースに書き込む際、まずダブルライトバッファ(ディスク上)に書き込んでから実際のデータのページに書き込む。
- InnoDBはリカバリ時にダブルライトバッファに書かれたデータをチェックし、それが完全ならばそのデータを実際のページに書き込み、不完全ならそのデータを破棄する。これによりページの部分書き込みを防ぐ。
innodb_doublewrite
ダブルライトバッファを有効/無効にする。無効にすると当然リカバリ時のDBMSによる原子性が保証されなくなるが、パフォーマンスが少し向上する。
ファイルシステムや(ディスク+)カーネルレベルでのアトミックライトが保証されているのであればダブルライトバッファを利用せずとも部分書き込みを排除することができるため無効にするとお得。
まとめ
教科書的なトランザクション技術からInnoDBの実装/パラメータをお勉強してみた。まとめてたら書きたいことがどんどん出てきて壮大になってしまったのでこのへんで。トランザクション技術楽しい！！٩( 'ω' )و
参考にあげてる資料はどれも最高に詳しくてわかりやすいのでもっと知りたい場合はそちらを見ると良さそう。
ご指摘などあればTwitterで優しく指摘してもらえると喜びます。
