Terminal Terminal | Web Web
Home  //  Play

KILL and SHOW QUERIES commands

Difficulty: Beginner
Estimated Time: 5 minutes

Manticoresearch - KILL and SHOW QUERIES commands

In this tutorial you will learn how to use 'KILL' and 'SHOW QUERIES' commands to stop query execution.

Note that if you install Manticore manually you'll need to install the 'manticore-extra' package to enable the functionality described in this course.

KILL and SHOW QUERIES commands

Step 1 of 2

Introduction

Sometimes you may want to stop the execution of a slow query. To do this with regard to SELECT queries, you can use KILL and SHOW QUERIES commands.

KILL terminates the execution of a SELECT query by its id which you can find in SHOW QUERIES:

KILL <query id>

SHOW QUERIES returns information about all the queries running now. It outputs a table with the following structure:

  • id: query id which can be used in KILL to terminate the query
  • query: query statement or a part of it
  • protocol: connection protocol, the possible values are sphinx , mysql , http , ssl , compressed and replication or combination (e.g. http,ssl or compressed,mysql )
  • host: ip:port of the client

An example of usage

Let's run a slow SELECT query on it ( The query is intentionally unrealistic to make Manticore process it for a considerable period of time):

mysql -P9306 -h0 -e "SELECT * FROM t WHERE MATCH('a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z');"

Now we'll run the query again, but this time we'll use SHOW QUERIES and KILL to stop it.

To start processing the query in the background, run the following command:

mysql -P9306 -h0 -e "SELECT * FROM t WHERE MATCH('a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|x|y|z');" &>> /dev/null &

Then run SHOW QUERIES to get the query's id:

mysql -P9306 -h0 -e "SHOW QUERIES"

Now let's retrieve the id from the output and run the KILL command with it:

query_id=$(mysql -P9306 -h0 -e "SHOW QUERIES" | grep 'SELECT' | awk '{print $2}') && echo $query_id

mysql -P9306 -h0 -e "KILL $query_id"

Lastly, let's run SHOW QUERIES again to make sure it's done:

mysql -P9306 -h0 -e "SHOW QUERIES"

As you see, the slow query has been successfully terminated.