Open5

MySQLのバルクインサートのベストプラクティスめも

経緯

鯖性能下げた事でバッチ間に合うか?みたいな不安が生まれた。速度調査するならいっその事最速にしようと思い調査。

tldr

特定条件(書けない)で
insert → 280秒
Load Data Infile → 120秒
最適化Load Data Infile → 2秒

最適化はオプションのみ考慮。my.confとかでforeign checkやらrepliのトリガをコネコネはしない。

load data infileとは?

ファイルからDBにいれる時に使える、mysql builtinのコマンド。最適最速。

# 間違ってたらごめんなさい

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'filename'
    [REPLACE | IGNORE]
    INTO TABLE tablename
    [FIELDS
        [TERMINATED BY 'delimiter']
        [[OPTIONALLY] ENCLOSED BY 'surrounding character']
        [ESCAPED BY 'escape sequence' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE {number} LINES]
    [(フィールド名, ...)]

delimiterのデフォルトはタブ。EOLのデフォルトは改行。

ちなみに逆にファイルに吐き出すならばSELECT INTO OUTFILEがベスト。
これ脳死で使うだけで2倍近く速い。けどそもそもforeign checkやらで大量データをぶちこむと、そういうただ挿入したいときの足かせがあまりに多すぎて普通にとんでもない時間がかかっちゃう。

なのでオプション系で最適化してく。

load data infileの高速化オプション

データ入れる時限定。そもそも構造壊しては駄目。

・最初にPKでファイルをソートしとく
・複数indexあるなら一旦alterでなくしとく。map生成がすごい重い。
・ファイルの整合性とれてるなら、unique_checks=0 で挿入時unique_checkしない。(スプシとか想定)
・ファイルの整合性とれてるなら、foreign_checks=0 で挿入時foreign_checkしない。(スプシとか想定)
innodb_autoextend_increment=64 などでauto_incremental_checkしてるとロックかかるので一旦外す。
・バッファを増やす。上限行くとそのたびにflushするので超重い。innodb_log_file_size=512M innodb_log_buffer_size=32M。これデフォルトでいいかもしれない。
・commitするたびにflushかかるのを止める、skip_innodb_doublewrite SET autocommit=0 SET innodb_flush_log_at_trx_commit=0 (ファイルがあるし)
・レプリ考慮しなくていいなら一旦binary logをsql_log_bin=OFFで止める。(ファイルがあるし)
・オートスケールもいらない(ファイルでサイズはわかってるはず。ユーザーレコードなら無)

これで信じられないほど爆速になる。
戻す手間の方がアレだけど極限状態なら生きる。

Thanks and References

https://dev.mysql.com/doc/refman/5.6/ja/insert-speed.html
  • 「テキストファイルからテーブルをロードする場合は LOAD DATA INFILE を使用します」
  • 「通常、これは INSERT ステートメントを使用する場合より、20 倍速くなります。」

https://dev.mysql.com/doc/refman/5.6/ja/load-data.html
  • 「ucs2、utf16、utf16le、または utf32 文字セットを使用するデータファイルはロードできません。」
  • 「ロード操作中に外部キー制約を無視するには、LOAD DATA を実行する前に SET foreign_key_checks = 0 ステートメントを発行します。」
  • 「空の MyISAM テーブルに対して LOAD DATA INFILE を使用した場合、一意でないインデックスはすべて (REPAIR TABLE として) 別のバッチに作成されます。通常、多くのインデックスがあるときは、この方法で LOAD DATA INFILE がはるかに早くなります。一部の極端なケースでは、ファイルをテーブルにロードする前に ALTER TABLE ... DISABLE KEYS でインデックスを無効にし、ファイルをロードしたあとに ALTER TABLE ... ENABLE KEYS を使用してインデックスを再作成することによって、インデックスをさらに高速に作成できます。」 → これは管理上かなり危険
  • 「読み取るすべての行に、無視したい共通のプリフィクスが含まれている場合は、LINES STARTING BY 'prefix_string' を使用して、プリフィクスとその前にあるすべてのものをスキップできます。」
  • 「IGNORE number LINES オプションを使用すると、ファイルの先頭にある行を無視できます。」

https://dev.mysql.com/doc/refman/5.6/ja/optimizing-innodb-bulk-data-loading.html
  • 「InnoDB にデータをインポートする場合、自動コミットモードでは挿入のたびに、ディスクへのログのフラッシュを実行するため、それをオフにします。インポート操作時に自動コミットを無効にするには、それを、SET autocommit ステートメントと COMMIT ステートメントで囲みます。」
  • 「副キーに UNIQUE 制約がある場合、インポートセッション中に一意性チェックを一時的にオフにすることで、テーブルインポートを高速化できます。大きいテーブルの場合、InnoDB はその挿入バッファーを使用して、セカンダリインデックスレコードを一括して書き込むことができるため、これにより、大量のディスク I/O が節約されます。データに重複キーが含まれていないことを確認してください。」
  • 「テーブルに FOREIGN KEY 制約がある場合、インポートセッションの間の外部キーチェックをオフにすることで、テーブルインポートを高速化できます。大きいテーブルの場合、これにより、大量のディスク I/O を節約できます。」
  • 「自動インクリメントカラムのあるテーブルへの一括挿入を実行する場合、innodb_autoinc_lock_mode をデフォルト値の 1 の代わりに 2 に設定します。」 → これはCSVから自動インクリかどうかのチェックが難しいのでやらない。

http://nippondanji.blogspot.com/2010/03/innodb.html
  • 「InnoDBはログファイルを使い切ってしまうと、バッファプール内のダーティページをディスクへフラッシュしてログファイルの解放をしなければならず、その間は更新が滞ってしまうのである。初期設定時にログファイルサイズだけはきちんと調整しておくようにしたい。」 → これはすべてのmysql設定を変えてしまう&、このコマンド終了したら直す方法も、mysqlを一度リセットしないといけないのでNG( innodb_log_file_size)これで4倍くらい早くなるんだけど。
  • 「ログファイルサイズの変更には注意が必要である。なぜなら、ログフ ァイル内にはテーブルスペースへ反映されていないデータが存在するからである。ログファイルのサイズを変更するには、いったんログファイルを削除しなければならないが、ログファイル内にテーブルスペースへ反映されていないデータが残っているとそのデータは消滅してしまう。安全にログファイルを削除するには、ログファイル内のデータを全て残らずテーブルスペースへ適用しなければならない。」 → これはすべてのmysql設定を変えてしまう&、このコマンド終了したら直す方法も、mysqlを一度リセットしないといけないのでNG( innodb_log_buffer_size)
  • 「データを大量にロードする場合はバイナリログを無効にしておくと良い。空のテーブルに対してデータを投入する場合は、バイナリログへ更新内容を書き出す意味は一切無い。(なぜなら、テーブルに投入するべきデータは手元にあるのだから!)特に、レプリケーションスレーブ構築時などのように、データをフルバックアップから戻す場合などにはバイナリログを有効化しておくのは本当に無駄なだけである。」 → レプリしてるのでダメ。
  • 「InnoDBは、デフォルトの動作ではコミットと同時にログファイルの内容をディスクへフラッシュする。これは永続性を保証するために必要な処理であり、マシンのクラッシュ時などにデータを失わないために必要である。データを失いたくなければ断じてこのオプションを変更してはならない!しかし、データをロードするときなどはデータが手元にあるので、クラッシュ時に多少のデータが失われても何ら問題はないので話は別である。(途中で失敗したらロードを最初からやり直せばいいだけだ。)innodb_flush_log_at_trx_commitを0または2に変更すると、コミット時にログファイルのフラッシュを行わないようになるので、I/Oの負荷軽減が期待出来る。」
  • 「InnoDBはテーブルスペースへ書き込みを行う際、同じ内容を2度書き込む。まずはじめにダブルライトバッファと呼ばれる領域へ、次に本番のページへ、である。そうすることで、クラッシュ時にデータファイルが破壊されるのを防いでいるのである。I/O帯域は増えてしまうが、データファイルの安全性が飛躍的に高まるのである。フルバックアップをリストアする際などはクラッシュ時のデータ破壊などを恐れる必要はないので、その間はダブルライトバッファを無効にしておいても何ら問題はない。」
  • 「 自動拡張時にはI/Oが発生するので、通常利用時にはなるべく自動拡張を避けた方がI/Oがスムーズになる。」
  • 「InnoDBテーブルへの更新においては、適当な間隔でコミットを行わなうべきである。InnoDBは更新時にUNDOログをロールバックセグメント内に作成する。UNDOログは不要になれば削除されるのだが、一度に大量の行を更新したり挿入したりすると肥大化してしまう。UNDOログが肥大化するとバッファプールの空きページが枯渇してしまい、テーブルスペース内に書き込まれることになってしまう。そうなるとI/Oの帯域が無駄になってしまうので、UNDOログが肥大化しないよう適切なサイズでコミットしよう。 この問題は、LOAD DATA INFILEコマンドでデータをロードするときに顕在化することが多いので注意しよう。LOAD DATA INFILEコマンドでは、全てのデータを一回のトランザクションとして投入してしまうためである。CSVファイルをインポートする場合には、次で説明するCSVストレージエンジンを活用すると幸せになれること請け合いである。」→CSVなら、select into outfile使うとストレージエンジン用のデータファイルができて、1000行おきにコミットを自動で行ってくれるようになる。ただこれ、ファイル一つにおきテーブル一つみたいにしないとCSVテーブルを作れない。1000行ずつなら、現状の1ファイルずつのロードで代替オッケー層

https://qiita.com/imaifactory/items/1d9e52073cb82571bdc4
  • インポート先がInnoDBだとすると、InnoDBへのインサートはClustered Indexの再構築であるという点を抑える必要がある。すなわちインポートするCSVをPrimary Keyでソートしておくと高速になる → これはマスタ側でどうにかする

https://open-groove.net/mysql/autocommit/ https://stackoverflow.com/questions/2950676/difference-between-set-autocommit-1-and-start-transaction-in-mysql-have-i-misse

  • 「オートコミットを有効にした場合(デフォルトで有効)、2.START TRANSACTION文を実行しなかった場合 単一のSQL文が実行された時点で自動的にコミットする。ロールバックはできない。」
  • 「オートコミットを有効にした場合(デフォルトで有効)、1.START TRANSACTION文を実行した場合 単一のSQL文が実行されただけではコミットせず、COMMIT文を実行した時点で初めてコミットされる。ROLLBACK文を実行すればロールバックする。」
  • 「オートコミットを無効にした場合 この場合のトランザクションは、SQL文の実行によって暗黙的に開始する。または、START TRANSACTION文の実行により明示的に開始する。どちらにしても、自動的にはコミットせず、COMMIT文を実行した時点で初めてコミットされる。また、ROLLBACK文を実行すればロールバックする。」 → これが最速
ログインするとコメントできます