InnoDBのAUTO_INCREMENTが遅い問題は5.1でどう改善されたのか

　MySQL5.1のGA版が出てから8ヶ月余りが経過しましたが、まだ5.0(あるいはそれ以前)をメインで使っている方も多いのではないでしょうか。5.1の何が良いのかいまいち分からないという方も多いかもしれません。そんな方にとって分かりやすい例の1つが、「5.1でInnoDBのAUTO_INCREMENT性能が大幅に改善された」という点です。私は仕事柄Web系の技術者の方と話をする機会もよくありますが、意外と知られていない改善なので(まさにトラフィックと同時接続数の多いWeb系システムのための改善なのに…)この機会に取り上げることにします。
　簡単に言えば、AUTO_INCREMENTを持つテーブルに対してINSERTをするクライアント数が数十、数百と増えていった時に、従来はスループットが指数関数的に落ちてしまっていたのが、5.1では高速かつ安定するようになりました。以下にmysqlslapのINSERTベンチマークの結果を示します。

mysqlslap  --concurrency=1,5,10,20,30,50,100,200,300,500 \
--iterations=1 --engine=innodb \
--auto-generate-sql --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=write \
--number-of-queries=100000

　MySQLの比較対象バージョンは5.0.83と5.1.37。my.cnfパラメータはWebアプリ向けにしました。log-binでバイナリログを有効化。innodb_flush_log_at_trx_commitは2にしてコミット時同期書き込みではなく「コミット時ファイルシステムキャッシュに書き込み」に変更(1にしていない理由は先週取り上げたグループコミットの性能問題を緩和するため)。max_connectionsを大きめにとってあります。

[mysqld]
innodb_flush_log_at_trx_commit=2
innodb_log_files_in_group=2
innodb_buffer_pool_size=11G
innodb_flush_method=O_DIRECT
innodb_log_file_size=256M
innodb_data_file_path=ibdata1:100M:autoextend
innodb_file_per_table
log-bin
max_connections=1000
table_cache=8192
server-id=1

　グラフの縦軸は計10万件をINSERTするのに要した時間です。見てのとおり、MySQL5.0では接続数の増加に対してINSERT性能が加速度的に低下しています。200接続のときは10万件のINSERTに150秒を要しており、秒間660件程度しかINSERTできていません。ディスクI/Oがボトルネックになっているわけでもないのに、660というのは低すぎる値です。さらに上図のグラフは同時接続数200の時点でグラフが切れていますが、それ以上のときは以下のようなエラーで止まってしまいました。

mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,100669,'qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2Bbp
zhwYBgPNYX9RmICWGkZD6fAESvhMzH3yqzMtXoH4BQNylbK1CmEIPGYlC6')
ERROR : Deadlock found when trying to get lock; try restarting transaction

　メッセージを見るとデッドロックのように見えますが、実際にはもちろん(2つのトランザクションが互いにロック待ちになる)デッドロックではありません。同じロックを待つクライアント数が一定ライン(ソース上の定数LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK:200固定)を超えると、デッドロック扱いにして強制的にロールバックさせる、というInnoDBの実装に起因します。
　一方MySQL5.1では接続数の増加に対してINSERT性能が安定しています。500接続まで試しましたが、どれも10万件のINSERTに5秒前後で終了しており、平均INSERT数は秒間2万前後になっています。現実的な用途ではテーブル/インデックスサイズが大きいことによるディスクI/Oがボトルネックになりがちなので、ここまで速くはなりませんが、それでも5.0に比べれば十分に優れた値と言えるでしょう。
　AUTO_INCREMENTな主キーはInnoDBのクラスタ索引と相性が良い(理由は今後解説)ので、性能問題が改善されたことには大きな意味があります。このAUTO_INCREMENTの動作の変化については、オンラインマニュアルに記載があります。ここでも紹介します。

●AUTO-INCテーブルロック
　AUTO_INCREMENTを持つテーブルに対してINSERTをする場合、そのINSERT文が完了するまでの間、「AUTO-INC」と呼ばれるテーブルロックが確保されるという特徴がありました。テーブルロックを持つ理由は、マスターに対して行った更新結果と、バイナリログに書かれているSQL文を実行した結果(スレーブに反映される)を確実に一致させるためです。例えば、空のテーブルtに対して以下の処理をするケースを考えてみます。

マスター：
1) INSERT INTO t (val) SELECT val FROM t2; (t2は1000レコードあると仮定)
2) INSERT INTO t (id, val) VALUES (null, 'abc'); (1001が割り当てられる)

　この場合、2)のAUTO_INCREMENTに何の値が入るかは、1)で何レコードINSERTされるかが確定しないと分かりません。そのため、1)の完了を待つ必要があります。バイナリログには以下のように記録されます。

SET INSERT_ID=1;
INSERT INTO t (val) SELECT val FROM t2;
SET INSERT_ID=1001;
INSERT INTO t (id, val) VALUES (null, 'abc');

　1)の完了を待ってから2)を実行することで、2)で入るAUTO_INCREMENT値が1001であることが特定できるので、バイナリログには「SET INSERT_ID=1001;」を埋めることができます。この結果、どのスレーブで処理をしても1001がINSERTされ、マスターとスレーブで値を一致させることができます。
　通常のトランザクションのロックは、トランザクションが終了(コミットまたはロールバック)するまで保持されますが、AUTO-INCテーブルロックはINSERT文の終了時点で解放されます。したがってINSERTをした後にロールバックをしても、増えたAUTO_INCREMENT値は元に戻りません。またロックの期間も短くなります。
　5.0までは、AUTO_INCREMENTを持つテーブルに対するINSERT文すべてがAUTO-INCテーブルロックをかけます。AUTO_INCREMENT列に対してNULLを入れると新しい番号が採番されるというのがMySQLの仕様ですが、NULLではなく明示的に値をセットしたときにも、AUTO-INCテーブルロックは確保されます。
　テーブルロックを持つことの最大のデメリットは、同時実行性能が極端に低下してしまうことです。ロックの期間が短いといっても、INSERTが集中すれば大量の待ち行列ができるため上図のようにとんでもなく性能が悪化します。



●5.1ではAUTO-INCテーブルロックではなくmutexになった
　しかし、本来はINSERT文の実行が終わるまで常にテーブルロックを保持しなければならないわけではありません。例えば、以下の2つのINSERTを考えてみます。

3) INSERT INTO t (id, val) VALUES (null, 'abc'); # 3が入った
4) INSERT INTO t (id, val) VALUES (null, 'def'); # 4が入った

　別々のクライアントから3)→4)の順に実行した場合、テーブルロックをINSERT完了まで保持しないと、AUTO_INCREMENT値の採番順序とコミット順序(バイナリログへの記録順序)がずれる可能性が高くなります。しかし、実はずれてしまっても問題は起きません。バイナリログには以下のように、次にどのAUTO_INCREMENT値をセットするかの情報も一緒に記録されるからです。例えば以下のような感じで記録されます。

SET INSERT_ID=4;
INSERT INTO t (id, val) VALUES (null, 'def');
SET INSERT_ID=3;
INSERT INTO t (id, val) VALUES (null, 'abc');

　このため、バイナリログへの記録の順番が逆転しても最終結果は同じになります。そもそも、テーブルロック自体がINSERTの終了時点で解放されてしまうので、以下のような処理をすればMySQL5.0でも逆転現象は起きます。

T1:
BEGIN;
INSERT INTO t VALUES(null, 1);

T2:
BEGIN;
INSERT INTO t VALUES(null, 2);
COMMIT;

T1:
COMMIT;

　この場合、AUTO_INCREMENTの採番はT1の方が先に行われますが、コミットおよびバイナリログへの記録はT2の方が先に行われます。それでも「SET INSERT_ID=N」がバイナリログに一緒に記録されるため、矛盾した結果にはなりません。

　このようなINSERT文では、文の完了までテーブルロックを持たなくても矛盾が無いように処理できます。同じAUTO_INCREMENT値が採番されてはまずいので、採番の部分はテーブルロックをかける必要がありますが、それ以外は不要です。
　つまり、次にどのAUTO_INCREMENT値を割り当てれば良いかが分かれば、INSERT文が終わるまでテーブルロックを保持する必要はありません。別の言い方をすれば、そのINSERT文によって何件INSERTされるかが事前に判断できる場合には、INSERT文が終わるまでテーブルロックを保持する必要は無いということです。3)も4)も、実際にINSERTされるのは多くても1件だということは、INSERT構文から(実際に処理をしなくても)判断できます。そして、世の中で実行される大半のINSERT文はこの形です。そこで5.1では、「何件INSERTされるかをSQL構文から判断できる場合」のAUTO_INCREMENTロックのかけ方が以下のように変更されました。

mutex(table->autoinc_mutex)を取る
AUTO_INCREMENT値を採番する
mutexを解放する

　番号が重複してはいけないので、採番処理自体はクリティカルセクションの中に置く必要がありますが、それ以外は並列で動作できます。INSERT文が完了するまでがクリティカルセクションであった従来と比べると、同時実行性が大幅に高まっています。これが上図のような大きな差が生じた原因です。現在MySQL5.0以下を使っていて、AUTO_INCREMENT性能の遅さに悩まされている方にこそ、ぜひMySQL5.1を試してみてほしいです。

　ただし、いつでもこのような高速な動作ができるわけではありません。これができるのは、事前に何レコード入れるのかが分かっている場合だけです。最初に例として挙げたINSERT ... SELECTやLOAD DATAのように、何レコードがINSERTされるかをSQL文の実行前に判断できない処理については、いくつまでAUTO_INCREMENT番号を付与すれば良いかを特定することができません。こうしたSQL文を実行するときは、AUTO-INCテーブルロックが引き続き確保されます。AUTO-INCテーブルロックが確保されている間は、通常のINSERT文であっても(mutexではなく)AUTO-INCテーブルロックを確保しようとします。このため同時実行性が低下します。
　これを防ぐ手立てとしては、innodb_autoinc_lock_modeというパラメータを2にして(デフォルトは1)、なおかつバイナリログのフォーマットをSQLステートメントベースから行ベースに変える、という方法があります。この場合にはAUTO-INCテーブルロックが確保されなくなります。ただし、SQL文ベースのバイナリログでは整合性を保証できないので行ベースにする必要があること、行ベースにすることでの性能低下があるなどの副作用があることに注意が必要です。ユースケースによっては検討の余地があるでしょう。innodb_autoinc_lock_mode=0は従来型の動作をしますが、これは互換性を保証するためのもの(1や2では、処理内容によってAUTO_INCREMENT番号が飛ぶ場合がある。詳細はマニュアルを参照)で、よほどの理由が無い限り設定する必要はありません。
　もしこのあたりの実装の詳細を知りたい方がいたら、ha_innodb.ccのha_innobase::innobase_lock_autoinc(void)あたりを見てみてください。innodb_autoinc_lock_modeが1のときは条件付でAUTO-INCテーブルロックをかけることなどが分かるでしょう。