MySQL 5.6中的全局查询超时 [英] Global query timeout in MySQL 5.6

问题描述

我需要在我的应用程序中全局应用查询超时.查询:SET SESSION max_execution_time=1使用MySQL 5.7完成此操作.我正在使用MySQL 5.6，目前无法升级.使用SQL Alchemy的任何解决方案也将有所帮助.

解决方案

存储查询进程列表的过程并根据需要技能.这样的存储过程可能看起来像:

 DELIMITER //
CREATE PROCEDURE stmt_timeout_killer (timeout INT)
BEGIN
    DECLARE query_id INT;
    DECLARE done INT DEFAULT FALSE;

    DECLARE curs CURSOR FOR
    SELECT id
    FROM information_schema.processlist
    WHERE command = 'Query' AND time >= timeout;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Ignore ER_NO_SUCH_THREAD, in case the query finished between
    -- checking the process list and actually killing threads
    DECLARE CONTINUE HANDLER FOR 1094 BEGIN END;

    OPEN curs;

    read_loop: LOOP
        FETCH curs INTO query_id;

        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Prevent suicide
        IF query_id != CONNECTION_ID() THEN
            KILL QUERY query_id;
        END IF;
    END LOOP;

    CLOSE curs;
END//
DELIMITER ;
 

或者，您可以在应用程序逻辑中实现所有这些功能，但是对于每个查询，要杀死每个查询，都需要分别往返数据库.然后剩下的就是定期调用它:

# Somewhere suitable
engine.execute(text("CALL stmt_timeout_killer(:timeout)"), timeout=30)

如何以及在何处完全取决于您的实际应用.

I need to apply a query timeout at a global level in my application. The query: SET SESSION max_execution_time=1 does this with MySQL 5.7. I am using MySQL 5.6 and cannot upgrade at the moment. Any solution with SQL Alchemy would also help.

解决方案

It seems there is no equivalent to max_execution_time in MySQL prior to versions 5.7.4 and 5.7.8 (the setting changed its name). What you can do is create your own periodic job that checks if queries have exceeded timeout and manually kill them. Unfortunately that is not quite the same as what the newer MySQL versions do: without inspecting the command info you'll end up killing all queries, not just read only SELECT, and it is nigh impossible to control at session level.

One way to do that would be to create a stored procedure that queries the process list and kills as required. Such stored procedure could look like:

DELIMITER //
CREATE PROCEDURE stmt_timeout_killer (timeout INT)
BEGIN
    DECLARE query_id INT;
    DECLARE done INT DEFAULT FALSE;

    DECLARE curs CURSOR FOR
    SELECT id
    FROM information_schema.processlist
    WHERE command = 'Query' AND time >= timeout;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Ignore ER_NO_SUCH_THREAD, in case the query finished between
    -- checking the process list and actually killing threads
    DECLARE CONTINUE HANDLER FOR 1094 BEGIN END;

    OPEN curs;

    read_loop: LOOP
        FETCH curs INTO query_id;

        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Prevent suicide
        IF query_id != CONNECTION_ID() THEN
            KILL QUERY query_id;
        END IF;
    END LOOP;

    CLOSE curs;
END//
DELIMITER ;

Alternatively you could implement all that in your application logic, but it would require separate round trips to the database for each query to be killed. What's left then is to call this periodically:

# Somewhere suitable
engine.execute(text("CALL stmt_timeout_killer(:timeout)"), timeout=30)

How and where exactly depends heavily on your actual application.

这篇关于MySQL 5.6中的全局查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！