How to customize `show processlist` in mysql?

In MySQL, the SHOW PROCESSLIST command is used to display all running threads in the system, including the queries currently being executed by each thread. This command is highly useful, especially when you need to view or optimize queries running on the current MySQL server. However, sometimes the output of this command may not provide sufficient information for your needs. At such times, you might need to customize the output to obtain more details or present the information in a more convenient format for analysis.

Customizing SHOW PROCESSLIST can be achieved through the following methods:

1. Using the INFORMATION_SCHEMA.PROCESSLIST Table

MySQL provides the INFORMATION_SCHEMA.PROCESSLIST table, which contains all the information available via the SHOW PROCESSLIST command. This table can be queried using standard SQL, allowing you to filter, sort, or format the output as needed.

Example:

sql
SELECT * 
FROM information_schema.processlist 
WHERE db = 'target_database' 
ORDER BY time DESC;

This query returns all processes for the target_database database, sorted by execution time in descending order.

2. Combining with Other Tools

In addition to direct database queries, you can leverage external tools like mytop or innotop to analyze the process list. These tools offer a dynamically updating interface, which is particularly well-suited for real-time monitoring of database status.

3. Writing Custom Scripts

If built-in commands and tools do not meet your requirements, you can write custom scripts to query the INFORMATION_SCHEMA.PROCESSLIST table and process the data according to your needs. You can implement this using any programming language that supports MySQL connections, such as Python, PHP, or Java.

Python Example:

python
import mysql.connector

 cnx = mysql.connector.connect(user='youruser', password='yourpassword',
                              host='127.0.0.1',
                              database='information_schema')
 cursor = cnx.cursor()
 query = ("SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO "
          "FROM PROCESSLIST "
          "WHERE COMMAND != 'Sleep' "
          "ORDER BY TIME DESC")

 cursor.execute(query)

 for (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) in cursor:
     print(f"{ID} {USER} @ {HOST} on {DB}: {COMMAND} for {TIME} seconds, state: {STATE}")
     if INFO:
         print(f"Query: {INFO}")

 cursor.close()
 cnx.close()

This Python script connects to the MySQL information_schema database, executes a query to view all non-sleeping processes, and displays them sorted by duration in descending order.

Summary: By employing the above methods, you can effectively customize and optimize the output of SHOW PROCESSLIST to suit your needs. Leveraging the flexibility of INFORMATION_SCHEMA.PROCESSLIST, integrating with existing tools, or developing custom scripts can significantly aid in managing and optimizing your MySQL server performance.