This post will help you to learn how to get a list of running queries in MySQL processlist.
How to Get a List of Running Queries in MySQL processlist?
In MySQL, there is a database named “information_schema” that contains metadata about the MySQL server including the “processlist” table. This processlist contains information about each running query, including the query ID, user, host, database, command, execution time, state, and additional information. However, MySQL offers different methods to view the running queries. Let’s head toward these methods.
Method 1: Using information_schema.processlist
To view the details of running queries, execute the command given below:
The command will select all columns of the processlist table.
Output
The output displayed detailed information about each running query.
Method 2: Filtering Sleeping Queries
If a user is only interested in the active queries, then the following command can be used:
The above command uses the “where” clause to filter out the queries that are not in the sleep state.
Output
The output provided the list of active queries.
Method 3: Using SHOW PROCESSLIST Command
To display the currently running queries by utilizing the “PROCESSLIST” execute the given below command:
This command will return the information of running queries in tabular form.
Output
Here in the above output, the detailed information of the running query can be observed.
Method 4: Obtain Detailed Processlist
MySQL also enables its users to obtain the more detailed value of running queries by including additional information such as the SQL statement being executed. To do so, run the given command:
The command uses the “\G” modifier to format the output vertically.
Output
Here you can see the output returned with detailed information about running queries.
Method 5: Using mysqladmin Command-Line Utility
The user can also utilize the “mysqladmin” utility to get the information of running queries on the MySQL server by executing the given command:
The command contains MySQL server username “md” in it. Make sure to change the username according to your MySQL server.
Output
The output displays the information about running queries in the processlist:
That’s all about getting the list of running queries in MySQL processlist.
Conclusion
In MySQL, various commands are used to get the list of running queries, such as “SHOW PROCESSLIST”, “SHOW FULL PROCESSLIST\G”, etc. It helps the users to perform important tasks such as troubleshooting, resource management, and query analysis. The user can utilize different commands to get the list of running queries in MySQL processlist. Moreover, the mysqladmin utility can also be used for providing the details of running queries of MySQL server.
