แสดง Query ที่กำลังรันอยู่ใน MySQL ด้วย SHOW PROCESSLIST

ใช้ SHOW PROCESSLIST และ SHOW FULL PROCESSLIST เพื่อดู Query ที่กำลัง Execute อยู่

Basic Syntax

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;  -- แสดง Info เต็ม (ไม่ Truncate)
SHOW FULL PROCESSLIST\G; -- Vertical Format (อ่านง่าย)

Output Columns

Column	Description
Id	Connection ID (ใช้ KILL)
User	MySQL User
Host	Client Host:Port
db	Current Database
Command	Sleep, Query, Connect, Binlog Dump, Daemon, etc.
Time	Seconds in current state
State	Starting, Checking permissions, Sending data, Locked, etc.
Info	SQL Statement (Truncated in PROCESSLIST, Full in FULL PROCESSLIST)

ตัวอย่าง Output

-- Table Format
+----+-----------------+-----------------+----------+---------+-------+------------------------+------------------+
| Id | User            | Host            | db       | Command | Time  | State                  | Info             |
+----+-----------------+-----------------+----------+---------+-------+------------------------+------------------+
| 4  | event_scheduler | localhost       | NULL     | Daemon  | 13785 | Waiting on empty queue | NULL             |
| 9  | root            | localhost:63587 | business | Query   | 0     | starting               | show processlist |
+----+-----------------+-----------------+----------+---------+-------+------------------------+------------------+

-- Vertical Format (\G)
*************************** 1. row ***************************
Id: 4
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 17385
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 9
User: root
Host: localhost:63587
db: business
Command: Query
Time: 0
State: starting
Info: SHOW FULL PROCESSLIST

การใช้งานจริง (Practical Usage)

1. หา Long-running Queries

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE TIME > 60 AND COMMAND = 'Query'
ORDER BY TIME DESC;

2. หา Locked Queries

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE STATE LIKE '%lock%' OR STATE = 'Locked';

3. นับ Connection ต่อ User

SELECT USER, COUNT(*) AS connections
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY USER
ORDER BY connections DESC;

4. Kill Problematic Query

KILL 12345;        -- Kill Connection
KILL QUERY 12345;  -- Kill Query Only (Keep Connection)

Performance Schema (Modern, Detailed)

-- Current Statements
SELECT * FROM performance_schema.events_statements_current
WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 12345);

-- History
SELECT * FROM performance_schema.events_statements_history
WHERE THREAD_ID = ...;

สิทธิ์ที่ต้องการ

User ปกติ: ดู Process ของตัวเอง
PROCESS Privilege: ดู Process ทุกคน
SUPER / CONNECTION_ADMIN: KILL ได้