How to locate and kill bad MySQL queries?

How to locate and kill bad MySQL queries?

Sometimes, the complexity of relational database systems will confuse you, but fortunately, using MySQL tools to manage queries can avoid these complexities. In this tutorial, I will show you how to find and kill any illegal MySQL queries.

To view the currently running query, log on to the MySQL terminal and run the 'show processlist' command:

1. Mysql> show processlist;
3. + -------- + --------------- + --------- + ------- + ------------------ + ----------- + --------------- + ------------- +
4. | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
5. + -------- + --------------- + --------- + ------- + ------------------ + ----------- + --------------- + ------------- +
6. | 78233 | root | 127.0.0.1: 37527 | mysql | Sleep | 16474 | NULL | 6 | 6 | 6 |
7. | 84546 | root | 127.0.0.1: 48593 | mysql | Sleep | 13237 | NULL | 2 | 2 | 2 |
8. | 107083 | root | 127.0.0.1: 56451 | mysql | Sleep | 15488 | NULL | 1 | 121 | 121 |
9. | 131455 | root | 127.0.0.1: 48550 | NULL | Query | 0 | NULL | show processlist | 0 | 0 |
10. + -------- + --------------- + --------- + ------- + ------------------ + ----------- + --------------- + ------------- +
11. 4 rows inset (0.03 sec)

First, you should check the 'time' item. Here, the number of seconds for the process to perform the "do what it does" operation. A process with the 'command' entry in the 'sleep 'status indicates that it is waiting for query acceptance. Therefore, it does not consume any resources. For any other process, if the 'time' exceeds a certain number of seconds, the problem occurs.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

In the preceding example, the only query that runs is our 'show processlist' command. Let's take a look at what happens if we have a poorly written query:

1. Mysql> show processlist;
3. + -------- + --------------- + ----------- + --------- + ------- + -------------- + -------------------------------- + ----------- + --------------- + ------------- +
4. | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
5. + -------- + --------------- + ----------- + --------- + ------- + -------------- + -------------------------------- + ----------- + --------------- + ------------- +
6. | 78233 | root | 127.0.0.1: 37527 | example | Sleep | 18046 | NULL | 6 | 6 | 6 |
7. | 84546 | root | 127.0.0.1: 48593 | example | Sleep | 14809 | NULL | 2 | 2 | 2 |
8. | 107083 | root | 127.0.0.1: 56451 | example | Sleep | 17060 | NULL | 1 | 121 | 121 |
9. | 132033 | root | 127.0.0.1: 54642 | example | Query | 27 | Sending data | select max (subtotal) from orders | 0 | 0 | 0 |
10. | 133933 | root | 127.0.0.1: 48679 | NULL | Query | 0 | NULL | show processlist | 0 | 0 |
11. | 134122 | root | 127.0.0.1: 49264 | example | Sleep | 0 | NULL | 0 | 0 | 0 |
12. + -------- + --------------- + ----------- + --------- + ------- + -------------- + -------------------------------- + ----------- + --------------- + ------------- +
13. 6 rows inset (0.00 sec)

Aha! Now we can see that a query has been running for nearly 30 seconds. If we do not want its process to continue running, we can pass its 'id' to the kill command:

1. Mysql> kill 132033;
2. Query OK, 0 rows affected (0.00 sec)
3. Mysql>

(Note that because we have not changed any data, MySQL always reports 0 rows affected .)

Use the kill command wisely to clear the backlog of queries. However, remember that it is not a permanent method-if these queries come from your program, you need to rewrite them, or you will continue to see the same problem.

For more information, see

MySQL documentation for different 'COMMAND:

• Https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html

Via: http://xmodulo.com/2014/07/find-kill-misbehaving-mysql-queries.html

Translator: hunanchenxingyu Proofreader: wxy

This article was originally translated by LCTT and launched with the Linux honor in China

This article permanently updates the link address: