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)
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:
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 done, we could instead of KILL query, 'PAUSE' it
Reading mariadb docs, i found this, but it's not implemented ?!
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
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