Restrict the "sending data" time for MySQL

I have a very small server (less than 1GB of memory) on which I am running MySQL and apache. There exists a php script that runs a MySQl query and shows the data. I modified the query in a way that the results returned would be millions of rows. While the query itself is not super complex, given my server's limited resources, it takes MySQL a long time to deliver the data back to PHP.

Despite setting max_execution_time to 30 in php.ini and setting both wait_timeout and interactive_time to 30 in my.cnf, the server still allows the scrip to be stuck for several minutes. When I run show full processlist on MySQL I see that the query is in "Sending data" stage.

My question: how can I restrict the duration of the "Sending data" phase?

1 answer

  • answered 2019-09-22 00:31 Maciej Król

    You can do this with PDO using PDO::MYSQL_ATTR_INIT_COMMAND and MAX_EXECUTION_TIME for MySQL 5.7+

    Working example

    $pdo = new \PDO('mysql:the_rest_of_dns;', 'usrname', 'passwd', [
        // Set time in miliseconds
        \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION MAX_EXECUTION_TIME=200;'
    ]);
    
    $result = $pdo->query("SELECT * FROM answers");
    
    /* 
     Catch mysql exception
    */
    if(($error = $pdo->errorInfo())[0] !== "00000") {
    
        /*
         Query execution was interrupted, maximum statement execution time exceeded
         exception exception handler
         */
        if($error[1] === 3024) {
            // Die with default error message
            die($error[2]);
        }
    }
    
    // If everything working fine, get the result
    print_r($result->fetchAll());
    

    One important note

    The timeouts only apply to read-only SELECT queries.

    If you don't want to set timeout on all queries, you can just type-hint that using:

    SELECT /*+ MAX_EXECUTION_TIME(1000) */ status, count(*) FROM articles GROUP BY status ORDER BY status;
    

    The second example was taken from here.