Keywords: MySQL | SHOW PROCESSLIST | Query Monitoring
Abstract: This article provides an in-depth exploration of the MySQL SHOW PROCESSLIST statement, focusing on how to view complete SQL queries using SHOW FULL PROCESSLIST. It explains why queries are truncated to 100 characters by default, compares performance differences between implementations, and demonstrates various methods for viewing full queries through practical code examples. The discussion covers user privilege impacts on query results and the importance of Performance Schema as a future alternative.
The MySQL Process List Query Truncation Issue
In MySQL database management, developers and database administrators frequently need to monitor currently executing SQL queries. The SHOW PROCESSLIST command is a primary tool for obtaining this information, but many users discover that returned query statements are truncated, showing only the first 100 characters.
SHOW FULL PROCESSLIST Solution
To view complete SQL query statements, the most straightforward and effective method is using the SHOW FULL PROCESSLIST command. Unlike the standard SHOW PROCESSLIST, the FULL keyword ensures that the Info field displays the complete query text without truncation.
Basic usage example:
SHOW FULL PROCESSLIST;This command returns detailed information about all current connections, including complete SQL statements. In contrast, without the FULL keyword, MySQL defaults to displaying only the first 100 characters of each statement.
User Privilege Requirements
To view process information for all users, PROCESS privilege is required. Users without this privilege can only see information about their own threads. System users typically refer to non-client threads spawned by the server for internal task handling.
Alternative Implementation Approaches
Beyond the standard SHOW PROCESSLIST implementation, MySQL provides alternatives based on Performance Schema. The Performance Schema processlist table doesn't require mutex locks and offers better performance characteristics. While the INFORMATION_SCHEMA.PROCESSLIST table can also query process information, this implementation has been deprecated and may be removed in future MySQL versions.
Performance Schema query example:
SELECT * FROM performance_schema.threads WHERE TYPE = 'FOREGROUND';Practical Application Scenarios
SHOW FULL PROCESSLIST is particularly valuable when diagnosing "too many connections" errors. By examining complete query statements, administrators can identify specific SQL statements causing connection overload and implement appropriate optimization measures.
In graphical management tools like phpMyAdmin, users must additionally click the "Full texts" option (typically displayed as "← T →") to view untruncated results.
Output Field Detailed Explanation
SHOW PROCESSLIST output includes several important fields:
- Id: Connection identifier, matching CONNECTION_ID() function return value
- User: MySQL user executing the statement
- Host: Client hostname and port
- db: Thread's default database
- Command: Type of command being executed by the thread
- Time: Duration the thread has been in its current state
- State: Current state of the thread
- Info: Complete statement being executed
Performance Considerations
While SHOW FULL PROCESSLIST provides comprehensive information, it may impact performance in high-concurrency environments. For production systems, using Performance Schema implementation or periodic sampling rather than continuous monitoring is recommended.
Conclusion
SHOW FULL PROCESSLIST remains the standard solution for MySQL query statement truncation issues. As MySQL evolves, Performance Schema offers more efficient alternatives. Database administrators should select appropriate monitoring methods based on specific requirements and environmental factors, while considering user privileges and performance implications.