January 14, 2015 by Peter Zaitsev
過去数ヶ月に渡って、InnoDBのトランザクション履歴の負債の危険性と、MVCCがMySQLのパフォーマンス問題の原因になりうることについて、いくつかの記事を書いてきた。この記事ではそれに関連して、InnoDBのトランザクション分離レベルとMVCC(multi-version concurrency control、多版型同時実行制御)との関連性、そしてそれらがMySQLのパフォーマンスにどう影響するのかを取り上げてみようと思う。
MySQLのマニュアルにはMySQLでサポートされているトランザクション分離レベルの詳細な説明がある。ここではそれを繰り返すことはせず、パフォーマンスへの影響に焦点を絞ってまとめてみる。
SERIALIZABLE : 全てのSELECTでロックを行い、ロック管理(ロックの設定はコストの大きい処理だ)と得られる並列性の点で非常に大きなオーバーヘッドの原因になってしまうので、原理的にマルチバージョニングを使い物にならなくしてしまう、最強の分離レベルだ。MySQLアプリケーションでは、非常に限られた場面でしか使われないモードだ。
REPEATABLE READ : デフォルトの分離レベルがこれで、通常はこれで十分で、多くのアプリケーションには便利なモードだ。このレベルだと、最初の読み込み時のデータが見えることになる(通常のロックをしない読み込みの場合)。ただしこれはコストが高くつく。InnoDBはトランザクションの開始に合わせてトランザクションの履歴を管理しなくてはならず、この処理が重いのだ。更新が非常に多く、アクセスする行が偏っているアプリケーションだと状況はより悪くなる。InnoDBに、何百というバージョンのある行を処理して欲しいとは思わないだろう。
パフォーマンスに対してという意味では、リードもライトも両方とも影響がある。行の複数のバージョンを走査するselectクエリは非常に重いし、それはupdateでも同じことだ。MySQL 5.6でバージョン管理が重大な競合を起こしてしまう問題がある場合特にそう言える。
ここで例を見てみよう。完全にメモリ内に納まるデータセットを使ってsysbenchを実行してみた。トランザクションを開始しオープンな間に、フルテーブルスキャンを行うクエリを何度か実行している。
sysbench --num-threads=64 --report-interval=10 --max-time=0 --max-requests=0 --rand-type=pareto --oltp-table-size=80000000 --mysql-user=root --mysql-password= --mysql-db=sbinnodb --test=/usr/share/doc/sysbench/tests/db/update_index.lua run
見ての通り、書き込みのスループットは劇的に落ちてしまっており、しかもクエリが実行されていない時でもトランザクションの実行中は下がりっぱなしだ。これは、REPEATABLE READ分離レベルの時にトランザクションと関係ないselectが実行され、かつその後にそのトランザクションがそのまま続く時に起こることがあるという悪い例のひとつではあるが、他のケースでも同様のパフォーマンス劣化に遭遇する可能性もあるだろう。
再現させてみたい人がいれば、以下の一連のクエリを使えばいい。
select avg(length(c)) from sbtest1;
begin;
select avg(length(c)) from sbtest1;
select sleep(300);
commit;
REPEATABLE READがデフォルトの分離レベルであるというだけでなく、InnoDBの論理バックアップにもこの考え方が使われる。mydumperやmysqldump --single-transactionを考えてみよう。ここまで見てきた事から考えると、これらのバックアップ方法はデータが大きくなるとリカバリ時間が長くなってしまうために使えなくなるということに限らず、書き込みが多い環境ではパフォーマンス影響も大きくなってしまうがために使えないというのが分かるだろう。
READ COMMITTEDモードは、トランザクション内での読み取り開始時ではなく、文ごとの開始時にバージョン情報を保持するという根本的な違い以外はほぼ同じである。このため、InnoDBはより少ないバージョンしか管理しなくて良くなる。これは、長く実行される文がない場合にはより効果的だ。長い時間がかかるselect、例えばレポーティングのクエリなどがある場合は、影響は深刻になる。
一般的には、デフォルトにREAD COMMITTED分離レベルを使い、必要なアプリケーションあるいはトランザクションがある場合のみREPEATABLE READを使うというのが、私の考える良い方法だ。
(訳者注) 原文のコメントでもやり取りがあるが、MySQL 5.7でデフォルトの分離レベルをREAD COMMITTEDに変更する提案がなされている。
READ UNCOMMITTED : 私が思うに、論理的な観点からしか記述されないため、最も理解されていないであろう分離レベルがこれだ(ドキュメントにこれについては2行しか書かれていないのも納得)。この分離レベルを使っている時には、トランザクションがコミットされていなくても、データベースに変更が加えられたと同時にその内容全てが見える。この分離レベルを使うのに適したユースケースのひとつとしては、大量のUPDATE文の実行中に、ダーティリードでどの行が見えてどの行が見えないのかが「確認」できるといった場合だろう。
というわけで、まだコミットされていない変更や、トランザクションでエラーが発生してコミットされないものまで見えてしまう。従って、このモードは細心の注意を払って使う必要がある。100%正確なデータが必要ない場合もある程度存在するだろうから、そういった時にはこのモードも便利ではある。
では、パフォーマンスの観点からはREAD UNCOMMITTEDはどうはたらくのだろうか？理論上は、READ UNCOMMITTEDモードの文が開始された時にInnoDBの行バージョンが作られても、それをパージしてしまえるはずである。しかし実際には、バグあるいは複雑な実装のせいでそうなっておらず、行バージョンは文の開始時から保持され続ける。そのため、READ UNCOMMITTEDで非常に長いSELECT文を実行すると、READ COMMITTEDを使っている時のように、大量の行バージョンが作られてしまう。これでは何の利点もない。
ただし、SELECTの際には重要な利点もある。READ UNCOMMITTEDの分離レベルでは、InnoDBは古い行バージョンを記録し続け、チェックする必要はない。最新の行バージョンは常に正しいからだ。古い行バージョンを見つけるには大量のIOが発生するため、undo領域がディスクにあふれているような時は、劇的にパフォーマンスが改善されることもある。
前に挙げたのと同じ重い更新の負荷をかけた状態でselect avg(k) from sbtest1;を並列で実行した時が、最も分かりやすい比較だろう。READ COMMITTED分離レベルでは、この文は終了しない。恐らく、スキャンされるよりも速くインデックスのエントリが作られてしまう事が原因だろう。一方でREAD UNCOMMITTED分離レベルでは、1分やそこらで実行完了してしまう。
まとめ : InnoDBの分離レベルを正しく選べば、アプリケーションを最高の性能で動作させることができる。正しい分離レベルを選ぶまでの道のりは色々だが、ある時は何の違いも得られないかもしれないし、ある時は全く劇的なものになるかもしれない。長いバージョン履歴を持つInnoDBとの関係は、かなり改善の余地があると考えられる。将来のMySQLバージョンではこの対処がされていくことを望んでいる。
