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

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

ただいまの
回答率

87.93%

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

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 3
  • VIEW 1,563

score 23

<環境>
CentOS6.5, Mysql5.5

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

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

<要約>
・DBサーバーA
テーブルX:データ件数約600万レコード

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

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

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Marcocco

    2016/07/26 18:21

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

    キャンセル

  • coco_bauer

    2016/07/26 18:40

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

    キャンセル

  • Marcocco

    2016/07/27 20:17

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

    キャンセル

回答 4

checkベストアンサー

+2

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

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

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 とは記述が異なっている可能性があります。
ご注意ください。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/03 12:36

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

    キャンセル

+1

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/27 19:44

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

    キャンセル

0

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/26 14:50

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

    キャンセル

0

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

$ mysqlcheck -u root -p -o dbtable

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/26 14:53

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

    キャンセル

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

  • ただいまの回答率 87.93%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る