Identify and Kill Queries with MySQL Command
In SQL, some unnecessary processes can degrade your system’s performance. Over time, threads pile up and stall your server, preventing users from accessing tables and executing requests.
When resource usage is extremely high, you may need to kill MySQL processes. To do this, we first need to identify the processes that are running. We will use the SHOW PROCESSLIST command to identify the processes that are running, and we will see the methods for killing these queries. In this article, we will use MySQL as a database.
Syntax
For identifying the processes
SHOW PROCESSLIST;
To kill a specific process
KILL THREAD_ID;
Step 1: Create a database. For this use the below command to create a database named GeeksForGeeks.
Query:
CREATE DATABASE GeeksForGeeks;
Output:
Creating Database
Step 2: Use the GeeksForGeeks Database.
Query:
USE GeeksForGeeks;
Output:
Using GeeksForGeeks Database
Step 3: Create a table STUDENT inside the database GeeksForGeeks. This table has 4 columns ROLL_NO, NAME, BRANCH, SCORE.
Query:
CREATE TABLE STUDENT(ROLL_NO INT,NAME
VARCHAR(20),BRANCH VARCHAR(40),SCORE INT);
Output:
Creating STUDENT Table
Step 4: Insert records in the STUDENT table.
Query:
INSERT INTO STUDENT VALUES(1,'Atharva','Computer Engineering',10);
INSERT INTO STUDENT VALUES(2,'Virat','Computer Engineering',9);
INSERT INTO STUDENT VALUES(3,'Rohit','Information Technology',9);
INSERT INTO STUDENT VALUES(4,'Rahul','Information Technology',9);
INSERT INTO STUDENT VALUES(5,'Ram','Electronics and TeleCommunication',8);
Output:
Output after Inserting this values
Step 5: Display the STUDENT table.
Query:
SELECT * FROM STUDENT;
Output:
Displaying the data in the STUDENT table.
Step 6: To find the processes which we want to kill, we have to load all the active processes.
Query:
SHOW PROCESSLIST;
Output:
PROCESSLIST
You may have more entries in your list. Now note the ID of the process you want to kill. The time column in this table is useful to get to know which process is running for a long time. So, you can decide according to the time, which process you want to kill first.
Step 7: To kill a process in MySQL, use the KILL query with the THREAD_ID you noted in the previous query.
Query:
KILL 9;
Output:
Killing the process with ID 9
PROCESSLIST after Killing the process with ID 9
Here, we have stopped the execution of the process whose ID was 9. And you can see the output “Query execution was interrupted”.
And after killing the process you can see that if we again use the command SHOW PROCESSLIST, we can see different IDs for the process because earlier we terminated the process, and now the ID of this process has changed which means it is started again.
Step 8: If you want to kill all processes that are running. As there is no massive command kill command in MySQL, we can use “CONCAT” to produce kill commands from the PROCESSLIST table. You can do this using the following query:
Query:
SELECT GROUP_CONCAT(CONCAT
('KILL ',id,';') SEPARATOR ' ')
FROM information_schema.
processlist WHERE user
<> 'system user';
Output:
Displaying the current PROCESSLIST
Killing all the processes
Hence, by following the above steps, we can identify and kill queries using the SHOW PROCESSLIST and KILL commands respectively. And also we can kill all queries using the “CONCAT”.
Last Updated :
07 Jan, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...