[Эта статья была написана Мухаммедом Ирфаном]
Администраторы баз данных часто сталкиваются с ситуациями, когда им необходимо завершить запросы, чтобы убедиться, что на сервере MySQL нет длительных запросов, которые могли бы повлиять на производительность. Длительные запросы могут быть результатом многих факторов. К счастью, Percona Server содержит несколько удобных инструментов для удаления проблемных процессов MySQL. Я выделю все инструменты с помощью некоторых примеров в этом посте.
pt-kill:
В этом блоге было несколько хороших постов об инструменте pt-kill, например Арунджит Аравиндан под заголовком « Как набор запросов может быть убит в MySQL с помощью pt-kill Percona Toolkit» . Давайте еще немного углубимся в pt-kill с помощью еще нескольких примеров. Что делает pt-kill? Это убивает соединения MySQL. Скажем, вы хотели запустить pt-kill из cronjob, а затем получать по электронной почте каждый убитый процесс / запрос. Вот типичный пример для этого.
$ pt-kill --interval 1 --run-time 1 --busy-time 5 --log /path/to/kill_long_running_thread.log --match-info "^(select|SELECT|Select)" --kill --print --user=xxxxxx --password=xxxxxxxxxxAssume this is running from a cronjob, When pt-kill executes, it will kill queries longer than 5 seconds. By default, pt-kill runs forever –run-time option tells how long pt-kill to run before exiting If –interval and –busy-time parameters are used together then the explicit –interval value is used. Otherwise the default interval is 30 seconds. Note: this will only kill all read queries as per the –match-info parameter.
The above command will log all killed queries in the file referenced with the –log option. If you need to be notified via email for every killed query, the command below will do it. Off-course, you need to have the system configured to send e-mail.
#!/bin/bash
tail -n 0 -F /path/to/kill_long_running_thread.log | while read LOG
do
echo "$LOG" | mail -s "pt-kill alert" [email protected]
doneYou can execute this shell script in the background within screen or with the nohup utility continuously to monitor the log file. It will send an email whenever any new killed query arrives to the referenced log file in the pt-kill command. Unfortunately, there is no option to notify-by-email in pt-kill at the moment, so this is sort of a workaround.
In order to log all killed queries into a database table you will need to use the –log-dsn option as per the example below.
$ pt-kill --interval 1 --busy-time 1 --create-log-table --log-dsn=h=localhost,D=percona,t=kill_log --daemonize --match-info "^(select|SELECT|Select)" --killAll killed queries will be logged into percona.kill_log table. The –daemonize option will run this command in the background forever and will kill all SELECT queries running longer than 1 second (–busy-time 1). The –interval option instructs pt-kill to scan processes every 1 second (–interval 1).
mysql> select * from kill_log;
+---------+-----------+---------------------+-------------------------+------------+-------+------+-----------+------+---------+------+------------+-----------------+---------+
| kill_id | server_id | timestamp | reason | kill_error | Id | User | Host | db | Command | Time | State | Info | Time_ms |
+---------+-----------+---------------------+-------------------------+------------+-------+------+-----------+------+---------+------+------------+-----------------+---------+
| 17 | 1 | 2015-01-10 08:38:33 | Query matches Info spec | | 35146 | root | localhost | NULL | Query | 0 | User sleep | SELECT SLEEP(5) | NULL |
| 20 | 1 | 2015-01-10 08:38:34 | Query matches Info spec | | 35223 | root | localhost | NULL | Query | 0 | User sleep | SELECT SLEEP(5) | NULL |
+---------+-----------+---------------------+-------------------------+------------+-------+------+-----------+------+---------+------+------------+-----------------+---------+With the help of logging killed queries into a database tables. You can easily get all the trends/and /statistics on killed queries via SQL.
By default the tool kills the oldest of the queries that would have been running for more than a given –busy-time. If you need to kill all the threads that have been busy for more than a specified –busy-time, then this will do it:
$ pt-kill --victims=all --busy-time=60Statement Timeout in Percona Server:The max-statement-time feature is ported from the Twitter patches. This feature can be used to limit the query execution time by specifying the timeout value in the max_statement_time variable. When the specified number of milliseconds is reached the serveraborts the statement and returns the error below to the client.
ERROR 1877 (70101): Query execution was interrupted, max_statement_time exceededLet me demonstrate this through another example:
mysql [localhost] {msandbox} (world) > SET max_statement_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (world) > show variables like 'max_statement_time';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_statement_time | 1 |
+--------------------+-------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (world) > SELECT * FROM City WHERE District = 'abcd';
ERROR 1885 (70101): Query execution was interrupted, max_statement_time exceeded
mysql [localhost] {msandbox} (world) > UPDATE City SET District='abcd' WHERE ID = 2001;
ERROR 1885 (70101): Query execution was interrupted, max_statement_time exceeded
mysql [localhost] {msandbox} (world) > ALTER TABLE City ADD INDEX district_idx (district);
ERROR 1885 (70101): Query execution was interrupted, max_statement_time exceededAs you can see from this example statement, the timeout feature works for all statements including SELECT/DML/DDL queries.
mysql [localhost] {msandbox} (world) > show status like 'Max_statement%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Max_statement_time_exceeded | 3 |
| Max_statement_time_set | 19 |
| Max_statement_time_set_failed | 0 |
+-------------------------------+-------+
3 rows in set (0.00 sec)The above mentioned status variables are stats for a statement timeout feature.Max_statement_time_exceeded will inform you that the total number of statements exceeded the defined timeout. Max_statement_time_set defines the number of statements for which execution time limit was set. You can find more details in this documentation. The statement timeout feature was introduced in Percona Server 5.6. You can check if your specific version of Percona Server supports this feature or not via the have_statement_timeout variable.
mysql [localhost] {msandbox} (world) > show global variables like 'have_statement_timeout';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_statement_timeout | YES |
+------------------------+-------+
1 row in set (0.00 sec)Bugs you should be aware of:
https://bugs.launchpad.net/percona-server/+bug/1388533 -> This affects how the feature interacts with stored procedures. If you use stored procedures, max_statement_time might not behave as you expect.
https://bugs.launchpad.net/percona-server/+bug/1307432 -> This is documentation bug. Percona Server timeouts might not be safe for some statements like DDL and should not be used with such queries, The documentation does not reflect this. You should be very careful if you set a global statement timeout, It affects data changing queries as well. For best results set the max_statement_time variable in a session before running queries that you want to be killed if they execute too long, instead of using a global variable.
https://bugs.launchpad.net/percona-server/+bug/1376934 -> This affects the statement timeout feature on the query level. You must set max_statement_time in a session or globally instead however, this bug is fixed in latest version i.e. Percona Server 5.6.22-72.0
InnoDB Kill Idle Transactions:
This feature was introduced in Percona Server 5.5. It limits the age of idle XtraDB transactions and will kill idle transactions longer than a specified threshold for innodb_kill_idle_transaction. This feature is useful when autocommit is disabled on the server side and you are relying on the application to commit transactions and want to avoid long running transactions that are uncommitted. Application logic errors sometimes leaves transactions uncommitted. Let me demonstrate it quickly through one example:
mysql [localhost] {msandbox} (world) > show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
mysql [localhost] {msandbox} (world) > show global variables like 'innodb_kill_idle_transaction';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_kill_idle_transaction | 10 |
+------------------------------+-------+
mysql [localhost] {msandbox} (world) > START TRANSACTION; SELECT NOW(); INSERT INTO City_backup (Name,CountryCode,District,Population) VALUES ('Karachi','PK','Sindh','1000000');
Query OK, 0 rows affected (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2015-01-31 07:11:39 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (world) > SHOW ENGINE INNODB STATUSG
*************************** 1. row ***************************
------------
TRANSACTIONS
------------
---TRANSACTION 173076, ACTIVE 10 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 0x7f862e9bf700, query id 176 localhost msandbox init
SHOW ENGINE InnoDB STATUS
TABLE LOCK table `world`.`City_backup` trx id 173076 lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
============================
mysql [localhost] {msandbox} (world) > SELECT NOW(); SELECT * FROM City_backup;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 16
Current database: world
+---------------------+
| NOW() |
+---------------------+
| 2015-01-31 07:12:06 |
+---------------------+
1 row in set (0.01 sec)
Empty set (0.00 sec) Conclusion:
In this post, I shared some tools that can help you get rid of long-running transactions to help ensure that you don’t run into performance issues. This is one of the many good reasons to use Percona Server, which has some extra features on top of vanilla MySQL server.