This article is to describe how to kill a MySQL process and how to use long-running queries using a few commands on the terminal.
1. Introduction
MySQL has tools to help us to manage and enhance our database performance. As an example, when a heavy query is executed on the database, it does a lot of issues like delay in data reading and further unavailability. In this article, we’ll see how to deal with these types of situations.
2. Pre-requisites
For this article, you’ll need MySQL Community Server installed. I’m using the version 8.0.21 on this example. Moreover, it is required a user with MySQL root privileges.
3. Identifying Processes
We need to identify what process are running on MySQL. Log in to MySQL with the following command:
$ mysql -uroot -p
Then, run this command that will show all process that are running:
mysql> show processlist; +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 3435 | Waiting on empty queue | NULL | | 11 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ 2 rows in set (0.00 sec)
Here we can see a lot of information about the processes. Let see them in depth below:
- Id: the identity of the process.
- User: the user executing that process.
- Host: the MySQL host where the instance is running.
- DB: the database where the process is been executed.
- Command: the type of command that is running (query, insert, update, and so on…).
- Time: how much time (in seconds) is the process running.
- State: the current state of the process.
- Info: the statement the thread is executing. Generally, it shows the script that is been executed by the processes.
More details about these informations see here.
4. Killing the Process
Now that we’ve seen how to identify the processes, we can choose what process we need to kill to “free” our database. How to do that? Firstly, pay attention to the columns Id, time, and state.
To know if that process is on a stuck, the TIME column is a great clue for that. Long-running queries can lock tables or delay the database. Furthermore, the STATE is also a good point to see with the process is hold.
The Id column will be used to kill the process using the command kill in MySQL like that:
mysql> KILL 11;
Note: Pay attention to the columns Id and User. So, be sure that is the right process that you want to kill, double-check the TIME and STATE columns to ensure your decision before running the kill command on MySQL.
5. Summary
In this article, we learn how to identify processes in MySQL. In addition, we saw how to kill a stuck process using a MySQL command, which is the easiest way to do it.
You can find more articles about MySQL here.
