mysqldumpの--order-by-primaryオプションについて
TIPSです。このようなテーブルがありまして、
CREATE TABLE `link` ( `id1` int(11) NOT NULL DEFAULT '0', `id2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id1`,`id2`), KEY `ix1` (`id2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
データは以下のような感じで、このときは2,900万レコードありました。
+---------+---------+ | id1 | id2 | +---------+---------+ | 5 | 69 | | 5 | 1022 | | 5 | 1487 | … | 1081 | 2021414 | | 1081 | 2087813 | | 1082 | 11 | | 1082 | 225 | … | 2494267 | 158887 | | 2494267 | 178845 | | 2494267 | 631394 | +---------+---------+
mysqldumpコマンドでバックアップすると、11秒で444MBのダンプファイルが出力されました。
$ time mysqldump -u xxx -pxxx xxx link > link.sql real 0m11.391s user 0m8.527s sys 0m0.529s $ ls -lh -rw-rw-r-- 1 taira taira 444M 1月 22 18:39 2012 link.sql
ところが、mysqlコマンドでリストアしようとすると異常に遅い。
$ time mysql -u xxx -pxxx xxx < link.sql real 94m23.951s user 0m11.661s sys 0m0.171s
わずか444MBのリストアに1時間半というのは尋常でない値で、どうしてだろうと思ってダンプファイルを見てみるとデータが主キーでソートされていないのです。
-- -- Dumping data for table `link` -- LOCK TABLES `link` WRITE; /*!40000 ALTER TABLE `link` DISABLE KEYS */; INSERT INTO `link` VALUES (1104,5),(1690,5),(6932,5),(7679,5),(8419,5),(10247,5) ,(11909,5),(13663,5),(13957,5),(16421,5),(51108,5),(56051,5),(68176,5),(73209,5) ,(74134,5),(80094,5),(80243,5),(90949,5),(105566,5),(141127,5),(146550,5),(14655 9,5),(147375,5),(164871,5),(172267,5),(197350,5),(214173,5),(256744,5),(260201,5 ),(295368,5),(363992,5),(467450,5),(535783,5),(619395,5),(651702,5),(688537,5),( 704851,5),(768590,5),(797017,5),(832366,5),(913097,5),(1072902,5),(1073296,5),(1 073968,5),(1074097,5),(1076529,5),(1076588,5),(1076596,5),(1099565,5),(1119078,5 ),(1134413,5),(1206043,5),(1217529,5),(1221166,5),(1284145,5),(1366678,5),(14288 06,5),(1520730,5),(1534350,5),(1601227,5),(1605124,5),(1643972,5),(1764627,5),(1 790257,5),(1806513,5),(2002342,5),(2092470,5),(2303850,5),(2323218,5),(2448464,5 ),(2459478,5),(2474731,5),(11,10),(58,10),(64,10),(68,10),(69,10),(91,10),(92,10 ),(102,10),(110,10),(115,10),(139,10),(159,10),(161,10),(312,10),(326,10),(1034, …
主キーでソートされていないデータのINSERTが遅いというのは、クラスタインデックスを利用しているInnoDBの宿命のようなものです。それにしても、なぜ主キーでソートされていないのでしょうか。
調べた結果、以下のことが分かりました。
- mysqldumpは単純に「SELECT /*!40001 SQL_NO_CACHE */ * FROM `link`」というSQLを実行しているだけである。
- 単純な全レコード取得に対して、MySQLがテーブルフルスキャン(InnoDBでは主キーのフルスキャンと同義)を選ぶとは限らない。
mysql> EXPLAIN SELECT * FROM link FORCE INDEX (PRIMARY); +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+ | 1 | SIMPLE | link | index | NULL | PRIMARY | 8 | NULL | 29403733 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM link; +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | link | index | NULL | ix1 | 4 | NULL | 29403733 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+ 1 row in set (0.00 sec)
今回id2カラムに対してix1というインデックスが作成されていて、InnoDBのセカンダリインデックスはリーフブロックの各インデックスエントリに主キーを含むのでこれは事実上カバーリングインデックスになっています。key_lenの値を見ると主キーの場合は8であるのに対してix1の場合は4となっており、MySQLからするとどうやらix1の方が効率的に見えてしまうようなのです。結果としてダンプファイルのデータは主キーでソートされていない状態で出力され、リストアの性能が大きく低下することになります。mysqldumpが悪いのかそれともmysqldが悪いのかは判断が難しいところですが、とにかくこれは不具合だと思いましたので報告しておきました(Bug#64097)。MySQL 5.5.20と5.1.61で確認済みです。
幸いにも簡単な回避方法があって、mysqldumpに--order-by-primaryオプションを付与することで「SELECT /*!40001 SQL_NO_CACHE */ * FROM `link` ORDER BY `id1`,`id2`」と明示的に主キーでソートしてくれるようになります。InnoDBのテーブルに対してmysqldumpを実行する際は、当面--order-by-primaryオプションを付与しておいた方が安全ではないかというのが私の見解です。
おまけとして、innodb_buffer_pool_sizeの設定とダンプファイルのデータが主キーでソートされているかどうかによって、リストア時間がどのように変化するのかを調べてみました。
innodb_buffer_pool_sizeがテーブルサイズに比べて大きい場合は、ダンプファイルが主キーでソートされていなくてもそれほど問題はありません。一方innodb_buffer_pool_sizeがテーブルサイズに比べて小さい場合は、リストア時間に大きな差が現れます。innodb_buffer_pool_sizeが小さく主キーでソートされていないパターンでは、リストア中ずっとストレージに対するランダムアクセスが続いていました。今回はストレージにPLEXTOR SSD M2Pシリーズ PX-256M2P
