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
MySQL 5.0のプロダクトライフサイクルが終了
あけましておめでとうございます。今年もよろしくお願いいたします。
新年最初から注意喚起ですが、2011年12月31日をもってMySQL 5.0のプロダクトライフサイクルが終了しました。今後MySQL 5.0に対して新たなバグ修正やセキュリティ修正は提供されませんので、現在MySQL 5.0を利用している方はバージョンアップを計画されることをおすすめいたします。
オラクル社による買収以降、ソフトウェア製品のサポートポリシーは以下のウェブサイトで確認できるようになっています。
Oracle Lifetime Support Policy for Oracle Software
MySQL 5.0のプロダクトライフサイクルが終了したということで、一年ぶりにバグ曲線を作成してみました。
MySQL 5.0や5.1と比較すると、MySQL 5.5のバグ曲線は早くも収束し始めているようです。しかしmysqldumpで--single-transactionと--flush-logsを組み合わせると一貫性バックアップにならない、sjis環境で意図したインデックスが使われない、レプリケーション環境でメモリの二重解放が発生するなど昨年見つかったいくつかの不具合についてその後の進展が見られず、MySQL 5.5が十分に枯れたと判断するにはまだ早いと考えています。
INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011
2011年8月のkazeburoさんのエントリに対する解説記事です。結論から言うとkazeburoさんの案に賛成なのですが、本日はどうしてそうなったのかというところを確認していきたいと思います。本記事はMySQL Casual Advent Calendar 2011の17日目のエントリです。16日目はakira1908jpさんでした。
当時の内容を覚えていない方は、先にkazeburoさんのエントリをご一読ください。また、テストケースがGitHubに公開されていますのでカジュアルに再現試験をすることも可能です。
問題のSQLをチューニングするには、MySQLがインデックスに対してどのようにアクセスするかという点についての知識が必要です。まずはインデックスに対するアクセス方式が一つだけではないということを、Oracle DatabaseとMySQLを対比させながら見ていきます。
Bツリーインデックスの構造
以下はentriesテーブルに付与されたセカンダリインデックスを図示したものです。entriesテーブルには主キー(id)に加えてセカンダリインデックス(user_id, status, created_at)が付与されていますが、以降の説明では一部スペースの関係でセカンダリインデックスのstatusカラムを省略しています。このセカンダリインデックスは、複数のカラムが指定された複合インデックス(Composite Index)になっているところがポイントです。リーフブロックの各インデックスエントリには、Oracle Databaseの場合はレコードの物理格納位置を指し示す10バイトの拡張ROWID、MySQL(InnoDB)の場合は主キーの値が格納されています。
インデックスアクセスの方式は大きく分けてOracle Databaseで5種類、MySQLで3種類あります。順に説明していきます。
1. Bツリーをたどり、特定のリーフブロックにアクセスする
WHERE user_id = 2 AND created_at = '2011-09-14'
Bツリーをたどり、リーフブロックに格納されたインデックスエントリを一つ読み取ります。ユニークインデックスに対して等価検索を行ったときのアクセス方式です。Oracle DatabaseではINDEX UNIQUE SCAN、MySQLではtype = constと呼ばれています。もっとも効率的です。
2. Bツリーをたどり、さらにリーフブロックのリスト構造をたどる
WHERE user_id = 2 AND created_at BETWEEN '2011-09-01' AND '2011-09-15'
Bツリーをたどり、リーフブロックに格納されたインデックスエントリを一つあるいは複数読み取ります。必要なインデックスエントリが複数のリーフブロックにまたがっている場合は、リーフブロック同士を連結しているリスト構造をたどって隣のリーフブロックへとアクセスしていきます。非ユニークインデックスに対して等価検索を行ったとき、あるいはインデックスに対して範囲検索を行ったときのアクセス方式です。Oracle DatabaseではINDEX RANGE SCAN、MySQLではtype = refあるいはtype = rangeと呼ばれています。二番目に効率的です。
3. リーフブロックのリスト構造を、最初から最後までたどる
WHERE created_at = '2011-09-10'
リーフブロックのリスト構造を最初から最後までたどり、すべてのインデックスエントリを読み取って検索条件に合致するものを返します。Oracle DatabaseではINDEX FULL SCAN、MySQLではtype = indexと呼ばれるアクセス方式です。図を見ると分かるように、本来アクセス不要なリーフブロックにも大量にアクセスすることになるため非効率であり、性能は良くありません。テーブルをフルスキャンするよりはまだ良いというレベルです。
このアクセス方式はインデックスに格納された順にデータを読み取っていくことで、ソート済みの状態で結果を取得できるところが特長です。Oracle Databaseではインデックスに対する有効な絞り込み条件がなく、かつ、結果をソートされた状態で返す必要があるときにこのアクセス方式が選ばれることがあります。有効な絞り込み条件がないというのは、複合インデックスのいくつかのカラムに対する検索条件が抜けている、NOT条件で検索している、検索条件のカラムに対して関数が適用されている、といったケースが考えられます。実際にこのアクセス方式を選ぶかどうかは統計情報を元にSQLオプティマイザが決めることであり、経験的にはこのアクセス方式が選ばれることはあまり多くありません。
MySQLについては後述します。
4. リーフブロックのリスト構造を、スキップしながら最初から最後までたどる (Oracle Databaseのみ)
※ 図がカジュアル
WHERE created_at = '2011-09-10'
リーフブロックのリスト構造を最初から最後までたどるのですが、このとき不要なブロックをスキップしながらインデックスエントリを読み取っていきます。Oracle Databaseでインデックスの前方カラムに対する有効な絞り込み条件がないものの、後方カラムに対する絞り込み条件がある場合に選ばれることのあるアクセス方式です。これはOracle 9iから導入された機能で、INDEX SKIP SCANと呼ばれています。
このアクセス方式が効率的であるかどうかは、スキップされる前方カラムのカーディナリティに依存します。例えば複合インデックスの第1カラムが性別である場合は、INDEX SKIP SCANの処理量はINDEX RANGE SCANを男性と女性で2回行う程度で済み、十分効率的と言えるでしょう。一方、前方カラムのカーディナリティが大きい場合は、処理量がINDEX FULL SCANに近いレベルにまで悪化することになります。
INDEX SKIP SCANは「FULL」という単語がついていないので問題視されないことが多いのですが、実際にはかなり遅いですので何らかの対策を打つべきです。INDEX SKIP SCANはINDEX FULL SCANを少しでも速くするための救済措置と理解する方が適切です。
MySQLにはこのような機能はありません。
5. リーフブロックを、リスト構造を無視してディスク格納順に読み取る (Oracle Databaseのみ)
※ 図がカジュアル
WHERE created_at = '2011-09-10'
リーフブロックをリスト構造を無視してディスク格納順に最初から最後までたどり、すべてのインデックスエントリを読み取ります。Oracle DatabaseでINDEX FAST FULL SCANと呼ばれるアクセス方式です。
インデックスのリーフブロックはディスク上に順番に並んでいるとは限りませんので、INDEX FULL SCANの場合は通常ディスクに対するランダムアクセスが行われます。一方このINDEX FAST FULL SCANでは、ランダムではなくシーケンシャルアクセスとなります。一般的にランダムアクセスはSSDを使わない限り極めて遅いですので、INDEX FAST FULL SCANの方に性能面での優位性があります。ただし、結果はソートされていない状態で返されます。
経験上INDEX FULL SCANはあまり見たことがないのですが、INDEX FAST FULL SCANは時々見かけます。テーブルをフルスキャンせざるを得ないようなSQLにおいて、いずれかのインデックスがカバーリングインデックスになっていれば、余計なカラムにディスク容量を取られていない分だけテーブルフルスキャンよりもINDEX FAST FULL SCANの方が有利となります。
MySQLにはこのような機能はありません。
INDEX FULL SCANを狙う
entriesテーブルの定義を再度確認します。
CREATE TABLE `entries` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `is_mobile` tinyint(3) unsigned NOT NULL, `title` varchar(512) NOT NULL, `body` text NOT NULL, `created_at` datetime NOT NULL, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `status` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`,`status`,`created_at`) ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4
チューニングしたいSQLを以下に示します。
SELECT COUNT(*) FROM entries WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59' AND is_mobile = 1;
念のためですが、このSQLをチューニングするための最適解は、kazeburoさんも述べていらっしゃるように(is_mobile, created_at)という複合インデックスを追加作成することです。綺麗なINDEX RANGE SCANにするため等価条件で検索されるカラムを前に、範囲条件で検索されるカラムを後ろに定義するところがポイントですが、あとは特に難しいところはないと思います。今回は諸事情によりインデックスの追加作成ができないという制約があって、それでもテーブルフルスキャンより効率的な方式があれば採用したい、という話題です。
先にOracle Databaseの挙動を確認しておきたいと思います。本記事の前半で5種類のインデックスアクセス方式を説明しましたが、Oracle DatabaseのSQLオプティマイザはその中からINDEX SKIP SCANを選択します。
SQL> r
1 SELECT COUNT(*)
2 FROM entries
3 WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59'
4* AND is_mobile = 1
COUNT(*)
----------
337
Execution Plan
----------------------------------------------------------
Plan hash value: 2621163538
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3833 (1)| 00:00:46 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| ENTRIES | 1668 | 23352 | 3833 (1)| 00:00:46 |
|* 3 | INDEX SKIP SCAN | ENTRIES_IX1 | 3335 | | 496 (0)| 00:00:06 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("IS_MOBILE"=1)
3 - access("CREATED_AT">=TO_TIMESTAMP('2011-12-10 00:00:00') AND
"CREATED_AT"<=TO_TIMESTAMP('2011-12-10 23:59:59'))
filter("CREATED_AT">=TO_TIMESTAMP('2011-12-10 00:00:00') AND
"CREATED_AT"<=TO_TIMESTAMP('2011-12-10 23:59:59'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3861 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedセカンダリインデックス(user_id, status, created_at)のうち検索条件に指定されているのは第3カラムのcreated_atのみですが、Oracle DatabaseではINDEX SKIP SCANによってこのインデックスを有効に活用することができます。テーブルフルスキャンをした場合と比較すると、アクセスデータブロック数を示すconsistent getsがテーブルフルスキャンの24,135に対しINDEX SKIP SCANでは3,861と減少しており、6倍程度効率的になっていることが分かります。レポーティングなど実行頻度の少ないSQLであれば、これ以上のチューニングは特に必要ないのではないかと思います。
SQL> r
1 SELECT /*+ FULL(entries) */ COUNT(*)
2 FROM entries
3 WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59'
4* AND is_mobile = 1
…
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24135 consistent getsMySQLの場合はどうすれば良いでしょうか。集計処理なのでINDEX UNIQUE SCAN(type = const)では意味がありませんし、INDEX RANGE SCAN(type = range)は複合インデックスの第1カラムから順番に検索条件に含まれていないと使えません。INDEX SKIP SCANやINDEX FAST FULL SCANはそもそも機能がありませんので、消去法でINDEX FULL SCAN(type = index)を狙うということになります。
MySQLでカジュアルにINDEX FULL SCANを狙ってみると、そう簡単には意図したSQL実行計画にならないことが分かります。元エントリのブックマークコメントで私とid:kazuhookuさんがしばらくハマっていました。
■何もしないとテーブルフルスキャンになる
mysql> EXPLAIN
-> SELECT COUNT(*)
-> FROM entries
-> WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59'
-> AND is_mobile = 1;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | entries | ALL | NULL | NULL | NULL | NULL | 102106 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
■FORCE INDEXは効かない
mysql> EXPLAIN
-> SELECT COUNT(*)
-> FROM entries FORCE INDEX (user_id)
-> WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59'
-> AND is_mobile = 1;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | entries | ALL | NULL | NULL | NULL | NULL | 102106 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
■サブクエリに追い出してみても効かない
mysql> EXPLAIN
-> SELECT COUNT(*)
-> FROM (
-> SELECT is_mobile
-> FROM entries FORCE INDEX (user_id)
-> WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59'
-> ) e
-> WHERE is_mobile = 1;
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3388 | Using where |
| 2 | DERIVED | entries | ALL | NULL | NULL | NULL | NULL | 102106 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+実はMySQLのINDEX FULL SCANには一つ罠があります。MySQLでINDEX FULL SCANが選択されるには、「SQLがそのインデックスに定義されたカラムにしかアクセスしないこと」という制約条件を満たす必要があるのです。言い方を換えると「カバーリングインデックスになるときにしかINDEX FULL SCANはアクセス方式の候補にならない」ということになります。マニュアルにも明記されているのですが、寡聞にして知りませんでした。
This join type is the same as ALL, except that only the index tree is scanned. This usually is faster than ALL because the index file usually is smaller than the data file. MySQL can use this join type when the query uses only columns that are part of a single index.
実際の挙動を確認してみると、以下のようになります。
■インデックスに定義されたカラムのみにアクセスすれば、INDEX FULL SCANになる
mysql> EXPLAIN
-> SELECT status
-> FROM entries
-> WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59';
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
| 1 | SIMPLE | entries | index | NULL | user_id | 13 | NULL | 102106 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
■is_mobileはこのインデックスに定義されたカラムではないので、テーブルフルスキャンになる
mysql> EXPLAIN
-> SELECT is_mobile
-> FROM entries
-> WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59';
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | entries | ALL | NULL | NULL | NULL | NULL | 102106 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
■idはこのインデックスに定義されたカラムではないが、INDEX FULL SCANになる
mysql> EXPLAIN
-> SELECT id
-> FROM entries
-> WHERE created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59';
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
| 1 | SIMPLE | entries | index | NULL | user_id | 13 | NULL | 102106 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+さらに、上記の3番目の例がポイントです。idカラムはこのインデックスには定義されていないのですが、MySQL(InnoDB)のセカンダリインデックスでは主キーの値がリーフブロックの各インデックスエントリに格納されています。そのため「SQLがそのインデックスに定義されたカラムにしかアクセスしないこと」という制約条件が満たされ、INDEX FULL SCANが選択できるのです。
ここまで理解すれば、問題のSQLをどのように書き換えれば良いのかが分かると思います。
- 「created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59'」という検索条件をインデックスで解決したい。
- しかし、使えそうなインデックスは今のところ(user_id, status, created_at)しかなく、検索条件として利用したいカラムが複合インデックスの後方に定義されてしまっている。
- このインデックスを活用できるアクセス方式は、MySQLの場合INDEX FULL SCAN(type = index)のみである。
- INDEX FULL SCANが選択されるには、SQLがそのインデックスに定義されたカラムにしかアクセスしないという制約条件を満たす必要がある。
- 制約条件を満たすために、is_mobileカラムをSQLから除外する必要がある。しかしis_mobileカラムは最終的には必要である。
- これらの条件をすべて満たすには、同じテーブルを2回SELECTして結果をJOINすればよい。JOINに用いるカラムはidである。
- idカラムは主キーであり、MySQL(InnoDB)のすべてのインデックスに暗黙的に含まれている。そのためINDEX FULL SCANの制約条件は満たされる。
mysql> EXPLAIN
-> SELECT COUNT(*)
-> FROM entries e1 INNER JOIN entries e2 ON e1.id = e2.id
-> WHERE e1.created_at BETWEEN '2011-12-10 00:00:00' AND '2011-12-10 23:59:59'
-> AND e2.is_mobile = 1;
+----+-------------+-------+--------+---------------+---------+---------+-------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+--------+--------------------------+
| 1 | SIMPLE | e1 | index | PRIMARY | user_id | 13 | NULL | 102106 | Using where; Using index |
| 1 | SIMPLE | e2 | eq_ref | PRIMARY | PRIMARY | 4 | scott.e1.id | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+--------+--------------------------+というわけで、ようやくkazeburoさんと同じ結論にたどり着きました。お疲れさまでした。
余談ですが、MySQL 5.6ではIndex Condition Pushdownという新機能によって、Oracle DatabaseにおけるINDEX SKIP SCANに近いアクセス方式が取れるようになるかもしれません。漢の奥野さんがEnterpriseZineに解説記事を寄稿していらっしゃいますので、ご一読ください。
しゅしゅミクモデルはしゅしゅPさま、たこルカモデルMk-IIは6666AAPさまの制作です。この場を借りて御礼申し上げます。MySQL Casual Advent Calendar 2011、18日目はsfujiwaraさんです。それではまた。
SystemTapでMySQL 5.5のDisk I/Oを分析する
2010年1月の記事SystemTapでMySQLのDisk I/Oを分析するの続きです。以前作成したSystemTapスクリプトは、実はMySQL 5.5のDisk I/Oを分析することができませんでした。というのも、MySQL 5.5からInnoDBが非同期I/Oを行うようになったのですが、以前のスクリプトは非同期I/Oに対応していなかったためです。本日はMySQL 5.5におけるInnoDBの非同期I/Oについて、確認していきたいと思います。
非同期I/Oとは
非同期I/Oとは、I/O処理をブロックされることなしに行う方式のことです。通常のI/O処理はそれが完了するまで待たされてしまうのですが、非同期I/Oを用いることでI/O処理の完了を待つことなしに他の処理を進めることができます。以下のウェブサイトでとても詳しく解説されています。
- バッファキャッシュとAIO（1） - O'Reilly Japan Community Blog
- バッファキャッシュとAIO（2） - O'Reilly Japan Community Blog
- バッファキャッシュとAIO（3） - O'Reilly Japan Community Blog
- バッファキャッシュとAIO（4） - O'Reilly Japan Community Blog
詳解 Linuxカーネル 第3版
MySQL 5.5のInnoDBにおける非同期I/Oの利用
InnoDBは元々Windowsでのみ非同期I/Oを行っており、それ以外のプラットフォームでは「simulated AIO」と呼ばれる擬似的な非同期I/Oを行っていました。これはSQLを実行するコネクションスレッドがI/O要求をキューイングし、I/OヘルパースレッドがI/O要求をキューから取り出して実際のI/Oを行うというものです。コネクションスレッドがI/Oの完了を待たなくて良いという意味では確かに非同期ですが、I/Oヘルパースレッドが行う処理はあくまで同期I/Oであるため、この方式はsimulated AIOと呼ばれていました。I/Oヘルパースレッドの数はinnodb_read_io_threads、innodb_write_io_threadsという二つのパラメータで設定することが可能です。
MySQL 5.5のInnoDBでは、LinuxにおいてOSネイティブの非同期I/Oが利用されるようになりました。このためにはあらかじめlibaioパッケージをインストールしておく必要がありますが、設定パラメータinnodb_use_native_aioはデフォルトで有効になっており、何もしなければ自動的に非同期I/Oが利用されるようになっています。ただし現在のLinuxカーネルでは、Direct I/Oを利用している場合にのみ非同期I/Oが非同期になるという点に注意してください。つまり、非同期I/Oを利用するにはinnodb_flush_method = O_DIRECTの設定が必須となります。
非同期I/Oが有効化されている場合、コネクションスレッドはI/O要求をキューイングするのではなく、直接非同期I/Oを発行するようになります。そしてI/Oヘルパースレッドは自分自身がI/O処理を行うのではなく、コネクションスレッドが行った非同期I/Oの完了を待機して後処理を行うという役割に変化します。具体的にはコネクションスレッドがio_submit(2)を行い、I/Oヘルパースレッドがio_getevents(2)を行うようになります。
これがどのようなメリットをもたらすかというと、I/Oヘルパースレッドの数を増やすことなしに一度に処理できるI/O要求の数を増やすことができるということになります。MySQL 5.1＋InnoDB Pluginの構成においてinnodb_read_io_threadsとinnodb_write_io_threadsはそれぞれデフォルトで4であり、読み込みで4つ、書き込みで4つまでのI/O要求を一度に処理することができました。しかし、MySQL 5.5においてはOSとストレージサブシステムが許す限りのI/O要求を一度に処理することができるようになります。例えばSATA Native Command Queuingでは、デバイスあたり最大32個のI/O要求をキューイングすることが可能となっています。
分析例
前回作成したSystemTapスクリプトを、非同期I/Oをプローブできるように改造しました。スクリプトは本エントリの末尾にあります。JdbcRunnerのTiny TPC-Cを用い、多重度16で負荷をかけたときのI/Oの様子を以下に示します。最初はMySQL 5.0.77のデータです。
-------- Synchronous I/O -------- ------- Asynchronous I/O -------- rTH r/s rKB/s wTH w/s wKB/s rTH r/s rKB/s wTH w/s wKB/s File 0 0 0 17 164 629 0 0 0 0 0 0 /u02/mysql/ib_logfile1 8 8 128 7 15 14496 0 0 0 0 0 0 /u02/mysql/ibdata1 17 354 6992 1 160 2656 0 0 0 0 0 0 /u02/mysql/tpcc/customer.ibd 0 0 0 1 5 80 0 0 0 0 0 0 /u02/mysql/tpcc/history.ibd 2 22 464 0 0 0 0 0 0 0 0 0 /u02/mysql/tpcc/item.ibd 6 8 128 1 12 224 0 0 0 0 0 0 /u02/mysql/tpcc/new_orders.ibd 17 58 928 1 56 1008 0 0 0 0 0 0 /u02/mysql/tpcc/order_line.ibd 10 20 320 1 14 240 0 0 0 0 0 0 /u02/mysql/tpcc/orders.ibd 17 1057 20656 1 513 9264 0 0 0 0 0 0 /u02/mysql/tpcc/stock.ibd
rTHはそのファイルを読み込んだスレッドの数、r/sは1秒あたりの読み込み回数、rKB/sは1秒あたりに読み込んだ容量(KB)を表しています。非同期I/Oは行われておらず、全て同期I/Oであることが分かります。テーブルスペースファイルに対して読み込みを行うスレッドは最大でコネクションスレッド数＋innodb_read_io_threadsの17個、書き込みを行うスレッドはinnodb_write_io_threadsの1つだけとなっています。
ここでお詫びですが、2009年の記事SSDの真の性能を引き出す MySQL 5.1.38 InnoDB Plugin - SH2の日記において、アーキテクチャの図が間違っていました。innodb_read_io_threadsは先読みだけを行うスレッドであり、通常のページ読み込みはコネクションスレッド自身が行います。コメント欄にあるid:mogwaingさんのご指摘が正しいです。どうもありがとうございました。描き直した図を以下に示します。
先読みというのは、ある程度連続したページの読み込みが要求された際に、フルスキャンだと判断して次に読まれるであろう先の方のデータをあらかじめ読み込んでおく仕組みのことです。詳しくはマニュアルの7.7. Changes in the Read-Ahead Algorithmをご覧ください。
次はMySQL 5.1.60＋InnoDB Pluginのデータです。
-------- Synchronous I/O -------- ------- Asynchronous I/O -------- rTH r/s rKB/s wTH w/s wKB/s rTH r/s rKB/s wTH w/s wKB/s File 0 0 0 17 222 886 0 0 0 0 0 0 /u02/mysql/ib_logfile1 6 6 96 4 12 11440 0 0 0 0 0 0 /u02/mysql/ibdata1 16 458 7328 4 157 2528 0 0 0 0 0 0 /u02/mysql/tpcc/customer.ibd 5 10 160 4 24 480 0 0 0 0 0 0 /u02/mysql/tpcc/history.ibd 6 9 144 0 0 0 0 0 0 0 0 0 /u02/mysql/tpcc/item.ibd 4 6 96 0 0 0 0 0 0 0 0 0 /u02/mysql/tpcc/new_orders.ibd 17 70 1120 4 46 976 0 0 0 0 0 0 /u02/mysql/tpcc/order_line.ibd 14 29 464 3 21 352 0 0 0 0 0 0 /u02/mysql/tpcc/orders.ibd 16 1089 17424 4 385 7104 0 0 0 0 0 0 /u02/mysql/tpcc/stock.ibd
MySQL 5.0.77と比べると、テーブルスペースファイルに対して書き込みを行っているスレッドの数が増えていることが分かります。非同期I/Oは行われていません。
最後に、MySQL 5.5.18のデータです。
-------- Synchronous I/O -------- ------- Asynchronous I/O -------- rTH r/s rKB/s wTH w/s wKB/s rTH r/s rKB/s wTH w/s wKB/s File 0 0 0 17 156 651 0 0 0 0 0 0 /u02/mysql/ib_logfile0 16 40 640 7 18 17072 0 0 0 4 62 992 /u02/mysql/ibdata1 16 306 4896 0 0 0 0 0 0 7 145 2320 /u02/mysql/tpcc/customer.ibd 1 1 16 0 0 0 3 5 80 1 1 16 /u02/mysql/tpcc/history.ibd 13 22 352 0 0 0 0 0 0 0 0 0 /u02/mysql/tpcc/item.ibd 9 22 352 0 0 0 0 0 0 2 36 576 /u02/mysql/tpcc/new_orders.ibd 16 80 1280 0 0 0 6 11 176 6 110 1760 /u02/mysql/tpcc/order_line.ibd 11 109 1744 0 0 0 0 0 0 2 12 192 /u02/mysql/tpcc/orders.ibd 16 1178 18848 0 0 0 0 0 0 7 701 11216 /u02/mysql/tpcc/stock.ibd
テーブルスペースファイルに対する書き込みが非同期I/Oになっていることが分かります。また先読みも非同期I/Oになったため、historyテーブルとorder_lineテーブルに対して先読みが行われていることが判別できます。
このように、MySQL 5.5でI/Oの仕組みが変わりました。ただ性能面ではMySQL 5.1＋InnoDB Pluginの時点で既にかなり良くなっているため、そこからの改善幅はあまり大きくはないと思います。おそらく、よほど高性能なストレージを持ってこない限りI/O性能の違いは分からないでしょう。
SystemTapスクリプト
今回作成したSystemTapスクリプトを以下に示します。前回と同様Perlスクリプトとのセットになっているため、以下のようにパイプでつなげて実行してください。
# stap io2.stp | perl io2.pl
global PROCNAME = "mysqld" global fds probe begin { printf("start\n") } probe syscall.read, syscall.pread { if (execname() == PROCNAME) { fds[pid(), tid()] = fd } } probe syscall.read.return, syscall.pread.return { if (execname() == PROCNAME && $return != -1) { printf("read\t%d\t%d\t%d\t%d\n", pid(), tid(), fds[pid(), tid()], $return) } } probe syscall.write, syscall.pwrite { if (execname() == PROCNAME) { fds[pid(), tid()] = fd } } probe syscall.write.return, syscall.pwrite.return { if (execname() == PROCNAME && $return != -1) { printf("write\t%d\t%d\t%d\t%d\n", pid(), tid(), fds[pid(), tid()], $return) } } probe syscall.io_submit { if (execname() == PROCNAME) { for (i = 0; i < nr; i++) { if ($iocbpp[i]->aio_lio_opcode == 0) { // IOCB_CMD_PREAD printf("aio_read\t%d\t%d\t%d\t%d\n", pid(), tid(), $iocbpp[i]->aio_fildes, $iocbpp[i]->aio_nbytes) } else if ($iocbpp[i]->aio_lio_opcode == 1) { // IOCB_CMD_PWRITE printf("aio_write\t%d\t%d\t%d\t%d\n", pid(), tid(), $iocbpp[i]->aio_fildes, $iocbpp[i]->aio_nbytes) } } } } probe syscall.close { if (execname() == PROCNAME) { printf("close\t%d\t%d\t%d\n", pid(), tid(), fd) } } probe timer.s(1) { printf("print\n") }
#!/usr/bin/perl use strict; use warnings; my (%file_map, $do_print, %has_io, %rthreads, %rtimes, %rbytes, %wthreads, %wtimes, %wbytes, %aio_rthreads, %aio_rtimes, %aio_rbytes, %aio_wthreads, %aio_wtimes, %aio_wbytes); $do_print = 0; while (my $line = <>) { chomp($line); my ($call, $pid, $tid, $fd, $size) = split(/\t/, $line); if (($call eq 'read') or ($call eq 'write') or ($call eq 'aio_read') or ($call eq 'aio_write')) { if (!defined($file_map{"$pid,$fd"})) { $file_map{"$pid,$fd"} = readlink("/proc/$pid/fd/$fd") or next; } my $file = $file_map{"$pid,$fd"}; if (substr($file, 0, 1) eq '/') { $do_print = 1; $has_io{$file} = 1; if ($call eq 'read') { $rthreads{$file}->{$tid} = 1; $rtimes{$file}++; $rbytes{$file} += $size; } elsif ($call eq 'write') { $wthreads{$file}->{$tid} = 1; $wtimes{$file}++; $wbytes{$file} += $size; } elsif ($call eq 'aio_read') { $aio_rthreads{$file}->{$tid} = 1; $aio_rtimes{$file}++; $aio_rbytes{$file} += $size; } elsif ($call eq 'aio_write') { $aio_wthreads{$file}->{$tid} = 1; $aio_wtimes{$file}++; $aio_wbytes{$file} += $size; } } } elsif ($call eq 'close') { if (defined($file_map{"$pid,$fd"})) { delete($file_map{"$pid,$fd"}); } } elsif ($call eq 'print') { my ($sec, $min, $hour, $day, $mon, $year) = localtime(); printf "%04d-%02d-%02d %02d:%02d:%02d\n", $year + 1900, $mon + 1, $day, $hour, $min, $sec; if ($do_print == 1) { print "-------- Synchronous I/O -------- ------- Asynchronous I/O --------\n"; print "rTH r/s rKB/s wTH w/s wKB/s rTH r/s rKB/s wTH w/s wKB/s File\n"; foreach my $file (sort keys %has_io) { printf "%3d %5d %6d %3d %5d %6d %3d %5d %6d %3d %5d %6d %s\n", scalar(keys(%{$rthreads{$file}})), defined($rtimes{$file}) ? $rtimes{$file} : 0, defined($rbytes{$file}) ? $rbytes{$file} / 1024 : 0, scalar(keys(%{$wthreads{$file}})), defined($wtimes{$file}) ? $wtimes{$file} : 0, defined($wbytes{$file}) ? $wbytes{$file} / 1024 : 0, scalar(keys(%{$aio_rthreads{$file}})), defined($aio_rtimes{$file}) ? $aio_rtimes{$file} : 0, defined($aio_rbytes{$file}) ? $aio_rbytes{$file} / 1024 : 0, scalar(keys(%{$aio_wthreads{$file}})), defined($aio_wtimes{$file}) ? $aio_wtimes{$file} : 0, defined($aio_wbytes{$file}) ? $aio_wbytes{$file} / 1024 : 0, $file; } print "\n"; $do_print = 0; %has_io = %rthreads = %rtimes = %rbytes = %wthreads = %wtimes = %wbytes = (); %aio_rthreads = %aio_rtimes = %aio_rbytes = %aio_wthreads = %aio_wtimes = %aio_wbytes = (); } } else { print "$call\n"; } }
Scientific Linux/CentOS 6.0でMySQL InnoDB Pluginを利用する
先日CentOS 6.0がリリースされたので、色々試している方も多いと思います。MySQLについて、Scientific Linux/CentOS 6.0では派生元のRed Hat Enterprise Linux 6.0と同様、バージョン5.1.52が採用されています。このディストリビューション付属版のMySQL 5.1.52ですが、残念なことにInnoDB Pluginが無効化されてしまっています。
* Fri Jan 8 2010 Tom Lane <tgl@redhat.com> 5.1.42-4 - Sync with current Fedora build, including: - Update to MySQL 5.1.42, for various fixes described at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-42.html - Disable symbolic links by default in /etc/my.cnf Resolves: #553653 - Remove static libraries (.a files) from package, per packaging guidelines - Change %%define to %%global, per packaging guidelines - Disable building the innodb plugin; it tickles assorted gcc bugs and doesn't seem entirely ready for prime time anyway. Resolves: #553632 - Start mysqld_safe with --basedir=/usr, to avoid unwanted SELinux messages (see 547485) - Stop waiting during "service mysqld start" if mysqld_safe exits Resolves: #544095
バージョン5.1.42のInnoDB PluginはまだRC版だったので無効化するのは正しいのですが、バージョン5.1.52を採用するにあたっても再度有効化はしてくれなかったようです。もったいないですね。
一方、最新版のMySQL 5.5を入れようとすると、今度はRPMの依存関係でmysql-libsをアンインストールする必要がでてきてしまい、
# yum erase mysql-libs Dependencies Resolved ==================================================================================================== Package Arch Version Repository Size ==================================================================================================== Removing: mysql-libs x86_64 5.1.52-1.el6_0.1 @sl-security 4.0 M Removing for dependencies: cronie x86_64 1.4.4-2.el6 @sl 166 k cronie-anacron x86_64 1.4.4-2.el6 @sl 41 k crontabs noarch 1.10-32.1.el6 @sl 2.4 k openoffice.org-brand x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 852 k openoffice.org-calc x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 28 k openoffice.org-calc-core x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 23 M openoffice.org-core x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 237 M openoffice.org-draw x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 25 k openoffice.org-draw-core x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 2.1 M openoffice.org-graphicfilter x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 663 k openoffice.org-impress x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 29 k openoffice.org-impress-core x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 3.0 M openoffice.org-langpack-en x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 0.0 openoffice.org-langpack-ja_JP x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 36 M openoffice.org-math x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 15 k openoffice.org-math-core x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 3.3 M openoffice.org-pdfimport x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 3.8 M openoffice.org-presenter-screen x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 4.6 M openoffice.org-writer x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 31 k openoffice.org-writer-core x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 15 M openoffice.org-xsltfilter x86_64 1:3.2.1-19.6.el6_0.5 @sl-security 1.9 M postfix x86_64 2:2.6.6-2.2.el6_1 @sl-security 9.7 M redhat-lsb x86_64 4.0-2.1.el6 @sl 22 k redland x86_64 1.0.7-11.el6 @sl 602 k sysstat x86_64 9.0.4-12.el6_0.1 @sl-fastbugs 753 k Transaction Summary ==================================================================================================== Remove 26 Package(s) Reinstall 0 Package(s) Downgrade 0 Package(s) Is this ok [y/N]:
と、Pythonほどではありませんがなかなか面倒なことになります。ううむ…
Scientific Linux/CentOS 6.0でMySQL 5.5を使いたい場合は、今のところRemiを使うのがよさそうです。依存関係を綺麗に解決してくれます。しかし今回はこうした外部リポジトリには頼らず、出たばかりのディストリビューションにあまり大きく手を加えない方向でInnoDB Pluginだけ有効化してみたいと思います。
RPMのビルド環境を作る
ここからはMySQLのSource RPMを元に、InnoDB Pluginを有効化したRPMを再作成していきます。ビルド環境の準備についてはrpmbuildコマンドを使おう - よくきたWikiなどを参考にしてください。.rpmmacrosには_topdir以外に、vendorとdistも定義しておくと便利です。サンプルを以下に示します。
%_topdir /home/taira/rpmbuild %vendor HIRATSUKA Sadao %dist .el6_0
Source RPMをhttp://ftp.jaist.ac.jp/pub/Linux/scientific/6/SRPMS/vendor/などから入手し、インストールします。
$ wget http://ftp.jaist.ac.jp/pub/Linux/scientific/6/SRPMS/vendor/mysql-5.1.52-1.el6_0.1.src.rpm --2011-07-20 00:31:15-- http://ftp.jaist.ac.jp/pub/Linux/scientific/6/SRPMS/vendor/mysql-5.1.52-1.el6_0.1.src.rpm ftp.jaist.ac.jp をDNSに問いあわせています... 150.65.7.130, 2001:200:141:feed::feed ftp.jaist.ac.jp|150.65.7.130|:80 に接続しています... 接続しました。 HTTP による接続要求を送信しました、応答を待っています... 200 OK 長さ: 20854090 (20M) [application/x-wais-source] `mysql-5.1.52-1.el6_0.1.src.rpm' に保存中 100%[==========================================================>] 20,854,090 2.93M/s 時間 8.0s 2011-07-20 00:31:23 (2.48 MB/s) - `mysql-5.1.52-1.el6_0.1.src.rpm' へ保存完了 [20854090/20854090] $ rpm -ivh mysql-5.1.52-1.el6_0.1.src.rpm 1:mysql 警告: ユーザ mockbuild は存在しません - root を使用します 警告: グループ mockbuild は存在しません - root を使用します …
SPECファイルを修正する
SPECS/mysql.specをテキストエディタで開き、241行目、--without-plugin-innodb_pluginと書いてあるところを--with-plugins-innodb_pluginへと書き換えます。with-pluginではなくwith-pluginsとsが付きます。
%configure \
--with-readline \
--with-ssl=/usr \
--without-debug \
--enable-shared \
--with-embedded-server \
--localstatedir=/var/lib/mysql \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--with-mysqld-user="mysql" \
--with-extra-charsets=all \
--with-big-tables \
--with-pic \
--with-plugin-innobase \
--with-plugins-innodb_plugin \
--with-plugin-partition \
--enable-local-infile \
--enable-largefile \
--enable-thread-safe-client \
--disable-dependency-tracking
RPMをビルドする
rpmbuildコマンドでRPMをビルドします。この時点でビルドに必要なパッケージが不足している場合は、インストールするように促されます。defineオプションでrunselftestを0に設定すると、ビルド後のテストをスキップすることができます。
$ rpmbuild -bb --define='runselftest 0' mysql.spec 実行中(%prep): /bin/sh -e /var/tmp/rpm-tmp.pDzWkP + umask 022 + cd /home/taira/rpmbuild/BUILD + cd /home/taira/rpmbuild/BUILD + rm -rf mysql-5.1.52 + /usr/bin/gzip -dc /home/taira/rpmbuild/SOURCES/mysql-5.1.52-nodocs + /bin/tar -xf - … + umask 022 + cd /home/taira/rpmbuild/BUILD + cd mysql-5.1.52 + rm -rf /home/taira/rpmbuild/BUILDROOT/mysql-5.1.52-1.el6_0.1.x86_64 + exit 0
いまどきのPCでは5分程度でビルドが完了します。
インストール、設定
作成されたRPMはRPMS/x86_64に保存されています。インストールします。
$ ls -lh 合計 43M -rw-r--r-- 1 taira taira 1.2M 7月 20 00:51 2011 mysql-5.1.52-1.el6_0.1.x86_64.rpm -rw-r--r-- 1 taira taira 516K 7月 20 00:51 2011 mysql-bench-5.1.52-1.el6_0.1.x86_64.rpm -rw-r--r-- 1 taira taira 135K 7月 20 00:51 2011 mysql-devel-5.1.52-1.el6_0.1.x86_64.rpm -rw-r--r-- 1 taira taira 12M 7月 20 00:51 2011 mysql-embedded-5.1.52-1.el6_0.1.x86_64.rpm -rw-r--r-- 1 taira taira 6.4M 7月 20 00:51 2011 mysql-embedded-devel-5.1.52-1.el6_0.1.x86_64.rpm -rw-r--r-- 1 taira taira 2.5M 7月 20 00:51 2011 mysql-libs-5.1.52-1.el6_0.1.x86_64.rpm -rw-r--r-- 1 taira taira 14M 7月 20 00:51 2011 mysql-server-5.1.52-1.el6_0.1.x86_64.rpm -rw-r--r-- 1 taira taira 7.2M 7月 20 00:51 2011 mysql-test-5.1.52-1.el6_0.1.x86_64.rpm # rpm -Uvh mysql-server-5.1.52-1.el6_0.1.x86_64.rpm mysql-5.1.52-1.el6_0.1.x86_64.rpm 準備中... ########################################### [100%] 1:mysql ########################################### [ 50%] 2:mysql-server ########################################### [100%]
小規模サーバ向けmy.cnfのサンプルです。さくらのVPS 512/1Gを想定しています。
[mysqld] ## character set character_set_server = utf8 collation_server = utf8_general_ci ## isolation level transaction_isolation = READ-COMMITTED ## connection max_connections = 151 thread_cache_size = 8 ## per connection buffer sort_buffer_size = 4M ## file descriptor table_open_cache = 4096 ## network max_allowed_packet = 16M ## storage engine default_storage_engine = InnoDB ## innodb plugin ignore-builtin-innodb plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so innodb_file_format = Barracuda innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_buffer_pool_size = 64M innodb_log_buffer_size = 8M innodb_log_file_size = 128M ## myisam key_buffer_size = 1M ## query cache query_cache_type = 1 query_cache_size = 16M ## binary log #server_id = 1 #log_bin = mysql-bin #binlog_format = MIXED #binlog_cache_size = 1M ## slow query log slow_query_log = 1 long_query_time = 1.0
show pluginsコマンドで、InnoDB Pluginが有効化されていることが確認できます。
mysql> show plugins; +---------------------+--------+--------------------+---------------------+---------+ | Name | Status | Type | Library | License | +---------------------+--------+--------------------+---------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | +---------------------+--------+--------------------+---------------------+---------+ 14 rows in set (0.00 sec)
ご利用ください。
MySQLでALTER TABLE文の進捗状況を確認する
MySQLでテーブルへのカラム追加やテーブルの再編成を行うには、ALTER TABLE文を使用します。MySQLのALTER TABLE文は、変更後の定義にもとづく作業用テーブルを作成し、変更前のテーブルから作業用テーブルへデータをコピーして、最後に二つのテーブルを入れ替えるという仕組みになっています。テーブルへのインデックス追加についても、現在のところ大半のケースで内部的にALTER TABLE文が実行されています。
ALTER TABLE文の怖いところは、処理がもうすぐ終わるのかどうかが分からないところです。テーブルサイズが1GBを超えるあたりから分単位の時間がかかるようになり、100GBともなると本当に終わるのか？と見ていて不安になります。メンテナンス時間が限られている場合は、作業を中断すべきかどうか難しい判断を迫られることもあります。
実は、というほどではありませんが、ALTER TABLE文の進み具合を確認する方法があります。
mysql> SHOW GLOBAL STATUS LIKE 'Handler_write'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Handler_write | 22472949 | +---------------+----------+ 1 row in set (0.00 sec)
ALTER TABLE文は内部的に作業用テーブルへのINSERT処理を実行するので、ステータス変数Handler_writeにINSERTされたレコード件数が記録されていきます。
mysql> ALTER TABLE order_line ENGINE = InnoDB; Query OK, 4796697 rows affected (1 min 4.78 sec) Records: 4796697 Duplicates: 0 Warnings: 0 mysql> SHOW GLOBAL STATUS LIKE 'Handler_write'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Handler_write | 27269647 | +---------------+----------+ 1 row in set (0.00 sec)
ですから、作業開始前にHandler_writeの値と対象テーブルのレコード件数を控えておけば、どこまで処理が進んだのかを確認することができるのです。InnoDBの場合はInnodb_rows_insertedでも同じ値が取れます。
#!/bin/bash while true do cat <<_EOF_ SHOW GLOBAL STATUS LIKE 'Handler_write'; _EOF_ sleep 10 done | mysql -u root -p -N
$ ./watch.sh Enter password: Handler_write 27270904 Handler_write 27270916 Handler_write 27270917 Handler_write 27270918 Handler_write 27981431 Handler_write 28791103 Handler_write 29546564 Handler_write 30319521 Handler_write 30980129 Handler_write 31629177 Handler_write 32067622 Handler_write 32067623 Handler_write 32067624 Handler_write 32067625 Handler_write 32067626
ただしテーブルサイズが大きくなるとINSERT処理がだんだん遅くなるため、最初の10分間で1億レコード処理できたとしても、次の10分間でまた1億レコード処理できるとは限らないという点には注意が必要です。
MySQL Info形式のマニュアル
本番環境のトラブルシューティングで、サーバルームに缶詰になって作業することがあります。インターネットには繋がらない、ノートパソコンは持ち込み禁止、場合によっては携帯電話も持ち込み禁止、突然呼ばれたのであの本
実はMySQLには公式サイトに掲載されているものと同じマニュアルが付属しており、infoコマンドで閲覧することができます。
$ info mysql
Red Hat Enterprise Linux/CentOSのバンドル版ではmysqlパッケージ、公式バイナリではMySQL-serverパッケージにマニュアルが入っています。Info形式のマニュアルは、GNUのソフトウェアで標準的に使われているものだそうです。
操作方法をメモしておきます。
| キー | 内容 |
|---|---|
| Enter | リンクをたどる |
| Space、BackSpace | 次のページに進む/前のページに戻る |
| n、p | 次の章節に進む/前の章節に戻る (深さを変えない。1.2→1.3) |
| ]、[ | 次の章節に進む/前の章節に戻る (深さを変える。1.2→1.2.1) |
| u | 上の章節に戻る |
| q | 終了する |
サーバルームでinfoコマンドの操作方法を思い出せない場合は、こうしています。
$ gunzip -c /usr/share/info/mysql.info.gz | less
今日はクエリキャッシュをクリアしようとして「FLUSH QUERY CACHE」を打っても何も起こらず困っていたのですが、このマニュアルのおかげで作業を進めることができました。「RESET QUERY CACHE」でした…。
