Above( MySQL self protection tool --pt kill) It is mentioned that the PT kill tool is used to kill the relevant sessions to protect the database. This paper also blocks the long-running SQL by modifying the database parameters.

1. Parameter introduction

mysql5.6 and later, a new variable has been added in the database to limit the maximum execution time of the statement. It is used to limit the timeout of the select statement on the server. It can effectively control the slow query in the database (it is recommended to be in the main database) to achieve the purpose of protecting the stability of the database.

However, the parameter name has changed before and after mysql5.7.8, for example:

mysql5.6 - mysql5.7.8 In previous versions, the parameter name is: max_statement_time (MS) 

mysql5.7.8 And later, the parameter is changed to: max_execution_time (MS)

In addition, this parameter has two levels: global and session. You can dynamically adjust the timeout of this session in some sessions.

2. operation demonstration

Create a large table in the test environment for demonstration. This case can be used to view historical articles or mass produce data.

Since the current version is basically 5.7 or later, the MySQL version 5.7 database is used for demonstration this time.

2.1 parameter default value

mysql> show global variables like 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.01 sec)

The default value is 0, which means that the maximum execution time is not limited.

For example, when executing the following SQL, run 3s+

mysql> select  count(*) from  test1;
+----------+
| count(*) |
+----------+
| 21991575 |
+----------+
1 row in set (3.89 sec)

2.2 demonstration of modifying parameters

modify

mysql> set session   max_execution_time=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.01 sec)

mysql> show  variables like 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.00 sec)

The parameters (session level) of this session demonstrate the current session, which will not affect the operation of other sessions. If the entire instance needs to be adjusted, you can modify the global variables.

mysql> select  count(*) from  test1;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

At this time, query execution will be interrupted for more than 1s

PS: after modifying the global variable, note that the connected session is not effective. In addition, this parameter only works for select, but not for DDL, UPDATE and delete operations. For example:

1 2 3 4 5 6

mysql> set session   max_execution_time=1000; Query OK, 0 rows affected (0.00 sec)   mysql> alter table test1 add tt int ; Query OK, 0 rows affected (25.65 sec) Records: 0  Duplicates: 0  Warnings: 0

3. summary

The common situation in the production environment is that the business code has timed out and exited the interaction with the database, but the initiated SQL is still running in the database. If retries are initiated frequently, more and more slow SQL will occur, resulting in high database load and affecting the stability and availability. Therefore, it is recommended to deploy the PT kill tool or modify the maximum execution time parameter to avoid running the select statement for a long time.

Compared with the PT kill tool, the method of modifying parameters is more convenient, but has greater limitations. Therefore, it needs to be deployed according to business needs.