CentrioHost 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
 123
45678910
11121314151617
18192021222324
25262728 

Over 20000 Satisfied Customers!

  • web hosting reviewer
    Valerie Quinn
    CTO, Acteon Group

    Centriohost staff were fantastic, I had a concern with a domain and they got back to me very quickly and they helped me to resolve the issue! ~ . . . Read more

  • Joomla hosting reviewer
    Collin Bryan
    Photographer, Allister Freeman

    I'm using centrio for my portfolio since 2006. The transition was seamless, the support was immediate, and everything works perfectly. ~ . . . Read more

  • dedicated server reviewer
    Harry Collett
    Actor, A&J Artists

    Very easy to understand & use even though I am not very technologically minded. No complications whatsoever & I wouldn't hesitate to recommend it to all. ~ . . . Read more

  • vps web hosting reviewer
    Porfirio Santos
    Technician, Diageo PLC

    Centrio support team have been amazingly responsive and helpful to any of my queries, thank you so much to the Centriohost have been amazingly responsive and helpful to any of my queries ๐Ÿ‘๐Ÿ‘๐Ÿ‘ ~ . . . Read more

  • wordpress hosting plans reviewer
    Catherine Auer
    Doctor, SmartClinics

    Anytime I've had a problem I can't solve, I've found Centriohost to be diligent and persistent. They simply won't let an issue go until the client is happy. ~ . . . Read more

  • reseller hosting reviewer
    Effectivo Social
    Freelancer, Fiverr

    Recommend their shared hosting for all my SME web design clients. Their cloud or VME offerings are too great to deal with. Pricing is perfect and suitable for all users (อ โ‰– อœส–อ โ‰–) ๐Ÿ‘Œ ~ . . . Read more

Top