XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.1
    • OTHER
    • None

    Description

      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 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

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              rspadim roberto spadim
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.