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

            should i open another MDEV just to explain the "PAUSE" and track?

            rspadim roberto spadim added a comment - should i open another MDEV just to explain the "PAUSE" and track?

            i'm reading patch code , there's a timeout_hard, is it being used or any plan about it? or it's something about killing mysqld and the thread have a kill_timeout condition and change it to timeout_hard? didn't found where it is used

            + KILL_TIMEOUT= 8,
            + KILL_TIMEOUT_HARD= 9,

            rspadim roberto spadim added a comment - i'm reading patch code , there's a timeout_hard, is it being used or any plan about it? or it's something about killing mysqld and the thread have a kill_timeout condition and change it to timeout_hard? didn't found where it is used + KILL_TIMEOUT= 8, + KILL_TIMEOUT_HARD= 9,

            other doubt, i didn't understood reading the source code

            how timeout thread know if we are timedout or not
            for example...
            1) we have a global thread that check each connection and send signals about timeout or not
            2) each connection with max timeout variable create a second thread to check timeout (not a global thread, but a per query thread)

            rspadim roberto spadim added a comment - other doubt, i didn't understood reading the source code how timeout thread know if we are timedout or not for example... 1) we have a global thread that check each connection and send signals about timeout or not 2) each connection with max timeout variable create a second thread to check timeout (not a global thread, but a per query thread)

            Yes, please create another mdev for new features.

            The HARD option is an addition to MariaDB. You can use HARD as an option for the KILL command. It means that the query should be killed as soon as possible, even if it would leave the table in an inconsistent state.
            See https://mariadb.com/kb/en/data-manipulation-kill-connection-query/ for details.

            It's 1)
            There is one thread created to handle timeout for all other threads. This works by doing a pthread_cond_timedwait() for the next timeout.
            See mysys/thr_timer.c for details of how this is implemented.

            monty Michael Widenius added a comment - Yes, please create another mdev for new features. The HARD option is an addition to MariaDB. You can use HARD as an option for the KILL command. It means that the query should be killed as soon as possible, even if it would leave the table in an inconsistent state. See https://mariadb.com/kb/en/data-manipulation-kill-connection-query/ for details. It's 1) There is one thread created to handle timeout for all other threads. This works by doing a pthread_cond_timedwait() for the next timeout. See mysys/thr_timer.c for details of how this is implemented.

            nice =] created

            about the hard, it's not being used yet by the timeout right? i'm considering that the current design could be the 'hard timeout' and we could create a soft timeout, the soft when the process receive the kill_timeout it will check if the current thread is or isn't blocking others process, i don't know the best method to do this but i think at the thread running is easier than a external process, since we alread have a lock about the current process and we just need to check mdl (at a first version) + storage level (partition/page/row) locks (in a second version), just to think about an roadmap about how to implmeemnt the "soft" timeout

            rspadim roberto spadim added a comment - nice =] created about the hard, it's not being used yet by the timeout right? i'm considering that the current design could be the 'hard timeout' and we could create a soft timeout, the soft when the process receive the kill_timeout it will check if the current thread is or isn't blocking others process, i don't know the best method to do this but i think at the thread running is easier than a external process, since we alread have a lock about the current process and we just need to check mdl (at a first version) + storage level (partition/page/row) locks (in a second version), just to think about an roadmap about how to implmeemnt the "soft" timeout

            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.