質問: <環境> 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台のサーバーでなぜこのような違いがあるのか何が考えられるでしょうか? また、調査するにはどのようにするのがよいでしょうか?
回答: 情報の補足、ありがとうございます。 この2台のサーバーでなぜこのような違いがあるのか何が考えられるでしょうか? ====================================== > > spin_lock、os_wait値はAの方が高い > > > ということから、DBサーバAではロックの取得待ちが頻発しており、 それがDBサーバAのCPU使用率を高めている、という可能性が考えられます。 なぜなら、どうやら spin_lock 中はビジーウェイトすることがあるようだからです。 > > InnoDB minimizes this issue by waiting a random time between subsequent polls. The delay is implemented as a busy loop. > > > また、調査するにはどのようにするのがよいでしょうか? =========================== 上の仮説を検証するためには、INFORMATION_SCHEMA テーブルを確認すると何か分かるかもしれません。 ちょっと情報が多すぎて私も追いきれていないのですが、以下のページなどが有用かと思います。 特に、最後のリンクで紹介されているクエリを何度も繰り返し実行してみて、`waiting_query`カラムや`blocking_query`カラムに同じようなクエリが頻繁に表示される場合、それが犯人だと推測できます。 または、一時的にスロークエリの閾値を下げてみることで、問題のあるクエリを検出できる可能性があります。 > > ※スロークエリも特に問題のあるものはない > > > とのことですが、例えば、現状 `long_query_time`の値をデフォルトの 10 に設定しているとすると、 1回の実行に5秒かかるクエリが10万回実行されていたとしても、それをスローログから検知することはできません。 一時的にスロークエリの閾値を下げてみることで、そのようなクエリがログに記録されるかもしれないからです。 ちなみに、本回答で紹介させていただいたMySQLのマニュアルのリンクについて、実はURLの "5.5/en" 部分を "5.6/ja" に書き換えると、すべて日本語訳のページを表示してくれますw ただし、日本語訳は バージョン5.6 のものですので、5.5 とは記述が異なっている可能性があります。 ご注意ください。