HostSEO Blog

Stories and News from IT Industry, Reviews & Tips | Technology Blog


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;

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.

Subscribe Now

10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!

Archive Calendar

SatSunMonTueWedThuFri
 12
3456789
10111213141516
17181920212223
24252627282930

Born in 2004 ... Trusted By Clients n' Experts

SEO Stars

They never made me feel silly for asking questions. Help me understand how to attract more people and improve my search engine ranking.

Read More

Emily Schneller Manager at Sabre Inc
SEO Stars

Took advantage of Hostseo's superb tech support and I must say, it is a very perfect one. It is very fast, servers reliability is incredible.

Read More

Leena Mäkinen Creative producer
SEO Stars

We're operating a worldwide network of servers with high quality standards requirements, we’ve choose hostseo to be our perfect partner.

Read More

Ziff Davis CEO at Mashable
SEO Stars

It’s very comfortable to know I can rely about all technical issues on Hostseo and mostly that my website and emails are safe and secured here.

Read More

Isaac H. Entrepreneur
SEO Stars

With hostseo as a hosting partner we are more flexible and save money due to the better packages with great pricing, free SEO n' free SSL too!

Read More

Madeline E. Internet Professional