CommentOut

MySQLで実行中のクエリ一覧を表示する方法 MySQLで実行中のクエリ一覧を表示する方法

MySQLで実行中のクエリ一覧を表示する方法

公開日:  最終更新日:

サーバーの動作が重い時、プロセスを確認すると「SQLが重いぞ」ってことあると思います。
私の場合、最近ですとAIの学習用クローラーが大量にアクセスしてきて、情報を表示するためのクエリが混み合ってしまい、MySQLの負荷が高まり重くなってしまうっていうことがありました。

こういった時、SQLの中で何が起きているのか調べたいですよね。
今回は、現在MySQL上でどれだけのクエリが実行中で、どんなクエリが動いているのか、調べる方法を紹介します。

MySQLで実行中クエリを表示するにはSHOW PROCESSLIST

MySQLで実行中のクエリ一覧を表示するには以下のコマンドを使用してください。

SHOW PROCESSLIST;

PROCESSLISTのカラム:Id

プロセスのIDです。プロセスをKILLする時にはこのIDを指定します。

PROCESSLISTのカラム:User

このプロセスを実行しているユーザーです。
このUserを確認することで、プロセスを実行しているのがrootなのかアプリケーション用に作ったユーザーなのかの判定ができます。

PROCESSLISTのカラム:Host

どこからのクエリなのか確認できます。
アプリケーションユーザーだったら基本的にアプリケーションサーバーからでしょうし、rootなら実行元のIPが表示されるはずです。

PROCESSLISTのカラム:db

クエリが実行されているデータベースが表示されます。
特定のDBで実行されていない場合、NULLが入ります。

PROCESSLISTのカラム:Command

実行中のプロセスの種類が入るようです。

  • Sleep
    クライアントが待機状態で、現在処理を行っていない状態です。
    DBコネクションを作ったけど、クエリを実行していない状態や、クエリを実行した後、コネクションをクローズしていない状態かなを示します。
  • Query
    クエリ(SELECT、INSERT、UPDATE、DELETE など)を実行中の状態です。
  • Connect
    クライアントが接続した直後の状態です。
  • Killed KILL
    コマンドによって終了処理中の状態です。
  • Execute
    ストアドプロシージャまたはプリペアドステートメントを実行中の状態です。
  • Statistics
    サーバーが統計情報を計算している状態です。
  • Binlog Dump
    レプリケーションのためにバイナリログを転送中(レプリカ用のスレッド)の状態です。
  • Table Dump
    スレーブが FLUSH TABLES WITH READ LOCK を使用して、データベースのダンプを取得中の状態です。

PROCESSLISTのカラム:Time

プロセスが今の状態になってからどれくらいの時間(秒)が経過したかを表します。
このTimeが長いと、それだけ処理に時間をかけているということが考えられます。

PROCESSLISTのカラム:State

クエリの現在の状態を表します。

  • init
    クエリの初期化処理中の状態です。
  • checking permissions
    ユーザーの権限をチェック中です。
  • Creating tmp table
    クエリ結果のための一時テーブルを作成中です。
  • copying to tmp table
    クエリの中間結果を一時テーブルにコピー中です。
  • Sending data
    クエリのデータをクライアントに送信中です。
  • Sorting result
    クエリ結果をソート中です。
  • Waiting for table level lock
    テーブルロックを待機中です。
  • Waiting for query cache lock
    クエリキャッシュのロックを待機中です。
  • Locked
    他のクエリによって、対象プロセスのクエリがロックされ、実行待ちの状態です。
  • User lock
    GET_LOCK() などのユーザーロックを待機中です。

PROCESSLISTのカラム:Info

実行されているクエリの内容が入ります。
ただし、実行中クエリが先頭の100文字までしか表示されません。

実行中クエリを全文表示するにはSHOW FULL PROCESSLIST

SHOW PROCESSLISTでは、実行中クエリが先頭100文字までしか取得できませんでしたが、全文取得する方法もあります。
以下のクエリを使用します。

SHOW FULL PROCESSLIST;

SHOW FULL PROCESSLISTで取得できる内容はSHOW PROCESSLISTとほぼ同じですが、Infoに入っているクエリが先頭の100文字ではなく、クエリの全文になっています。

実行中のクエリ情報を絞り込む

上記で紹介したSHOW PROCESSLISTは便利なクエリですが、一方で情報の絞り込み(WHERE)や並べ替え(ORDER BY)ができません。
そこで使用するのが、INFORMATION_SCHEMA.PROCESSLISTです。
INFORMATION_SCHEMA経由でPROCESSLISTの情報を取得すると、WHERE句やORDER BY句が使えます。

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

これが何に使えるかというと、実行に3秒以上かかっているクエリを見つける時に、以下のようなクエリで見つけ出すことが出来ます。

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 3 ORDER BY TIME DESC;

上記クエリを用いることで、3秒以上かかっているクエリを時間のかかっている順に表示できます。

実行中のクエリを停止させる方法

「もうクエリの実行時間が長すぎてどうにもならん!」という時には、強制的にクエリを停止させることもできます。

KILL {プロセスID};

ここのプロセスIDはPROCESSLISTに表示されているIdを入れます。

宣伝
WordPressサイトのテンプレート編集やトラブル対応、バグ修正、簡単なJavascriptの作成(カルーセルやバリデーション等)など、小規模なスポット対応を受け付けております。
もしお困りごとがありましたら、お問い合わせフォームよりご相談ください。

この記事を書いた人

uilou

uilou

プログラマー

基本的に、自分自身の備忘録のつもりでブログを書いています。 自分と同じ所で詰まった人の助けになれば良いかなと思います。 システムのリファクタリングを得意としており、バックエンド、フロントエンド、アプリケーション、SQLなど幅広い知識と経験があります。 広いだけでなく、知識をもっと深堀りしていきたいですね。