Above( MySQL self protection tool -- Pt kill) It is mentioned that the PT kill tool is used to kill related sessions to protect the database. In this paper, the purpose of blocking long-running SQL is achieved by modifying database parameters.

1. Parameter introduction

mysql5.6 and later, a new variable is added on the database side to limit the maximum execution time of the statement, which is used to limit the timeout of the select statement on the server side, which can effectively control the slow query in the database (it is recommended to be in the main database), so as to protect the stability of the database.

But mysql5 The parameter name has changed before and after version 7.8, for example:

mysql5.6 - mysql5.7.8 In previous versions, the parameter name was: 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, which can dynamically adjust the timeout of this session in some sessions.

2. Operation demonstration

Build a large table in the test environment to demonstrate. The case can view historical articles or batch data implementation.

Since 5.7 and later versions are basically used at present, MySQL 5.0 is used this time 7 database.

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 the parameters (session level) of this session to demonstrate the situation of this session, which will not affect the operation of other sessions. If the whole instance needs to be adjusted, modify the global variable.

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)

At this time, the execution of the query will be interrupted because it exceeds 1s

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

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

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

A 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 retry is initiated frequently, more and more slow SQL will occur, resulting in high database load and affecting 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 Pt kill tool, the method of modifying parameters is more convenient, but has great limitations. Therefore, it needs to be deployed according to business needs.