Show and kill running processes in MySQL

The KILL command terminates a connection thread by ID along with the related active query, if there is one. Then, to identify queries for deletion, you need to see processes on the server - and the SHOW PROCESSLIST command will be a fine solution. It's not an elegant way to fix database issues, but rather an effective last resort tool. There are 4 major reasons for that:

  • If a long-running query holds other transactions from executing your more relevant query
  • If a large number of faulty queries block viable queries
  • If there are orphan processes after a client was disconnected from a server
  • 'Too many connections' message
None of these scenarios are great, so before executing KILL, make sure other solutions have been tried. But once you know the KILL method is necessary, you will have a few different options. But in this particular guide, we will focus on the more 'intuitive' way of showing and killing faulty queries using simple commands. You should keep in mind that KILL has two modifiers - CONNECTION and QUERY. KILL CONNECTION is essentially the same as KILL, while KILL QUERY terminates only the query for the specified connection ID and leaves the connection itself intact.

MySQL SHOW PROCESSLIST

To kill a query, we first need to track down the query that is slowing the performance - it's usually the one that takes the most time to run. For that measure, we need to look at the table that will show running MySQL queries which is done by the processlist command:

show full processlist;

The FULL modifier allows us to see the query text in its entirety instead of the first 100 symbols we would get without this modifier. In the id column, you will see the connection thread id of any currently running query - you can then use this id in the KILL command.

You can also retrieve detailed information about connections using the following queries:

SELECT * FROM information_schema.PROCESSLIST p;
SELECT * FROM performance_schema.threads t;
Show MySQL processlist in MySQL cmd

Kill command

So, after we locate, let's say, the most time-consuming query by reviewing the Time column, we execute the KILL command on the desired query:

KILL id;

Here, 'id' is the number of the query you need to terminate.

Kill command in MySQL cmd

Session Manager in dbForge Studio

We saw how to identify and kill queries using a regular MySQL console. Let's now do it in dbForge Studio for MySQL - custom programs were build to make database administration plain, visual, and simple. Let's say, we are using Amazon RDS SQL instance and run it as a Master user. So we run the same script in the admin query window (New SQL in the upper left corner) to get the MySQL process list:

show full processlist;

or simply open Database > Session Manager in the upper menu.

Also, the following commands work here:

  • Kill Query is an analogue to KILL QUERY
  • Kill Session is an analogue to KILL CONNECTION
  • Kill User Sessions kills all sessions of a user selected in the grid
Session Manager in dbForge Studio for MySQL

Showcasing why KILL is necessary

To showcase the query lifecycle from start to finish, we can create and then delete it inside the SQL document using commands. For that measure, we use MySQLslap and run auto-generated sql (auto-generate-sql) statements also specifying 50 concurrent connections (concurrency=50) for 10000 times (iterations=10000).

Creating a slow query in MySQLslap

Rogue query in Session Manager

After we switch back to Session Manager and refresh the screen, we can see the '28' process id we just create in MySQLslap. It is heavily loading the server and is not letting other users connect, while the existing queries are slowed down. So, let's get to KILLing it.

Rogue query in Session Manager in MySQL

Killing a query using dbForge Studio

So, to kill the query that is loading the server, you need to go back to the admin query window and run the following command:

KILL (28);

where 28 is the id of the query.

But if you're using Amazon RDS, you may see the "You are not owner of thread" message.
You are not owner message in MySQL

Privileges

Only admin is the true superuser, meanwhile master doesn't have the same root privilege. So unless you want to run the whole process in a non-managed MySQL instance, you should run the following command:

CALL mysql.rds_kill(28);

In the Output tab, we see that the previous attempt to kill the query using KILL command was hampered by the lack of the root privilege. But once we entered the mysql.rds_kill command, the action was successful.

The CONNECTION_ADMIN or SUPER privileges will allow you to kill queries no only for your connections, but also for foreign ones. The SYSTEM_USER privilege may also be needed in some cases. Similarly, the PROCESS privilege allows you to see foreign connections in addition to yours when using SHOW PROCESSLIST.

Successful query kill in MySQL

Checking results in Session Manager

After it's done, we can go to Session Manager and check the result by pressing Refresh. You will notice several new queries from routine workflow, but the rogue '28' query is no longer there.

Query removed from Session Manager in MySQL

Checking results in MySQLslap

At the same time, if we go to MySQLslap, you will see that connection to MySQL Server was lost during query. This means the query is gone after we executed a proper command.

Query removed from MySQLslap cmd

Conclusion

Session Manager is a great part of dbForge Studio for MySQL to make your database administration. In a user-friendly UI, you can track and handle processes via simple clicks rather than executing complex commands. Whether you spot a long-processing query or the 'too many connections' message, be sure that Session Manager will take care of the problem in a blink of an eye. It's convenient for those who are used to the regular cmd console and who prefer doing things via clicks.

dbForge Studio for MySQL

Cutting-edge MySQL IDE for database development and management

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Session Manager
Yes
Yes
Yes
Yes