Sometimes MySQL queries take a very long time and slow the database processing. We can find and kill these stuck processes.
Table of Contents
MySQL Kill Process Steps
MySQL Kill Process involves running two commands.
- Find the running processes using
show processlist
command. - Kill the process using
kill ID
command.
1. Find the Stuck MySQL Process
I am using root
user to show processes for all the users. If you are using a database-specific user, you will get the results for that database only.
MariaDB [(none)]> show processlist; +--------+------------+-----------+------------+---------+------+---------------------------------+-----------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+------------+-----------+------------+---------+------+---------------------------------+-----------------------------+----------+ | 566697 | jour_wp | localhost | jour_wp | Query | 1130 | Waiting for table metadata lock | OPTIMIZE TABLE wp_comments | 0.000 | | 566698 | jour_wp | localhost | jour_wp | Query | 1130 | Waiting for table level lock | OPTIMIZE TABLE wp_comments | 0.000 |
It’s clear from the output that these processes are stuck for more than 1000 seconds.
The Query shows only 100 characters of the Query. If that’s not sufficient then use the SHOW FULL PROCESSLIST
command to get the complete query information.
2. Kill MySQL Process by ID
The show processlist
command shows the MySQL process IDs. We can run the kill ID
command to terminate them.
MariaDB [(none)]> kill 566697; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> kill 566698; Query OK, 0 rows affected (0.00 sec)
We can check the process list again to make sure all the stuck processes are cleared.