When your MySQL server encounters issues and you need to identify SQL queries with the worst performance, the first action every server administrator typically takes is to open the MySQL console and start investigating using the SHOW PROCESSLIST or SHOW FULL PROCESSLIST commands.
On servers with many concurrent connections to multiple databases, you will often see numerous rows with the command text: Sleep.
When I am troubleshooting slow SQL queries, I do not want to be distracted by these sleeping connections; I want to focus only on the problematic SQL statements.
My preferred solution, especially on newer MySQL versions, is to avoid using the SHOW PROCESSLIST command and instead retrieve data directly from the information_schema:
Standard Table View for Query Investigation
Sometimes, I prefer the standard table view, where results are displayed as a table (using a semicolon at the end of the SQL command):
SELECT * FROM information_schema.processlist WHERE command != 'Sleep' AND time > 1 AND user <> 'system user' AND user <> 'replicator' ORDER BY time;
Alternative Display Mode for Long SQL Statements
If the SQL statements are too long, I prefer to use a different display mode by appending \G at the end of the command:
SELECT * FROM information_schema.processlist WHERE command != 'Sleep' AND time > 1 AND user <> 'system user' AND user <> 'replicator' ORDER BY time\G
Advantages Over SHOW PROCESSLIST
Compared to the SHOW PROCESSLIST command, this approach offers several advantages:
- You can display only the rows where the execution time meets your criteria.
- You can filter out connections from specific users (for example, in my code, I have excluded the replicator and system user accounts).
- You can sort the results as needed.
Note: Using information_schema.processlist for filtering and sorting provides more flexibility and focus, especially on busy servers.