INSERTステートメントの最適化について(MySQL InnoDBストレージエンジン)

はじめに

モチベーションクラウドシリーズAdvent Calendar 2019の21日目を担当する松本です。

現在、モチベーションクラウドは10数名規模の会社様から数万人規模の大企業様まで幅広くご利用いただいております。私たち開発チームはどの規模のユーザー様にも快適にご利用いただけるよう、日々性能改善に取り組んでいます。

今回は、MySQL InnoDBストレージエンジンにおける、INSERTステートメントの並列性向上および挿入速度向上が期待できるTipsをまとめていきます。

前提条件

まず、行の挿入に必要な時間は以下の要因で決まります。(MySQLドキュメントより)
(数字は割合)

VALUESリストを複数指定

個別の単一行のINSERTステートメントより、複数のVALUESリストを使用して、同時に複数行挿入する。

単一行

INSERT INTO t1 (c1, c2) VALUES ('a', 1);
INSERT INTO t1 (c1, c2) VALUES ('b', 1);
INSERT INTO t1 (c1, c2) VALUES ('c', 1);

複数のVALUESリスト

INSERT INTO t1 (c1, c2) VALUES ('a', 1), ('b', 1), ('c', 1);

多くの行データを一度に送ることで、すべてのインデックスの更新と、一貫性のチェックを最後まで延期できるため、単一行INSERTに比べて、大幅なパフォーマンス向上が見込めます。

デフォルト値の省略

カラムにデフォルト値がある場合、デフォルト値と異なる場合のみ、値を指定する。

デフォルト値あり

INSERT INTO t1 (c1, c2) VALUES ('a', 1), ('b', 1), ('c', 1);

デフォルト値なし

INSERT INTO t1 (c1) VALUES ('a'), ('b'), ('c');

デフォルト値の登録をDB側に任せることにより、DBサーバーへのクエリー送信サイズやMySQLが実行するクエリー解析が減るため、パフォーマンス向上が見込めます。

AUTO_INCREMENTロック(innodb_autoinc_lock_mode)

InnoDBではAUTO_INCREMENTカラムを含むテーブルへINSERTを行う際、AUTO-INCロック呼ばれるテーブルレベルでロックする仕組みがあります。
これは、一度に複数行挿入されるINSERTステートメントに、該当ステートメント(トランザクションではない)が終了するまでAUTO-INCロック取得することで、仮に並列にINSERTステートメントが実行された場合も、1つのINSERTステートメントによって生成される自動インクリメント値は連続した値となることを保証するためです。
以下のように実行中のトランザクションが2つあり、トランザクション1の方が早く処理が実行されていた場合、

トランザクション１

INSERT INTO t1 (c1) SELECT another_c1 FROM another ...;

トランザクション2

INSERT INTO t1 (c1) VALUES ('a-2'), ('b-2'), ('c-2')...;

トランザクション1のステートメントが終了するまでAUTO-INCロックが保持されるため、トランザクション2の実行は待たされることになります。そのため、トランザクション2で発行されるAUTO_INCREMENT値はトランザクション1で挿入された行のすべてのAUTO_INCREMENT値より大きい値が割り当てられることになります。

なぜAUTO-INCロックが必要か

ステートメントベースのレプリケーションを使用していた場合、複数のINSERTステートメントが同じ順番に実行される限り、AUTO-INCロックの使用により、マスタ-スレーブ間でAUTO_INCREMENT値が同じになることが保証されます。

innodb_autoinc_lock_modeの設定値

このロックの挙動はinnodb_autoinc_lock_modeで変更することができます。
ロックの挙動を左右する、INSERTの種類は以下に分類されます。

• 単純挿入
  • 挿入行数が事前に(ステートメントの初期化時に)わかるステートメント。単一行および複数のVALUESリストのINSERT。
• 一括挿入
  • 挿入行数が事前にわからないステートメント。INSERT...SELECTなど。
• 混在モード挿入
  • INSERT ... ON DUPLICATE KEY UPDATEなど。

innodb_autoinc_lock_modeに設定できるパラメータは以下の3つです。

• innodb_autoinc_lock_mode=0 従来ロックモード

  • innodb_autoinc_lock_modeが存在する前と同じ挙動
  • ステートメントベースのレプリケーションで使用しても安全
  • 全てのINSERTステートメントでAUTO-INCロックが取得され、AUTO_INCREMENT値が連続した値になる
  • 並列性=×

• innodb_autoinc_lock_mode=1 連続ロックモード

  • 「単純挿入」の場合、AUTO-INCロックを取得しない、別のトランザクションが「一括挿入 or 混在モード挿入」でAUTO-INCロックを取得している場合はステートメントの実行が待たされる
  • ステートメントベースのレプリケーションで使用しても安全
  • 「混在モード挿入」の場合、実際に挿入される行以上にAUTO_INCREMENT値が増え、ステートメント間でAUTO_INCREMENT値のギャップが発生する
  • 並列性=△
  • MySQL8.0以前のデフォルト

• innodb_autoinc_lock_mode=2 インターリーブロックモード

  • 全てのINSERTステートメントでAUTO-INCロックを使用しない
  • ステートメントベースのレプリケーションでの使用は安全でない
  • 並列実行されている複数のステートメント間で同時にAUTO_INCREMENT値を割り当てているため、1ステートメントで挿入される行のAUTO_INCREMENT値の連続性は保証されない
  • 並列性=○
  • MySQL8.0のデフォルト

MySQL8.0以前で、ステートメントベースのレプリケーションでなくて良いケースでは、インターリーブロックモード(2)に変更することで、INSERTステートメントの並列性向上が見込めます。

変更バッファリング(innodb_change_buffering)

INSERT、UPDATE, DELETEステートメントの実行時に、セカンダリインデックスへの更新によるディスクIOを抑制する仕組みです。デフォルトはall。

innodb_change_bufferingの設定値

値	説明
all	挿入、削除マーキング、物理的な削除操作をバッファリング
none	バッファリングしない
inserts	挿入操作をバッファリング
deletes	削除マーキング操作をバッファリング
changes	挿入と削除マーキング操作をバッファリング
purges	物理的な削除操作をバッファリング

影響を受ける行と更新するセカンダリインデックスが大量にある場合は、変更バッファのマージに大量の時間がかかる場合もあるとのことで、注意が必要です。

なお、Amazon RDS for MySQLでは以下の理由からnoneが推奨されています。

Amazon RDS for MySQL のパラメータ設定 パート 1: パフォーマンス関連のパラメータ

変更バッファリングでは値を none にして無効にすることを推奨します。これは、all ではアップグレード時のシャットダウン時間がきわめて長くなるためです。この機能はディスク速度の遅い時代には有用でしたが、現在では意味がありません。

また、Aurora MySQLではそもそもこの設定値が無効化されています。

最後に

今回のまとめは以上となります。
間違った解釈などあれば、ご指摘いただければと思います。

参考

8.2.5.1 Optimizing INSERT Statements
15.5.2 Change Buffer
15.6.1.6 AUTO_INCREMENT Handling in InnoDB
AuroraかRDSどちらを選ぶべきか比較する話をDevelopers.IO 2019 in OSAKAでしました #cmdevio
適切ではない MySQL パラメータおよびステータス変数