この記事の要点

SHOW PROCESSLIST で実行中スレッド一覧、SHOW FULL PROCESSLIST で SQL 全文表示 information_schema.processlist / performance_schema.threads でフィルタや集計可能 KILL でスレッド切断、KILL QUERY はクエリだけ中断（接続は残す） 長時間クエリ自動 KILL は pt-kill（Percona Toolkit）、pt-kill --busy-time=60 --kill ロック状況は SHOW ENGINE INNODB STATUS / performance_schema.data_locks

1. SHOW PROCESSLIST: 実行中スレッド一覧

-- 全スレッド（SQL は 100 文字で truncate）
SHOW PROCESSLIST;

-- SQL を最後まで表示
SHOW FULL PROCESSLIST;

出力カラム:

列	意味
Id	スレッド ID（KILL で使う）
User	ログインユーザー
Host	接続元 host:port
db	使用中 DB
Command	Sleep / Query / Locked / Connect 等
Time	その状態の経過秒
State	細かい状態（Sending data / Waiting for lock 等）
Info	実行中の SQL（FULL でないと 100 文字 truncate）

2. information_schema.processlist: フィルタしやすい

-- 60 秒以上動いているクエリ
SELECT id, user, host, db, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 60
ORDER BY time DESC;

-- 特定ユーザーの接続
SELECT * FROM information_schema.processlist WHERE user = 'app';

-- Sleep 状態のコネクション数
SELECT COUNT(*) FROM information_schema.processlist WHERE command = 'Sleep';

-- 接続元ごとの集計
SELECT SUBSTRING_INDEX(host, ':', 1) AS host, COUNT(*) AS conns
FROM information_schema.processlist
GROUP BY host
ORDER BY conns DESC;

3. performance_schema（推奨: 5.7+）

-- スレッド一覧（より詳細）
SELECT thread_id, processlist_id, processlist_user, processlist_command,
       processlist_time, processlist_state, processlist_info
FROM performance_schema.threads
WHERE processlist_command != 'Sleep';

-- 各スレッドの累積待ち時間
SELECT thread_id, event_name, sum_timer_wait / 1e9 AS ms
FROM performance_schema.events_waits_summary_by_thread_by_event_name
ORDER BY sum_timer_wait DESC LIMIT 20;

-- 現在のロック
SELECT * FROM performance_schema.data_locks\G
SELECT * FROM performance_schema.data_lock_waits\G

4. KILL: クエリ / 接続を強制終了

-- 接続ごと切断（接続が再接続を試みなければ完全停止）
KILL 1234;

-- クエリのみ中断（接続は残る → ロールバック後 idle 状態へ）
KILL QUERY 1234;

-- 60 秒以上の SELECT を全 KILL
SELECT CONCAT('KILL ', id, ';') AS cmd
FROM information_schema.processlist
WHERE command = 'Query' AND time > 60 AND info LIKE 'SELECT%';
-- ↑ 結果を copy & paste で実行

-- 自動 KILL（procedure 化例）
DELIMITER $$
CREATE PROCEDURE kill_long_queries(IN max_time INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE pid BIGINT;
  DECLARE cur CURSOR FOR
    SELECT id FROM information_schema.processlist
    WHERE command = 'Query' AND time > max_time;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO pid;
    IF done THEN LEAVE read_loop; END IF;
    SET @sql = CONCAT('KILL ', pid);
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;

CALL kill_long_queries(120);

5. mysqladmin processlist

# CLI から確認
mysqladmin -u root -p processlist

# verbose（FULL 相当）
mysqladmin -u root -p -v processlist

# 1 秒ごと更新（top 風）
mysqladmin -u root -p -i 1 processlist

# 特定 thread を kill
mysqladmin -u root -p kill 1234

6. pt-kill: 長時間クエリの自動殺し

Percona Toolkit の pt-kill は条件にマッチしたクエリを自動 KILL します。本番でも定番。

# 60 秒以上動いている SELECT を kill（dry-run）
pt-kill --host=127.0.0.1 --user=root --ask-pass \
    --busy-time=60 --match-info "^SELECT" --print

# 実際に kill
pt-kill --host=127.0.0.1 --user=root --ask-pass \
    --busy-time=60 --match-info "^SELECT" --kill

# デーモン化
pt-kill --daemonize --interval=5 --busy-time=120 \
    --ignore-user=replica --kill \
    --log=/var/log/pt-kill.log

7. ロックを調べる

-- 8.0+: 推奨
SELECT * FROM performance_schema.data_locks\G
SELECT * FROM performance_schema.data_lock_waits\G

-- 全文ステータス（InnoDB のロック状況・最新デッドロック）
SHOW ENGINE INNODB STATUS\G
-- → "TRANSACTIONS" セクションを見る
-- → "LATEST DETECTED DEADLOCK" でデッドロック詳細

-- 5.7: information_schema
SELECT * FROM information_schema.innodb_trx;
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

-- 待たせている / 待たされている関係
SELECT
  waiting_pid, waiting_query, blocking_pid, blocking_query
FROM sys.innodb_lock_waits;

8. スロークエリログ

-- 設定確認
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 動的に ON
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- パス確認
SHOW VARIABLES LIKE 'slow_query_log_file';

解析は pt-query-digest が定番:

pt-query-digest /var/log/mysql/slow.log | head -100

Command / State の意味

Command	意味
Sleep	クエリ待ち（idle）
Query	クエリ実行中
Connect	接続確立中
Killed	KILL 受信、後始末中
Locked	ロック待ち（古い表記、5.7 以降は State 側）

FAQ

Q: KILL したのに消えない
A: 大きなトランザクションはロールバック完了まで状態が Killed のまま残ります。Innodb 内部で undo を巻き戻し中。気長に待つか、極端な場合は再起動。

Q: アプリ側で接続プールしているせいか Sleep が多い
A: 健全。wait_timeout（デフォルト 28800 秒）を超えると自動切断されます。プール側で idle timeout を設定。

Q: 他人のクエリを kill できない
A: PROCESS 権限（全 thread 表示）と SUPER / CONNECTION_ADMIN（他人を KILL）が必要。