CentrioHost Blog

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;

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.