CristGuard

Mysql

how to customize show processlist in mysql

19 September 2026 · 14 min read

how to customize show processlist in mysql

Managing database performance effectively often requires a deep dive into the processes running within your MySQL server. The show processlist command is a fundamental tool for observing these processes, offering insights into query execution, connection states, and potential bottlenecks. However, the default output can be overwhelming, especially in busy environments with numerous active threads. Learning how to customize show processlist in MySQL allows you to filter, sort, and extract specific information, making it easier to identify and address performance issues. This tailored approach transforms a generic monitoring tool into a powerful diagnostic instrument, enabling more efficient database administration and optimization. Understanding the nuances of customizing this command will empower you to proactively manage your MySQL server’s health and responsiveness.

Understanding the Default show processlist Output

The show processlist command, executed directly in the MySQL client, provides a snapshot of all currently running threads. Each row in the output represents a single connection to the MySQL server, along with details about its state and activity. The default output includes columns such as ID (the connection ID), User (the MySQL user associated with the connection), Host (the client host), db (the default database), Command (the command being executed), Time (the time in seconds the thread has been in its current state), State (a brief description of the thread’s state), and Info (the SQL query being executed, if any). However, the Info column is often truncated for long queries, which can hinder effective troubleshooting. Understanding these default columns is crucial before attempting any customization, as it sets the foundation for what you aim to refine and filter.

The show processlist command is useful, but it only shows a limited view of the running processes. The FULL keyword provides access to the complete query text in the Info column, which is invaluable when debugging long or complex SQL statements. Another crucial aspect is understanding the different states a thread can be in. States like “Sleep,” “Query,” “Locked,” and “Waiting for table metadata lock” offer clues about potential performance bottlenecks. For example, a high number of threads in the “Sleep” state might indicate inefficient connection handling, while threads frequently “Locked” suggest contention issues. By interpreting the show processlist output effectively, you can identify the root causes of performance problems and take appropriate action. According to MySQL documentation [^1^][MySQL Documentation], regularly monitoring the processlist is a key best practice.

Consider a scenario where your web application experiences sudden slowdowns. Executing show processlist reveals numerous threads in the “Locked” state, all related to a specific table. This immediately points to a potential locking issue within your database schema or application logic. Without this information, diagnosing the performance problem would be significantly more challenging and time-consuming. Further investigation, such as examining the relevant SQL queries and table structure, would then be necessary to resolve the locking issue and restore optimal performance. Understanding the default output and its limitations is the first step towards customizing show processlist to meet your specific monitoring needs. This detailed view is vital for proactive database management.

Customizing with INFORMATION_SCHEMA.PROCESSLIST

While show processlist is useful for a quick overview, querying the INFORMATION_SCHEMA.PROCESSLIST table offers more flexibility and control over the output. This table provides the same information as show processlist but allows you to use standard SQL queries to filter, sort, and aggregate the data. This means you can create customized views tailored to your specific monitoring requirements. For example, you can filter the processlist to show only queries that have been running for more than a certain amount of time, or only queries from a specific user or host. This level of customization is impossible with the basic show processlist command. Using INFORMATION_SCHEMA.PROCESSLIST enables more targeted and efficient performance analysis.

To start customizing, you can use a simple SELECT statement against INFORMATION_SCHEMA.PROCESSLIST. For instance, SELECT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != ‘Sleep’ will show all processes that are actively executing queries, excluding idle connections. You can also filter by user: SELECT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = ‘your_user’. Combining multiple conditions with AND and OR operators allows for even more refined filtering. For example, the following query is optimized to be a featured snippet: SELECT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 60 AND COMMAND != ‘Sleep’ AND DB = ‘your_database’. This query displays all active queries running for more than 60 seconds in a specified database. Regularly leveraging customized queries against INFORMATION_SCHEMA.PROCESSLIST gives you a proactive approach to identifying performance bottlenecks within your MySQL server.

Consider a situation where you’re investigating slow query performance. Instead of sifting through the entire show processlist output, you can use a query like SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 30 ORDER BY TIME DESC to identify the queries that have been running for the longest time. This immediately highlights the problematic queries, allowing you to focus your optimization efforts where they’re most needed. Furthermore, you can join INFORMATION_SCHEMA.PROCESSLIST with other tables in INFORMATION_SCHEMA to gather more detailed information about the queries and the tables they’re accessing. This level of customization offers a powerful and efficient way to diagnose and resolve performance issues. Utilizing this approach aligns with expert recommendations for effective database monitoring [^2^][Percona Blog].

Practical Examples of Custom Queries

The power of INFORMATION_SCHEMA.PROCESSLIST lies in its ability to be tailored to specific monitoring needs. Here are a few practical examples of custom queries that can provide valuable insights into your MySQL server’s performance. These examples demonstrate how to filter, sort, and aggregate data to identify potential bottlenecks and optimize query execution. Understanding these examples will enable you to create your own customized queries that address your unique monitoring challenges.

  • Finding Long-Running Queries: SELECT ID, USER, HOST, DB, TIME, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != ‘Sleep’ AND TIME > 60 ORDER BY TIME DESC; This query identifies queries that have been running for more than 60 seconds, sorted by execution time.
  • Identifying Queries from a Specific Host: SELECT ID, USER, DB, COMMAND, TIME, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST LIKE ‘192.168.1.%’; This query lists all connections originating from a specific IP address range.

Another useful example is aggregating data to identify users with the most active connections: SELECT USER, COUNT() AS connection_count FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY USER ORDER BY connection_count DESC;. This query can reveal users who might be consuming excessive resources. You can also use subqueries to find specific queries associated with a particular connection ID: SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = (SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = ‘problem_user’ LIMIT 1);. These practical examples illustrate the flexibility and power of INFORMATION_SCHEMA.PROCESSLIST for targeted database monitoring. Experimenting with different queries and filters will help you develop a deeper understanding of your MySQL server’s behavior. Remember to adjust the thresholds and conditions to match your specific environment and performance goals. Effective use of these techniques is a hallmark of skilled database administration.

Consider a real-world scenario where a specific user is suspected of causing excessive database load. Instead of manually examining the entire processlist, you can use the query SELECT ID, USER, HOST, DB, COMMAND, TIME, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = ‘suspect_user’ AND COMMAND != ‘Sleep’ ORDER BY TIME DESC; to pinpoint the queries being executed by that user and their execution times. This allows you to quickly identify problematic queries and work with the user to optimize their code. Furthermore, you can combine this with connection count aggregation to determine if the user is exceeding their allocated connection limit. This targeted approach minimizes the impact on other users and ensures optimal database performance. Effective database management involves proactive monitoring and targeted troubleshooting.

Best Practices and Considerations

While customizing show processlist and using INFORMATION_SCHEMA.PROCESSLIST provides powerful monitoring capabilities, it’s essential to follow best practices to avoid negatively impacting database performance. Querying INFORMATION_SCHEMA.PROCESSLIST too frequently, especially with complex queries, can add overhead to the MySQL server. Therefore, it’s crucial to strike a balance between monitoring frequency and server load. Consider implementing caching mechanisms or using a dedicated monitoring tool to minimize the impact on production databases. Furthermore, ensure that you have appropriate permissions to access INFORMATION_SCHEMA.PROCESSLIST, as granting excessive permissions can pose a security risk.

  1. Minimize Query Frequency: Avoid running complex queries against INFORMATION_SCHEMA.PROCESSLIST too frequently.
  2. Use Caching: Implement caching mechanisms to store the results of frequently executed queries.
  3. Grant Appropriate Permissions: Restrict access to INFORMATION_SCHEMA.PROCESSLIST to authorized users only.

Another important consideration is the impact of long-running queries on database performance. While show processlist and INFORMATION_SCHEMA.PROCESSLIST can help identify these queries, it’s crucial to address the underlying causes. Long-running queries often indicate inefficient SQL code, missing indexes, or data contention issues. Optimizing these queries through code reviews, index creation, and schema adjustments can significantly improve overall database performance. Regular performance tuning and optimization are essential for maintaining a healthy and responsive MySQL server. Remember to monitor the resource utilization of your monitoring queries themselves to ensure they aren’t contributing to the problem. According to performance experts [^3^][Severalnines Blog], proactive monitoring is crucial.

Infographic here
Finally, remember that show processlist and INFORMATION\_SCHEMA.PROCESSLIST provide a snapshot in time. To gain a comprehensive understanding of database performance, it's essential to collect historical data and analyze trends over time. Implement a monitoring solution that captures and stores processlist data, along with other performance metrics such as CPU usage, memory consumption, and disk I/O. This historical data can help you identify patterns, predict future performance issues, and track the effectiveness of your optimization efforts. Comprehensive monitoring is key to long-term database health and stability. You can also use [other MySQL tools](https://courthousezoological.com/n7sqp6kh?key=e6dd02bc5dbf461b97a9da08df84d31c) for enhanced monitoring and management.

FAQ

How do I see the full query in show processlist?
Use show full processlist; instead of show processlist;. This displays the complete query in the "Info" column.
What does the "State" column in show processlist indicate?
The "State" column provides a brief description of what the thread is currently doing, such as "Sleep," "Query," or "Locked."
Can I kill a process using show processlist?
Yes, you can use the KILL command followed by the process ID to terminate a connection. For example: KILL 123;.
By mastering the art of customizing show processlist and leveraging the flexibility of INFORMATION\_SCHEMA.PROCESSLIST, you gain invaluable insights into your MySQL server's inner workings. This detailed understanding empowers you to proactively identify and resolve performance bottlenecks, ensuring optimal database health and responsiveness. Take the time to experiment with different queries, filters, and monitoring techniques to develop a customized approach that meets your specific needs. Embrace these tools, and you'll be well-equipped to tackle even the most challenging database performance issues. Consider exploring related topics such as slow query logging and performance schema to further enhance your database monitoring capabilities. **Question & Answer :** I want to order by Time,but seems no way to do that ? 
mysql> show processlist; +--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+ | 1 | system user | | NULL | Connect | 226953 | Waiting for master to send event | NULL | | 2 | system user | | v3 | Connect | 35042 | Locked | update postings a left join cities b on b.id=a.job_city_id left join states h on h.id=b.stat | | 313888 | irnadmin | 172.19.0.239:40136 | v3 | Sleep | 0 | | NULL | | 314075 | irnadmin | 172.19.0.239:41113 | v3 | Sleep | 0 | | NULL | | 314118 | irnadmin | 172.19.0.239:41282 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ | | 314686 | irnadmin | 172.19.0.239:43251 | v3 | Sleep | 0 | | NULL | | 314732 | irnadmin | 172.19.0.239:43436 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ | | 314984 | irnadmin | 172.19.0.239:44366 | v3 | Sleep | 2 | | NULL | | 315051 | irnadmin | 172.19.0.239:44713 | v3 | Query | 0 | NULL | NULL | | 315198 | irnadmin | 172.19.0.239:51569 | v3 | Sleep | 2 | | NULL | | 315280 | irnadmin | 172.19.0.239:51849 | v3 | Query | 34978 | freeing items | SELECT id, email_address, type, closed, robotno FROM accounts WHERE screen_name = 'ShantanuS' | | 315320 | irnadmin | 172.19.0.239:52045 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ | | 315384 | irnadmin | 172.19.0.239:52463 | v3 | Sleep | 1 | | NULL | | 452248 | irnadmin | 172.19.0.28:54899 | v3 | Query | 34978 | freeing items | SELECT id, email_address, type, closed, robotno FROM accounts WHERE screen_name = 'LIZW0218' | | 452291 | irnadmin | 172.19.0.28:55045 | v3 | Sleep | 1 | | NULL | | 452316 | irnadmin | 172.19.0.28:55144 | v3 | Sleep | 0 | | NULL | | 452353 | irnadmin | 172.19.0.28:55278 | v3 | Sleep | 0 | | NULL | | 452382 | irnadmin | 172.19.0.28:55371 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND | | 452413 | irnadmin | 172.19.0.28:55479 | v3 | Sleep | 1 | | NULL | | 452541 | irnadmin | 172.19.0.28:55946 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND | | 452626 | irnadmin | 172.19.0.28:56215 | v3 | Sleep | 2 | | NULL | | 452711 | irnadmin | 172.19.0.28:39916 | v3 | Sleep | 0 | | NULL | | 452781 | irnadmin | 172.19.0.28:40161 | v3 | Sleep | 1 | | NULL | | 452904 | irnadmin | 172.19.0.28:40955 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. | | 453014 | irnadmin | 172.19.0.28:41291 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND | | 453057 | irnadmin | 172.19.0.28:41377 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. | | 453084 | irnadmin | 172.19.0.28:41441 | v3 | Sleep | 0 | | NULL | | 453112 | irnadmin | 172.19.0.28:41536 | v3 | Sleep | 0 | | NULL | | 453156 | irnadmin | 172.19.0.28:41653 | v3 | Query | 34978 | freeing items | SELECT protocol FROM accounts WHERE email_address= '***@gtalk.jabber.jobirn.c | | 453214 | irnadmin | 172.19.0.28:41800 | v3 | Sleep | 5 | | NULL | | 453243 | irnadmin | 172.19.0.28:41991 | v3 | Sleep | 0 | | NULL | | 453313 | irnadmin | 172.19.0.28:42255 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND | | 453396 | irnadmin | 172.19.0.28:53718 | v3 | Sleep | 2 | | NULL | | 453476 | irnadmin | 172.19.0.28:54019 | v3 | Sleep | 0 | | NULL | | 453561 | irnadmin | 172.19.0.28:54352 | v3 | Sleep | 3 | | NULL | | 453594 | irnadmin | 172.19.0.28:54456 | v3 | Sleep | 0 | | NULL | | 453727 | irnadmin | 172.19.0.28:55166 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ | | 453786 | irnadmin | 172.19.0.28:55320 | v3 | Sleep | 4 | | NULL | | 610140 | irnadmin | 172.19.0.28:33848 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. | | 685119 | irnadmin | 172.19.0.27:37251 | v3 | Query | 34980 | Sending data | select postings.id id,category, job_desc_title, IF(c1.name is not null,c1.name,IF(c2.name is not n | | 685226 | irnadmin | 172.19.0.139:57274 | v3 | Query | 34735 | Locked | SELECT job_desc_title,job_desc,job_state_name,job_city_name,company_categories.name,postings.categor | | 685229 | irnadmin | 172.19.0.139:57278 | v3 | Query | 34735 | Locked | SELECT job_desc_title,job_desc,job_state_name,job_city_name,company_categories.name,postings.categor | | 685232 | irnadmin | 172.19.0.139:57283 | v3 | Query | 34734 | Locked | select job_desc_title,job_desc from postings where id=287650 | | 685233 | irnadmin | 172.19.0.139:57286 | v3 | Query | 34734 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job | | 685235 | irnadmin | 172.19.0.28:37502 | v3 | Query | 34734 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job | | 686496 | irnadmin | 172.19.0.239:33306 | v3 | Query | 32589 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job | | 686503 | irnadmin | 172.19.0.28:54051 | v3 | Query | 32588 | Locked | SELECT job_desc_title, job_desc, IF(postings.category IS NOT NULL, postings.category, job_categories | | 709550 | root | localhost | v3 | Query | 0 | NULL | show processlist | | 710084 | irnadmin | 172.19.0.27:53285 | NULL | Query | 0 | removing tmp table | show status where Variable_name='Threads_running' | +--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+ 49 rows in set (0.00 sec)

Newer versions of SQL support the process list in information_schema:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

You can ORDER BY in any way you like.

The INFORMATION_SCHEMA.PROCESSLIST table was added in MySQL 5.1.7. You can find out which version you’re using with:

SELECT VERSION()