たった3秒でInnoDBのデータローディングが快適になるライフハック

MySQLに限った話ではないが、データベース管理システムに大量のデータを投入するのは時間が掛かり大変苦痛を伴う作業である。劇的に効能があるわけではないが、MySQLを利用しているとき、特にInnoDBを使っている場合にはデータの投入を高速化するためにいくつかテクニックがあるので紹介しよう。皆さんの作業時間が短縮され、少しでも早く帰路に着いたりサービスインさせたりという形でお役に立てれば幸いである。ちなみに、タイトルはネタであるのだが、もし本当に3秒で以下の全ての設定を行えた人が居たら教えて頂きたい！

ログファイルサイズの調整

データ投入時に限った話ではないが、ログファイルサイズを調整するのは更新性能にとって非常に重要なファクターである。バッファプールのサイズが重要なことに代わりはないが、同じぐらいログファイルのサイズも重要である。InnoDBはログファイルを使い切ってしまうと、バッファプール内のダーティページをディスクへフラッシュしてログファイルの解放をしなければならず、その間は更新が滞ってしまうのである。初期設定時にログファイルサイズだけはきちんと調整しておくようにしたい。

ログファイルサイズの変更には注意が必要である。なぜなら、ログファイル内にはテーブルスペースへ反映されていないデータが存在するからである。ログファイルのサイズを変更するには、いったんログファイルを削除しなければならないが、ログファイル内にテーブルスペースへ反映されていないデータが残っているとそのデータは消滅してしまう。安全にログファイルを削除するには、ログファイル内のデータを全て残らずテーブルスペースへ適用しなければならない。そのためには次のような手順を踏む必要がある。

1. innodb_fast_shutdownシステム変数の値を2以外にする。
2. MySQLをシャットダウンする。
3. MySQLを起動して、データの更新を行わずにすぐシャットダウンする。（--skip-networkingオプションなどを利用するのが望ましい。）
4. MySQLをシャットダウンする。
5. ログファイル（ib_logfile*）を削除する。
6. MySQLを起動する。

ログファイルのサイズはどの程度が適切か？ということについては、こちらのエントリで説明しているので参照して欲しい。

ログバッファサイズの調整

InnoDBはログファイルへの書き込みを行う前に、その内容をトランザクションの完了時までいったんバッファ内に格納するようになっている。バッファが充分に大きければ、コミット時までトランザクションの内容を全てバッファ内に格納することが出来るため、無駄なI/Oが発生しないので性能の向上に繋がるというわけだ。ログバッファはトランザクションのサイズが大きい（つまり更新する行数が多い）場合に特に役立つ。データをロードするときなどがまさにこれに当てはまるのだ。普段、OLTP向けの負荷を処理する場合にはあまり大きくなくても構わないが、データをロードするときなどは事前に大きくしておくと効果がある。ログバッファサイズはinnodb_log_buffer_sizeオプションで調整する。データロード時にはとりあえず32MB程度は確保しておくといいだろう。

バイナリログを無効にする

データを大量にロードする場合はバイナリログを無効にしておくと良い。空のテーブルに対してデータを投入する場合は、バイナリログへ更新内容を書き出す意味は一切無い。（なぜなら、テーブルに投入するべきデータは手元にあるのだから！）特に、レプリケーションスレーブ構築時などのように、データをフルバックアップから戻す場合などにはバイナリログを有効化しておくのは本当に無駄なだけである。データを大量にロードする場合はバイナリログを無効化することで、無駄なファイル書き込みを減らすようにしよう。

log_binオプションを無効にしてMySQLを起動するか、SUPER権限を持っているユーザーなら次のようにsql_log_binを変更してそのセッションだけバイナリログを無効にすることが出来る。

mysql> SET sql_log_bin=OFF;
mysql> SOURCE 'dump.sql'

コミット時のディスクフラッシュ

InnoDBは、デフォルトの動作ではコミットと同時にログファイルの内容をディスクへフラッシュする。これは永続性を保証するために必要な処理であり、マシンのクラッシュ時などにデータを失わないために必要である。データを失いたくなければ断じてこのオプションを変更してはならない！しかし、データをロードするときなどはデータが手元にあるので、クラッシュ時に多少のデータが失われても何ら問題はないので話は別である。（途中で失敗したらロードを最初からやり直せばいいだけだ。）innodb_flush_log_at_trx_commitを0または2に変更すると、コミット時にログファイルのフラッシュを行わないようになるので、I/Oの負荷軽減が期待出来る。

ダブルライトバッファの無効化

InnoDBはテーブルスペースへ書き込みを行う際、同じ内容を2度書き込む。まずはじめにダブルライトバッファと呼ばれる領域へ、次に本番のページへ、である。そうすることで、クラッシュ時にデータファイルが破壊されるのを防いでいるのである。I/O帯域は増えてしまうが、データファイルの安全性が飛躍的に高まるのである。

フルバックアップをリストアする際などはクラッシュ時のデータ破壊などを恐れる必要はないので、その間はダブルライトバッファを無効にしておいても何ら問題はない。また、ZFSのように書き込みの一貫性が保証されているファイルシステム利用時には、ダブルライトバッファは必要がないので常時無効にすることが可能である。ZFSの詳細については、以前書いたエントリで紹介しているので参照されたい。

ダブルライトバッファを無効にするには、--skip_innodb_doublewriteオプションを利用しよう。

自動拡張の抑制

InnoDBはデフォルトではテーブルスペースを自動拡張するようになっている。当然ながら自動拡張時にはI/Oが発生するので、通常利用時にはなるべく自動拡張を避けた方がI/Oがスムーズになる。利用出来るディスク領域のサイズは予め決まっていることが殆どなので、その場合は次のようにデータファイルのサイズを決め打ちで初期化しておくといいだろう。そうすると当然初期化に時間がかかってしまうことになるので、その点は悩ましいところである。自動拡張時は一気に初期化したほうが効率がいいので、innodb_autoextend_incrementを組み合わせて次のように設定するという折衷案がおすすめである。

innodb_data_file_path=ibdata1:10G:autoextend:max:100G
innodb_autoextend_increment=64

ちなみに、いったんデータファイルが拡張してしまうと縮退することは出来ないので、安全のためmax値を指定しておこう。

パーティショニング

パーティショニングを上手に工夫することで、データのロードが高速になる場合がある。パーティショニングの種類はRANGEに限られるのだが、主キーと同じように単調増加するタイムスタンプ値などでRANGEパーティショニングをすると、セカンダリインデックスのキャッシュヒット率が向上するからである。セカンダリインデックスは通常主キーとは何ら関係のない値が格納されているので、テーブル全体に値が散らばっている。するとセカンダリインデックス全体がキャッシュされる確率は非常に低下するのだが、RANGEパーティショニングを使ってパーティションをコンパクトにすると、該当するセカンダリインデックスがキャッシュされる確率も向上するのである。InnoDBには挿入バッファという仕組みがあり、セカンダリインデックスへの更新が多少効率化されているのであるが、パーティショニングを利用してキャッシュ効率を上げた方が効果は高い。

更新のサイズ

InnoDBテーブルへの更新においては、適当な間隔でコミットを行わなうべきである。InnoDBは更新時にUNDOログをロールバックセグメント内に作成する。UNDOログは不要になれば削除されるのだが、一度に大量の行を更新したり挿入したりすると肥大化してしまう。UNDOログが肥大化するとバッファプールの空きページが枯渇してしまい、テーブルスペース内に書き込まれることになってしまう。そうなるとI/Oの帯域が無駄になってしまうので、UNDOログが肥大化しないよう適切なサイズでコミットしよう。

この問題は、LOAD DATA INFILEコマンドでデータをロードするときに顕在化することが多いので注意しよう。LOAD DATA INFILEコマンドでは、全てのデータを一回のトランザクションとして投入してしまうためである。CSVファイルをインポートする場合には、次で説明するCSVストレージエンジンを活用すると幸せになれること請け合いである。

CSVストレージエンジンの活用

まさにこれがMySQLの醍醐味！なのであるが、ストレージエンジンを上手に使いこなすことで様々な処理を効率化することが出来る。CSVストレージエンジンを使うと、CSV（カンマ区切り）ファイルをそのままテーブルとして認識することが出来てしまうのである。CREATE TABLEでテーブルを定義し、CSVファイルを入れ替えてFLUSH TABLESすればMySQLからデータの準備は完了である。ALTER TABLEを使ってInnoDBに変換すればいっちょあがりだ。この方法なら、1000行おきにコミットを行ってくれるのでLOAD DATA INFILEコマンド利用時のようにロールバックセグメントが肥大化する問題は生じない。

CSVストレージエンジン用のデータファイルを作成するには、次のようにSELECT ... INTO OUTFILEを実行しよう。

mysql> SELECT * INTO OUTFILE 'filename.CSV' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM t;

CSVストレージエンジンにはインデックスを定義することが出来ないので、ロードする先のMySQLサーバでインデックス抜きのCSVテーブルを作成しよう。

mysql> CREATE TABLE テーブル名 (テーブル定義諸々・・・ただしインデックスなし) ENGINE CSV;

そうするとデータベースディレクトリ内に「テーブル名.CSV」というファイルが作成されるので、これを先ほど作成したCSVファイルと差し替えよう。そしてALTER TABLEを実行すればデータのローディングは完了だ。

CSVテーブルにはインデックスがないのでALTER TABLEを使ってインデックスを作成する必要があるのだが、ALTER TABLEを何度も行うと返って時間が掛かってしまうことになる。そこで、次のようにコマンドひとつでインデックス作成とストレージエンジンの変更をするように心がけよう。

mysql> ALTER TABLE t ADD PRIMARY KEY (id), ADD INDEX (col1, col2), ...., ENGINE INNODB;

まとめと次回予告

今回紹介した「データのローディングを高速化するテクニック」は次の通りである。

• ログファイルサイズとログバッファサイズを調整する。
• ロード中はバイナリログ、ダブルライトバッファを無効化し、コミット時のディスクフラッシュの動作を変更しておく。
• RANGEパーティショニングでセカンダリインデックスのキャッシュ効率を向上させる。
• 更新のサイズをあまり大きくしすぎない。
• CSVストレージエンジンを活用する。

なお、今回紹介したロード時間短縮のチューニングを全て適用すると、次のようなパラメータになる。

#log-bin=binlog <--- バイナリログの設定をコメントアウト

innodb_log_file_size=512M
innodb_log_buffer_size=32M
skip_innodb_doublewrite
innodb_flush_log_at_trx_commit=2
innodb_data_file_path=ibdata1:10G:autoextend:max:100G
innodb_autoextend_increment=64

バイナリログの設定や、ダブルライト、コミット時のディスクフラッシュの動作については、データのロードが完了したら速やかに元に戻しておこう。（通常利用時にクラッシュしたら大変である。） なお、これらの効果を実感して頂くために簡単なベンチマーク結果を紹介しよう。Wikipediaのデータは無料で公開されており、以下のページから誰でも入手が可能となっている。 Wikimedia Downloads http://download.wikimedia.org/ データサイズが大きいのでローディングのテストにはちょうどいい。今回はjawikiのcategorylinksテーブルのデータを用いてロード性能の測定を行った。ハードウェアはちょっと古いPCで、SATA-HDD使用。敢えて全てのデータがキャッシュに収まらないようにするため、InnoDBバッファプールのサイズは128MBにしてある。（実際の利用シーンではデータサイズもバッファプールももっと大きいだろう。）条件を変えながらロードにかかった時間を測定した結果は次の通り。

項番	測定条件	経過時間
1	InnoDBバッファプール=128M以外未調整	60m59s
2	上記に加えてログファイルサイズ、ログバッファサイズ調整	14m41s
3	上記に加えてバイナリログ無効化、ダブルライトバッファ無効化、コミット時のディスクフラッシュ動作変更(2)	10m47s
4	上記に加えて月単位でRANGEパーティショニング	6m33s
5	LOAD DATA INFILE（項番3と同じ条件）	15m12s
6	CSVストレージエンジンからALTER TABLE（項番3と同じ条件）	9m38s

まさに劇的ビフォーアフターである！！しかし、MySQLの実力はまだまだこんなものではない。次回は、MySQLの更新性能をさらに別次元まで高めるためのテクニックを紹介しようと思うので乞うご期待！！

追記: InnoDB Pluginの圧縮テーブルを利用するとI/O帯域が節約されるのでローディングは速くなる。項番(4)と同じ条件で、InnoDB Plugin/ROW_FORMAT=Compressedにすると、5m50s程度まで実行時間が短縮された。InnoDB Pluginの使い方については前回のエントリを参照。