質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.44%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

6回答

10475閲覧

MySQL 5.6 移行後の性能劣化

takotakot

総合スコア1111

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

6グッド

9クリップ

投稿2016/08/09 06:57

編集2016/08/16 14:56

どうにも分からないので困っています。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

よろしくお願いいたします。

hyper-drums-ko, KiyoshiMotoki, yodel, nullbot, ikuwow, maisumakun👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答6

0

ベストアンサー

MySQL総合1位、MariaDB総合1位の方でも原因が分からないとは。

ちなみに、以下のような原因です。
最後にIN句のままで改善するSQLだけは載せておくので、
取り急ぎの対応としてはMySQL5.6.30でも1秒切れると思いますよ。

○原因
→実行計画にある「possible_keys」に2つほど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

tomari_perform

総合スコア760

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takotakot

2016/08/16 08:48

完璧な回答でした。驚きました。ありがとうございます。 体感(の計算量オーダ)とご説明はきれいに一致します。 実行計画が変わらないので、ヒントは無用と思っていたのですが、そうではないのですね。 実行計画の表示が表面上変わらないのに、"USE INDEX (PRIMARY) " を用いると内部の動作が変わるところ、教えていただくことはできますか? ある計画で結論が出るのがあまりに遅い場合、他の計画も試すのでしょうか。専門用語はいくら使っていただいても構いません。
takotakot

2016/08/16 08:50

テーブル作成の最後の , は、後に続く部分を削ったときに、カンマだけ残りました。すみません。
takotakot

2016/08/16 08:55

連投すみません。使われる INDEX はあくまでもそれが使われているというだけで、どのような探索が行われているかは別…とか、そういうことでしょうか。
tomari_perform

2016/08/16 17:06

解決されたようで良かったです。 (皆さん興味があるようだったので、私への連絡専用のSkypeID作りました。) >使われる INDEX はあくまでもそれが使われているというだけで、どのような探索が行われているかは別…とか、そういうことでしょうか。 →なるほど。そういう勘違いでしたか。  前提として、実行計画の1行に2つ以上のINDEXがある場合、実際の検索では基本的に  「全部のINDEXを利用する」ではなく、「どれか1つを利用する」になります。  また、実行計画に2つ以上のINDEXがある場合は、  MySQLのバージョン、統計情報、SQL文の形、等によって、  どれが選ばれるかは違います。  たとえば、紙媒体の漢字辞典をイメージして頂くと、  あいうえお順に並んでいる辞書と  画数順に並んでいる辞書があった場合、  2つの辞書を同時に使って漢字を検索する事は出来ない、と言う事です。 >ヒントは無用と思っていたのですが、そうではないのですね。 →はい、多くの方が無用(もしくは利用価値・利用方法が分からない)と思っているようですが、そんなことありません。  もちろん、ちゃんと動いていたり、数件~数千件程度のテーブルであればそれはそれで良いですが。  特に、データベースをバージョンアップする機会があるプロジェクトでヒント句がない場合は今回のような事は良く起こります。  ヒント句あり派のほとんどはデータベースのバージョンアップ経験者もしくは、  データベースを定期的(数年に1度)に更新するプロジェクトに参加しているエンジニアがほとんどでしょう。  今回のヒント句なしの動作について、  次のような疑問があるかもしれませんが、MySQLでは以下の理由です。  疑問1「実行計画になぜ2つ以上のINDEXを表示するのか」  ⇒実行計画を取得した後、テーブルのデータを変更し、統計情報が変わっても、   必ず、どれかのINDEXを使うようになるため、表示しています。   ちなみに、Oracleの実行計画では逆に1つしか表示しませんが、   上記のように、統計情報等が変わると、   実行計画通りの動きをしてくれない現象が発生します。  疑問2「PRIMARYを指定しているのに、なぜINDEXが優先されたのか。」  ⇒MySQL5.5では、PRIMARY優先思考でしたが、MySQL5.6ではSQL文優先思考になっているためです。(※IN句の扱いでのお話に限る)   ※SQL文上は、PRIMARIで絞り込み見込み件数(2200件)より、INDEXで絞り込み見込み件数(2000件)の方が少ないからです。   この絞り込み見込み件数を見誤って、性能劣化した理由は、IN句の動作です。   IN句は基本的に該当項目を[=]にし、UNION ALLでつなげた結果とほぼ同等になります。   ちなみに、今回のようなケースがあるため、OracleではIN句の中身は1000個が限界になっています。 >実行計画の表示が表面上変わらないのに、"USE INDEX (PRIMARY) " を用いると内部の動作が変わるところ、教えていただくことはできますか? →漢字辞典の例において、『「あ」と「い」と「う」から始まる1画と2画の漢字を探す』場合、どちらの辞典を使いますか?  ・・・と、データベースにどのINDEXを利用するか任せるには、限界があります。  ヒント句(USE INDEX (PRIMARY))を使えば、どんなにテーブルのデータが変わっても、  PRIMARYを使った検索になり、【MySQLがどのINDEXを使うか迷う時間】もなくなります。  ヒント句がないと、統計情報の変更等によって、  利用するINDEXが変わり、「ある日突然性能劣化した」なんて事は良くある話です。 >ある計画で結論が出るのがあまりに遅い場合、他の計画も試すのでしょうか。 →たとえば、スタート地点からゴール地点に行くまでに、  絶対に通らなければならない場所(検索結果)があり、そこを通るための道(ヒント)が2つ以上ある場合、  1つの道を選択し、あまりに時間がかかったら、中断して別の道を選びなおす事をするか、と言う事でしょうか?  であれば、答えは「基本的に片方の道を選択したら、KILL等されない限り、ゴール地点まで全力で突っ走ります。」です。 <おまけ> ・SHOW PROFILEはMySQL5.6.7以降では非推奨コマンドです。  勉強のために利用されても良いですが、  必ず、SET profiling = 0;で戻してあげてくださいね。
takotakot

2016/08/17 08:30

詳細にありがとうございます。 > 1つの道を選択し、あまりに時間がかかったら、中断して別の道を選びなおす事をするか、と言う事でしょうか? はい、そういう意味でお聞きしました。並列検索し、最速のもので応答する可能性と申し上げてもよかったです。「他の計画はしない」ということが分かりました。 >>ヒントは無用と思っていたのですが、そうではないのですね。 >→はい、多くの方が無用(もしくは利用価値・利用方法が分からない)と思っているようですが、そんなことありません。 「ある日突然」のお話しや「MySQL が予想する実行計画から変更」するときの USE INDEX 付加の意味は既知・理解していましたが、今回 「実行計画が同じでも、調査の時間がかかる」ということで、USE INDEX の効能を再認識させて頂きました。 https://dev.mysql.com/doc/refman/5.6/ja/general-thread-states.html によりますと、 statistics: 『サーバーはクエリー実行プランを開発するための統計を計算しています。スレッドが長期間この状態にある場合、サーバーはディスクに依存してほかの作業を実行している可能性があります。』 で、これが長い…ということは「【MySQLがどのINDEXを使うか迷う時間】」が一番大きかった。という解釈をいたしました。
guest

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

KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takotakot

2016/08/09 10:53

おお!そういうバグもあるんですね。今回速度をあげたいのは、5.6.30 です。 5.5 でとてつもなく遅かったのは、そういうバグも関係するかもしれません。
takotakot

2016/08/09 13:03

調べて追記しましたが、"Queries with many values in a IN() clause were slow" とよく似た状況かもしれません。5.6.30 ではあるのですが...。 回答の精度に感銘を受けました。
KiyoshiMotoki

2016/08/10 02:20

takotakot様 返信および情報の追記、ありがとうございます。 > 今回速度をあげたいのは、5.6.30 です。 ということであれば、私の回答の線はなさそうですね。 追記いただいた情報も確認させていただきましたが、なかなか興味深い現象ですね。 何か分かりましたら、こちらでご連絡させていただきます。 もっとも、 > 本来行いたい処理 のSQL文で性能が改善することがすでにお分かりのようですので、可能であれば 原因の追求は程々にして問題のSQL文を修正してしまう、というのも1つの手だと思います (^^; その方が時間の節約になると思いますので。
guest

0

内容が気になって仕方なく、一応質問二つ伺います。
・両方カンマ区切りの時の、「SHOW PROFILE」の結果
・Order Byを抜いた状態での速度(両方カンマ区切り、片方カンマ区切り、全部サブQuery)

関係ないと申し訳ございません。(「tomari_perform」さんの答えで解決でしょうか?)

投稿2016/08/16 08:08

編集2016/08/16 08:13
fatty.rabbit

総合スコア12

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takotakot

2016/08/16 14:51

・SET profiling=1; QUERY; SHOW PROFILE; を cat xx|mysql した結果 今から質問に追記します。 ・Order By なし 両方カンマ: 1.97 秒、片方: 0.036/0.064 秒、サブクエリ: 0.040 秒 両方カンマ以外は大きな差が出ません。
takotakot

2016/08/17 08:31

profile は使ったことがありませんでした。 profiling を用いることで、「本当に違うのはどこか」を分かりやすく見ることができました。ありがとうございます。
guest

0

もしもテーブルがページ単位のファイルに成った事が影響しているとしたら
V5.6からinnodb_file_per_tableがデフォルトで有効になった様なので、元と同じとするなら0を指定を試されてはいかがでしょうか?
innodb_file_per_table = 0

投稿2016/08/09 11:07

A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takotakot

2016/08/09 11:44

mysql_upgrade -u root -p --force はしたりしなかったりですが、念のため innodb_file_per_table = 0 追記しました。以後の実験はこの環境下になります。
guest

0

あてずっぽうですが、DBのエンジンが異なっていませんか?
5.1 -> MyISAM
5.6 -> InnoDB

投稿2016/08/09 08:05

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takotakot

2016/08/09 10:49

ありがとうございます。 ENGINE=InnoDB 指定で CREATE しているので、それはないと思います。5.5 に展開してパフォーマンス計測しても問題はありませんでした。
退会済みユーザー

退会済みユーザー

2016/08/09 11:01 編集

「ないと思う」ではなくて、確認してほしい。 mysql> use information_schema; mysql> select table_name, engine from tables where table_schema = "DB名" なぜなら、MySQL5.5 からデフォルトエンジンが MyISAM から InnoDB に変更されている。
takotakot

2016/08/09 11:36

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 に「ならない」可能性はありますか?ぜひ教えてください。
退会済みユーザー

退会済みユーザー

2016/08/09 11:39

> そもそも「ENGINE=InnoDB 指定で CREATE している」のに、InnoDB に「ならない」可能性はありますか?ぜひ教えてください。 可能性があるかどうかの判断ができないから、確認して欲しかったという意味です。
takotakot

2016/08/09 11:41

わかりました、ありがとうございます。
guest

0

直接の原因までちょっと読み取れませんが気になる点が2つ

  • MySQLでよく言われるINの問題

EXISTSなどに変えられないか?
もしくは、テンポラリテーブルなどにINに当たるものを投入して結合で処理できないか?

  • NULLの問題

BIGINTでNULLを許可しているが検索負荷があるようなので0などで
NOT NULLにして代替できないか

投稿2016/08/09 07:31

yambejp

総合スコア115275

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takotakot

2016/08/09 07:39

EXISTS はそうですね、やってみて報告します。 NULL の検索負荷はないと思います。実際には NULL のレコードは存在しません。
takotakot

2016/08/09 13:05

追記をしましたが、IN 句(節?)内を SELECT 文に切り替えると実用上問題ない速度になりました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.44%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問