MyISAMのコンカレントインサート機能
こんにちは。今回はMyISAMストレージエンジンの並列インサート機能（Concurrent Insert)についてちょっと書いてみたいと思います。
MyISAMは皆さんご存知のように、テーブルレベルのロックのみで行レベルでのロックがサポートされていません。MySQLではSELECTコマンドの実行時に暗黙的リードロック(Shared Read lock)がかかるようになっており、一般的にはこのロックが対象のテーブルに存在している場合には、書き込み処理（INSERT,UPDATE,DELETE,ALTER TABLEなど)がロック待ちとなります。SELECTの実行が完了してからINSERTなどが実行される形になります。
MyISAMはテーブルロックなので、SELECTの対象としている行以外への更新も同様に待たされてしまい、同時処理性能に影響を与えているであろうことは容易に想像できると思います。
MyISAMには、コンカレントインサート(Concurrent Insert)と呼ばれる機能があります。コンカレントインサートとは、他のコネクション（スレッド）がSELECT文を発行もしくはLOCK文を実行している時であってもコンカレントインサート処理が対象のテーブルに対して書き込みロックを獲得できる（＝Shared Read lockがかかっている状態のテーブルにINSERTできる）機能です。なおSELECTのShared Read lock要求は既にターゲットのテーブルにコンカレントインサートが書き込みロックをかけていても通るようになっています。
要するに、１つまたは複数のSELECTと１つのINSERTを同時実行できるようにする機能です。
コンカレントインサートが使用可能になるLOCKコマンドは以下となります。
LOCK TABLE テーブル名 READ LOCAL;
(LOCALをつけないとコンカレントインサートは利用不可になります。）
[設定方法]
グローバルサーバ変数：concurrent_insertを設定します。
0 : コンカレントインサートを使用しません。（使用できる状況でも使用しない）
1 : 対象のMyISAMテーブルに「穴」がない場合に限り（最後尾にインサートできる場合）、コンカレントインサートを実施します。
2 : 対象のMyISAMテーブルに「穴」があっても、レコードを強制的に最後尾にインサートし、コンカレントインサートを実施します。
ここで言う「穴」とは、ストレージのレコードシーケンスの途中に「穴」がある場合のことを言います。
MyISAMでは、一度DELETEしたデータは物理的には消去されず削除フラグを立てるだけとなっています。INSERTのみで作られたテーブルデータには「穴」はありませんが一度最後尾以外の行をDELETEすると削除フラグが立ちそこが「穴」となります。MyISAMは次回インサート時になるべくその「穴」の場所にデータを挿入しようとします。
concurrent_insert=2の場合は、対象のテーブルに既にリードロックがかけられていた場合のみ「穴」ではなく最後尾へデータを挿入します（穴はそのまま）。テーブルに何もロックがない場合は「穴」の場所にデータが挿入されます。concurrent_insert=1に比べて領域の使用効率が悪くはなります。なお、「穴」は次のコマンドで物理的になくすことが出来ます。
mysql> optimize table test.locktest;
+---------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.locktest | optimize | status | OK |
+---------------+----------+----------+----------+
1 row in set (0.00 sec)
[設定コマンド例]
set global concurrent_insert=0;
set global concurrent_insert=1;
set global concurrent_insert=2;
※デフォルトは1となっています。
グローバルサーバ変数なので、次回コネクション作成時から有効になります。
または、my.cnfなどの設定ファイルに書いておくと良いでしょう。
こう書いてみると常時1か2に設定したくなるような機能ですが実際のところはどうなのでしょうか。
ソース分析後に手元の環境で簡単なベンチマークをしてみました。
まずは、sysbenchでのテスト結果です。
コンカレントインサート有の場合（強制使用）
concurrent_insert=2;
OLTP test statistics:
queries performed:
read: 1400
write: 500
other: 0
total: 1900
transactions: 100 (473.84 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900 (9002.99 per sec.)
other operations: 0 (0.00 per sec.)
Test execution summary:
total time: 0.2110s
total number of events: 100
total time taken by event execution: 9.3607
per-request statistics:
min: 35.74ms
avg: 93.61ms
max: 155.12ms
approx. 95 percentile: 140.13ms
Threads fairness:
events (avg/stddev): 1.0000/0.40
execution time (avg/stddev): 0.0936/0.04
コンカレントインサート無しの場合
concurrent_insert=0;
OLTP test statistics:
queries performed:
read: 1400
write: 500
other: 0
total: 1900
transactions: 100 (532.79 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900 (10122.93 per sec.)
other operations: 0 (0.00 per sec.)
Test execution summary:
total time: 0.1877s
total number of events: 100
total time taken by event execution: 7.4302
per-request statistics:
min: 34.88ms
avg: 74.30ms
max: 152.66ms
approx. 95 percentile: 128.75ms
Threads fairness:
events (avg/stddev): 1.0000/0.76
execution time (avg/stddev): 0.0743/0.05
コンカレントインサート無しの方が若干ですがスループットが良いという結果となりました。
今回たまたまというわけではなく、ほぼ毎回このような結果となります。
実行時間の短いSELECTとINSERTは比較的効率的に処理されているためと推測されます。
逆にコンカレントインサートのオーバヘッドが目立つ形になったといったところでしょうか。
(sysbenchでは、INSERT,DELETE,UPDATEがミックスされてしまうので、INSERT以外の文が発生させるロックが多少結果に影響を与えると考えられます。念のためにmysqlslapでSELECTとINSERTのみのベンチを実施し結果は同様になるのを確認しました。詳細はここでは割愛します）
MyISAMの処理フローから逆算すると、コンカレントインサート機能が真価を発揮するのは実行時間の長いSELECT文が頻繁に流れているような状況下と仮定することができます。
そのような状況をシミュレートしてベンチマークテストを実施してみました。
今回は複雑なシナリオなので、JMeterを使ってみました。(SuperSmackなどでも良いと思います）
以下のスレッドを同時実行して性能を見るシナリオです。
・SELECT文（短い）とINSERT文をたくさん実行するスレッド（複数）
・長いSELECT文（ランダムで最大約1秒リードロックを保持する）を何度も実行するスレッド（１つ）
こんな感じです。
実行時間の長いSELECTはそれだけ長くリードロックを保持するので、コンカレントではないインサートは待ちとなってしまいます。
結果を見てみましょう。
concurrent_insert=2;
コンカレントインサート強制使用
テーブルロックの状況
mysql> show status like 'Table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 1185 |
| Table_locks_waited | 20 |
+-----------------------+-------+
2 rows in set (0.00 sec)
コンカレントインサートを使用しない
concurrent_insert=0;
テーブルロックの状況
mysql> show status like 'Table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 865 |
| Table_locks_waited | 390 |
+-----------------------+-------+
2 rows in set (0.01 sec)
コンカレントインサート使用時はINSERTのスループットは113.2、不使用時は66.6となっています。(SELECT REQ/SELECT REQ2はINSERTと同じスレッドなのでINSERTのスループットに引っ張られます)
※並列スレッドを100程度に絞り込んでいるのと、スレッド間での処理のずれを作り出すためにランダムでの待ち時間を入れているため絶対的なスループットは少なめとなっています。
長いSELECTが稼動していると、コンカレントインサートなしでは（当然ですが）ロック待ちになるケースが増えています。このロック待ちがスループットを落とす原因となっています。逆にコンカレントインサートを使っているとほとんどロック待ちが出ないのが観測されました。
結論としては、よく現在のMySQLの使われ方を分析した上でコンカレントインサート機能の使用/不使用を決定した方が良いでしょう。FITするケースにはパフォーマンスチューニングの一手段として使っても良いと思います。場合によってはOFFにすることも有効だと考えられます。