MySQLのSHOW PROCESSLISTでアクティブセッションを確認する方法｜長時間クエリの特定とKILL文での強制終了手順

この記事のポイント

・SHOW FULL PROCESSLISTでInfo列のクエリ全文を切り詰めなく確認できる
・Time列が大きい行を見てKILL 接続IDで長時間クエリを強制終了できる
・INFORMATION_SCHEMA.PROCESSLISTでWHERE句による絞り込みが可能
・Sleepコネクション過多はwait_timeoutの調整で改善できる

# mysql コマンドで接続する
mysql -u root -p

# 接続後にPROCESSLISTを表示する
SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;

+----+------+--------------------+-------+---------+------+--------------+---------------------------------------------------------------------+
| Id | User | Host               | db    | Command | Time | State        | Info                                                                |
+----+------+--------------------+-------+---------+------+--------------+---------------------------------------------------------------------+
|  3 | app  | 192.168.1.10:52100 | appdb | Query   |   87 | Sending data | SELECT * FROM orders WHERE status = 'pending' AND created_at < ... |
|  5 | root | localhost          | NULL  | Query   |    0 | init         | SHOW FULL PROCESSLIST                                               |
|  8 | app  | 192.168.1.11:49800 | appdb | Sleep   |  320 |              | NULL                                                                |
+----+------+--------------------+-------+---------+------+--------------+---------------------------------------------------------------------+

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE,
       SUBSTRING(INFO, 1, 120) AS INFO_SHORT
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE TIME > 10
  AND COMMAND != 'Sleep'
ORDER BY TIME DESC;

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE STATE LIKE '%lock%'
ORDER BY TIME DESC;

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE DB = 'appdb'
ORDER BY TIME DESC;

# 60秒以上実行中のクエリを表示（Sleep除く）
SELECT ID, USER, TIME, STATE, SUBSTRING(INFO, 1, 100) AS SQL_SHORT
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE TIME >= 60
  AND COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- 接続IDが 123 のセッションを切断する（実行中クエリごと強制終了）
KILL 123;

-- クエリだけを停止して接続は維持する（MySQL 5.7以降）
KILL QUERY 123;

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| wait_timeout        | 28800 |
| interactive_timeout | 28800 |
+---------------------+-------+

SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;

[mysqld]
wait_timeout = 300
interactive_timeout = 300

# MySQL 8.0以降：ロックの保有者と待機中セッションを確認する
SELECT
  r.trx_id            AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id            AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread
FROM information_schema.innodb_lock_waits w
  INNER JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_trx_id;