信頼はずっと、挑戦はもっと。

お問い合わせ
TEL:03-3496-3888

BLOG コアテックの社員ブログ (毎週月曜~金曜更新中)

2016

10

6月

Database:Mysqlがめっちゃ重い時

テクログ

 

こんにちは、ni-yoです。

すごく開発者向けな話になりますが、MysqlのDBが応答返せないくらいめっちゃ重い。
モニターするとDBのCPUやメモリが異常に高騰している…とかありますよね。

 

その時に負荷の原因となり得る時間がかかっているクエリを探すためには、SHOW FULL PROCESSLISTというコマンドが便利です。

 

MYSQLのSQLを発行する画面で
SHOW FULL PROCESSLIST;
を実行!

 

出てくる一覧としては、

id
USER
Host
db
Command
Time
State
Info

がでてきます。

 

IDはプロセスID
UserはMysqlの実行ユーザ名
Hostは接続元
dbは対象のデータベース
Timeは実行している時間(秒)
Stateは実行している状態(init処理、データ送信処理等の状態)
Infoは実行しているモノ(主にSQL)

です。

 

見方はわりとシンプルに

CommandがQuery且つTimeが異常に高い値(数十秒を超えている等)を探します。
そういったSQLがあった場合例えばIndex不足で異常に処理時間がかかっていたりという可能性があります。

 

もしそれがあった場合、Infoに書いてあるSQLを確認して、どの処理から発行されたSQLなのか、そのSQL
は正しいSQLなのかを確認しておくと後に同様の事象が発生する事を抑止することができますが、
どちらにせよそういった原因となるSQLがいつまでも動いていては負荷の原因が取り除かれませんので、
勇気を持ってMysql上から問題のプロセスを取り除きます。

 

SHOW FULL PROCESSLISTで下記のような時間がかかっているSQLが発見された場合(Timeが300なので5分間応答が返ってきていない)
45384100 root (発行元IPアドレス):(ポート番号) (テーブル名) Query 300 Sending data SELECT DISTINCT…(省略)

 

KILL (プロセス番号)
で上記SQLの実行を停止できます。

 

プロセス番号は一番左のカラムになりますので、
勇気を持って下記コマンドを実行します!

KILL 45384100

をたたくと、他の動作には影響を与えず上記問題のSQLの実行を強制停止できます。
(Windowsのタスクマネージャにあるプロセスを落とすのと同じような感じです)

 

何か異常に時間がかかる場合は上記処置で負荷は改善されるかと思います。

 

負荷の原因は一概に言えませんが、例えばSHOW PROCESSLISTでプロセスが100個とか出てくるのは基本的には
異常であると見なせると思います。

 

例えば月1千万PVあるサイトだったとしても、1秒で考えれば
10,000,000 ÷ 30(日) ÷ 24(時間) ÷ 60(分) ÷ 60(秒) ≒ 4回/秒
ということなので、もちろんピーク時間のなどはありますが慣らすと1秒に4回程度の閲覧になるはずです。

 

ユーザページで様々なSQLを発行しているとしても大概のWebサイトはシングルスレッドで1つのSQLが終わったら次を発行の
動作しているとは思うのでSQLの個数を考えるとしたら概ね10個以内に収まるはずです。

 

月数億PVも行くような超大手のようなサイトでは上記の考えはあてはまりませんが、月1千万PV程度にも満たないサイトが

プロセスが100個以上いくような場合には、

Mysqlのコネクションが切れない(もしくは不必要に長い)設定がサーバにしてある。
DosやDDos等通常ありえない大量のリクエストが送信されている。

等の部分を確認する必要があるかもしれません。

 

 

それでは(* ̄▽ ̄)ノ~~ マタネー♪

この記事を書いた人

マスオさん

新納誠一

所 属:
役員
出身地:
新潟
仕事内容:
プログラミング