It’s possible to have a case where numerous threads are running on your MySQL server. Among these threads, the idle ones may cause an error on your MySQL server unless you can view the running processes and kill those that you currently don’t need.
MySQL is a relational DBMS that offers various options for users to list the running processes. We will explain how to show the running MySQL processes.
Identifying the MySQL Processes
When you have a MySQL database on your server, you may occasionally need to check its status to check the amount of load it has. When you notice the delays or problems with various queries on your MySQL database, it signals that you may have excessive and idle threads.
Hence, understanding what threads are causing problems and how you can minimize the load is handy. With MySQL, you can use different options to analyze the running processes. We will discuss two options that will help show the MySQL processes.
Method 1: Via the Show Processlist Command
When you access MySQL on the command line, you can use the SHOW PROCESSLIST command to display the information regarding all the running processes in your MySQL server. When executed, it gives the server a snapshot of various connections and their information such as the state, time, etc.
The following is the command to execute:
Note the various columns from the output. Let’s briefly discuss each:
- Id – It displays the process ID of the running process. In the case of numerous running processes, each has its unique ID.
- User – It represents the user that is associated with the particular thread.
- Host – It shows the host where the particular client is connected. It is the host name of the specific client that issued the statement.
- DB – If a database for a particular thread is selected, it appears under the DB column. If it shows NULL, no database is selected.
- Command – It shows the command that is being executed by the thread.
- Time – For the particular thread, this column tells how long the thread is in its current state.
- State – It shows what state or event is the thread engaged in.
- Info – It shows what statement the thread is currently executing.
In this case, the previous output is the result that we get from our SHOW PROCESSLIST command. The result is displayed in a tabular manner. Suppose you want to view the same results but in a vertical manner. You can use the following command instead:
Optionally include [FULL] in the command to ensure that you don’t miss out on any running process.
Method 2: Via the INFORMATION_SCHEMA.PROCESSLIST Option
MySQL has the INFORMATION_SCHEMA.PROCESSLIST table that contains a list of all active connections that are going to its server. By accessing this table, you get the details of all those active connections to know their host, process ID, state, command, etc. that are similar to what you get with the SHOW PROCESSLIST command.
Here’s the command to execute:
Once you execute the command, you get an output that is similar to the one in the following which shows all the details of the MySQL running processes:
How to Kill the MySQL Running Processes
Suppose you have an issue with the running processes. You can kill any idle process to reduce the load time of your server and enhance the efficiency. The first task is to identify the ID of the particular thread. We’ve given two methods to find the ID of any thread.
Once you have the ID, you can execute the “kill” command with the following syntax:
Once you execute the command, you will get a success output that shows the affected row, and the query will disconnect from the server. That’s how you kill a MySQL process.
Conclusion
MySQL offers two ways that you can use to show the running processes. We discussed both options, the given the syntax and an example command, to help you understand the MySQL SHOW PROCESSLIST. Still, we’ve seen how you can kill a running process. Hopefully, you now understand how the MySQL SHOW PROCESSLIST works.
