常連

回答者

自己紹介

どうにも分からないので困っています。AWS EC2 上の MySQL を 5.1 から 5.6 に移行しました。特定の状況下でパフォーマンスの劣化が見られたので、原因の追究を手伝って下さる方を探しています。MySQL 5.5 までは問題がないように思います。nano インスタンスですが、メモリ不足ではないと思いますし、steal もない状況で比較しています。

**※最下部に現状を追記しました。**

# テーブル
```SQL
CREATE TABLE `dtb_bill` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
) ENGINE=InnoDB AUTO_INCREMENT=8549 DEFAULT CHARSET=utf8
```

7518 レコードですが、customer_id は 5611 通りです。

# クエリ
これについて、

```SQL
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE
 (customer_id IN (...)) -- 約 2000 通り
 AND
 (id IN (...)) -- 約 2200 通り
 ORDER BY `id` desc LIMIT 20;
```

というクエリを発行しています。IN 句の中に意味のない数字は大量にあります。
どうしてそんなものを…という質問については、すみません、今回はそこは無しでお願いします。
※追記: サブクエリはありません。IN の中には数値の列が与えられます。
※追記: 数が逆でした。

# my.cnf
関連しそうな my.cnf の設定は
``` my.cnf
query_cache_type        = 1
query_cache_limit       = 16M
query_cache_size        = 128M
innodb_buffer_pool_size = 48M
tmp_table_size         = 32M # not set
max_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
ALTER 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
ALTER 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
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE 
 (customer_id IN (SELECT文 A))
 AND
 (id IN (SELECT文 B))
 ORDER BY `id` desc LIMIT 20;
```
が本来行いたい処理であるようです。

これを
```SQL
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE 
 (customer_id IN (SELECT文 A の「結果カンマ区切り」))
 AND
 (id IN (SELECT文 B の「結果カンマ区切り」))
 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

```

よろしくお願いいたします。

MySQL 5.6 移行後の性能劣化

お世話になります

公開しているサーバー(Centos 自宅サーバー)がアクセスが困難なほどに重くなるタイミングがあって、ご質問させていただきます

自宅で公開しているサーバーは
ルーター → WEBサーバー → (Mysql, Redisクラスター, MongoDBのレプリカセットのDBサーバー)という構成です
(その他にDNSもあり)


重くなるタイミングは、ルーターからの接続WEBサーバーを**別のWEBサーバー(構成管理ツールで作成した同じ機能のWEBサーバー)に切り替えるタイミング**で、長くて10分ほどサイトが反応してないのでは？と思われるほどの重さになります

その後**時間の経過で重さは解消**されます
(再び元のWEBサーバーに戻すと、戻す際に10分ほどサイトが反応しない同様の反応がある)


上記の反応しない時間は、アクセスが多い時間帯のみ発生して(より長く)、アクセスが少ない時間には発生していません(又は重くなるほど発生していない)



時間の経過とともに重さが解消されるので実害はなさそうですが、WEBサーバーに対してロードバランサを使用していたのが上記の問題のために事実上使用不可能になっています(ロードバランサで切り替えるとサイトが継続して反応しないことに)(ロードバランサを使用しないで、ルーターから切り替えても上記の現象は発生)

アクセスが多いタイミングで発生するので長時間の再現をしづらいのも困っている点です(秒間2,3アクセスから)

Muninでチェックしたところ、デフォルトの監視項目では変化した点は特になさそうです

ログをチェックしたところ、Apacheのログ・PHPのエラーログとも通常通りでエラーは出力されていませんでした

ネットワークを切り替えたタイミング(例えばWEBサーバー192.168.0.100からWEBサーバー192.168.0.101)で発生しているので**ネットワークの問題**だと思っていますが、1週間ほど迷宮をさまよっています

解決方法をぜひお願い致しますm(__)m