🪄 MySQL SHOW PROCESSLIST Command – Monitor Active Connections & Queries

🧲 Introduction – Why Use SHOW PROCESSLIST?

In a live database environment, knowing which users are connected, what queries they are running, and which processes are causing bottlenecks is essential for diagnosing issues and improving performance. The SHOW PROCESSLIST command in MySQL allows DBAs and developers to inspect and monitor all active threads (processes) connected to the server.

🎯 In this guide, you’ll learn:

• What the SHOW PROCESSLIST command does
• How to interpret each column
• Use cases for performance monitoring and troubleshooting
• How to kill problematic queries
• Best practices for production use

🔎 Syntax – Basic Usage

SHOW PROCESSLIST;

Or to view full queries (not truncated):

SHOW FULL PROCESSLIST;

🧠 You need PROCESS privilege to see all threads. Without it, you’ll only see your own.

📊 Output Columns Explained

✅ Example Output

SHOW FULL PROCESSLIST;

🚦 Common Command Values

🚨 Use Cases – Why This Matters

🐢 Find Long-Running Queries

SHOW FULL PROCESSLIST;

Sort by Time to identify queries that are running too long.

🔒 Identify Locked Queries

Look for State = "Locked" — these may indicate deadlocks or waiting transactions.

⚔️ Kill a Problematic Process

KILL 10;

Kills thread with Id = 10. ⚠️ Use cautiously — it rolls back any ongoing transaction.

📘 Best Practices

✅ Use SHOW FULL PROCESSLIST to view complete SQL queries
🧠 Filter results by writing:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';

🔒 Grant only necessary users the PROCESS privilege to avoid exposing all sessions

🔁 Automate query logging using slow_query_log or performance_schema for large apps

🧪 Real-World Applications

📌 Summary – Recap & Next Steps

The SHOW PROCESSLIST command gives you a real-time snapshot of all active and idle threads in your MySQL server. It’s indispensable for troubleshooting, performance tuning, and live monitoring.

🔍 Key Takeaways

• Use SHOW FULL PROCESSLIST for full query visibility
• Check Time and State to detect long or stuck queries
• Use KILL to stop problematic threads (with caution)
• Automate log monitoring in production setups

⚙️ Real-World Relevance
Every serious MySQL application benefits from active monitoring using SHOW PROCESSLIST. It gives DBAs instant insight into server health.

❓ FAQ – MySQL SHOW PROCESSLIST

❓ What privilege is required to see all processes?
✅ You need the PROCESS privilege to view all threads.

❓ Is it safe to kill a thread using KILL?
⚠️ Yes, but it rolls back any active transaction. Always verify before killing.

❓ How can I detect sleeping connections?
✅ Look for Command = 'Sleep'. These are idle connections that may exhaust resources.

❓ How often should I run SHOW PROCESSLIST?
✅ In dev or testing: as needed. In production: use monitoring tools like Percona, PMM, or Performance Schema.

❓ Can I view this information programmatically?
✅ Yes. Query from INFORMATION_SCHEMA.PROCESSLIST.