We're updating the issue view to help you get more done.Learn more

query timeouts

Hi guys, maybe this could be done easly (maybe in GSoC)
Some queries when executed in tables with high intensive SELECT/INSERT/UPDATE/DELETE, should be executed in a max time, or it start to lock the server

twitter/mysql have a similar feature, after some pre defined time, the query is canceled by server (KILL the query in a background thread)

example:
1)SELECT /* MAX_QUERY_TIME = 10 */ * FROM SOME_BIG_TABLE
2)UPDATE SOME_BIG_TABLE SET VALUE='SOME_VALUE' WHERE PRIMARY_KEY='SOME VALUE'

the UPDATE will be blocked by SELECT while it's executing... the point is, kill (1) after 10 seconds if there's a blocked query trying to use this table, in this case the query (2)

this help a lot myisam tables (with table lock only method, no page and no row lock levels) with poor performace of select+update+delete+insert

here some information about twitter mysql implementation:
https://github.com/twitter/mysql/wiki/Statement-Timeout

i prefer a float value based in seconds (instead milliseconds), i didn't found information to set it off, maybe set max_statement_time=0


after MDEV-4623 Open done, we could instead of KILL query, 'PAUSE' it

Reading mariadb docs, i found this, but it's not implemented ?!

https://kb.askmonty.org/en/how-to-limittimeout-queries/

MAX_QUERY_TIME variable

You can limit the time any query is executed by setting the MAX_QUERY_TIME variable. The variable takes the number of seconds (with microsecond precision) the query should be executed. If the query is run more than the given timeout, it will be killed as by a KILL SOFT command.
SET @@SESSION.MAX_QUERY_TIME=0.5; # Abort if query takes more than 1/2 second
SELECT SLEEP(100);
max_query_time can also be given as an argument to mysqld at startup. This sets the GLOBAL variable that is used as default for all new connections.
You can check how many times timeouts has expired by examining the status variable max_query_timeouts:
show global status like 'max_query_timeouts';
This feature is scheduled for MariaDB 5.5.29

Status

Assignee

Michael Widenius

Reporter

Roberto Spadim