Mysql5.5で２台のDBサーバーでパフォーマンスに差異がある場合の調査方法を教えてください

Question

＜環境＞
CentOS6.5, Mysql5.5

＜問題点＞
Mysql5.5でまったく同じ性能、設定のDBサーバーが２台あり、テーブル定義は同じですがデータの内容が違うDBがそれぞれにあります。

使用ユーザーが増えるにつれ処理が重くなってきたので調査したところ、DBサーバーAだけがDBサーバーBの１．５倍ほど
CPU使用率が高く、spin_lockも多く発生していることがわかりました。
それで、コネクション数やSQLの発行数などを調査したところ、DBサーバーBの方がAよりも１．３倍ほど値が高い傾向でした。spin_lockは多いもののデッドロックは発生しておらず、スロークエリも問題となるようなものもありません。

＜要約＞
・DBサーバーA
テーブルX:データ件数約６００万レコード

・DBサーバーB
テーブルX':データ件数約６００万レコード

※サーバー性能は同じ
※Mysqlの設定は同じ
※Update、Insert、Deleteの処理件数はBの方が多い
※CPU使用率、spin_lock、os_wait値はAの方が高い
※デッドロックは発生していない
※スロークエリも特に問題のあるものはない
※プログラムは同じ
※ユーザーIDにより使用するDBを振り分けている

＜質問内容＞
この２台のサーバーでなぜこのような違いがあるのか何が考えられるでしょうか？
また、調査するにはどのようにするのがよいでしょうか？

Comments

[KiyoshiMotoki]

ご質問の主意は何でしょうか？単に「２台のサーバーでなぜこのような違いがあるのか」知りたいだけなのか、または「処理が重くなってきた」ことを改善したいのでしょうか？後者の場合、どちらのサーバの何を（処理速度なのか、CPUまたはI/Oの負荷を下げたいのか、、など）改善したいのかを明記すると、具体的な回答がつきやすくなるかと思います。

[Marcocco]

ご連絡ありがとうございます。質問内容がわかりにくくすいません。質問内容修正しました。
質問の意図は、もちろん改善をしたいのですがその前に見解と調査方法がしりたいと考えています。

[dupont_kedama]

テーブルX の それぞれの（レコード数ではなく）データ容量は同等でしょうか？

[Marcocco]

はい、ほぼ同じなのです。

[coco_bauer]

「ユーザーIDにより使用するDBを振り分けている」という事は、サーバーAとサーバーBの負荷は異なりますよね。負荷が異なれば、パフォーマンス（レスポンス）が異なるのは自然だと思います。　それぞれのサーバのログから、どんな要求を受け付けたのかを調べると共に、サーバの負荷情報（CPU利用率、ディスクへのアクセス数、レスポンス時間等のデータ）の推移と対応づけてみれば、パターンが見つかるかもしれません。

[Marcocco]

ご連絡ありがとうございます。確かにおっしゃる通りで、パフォーマンスに差が出るのはわかるのですが
あまりにも差が開いており、ログをみても情報の推移を見ても、原因が見いだせていないのが現状です。

Answer 1

情報の補足、ありがとうございます。

この２台のサーバーでなぜこのような違いがあるのか何が考えられるでしょうか？

spin_lock、os_wait値はAの方が高い

ということから、DBサーバAではロックの取得待ちが頻発しており、
それがDBサーバAのCPU使用率を高めている、という可能性が考えられます。

なぜなら、どうやら spin_lock 中はビジーウェイトすることがあるようだからです。
https://dev.mysql.com/doc/refman/5.5/en/innodb-performance-spin_lock_polling.html

InnoDB minimizes this issue by waiting a random time between subsequent polls. The delay is implemented as a busy loop.

また、調査するにはどのようにするのがよいでしょうか？

上の仮説を検証するためには、INFORMATION_SCHEMA テーブルを確認すると何か分かるかもしれません。
https://dev.mysql.com/doc/refman/5.5/en/information-schema.html

ちょっと情報が多すぎて私も追いきれていないのですが、以下のページなどが有用かと思います。
https://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema-transactions.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-trx-table.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-locks-table.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-lock-waits-table.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema-examples.html

特に、最後のリンクで紹介されているクエリを何度も繰り返し実行してみて、waiting_queryカラムやblocking_queryカラムに同じようなクエリが頻繁に表示される場合、それが犯人だと推測できます。

または、一時的にスロークエリの閾値を下げてみることで、問題のあるクエリを検出できる可能性があります。
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_long_query_time
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_min_examined_row_limit

※スロークエリも特に問題のあるものはない

とのことですが、例えば、現状 long_query_timeの値をデフォルトの 10 に設定しているとすると、
1回の実行に5秒かかるクエリが10万回実行されていたとしても、それをスローログから検知することはできません。

一時的にスロークエリの閾値を下げてみることで、そのようなクエリがログに記録されるかもしれないからです。

---

ちなみに、本回答で紹介させていただいたMySQLのマニュアルのリンクについて、実はURLの "5.5/en" 部分を "5.6/ja" に書き換えると、すべて日本語訳のページを表示してくれますw

ただし、日本語訳は バージョン5.6 のものですので、5.5 とは記述が異なっている可能性があります。
ご注意ください。

Comments

[Marcocco]

連絡が遅くなってすいません。詳細な回答に感動です。実はその後、調査を待っていられないとのことでプログラム改良で対応され、環境が変わってしまいました。。。でのすので、次回、今後調査する際の糧とさせていただきます。ありがとうございました。

Answer 2

レプリケーションなし、ユーザーＩＤで水平分割の運用でしょうか？
通常ありえないことばかりですが、なにかヒントになることでもあれば・・・

1. Aサーバーのほうがクエリキャッシュのヒット率が低い
2. Aサーバーが複雑なSQLの割合が多い（JOIN/サブクエリなど）
3. もしくは、複雑なSQLに影響するデータ数が多い
4. クラウドの共有サーバーの場合、同じハード上の他サーバーに引っ張られている
5. ストレージの劣化
6. MySQL以外のサービスが影響

Comments

[Marcocco]

連絡が遅くなってすいません。はい、水平分割です。
回答の観点で調査してみます。ありがとうございました！

Answer 3

もう既に試されたとは思いますが書いておきます。
Deleteが多いと、徐々にHDD領域を多めに取ってしまうようになると思います。
ですので、テーブルの最適化を試してはどうでしょうか。

$ mysqlcheck -u root -p -o db名 table名

InnoDB の場合は、
テーブルダンプ ⇒ テーブル削除 ⇒ テーブル最作成 ⇒ テーブルリストア
の手順で可能だそうです。

Comments

[Marcocco]

ご連絡ありがとうございます。本番機なので原因が特定できずにこの作業を行いますとはいえない状況なのです。。。

Answer 4

同じテーブルをA,Bに用意して検証してみないとあまり意味ないのでは？

データへのアクセスはどのようにされていますか？
ODBCやPDOでプログラムから読んでいます？

あまり関係ないかもしれませんがデータが多くなればHDDなどのファイルI/Oも
それなりに影響があるのかもしれません

Comments

[Marcocco]

ご連絡ありがとうございます。PDOを使っています。
そうですね。断片化も遅くなる原因になるかとは思います。
しかし、同じ時期に構築したAとBで大きく違うのが不可解なのです。