Open In App

Identify and Kill Queries with MySQL Command

Improve
Improve
Like Article
Like
Save
Share
Report

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
Previous
Next
Share your thoughts in the comments
Similar Reads