Q&A
おお！そういうバグもあるんですね。今回速度をあげたいのは、5.6.30 です。
5.5 でとてつもなく遅かったのは、そういうバグも関係するかもしれません。
どうにも分からないので困っています。AWS EC2 上の MySQL を 5.1 から 5.6 に移行しました。特定の状況下でパフォーマンスの劣化が見られたので、原因の追究を手伝って下さる方を探しています。MySQL 5.5 までは問題がないように思います。nano インスタンスですが、メモリ不足ではないと思いますし、steal もない状況で比較しています。
※最下部に現状を追記しました。
テーブル
SQL
1CREATE TABLE `dtb_bill` ( 2 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 3 `customer_id` bigint(20) unsigned DEFAULT NULL, 4 PRIMARY KEY (`id`), 5 KEY `customer_id` (`customer_id`), 6) ENGINE=InnoDB AUTO_INCREMENT=8549 DEFAULT CHARSET=utf8
7518 レコードですが、customer_id は 5611 通りです。
クエリ
これについて、
SQL
1SELECT `dtb_bill`.* FROM `dtb_bill` WHERE 2 (customer_id IN (...)) -- 約 2000 通り 3 AND 4 (id IN (...)) -- 約 2200 通り 5 ORDER BY `id` desc LIMIT 20;
というクエリを発行しています。IN 句の中に意味のない数字は大量にあります。
どうしてそんなものを…という質問については、すみません、今回はそこは無しでお願いします。
※追記: サブクエリはありません。IN の中には数値の列が与えられます。
※追記: 数が逆でした。
my.cnf
関連しそうな my.cnf の設定は
my.cnf
1query_cache_type = 1 2query_cache_limit = 16M 3query_cache_size = 128M 4innodb_buffer_pool_size = 48M 5tmp_table_size = 32M # not set 6max_heap_table_size = 32M
こんなところでしょうか。SWAP 発生はありません。
状況
5.5.46-1.10.amzn1 と 5.6.30-1.15.amzn1 の比較です。
示したクエリの発行で
- MySQL 5.5 time コマンドで 0.120 秒程度
- MySQL 5.6 time コマンドで 1.923 秒程度
ORDER BY と LIMIT をやめると、約1930件あるのですが、
- 5.5 time コマンドで 0.078 秒程度
- 5.6 time コマンドで 1.937 秒程度
で、傾向は変わりません。
クエリキャッシュは有効で、2回目以降は早いです。1回目の実行を高速化したいです。目標は、MySQL 5.6 で 1秒を切るところです。
※追記
クエリ自体が 160 KB なので、それが悪影響を起こしている可能性はあります。
備考
テーブルの DROP と CREATE をしても、状況は変化しません。
同一インスタンスで、アップグレード・ダウングレードを繰り返していますので、何か悪く働いている可能性はあります。
その他いろいろ条件を変えてテストしているのですが、ORDER BY ありのクエリを EXPLAIN EXTENDED すると
MySQL 5.5:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dtb_bill index PRIMARY,customer_id PRIMARY 8 NULL 77 2724.68 Using where
MySQL 5.6:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dtb_bill index PRIMARY,customer_id PRIMARY 8 NULL 71 2954.93 Using where
であり、実行計画に目立った際は見られません。
INDEX を加えて、
SQL
1ALTER TABLE dtb_bill ADD INDEX cust_id(customer_id, id);
とした場合は(だました場合は)挙動が大きく異なり
- 5.5 160 秒以上
- 5.6 3.732 秒程度
です。実行計画は
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dtb_bill index PRIMARY,customer_id PRIMARY 8 NULL 77 2724.68 Using where 1 SIMPLE dtb_bill index PRIMARY,customer_id,cust_id PRIMARY 8 NULL 77 2724.68 Using where
の上段から下段に変わるのですが、使用インデックスが変わらないのにもかかわらず、顕著なパフォーマンス劣化が見られます。
SQL
1ALTER TABLE dtb_bill DROP INDEX cust_id;
直後に速度は元に戻ります。何かのバッファメモリが足りないのでしょうか？
備考その2
実は、実際のアプリケーション上では、他にも type, status 等のカラムがあり、WHERE 句に (status = '3' AND type <> 3) AND (type = '1') がついていました。どちらも KEY がついています。
5.1 では Using intersect(status,type); Using where; Using filesort
5.5 では Using intersect(status,PRIMARY); Using where; Using filesort
5.6 では type を見て、Using where でした。
こちらはこちらでまた面白いので、別途質問するかもしれません。
追記
アプリケーション側のコードを追って、SQL を突き止めました。テーブルの構成は、もともとの構成に戻してはいますが、概要は変わりません。
SQL
1SELECT `dtb_bill`.* FROM `dtb_bill` WHERE 2 (customer_id IN (SELECT文 A)) 3 AND 4 (id IN (SELECT文 B)) 5 ORDER BY `id` desc LIMIT 20;
が本来行いたい処理であるようです。
これを
SQL
1SELECT `dtb_bill`.* FROM `dtb_bill` WHERE 2 (customer_id IN (SELECT文 A の「結果カンマ区切り」)) 3 AND 4 (id IN (SELECT文 B の「結果カンマ区切り」)) 5 ORDER BY `id` desc LIMIT 20;
で処理させていて(一度途中経過を取っているようです)、そうすると、性能劣化が生じます。
両方 SELECT 文なら、0.009 秒、片方 SELECT 文の時、A が SELECT のとき 0.009 秒、B が SELECT のとき 0.035 秒でした。両方カンマ区切りにすると、約 1.9 秒で、これだけやけに遅いです。
クエリの実行計画は、両方カンマ区切りが
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dtb_bill range PRIMARY,customer_id PRIMARY 8 NULL 2100 100.00 Using where
で A が SELECT 文のとき
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dtb_bill range PRIMARY,customer_id PRIMARY 8 NULL 2100 100.00 Using where 1 SIMPLE dtb_customer eq_ref PRIMARY,----------_by PRIMARY 8 perf_test.dtb_bill.customer_id 1 100.00 Using where
で B が SELECT 文のとき、
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dtb_bill index PRIMARY,customer_id PRIMARY 8 NULL 20 37425.00 Using where 1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 9 perf_test.dtb_bill.id 1 100.00 NULL 2 MATERIALIZED dtb_----- ALL NULL NULL NULL NULL 7434 100.00 Using where
でした。
「カンマ区切りのデータを複数 IN 句に与える」ことで劣化させられるのですが、果たしてその原因は…分かる方いらっしゃいますでしょうか。
追記 USE INDEX 付加
USE INDEX (PRIMARY) を付加したところ、0.022 秒程度のクエリとなりました。実行計画は以下です。
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE dtb_bill range PRIMARY PRIMARY 8 NULL 2100 100.00 Using where
追記 SHOW PROFILE 結果
右が、USE INDEX 付加後。statistics が大きく異なる。
Status Duration starting 0.005219 0.005266 checking permissions 0.000007 0.000005 Opening tables 0.000023 0.000017 init 0.003204 0.002664 System lock 0.000010 0.000007 optimizing 0.001004 0.000933 statistics 1.818584 0.002057 preparing 0.001847 0.001844 Sorting result 0.000004 0.000004 executing 0.000002 0.000002 Sending data 0.000102 0.000078 end 0.000004 0.000003 query end 0.000005 0.000004 closing tables 0.000009 0.000006 freeing items 0.000598 0.000533 cleaning up 0.000008 0.000008
よろしくお願いいたします。
回答6件
0
ベストアンサー
MySQL総合1位、MariaDB総合1位の方でも原因が分からないとは。
ちなみに、以下のような原因です。
最後にIN句のままで改善するSQLだけは載せておくので、
取り急ぎの対応としてはMySQL5.6.30でも1秒切れると思いますよ。
○原因
→実行計画にある「possible_keys」に２つほどINDEXとPRIMARYがあります。
MySQL5.5では、PRIMARYが優先され、MySQL5.6では、INDEXが優先されているんです。
そのため、MySQL5.5では、PRIMARYの行数分（2000通り）の検索で済むのに対し、
MySQL5.6では、INDEXの行数分×PRIMARYの行数分（2200×2000≒4400000通り）の検索が走ります。
※とある理由による仕様変更です。
○SQL
SELECT dtb_bill.*
FROM dtb_bill USE INDEX (PRIMARY)
WHERE ( customer_id IN (1,2,3) ) -- 約 2000 通り
AND ( id IN (1,2,3) ) -- 約 2200 通り
ORDER BY id desc
LIMIT 20
;
PS：テーブルを作る際、)の前の,は不要です。
投稿2016/08/16 04:24
総合スコア760
0
MySQL 5.6 のマイナーバージョンはいくつでしょうか？
5.6.10 以下であれば、MySQLのバグの可能性があります。
https://bugs.mysql.com/bug.php?id=68046
5.6.11 で修正されているようですので、もし、5.6.10 以下をお使いであれば、バージョンアップできないか検討してみてください。
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-11.html
Queries with many values in a IN() clause were slow due to inclusion of debugging code in non-debugging builds. (Bug #68046, Bug #16078212)
References: See also: Bug #58731, Bug #11765737.
投稿2016/08/09 08:02
総合スコア4791
調べて追記しましたが、"Queries with many values in a IN() clause were slow" とよく似た状況かもしれません。5.6.30 ではあるのですが...。
回答の精度に感銘を受けました。
takotakot様
返信および情報の追記、ありがとうございます。
> 今回速度をあげたいのは、5.6.30 です。
ということであれば、私の回答の線はなさそうですね。
追記いただいた情報も確認させていただきましたが、なかなか興味深い現象ですね。
何か分かりましたら、こちらでご連絡させていただきます。
もっとも、
> 本来行いたい処理
のSQL文で性能が改善することがすでにお分かりのようですので、可能であれば
原因の追求は程々にして問題のSQL文を修正してしまう、というのも1つの手だと思います (^^;
その方が時間の節約になると思いますので。
0
内容が気になって仕方なく、一応質問二つ伺います。
・両方カンマ区切りの時の、「SHOW PROFILE」の結果
・Order Byを抜いた状態での速度（両方カンマ区切り、片方カンマ区切り、全部サブQuery）
関係ないと申し訳ございません。（「tomari_perform」さんの答えで解決でしょうか？）
投稿2016/08/16 08:08
編集2016/08/16 08:13
総合スコア12
0
あてずっぽうですが、DBのエンジンが異なっていませんか？
5.1 -> MyISAM
5.6 -> InnoDB
投稿2016/08/09 08:05
退会済みユーザー
総合スコア0
ありがとうございます。
ENGINE=InnoDB 指定で CREATE しているので、それはないと思います。5.5 に展開してパフォーマンス計測しても問題はありませんでした。
「ないと思う」ではなくて、確認してほしい。
mysql> use information_schema;
mysql> select table_name, engine from tables where table_schema = "DB名"
なぜなら、MySQL5.5 からデフォルトエンジンが MyISAM から InnoDB に変更されている。
5.1 は lib/mysql のバックアップを残さずにアップグレードしてしまったので、アップグレード直前のデータベースそのものはありません。dump した SQL はありますし、リストアしたテーブルはいつでも作れますが。
再度 5.6 を remove、5.5 を install し、CREATE しなおしましたが、InnoDB でした。5.5 を remove 5.1 を install したところ、InnoDB で、再度 DROP/CREATE したところ、InnoDB でした。パフォーマンスには問題がありませんでした。(0.155 秒)
再度 5.1 を remove し、5.6 を install して、/etc/my.cnf に
performance_schema=0
innodb_buffer_pool_instances = 1
を追記し(上はメモリ不足回避、下は 5.5 にあわせるため)ました。確認したところ、InnoDB でした。問題の SQL を実行したところ、1.896 秒でした。
そもそも「ENGINE=InnoDB 指定で CREATE している」のに、InnoDB に「ならない」可能性はありますか？ぜひ教えてください。
> そもそも「ENGINE=InnoDB 指定で CREATE している」のに、InnoDB に「ならない」可能性はありますか？ぜひ教えてください。
可能性があるかどうかの判断ができないから、確認して欲しかったという意味です。
わかりました、ありがとうございます。
0
直接の原因までちょっと読み取れませんが気になる点が２つ
- MySQLでよく言われるINの問題
EXISTSなどに変えられないか？
もしくは、テンポラリテーブルなどにINに当たるものを投入して結合で処理できないか？
- NULLの問題
BIGINTでNULLを許可しているが検索負荷があるようなので0などで
NOT NULLにして代替できないか
投稿2016/08/09 07:31
総合スコア114769
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/08/16 08:48
2016/08/16 08:50
2016/08/16 08:55
2016/08/16 17:06
2016/08/17 08:30