How to set a maximum execution time for a mysql query

Question

I would like to set a maximum execution time for sql queries like set time limit   in php  How can I do

User · Answer

pt kill has an option for such   But it is on-demand  not continually monitoring   It does what  Rafa suggested   However see --sentinel for a hint of how to come close with cron

User · Answer

Please rewrite your query like  select     MAX EXECUTION TIME 1000       from table   this statement will kill your query after the specified time

User · Answer

You can find the answer on this other S O  question  MySQL - can I limit the maximum time allowed for a query to run   a cron job that runs every second on your database server  connecting and doing something like this   SHOW PROCESSLIST Find all connections with a query time larger than your maximum desired time Run KILL  process id  for each of those processes

User · Answer

If you re using the mysql native driver  common since php 5 3   and the mysqli extension  you can accomplish this with an asynchronous query    lt  php     Here s an example query that will take a long time to execute   sql         select       from information schema tables t1     join information schema tables t2     join information schema tables t3     join information schema tables t4     join information schema tables t5     join information schema tables t6     join information schema tables t7     join information schema tables t8      mysqli   mysqli connect  localhost    root         mysqli- gt query  sql  MYSQLI ASYNC   MYSQLI USE RESULT    links    errors    reject        links      mysqli      wait up to 1 5 seconds  seconds   1   microseconds   500000    timeStart   microtime true    if  mysqli poll  links   errors   reject   seconds   microseconds   gt  0        echo  query finished executing  now we start fetching the data rows over the network    n        result    mysqli- gt reap async query        if   result            while   row    result- gt fetch row                     print r  row               if  microtime true  -  timeStart  gt  1 5                       we exceeded our time limit in the middle of fetching our result set                  echo  timed out while fetching results n                   var dump  mysqli- gt close                     break                                  else       echo  timed out while waiting for query to execute n       var dump  mysqli- gt close         The flags I m giving to mysqli query accomplish important things  It tells the client driver to enable asynchronous mode  while forces us to use more verbose code  but lets us use a timeout and also issue concurrent queries if you want    The other flag tells the client not to buffer the entire result set into memory    By default  php configures its mysql client libraries to fetch the entire result set of your query into memory before it lets your php code start accessing rows in the result  This can take a long time to transfer a large result  We disable it  otherwise we risk that we might time out while waiting for the buffering to complete   Note that there s two places where we need to check for exceeding a time limit       The actual query execution while fetching the results data    You can accomplish similar in the PDO and regular mysql extension  They don t support asynchronous queries  so you can t set a timeout on the query execution time  However  they do support unbuffered result sets  and so you can at least implement a timeout on the fetching of the data   For many queries  mysql is able to start streaming the results to you almost immediately  and so unbuffered queries alone will allow you to somewhat effectively implement timeouts on certain queries  For example  a   select   from tbl with 1billion rows   can start streaming rows right away  but    select sum foo  from tbl with 1billion rows   needs to process the entire table before it can start returning the first row to you  This latter case is where the timeout on an asynchronous query will save you  It will also save you from plain old deadlocks and other stuff   ps - I didn t include any timeout logic on the connection itself

User · Answer

pt kill has an option for such   But it is on-demand  not continually monitoring   It does what  Rafa suggested   However see --sentinel for a hint of how to come close with cron

User · Answer

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   SELECT      MAX EXECUTION TIME 1000     --in milliseconds    FROM table   Note that this only works for read-only SELECT statements  Update  This variable was added in MySQL 5 7 4 and renamed to max execution time in MySQL 5 7 8   source

User · Answer

Please rewrite your query like  select     MAX EXECUTION TIME 1000       from table   this statement will kill your query after the specified time

User · Answer

You can find the answer on this other S O  question  MySQL - can I limit the maximum time allowed for a query to run   a cron job that runs every second on your database server  connecting and doing something like this   SHOW PROCESSLIST Find all connections with a query time larger than your maximum desired time Run KILL  process id  for each of those processes

User · Answer

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   SELECT      MAX EXECUTION TIME 1000     --in milliseconds    FROM table   Note that this only works for read-only SELECT statements  Update  This variable was added in MySQL 5 7 4 and renamed to max execution time in MySQL 5 7 8   source

User · Answer

If you re using the mysql native driver  common since php 5 3   and the mysqli extension  you can accomplish this with an asynchronous query    lt  php     Here s an example query that will take a long time to execute   sql         select       from information schema tables t1     join information schema tables t2     join information schema tables t3     join information schema tables t4     join information schema tables t5     join information schema tables t6     join information schema tables t7     join information schema tables t8      mysqli   mysqli connect  localhost    root         mysqli- gt query  sql  MYSQLI ASYNC   MYSQLI USE RESULT    links    errors    reject        links      mysqli      wait up to 1 5 seconds  seconds   1   microseconds   500000    timeStart   microtime true    if  mysqli poll  links   errors   reject   seconds   microseconds   gt  0        echo  query finished executing  now we start fetching the data rows over the network    n        result    mysqli- gt reap async query        if   result            while   row    result- gt fetch row                     print r  row               if  microtime true  -  timeStart  gt  1 5                       we exceeded our time limit in the middle of fetching our result set                  echo  timed out while fetching results n                   var dump  mysqli- gt close                     break                                  else       echo  timed out while waiting for query to execute n       var dump  mysqli- gt close         The flags I m giving to mysqli query accomplish important things  It tells the client driver to enable asynchronous mode  while forces us to use more verbose code  but lets us use a timeout and also issue concurrent queries if you want    The other flag tells the client not to buffer the entire result set into memory    By default  php configures its mysql client libraries to fetch the entire result set of your query into memory before it lets your php code start accessing rows in the result  This can take a long time to transfer a large result  We disable it  otherwise we risk that we might time out while waiting for the buffering to complete   Note that there s two places where we need to check for exceeding a time limit       The actual query execution while fetching the results data    You can accomplish similar in the PDO and regular mysql extension  They don t support asynchronous queries  so you can t set a timeout on the query execution time  However  they do support unbuffered result sets  and so you can at least implement a timeout on the fetching of the data   For many queries  mysql is able to start streaming the results to you almost immediately  and so unbuffered queries alone will allow you to somewhat effectively implement timeouts on certain queries  For example  a   select   from tbl with 1billion rows   can start streaming rows right away  but    select sum foo  from tbl with 1billion rows   needs to process the entire table before it can start returning the first row to you  This latter case is where the timeout on an asynchronous query will save you  It will also save you from plain old deadlocks and other stuff   ps - I didn t include any timeout logic on the connection itself

[mysql] How to set a maximum execution time for a mysql query?

Examples related to mysql

Examples related to sql

Examples related to time

Examples related to limit