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:
Step 2: Use the GeeksForGeeks Database.
Query:
USE GeeksForGeeks;
Output:
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:
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:
Step 5: Display the STUDENT table.
Query:
SELECT * FROM STUDENT;
Output:
Step 6: To find the processes which we want to kill, we have to load all the active processes.
Query:
SHOW PROCESSLIST;
Output:
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:
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:
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”.