如果MySQL查询花费的时间太长，如何停止?

Is it possible to timeout a query in MySQL?

是否可以在MySQL中超时查询?

That is, if any query exceeds the time I specify, it will be killed by MySQL and it will return an error instead of waiting for eternity.

也就是说，如果任何查询超过我指定的时间，它将被MySQL终止，它将返回一个错误，而不是等待永恒。

10 个解决方案

#1

There is a nice Perl script on CPAN to do just this: http://search.cpan.org/~rsoliv/mysql-genocide-0.03/mysql-genocide

在CPAN上有一个很好的Perl脚本可以做到这一点:http://search.cpan.org/~rsoliv/mysql-genocide-0.03/mysql-种族灭绝。

One only needs to schedule it to run with the proper parameters. Create a CRONtab file /etc/cron.d/mysql_query_timeout to schedule it to run every minute:

我们只需要将它安排为使用适当的参数运行。创建CRONtab文件/etc/ cron。d/mysql_query_timeout调度它每分钟运行一次:

* * * * * root /path/to/mysql-genocide -t 7200 -s -K

* * * * root /path/to/ myl种族灭绝- t7200 -s -K

Where 7200 is the maxiumum allowed execution time in seconds. The -s switch filters out all except SELECT queries. The -K switch instructs the script to kill the matching processes.

其中7200是最大允许执行时间的秒数。除了SELECT查询之外，-s开关将过滤掉所有查询。k开关指示脚本终止匹配进程。

The root user should be able to run local mysql tools without authentication otherwise you will need to provide credentials on the command line.

根用户应该能够在不进行身份验证的情况下运行本地mysql工具，否则需要在命令行上提供凭据。

#2

I just set up the following bash script as a cron job to accomplish this with MySQL 5.0 (kills any query that has been executing for more than 30 seconds). Sharing it here in case it proves useful to anyone (apologies if my bash scripting style is inefficient or atrocious, it is not my primary development language):

我只是将下面的bash脚本设置为cron作业，以便使用MySQL 5.0实现这一点(删除已执行30秒以上的任何查询)。在这里分享它，以防它对任何人都有用(如果我的bash脚本风格效率低下或糟糕，它不是我的主要开发语言，请道歉):

#!/bin/bash
linecount=0
processes=$(echo "show processlist" | mysql -uroot -ppassword)
oldIfs=$IFS
IFS='
'
echo "Checking for slow MySQL queries..."
for line in $processes
do
    if [ "$linecount" -gt 0 ]
        then
            pid=$(echo "$line" | cut -f1)
            length=$(echo "$line" | cut -f6)
            query=$(echo "$line" | cut -f8)
            #Id User    Host    db  Command Time    State   Info
            if [ "$length" -gt 30 ]
                then
                    #echo "$pid = $length"
                    echo "WARNING:  Killing query with pid=$pid with total execution time of $length seconds! (query=$query)"
                    killoutput=$(echo "kill query $pid" | mysql -uroot -ppassword)
                    echo "Result of killing $pid:  $killoutput"
            fi
    fi
    linecount=`expr $linecount + 1`
done
IFS=$oldIfs

#3

Starting with MySQL 5.1 you can create a stored procedure to query the information_schmea.PROCESSLIST table for all queries that match your criteria for "long running" then iterate over a cursor to kill them. Then setup that procedure to execute on a recurring basis in the event scheduler.

从MySQL 5.1开始，可以创建一个存储过程来查询information_schmea。对于所有匹配“长时间运行”条件的查询，然后遍历游标以杀死它们。然后设置该过程以在事件调度器中重复执行。

See: http://forge.mysql.com/tools/tool.php?id=106

参见:http://forge.mysql.com/tools/tool.php?id=106

#4

I thought it has been around a little longer, but according to this,

我以为它已经存在了一段时间，但是根据这个，

MySQL 5.7.4 introduces the ability to set server side execution time limits, specified in milliseconds, for top level read-only SELECT statements.

MySQL 5.7.4引入了为*只读选择语句设置服务器端执行时间限制(以毫秒为单位)的功能。

SELECT 
MAX_STATEMENT_TIME = 1000 --in milliseconds
* 
FROM table;

Note that this only works for read-only SELECT statements.

注意，这只适用于只读选择语句。

#5

The MySQL forum has some threads about this.

MySQL论坛对此有一些线程。

This post details how to set up timeouts on the server using innodb_lock_wait_timeout.

本文详细介绍了如何使用innodb_lock_wait_timeout在服务器上设置超时。

Here's a way to do it programmatically, assuming you're using JDBC.

这里有一种编程方式，假设您正在使用JDBC。

#6

I don't think the egrep above would find "2000".
Why not try just selecting the id as well, and avoiding all of that posh shell stuff:

我不认为上面的白鹭会找到“2000”。为什么不试着选择id，同时避免那些时髦的外壳材料:

mysql -e 'select id from information_schema.processlist where info is not null and time > 30;'

#7

Here is my script :

这是我的脚本:

mysql -e 'show processlist\G' |\
egrep -b5 'Time: [6-9]{3,10}' |\
grep 'Id:' |\
cut -d':' -f2 |\
grep -v '155' |\ ## Binary Log PID
sed 's/^ //' |\
while read id
do
    mysql -e "kill $id;"
done

#8

Since MySQL 5.7.8 there is max_execution_time option that defines the execution timeout for SELECT statements.

因为MySQL 5.7.8中有max_execution_time选项，它定义了SELECT语句的执行超时。

#9

I think this old question needs an updated answer.

我认为这个老问题需要一个更新的答案。

You can set a GLOBAL timeout for all your read-only SELECT queries like this:

您可以为所有只读选择查询设置一个全局超时:

SET GLOBAL MAX_EXECUTION_TIME=1000;

集全球MAX_EXECUTION_TIME = 1000;

The time specified is in milliseconds.

指定的时间以毫秒为单位。

If you want the timeout only for a specific query, you can set it inline like this:

如果您只想要特定查询的超时，您可以将其设置为如下所示:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ my_column FROM my_table WHERE ...

从my_table中选择/*+ MAX_EXECUTION_TIME(1000) */ my_column，其中…

MySQL returns an error instead of waiting for eternity.

MySQL返回一个错误，而不是等待永恒。

Note that this method only works for read-only SELECTs. If a SELECT statement is determined not to be read-only, then any timer set for it is cancelled and the following NOTE message is reported to the user:

注意，此方法仅适用于只读选择。如果一个SELECT语句被确定不是只读的，那么它的任何定时器都被取消了，并且向用户报告了以下注意事项:

Note 1908 Select is not a read only statement, disabling timer

注意1908年的Select不是只读语句，禁用计时器。

For statements with subqueries, it limits the top SELECT only. It does not apply to SELECT statements within stored programs. Using the MAX_EXECUTION_TIME hint in SELECT statements within a stored program will be ignored.

对于带有子查询的语句，它只限制top SELECT。它不适用于在存储程序中选择语句。在存储程序中的SELECT语句中使用MAX_EXECUTION_TIME提示将被忽略。

#10

To kill queries based on their thread_id try the query and kill them manually,

若要基于它们的thread_id来杀死查询，请手动执行查询并将其杀死，

SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query' AND TIME > 10;

从INFORMATION_SCHEMA选择ID。其中命令= 'Query'和时间> 10;

An alternative and interactive approach would be to output the values to a file using the query,

另一种可选的交互式方法是使用查询将值输出到文件中，

SELECT CONCAT('KILL ',ID,';')
FROM   INFORMATION_SCHEMA.PROCESSLIST
WHERE  USER = ‘new’
   AND COMMAND = 'Query'
   AND TIME > 10
  INTO   OUTFILE '/tmp/thread_kill.txt';

and then kill the threads in the file using,

然后用，

 \. /tmp/thread_kill.txt

Another alternative is to look for monitoring tools like MONyog, MySQL Enterprise Monitor, Percona toolkit where you can let the tool to look for the longer/slow queries, locking queries and kill them. With MONyog the added advantage is the slow queries can not just be killed but also inform the user by email regarding which query held up the resources and made the server slow-down.

另一种选择是寻找监视工具，如MONyog、MySQL Enterprise Monitor、Percona工具包，在那里您可以让工具查找较长/慢的查询，锁定查询并终止它们。使用MONyog的另一个好处是，慢速查询不仅可以被终止，还可以通过电子邮件通知用户哪个查询占用了资源并使服务器慢下来。

#1