MariaDB Development
  1. MariaDB Development
  2. MDEV-4911

add KILL query id, and add query id information to processlist

    Details

    • Type: Task Task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Fix Version/s: 10.0.5
    • Labels:
      None
    • Global Rank:
      3481

      Description

      This MDEV comes from a sync problem about SHOW PROCESSLIST and KILL command, i will add two parts to this MDEV:


      FIRST PART:
      let me explain the problem:

      there's two connections (1) and (2) and they will execute some commands:

      1) SELECT * FROM A_BIG_TABLE_THAT_WILL_EXPEND_MANY_TIME
      2) SHOW PROCESSLIST /* WHAT HAPPENED WITH MARIADB? USERS ARE REPORTING SYSTEM LOCKED OR SLOW!? */

      THREAD ID COMMAND
      10 SELECT * FROM A BIG TABLE....
      11 SHOW PROCESSLIST

      1) BEGIN TRANSACTION
      2) KILL 10 /* WELL THE SELECT IS KILLING MY SERVER, I WILL KILL IT! */

      1) NOOOOOOOOOOO! MY BEGIN TRANSACTION WAS KILLED!
      2) NOOOOOOOOOOO! I SENT KILL COMMAND TO WRONG QUERY!

      =] removing the last two commands, the problem here is how to kill the right query, and not the right thread...

      well contacting Sergei Golubchik and maria-developer / maria-discussiong mail list, i think the best way is:

      1) add a new parameter to KILL command, the new syntax is:

      KILL [ CONNECTION [ QUERY [ ID ] ] | QUERY [ ID ] ] id 
      or (same thing) 
      KILL [ CONNECTION | CONNECTION QUERY [ ID ] | QUERY | QUERY ID ] id 
      

      examples:
      today KILL commands:

      KILL QUERY 1;  -> this will kill thread id 1, but don't disconnect client
      KILL 1; or KILL CONNECTION 1;  -> this will kill thread 1 and disconnect client
      

      new KILL commands:

      KILL QUERY ID 1; -> this will kill query id 1, but don't disconnect client
      KILL CONNECTION QUERY ID 1; KILL CONNECTION QUERY 1; -> this will kill query id 1, and disconnect client
      

      I was thinking about add a redundant command with thread id + query id, and asked to sergei...
      since query id is a big int (or a 32 bit int) we have low probability of kill a wrong query id, since the show processlist and the kill command normally take <1 second for scripts/programs, and take <10 seconds for a dba sql interface, and considering a very busy server (1.000.000 qps) we have many time to the query id be the same, for 32 bit overflow we have more than should have 2^32 (4.294.967.296) queries, more than 10 seconds (10.000.000 queries) with busy server...
      resuming... we WILL NOT add the redundant command (thread id + query id) just the query id command

      2) we WILL NOT change the protocol kill command
      (http://dev.mysql.com/doc/internals/en/com-process-kill.html#packet-COM_PROCESS_KILL), just change the SQL (COM_QUERY) KILL command

      that's all! good job =)


      SECOND PART: (done with diff_10_0_4_sql_show.cc file)
      add the QUERY ID column to processlist

      SELECT * FROM information_schema.PROCESSLIST

      ID QUERY_ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS
      2 10 rspadim 179.213.3.11:30165 information_schema Query 0 executing select * from processlist 0.240 0 0 0.000 83288 0


      important commend from mail list:

      Justin Swanhart
      greenlion@gmail.com
      Percona, Inc
      
      Hi,
      
      KILL THREAD THREAD_ID WITH QUERY_ID QUERY_ID
      and
      KILL QUERY THREAD_ID WITH QUERY_ID QUERY_ID
      and possibly
      KILL QUERY WITH QUERY_ID
      
      should be supported.  This is a very important and missing feature which is included in other forks.
      
      I_S.PROCESS_LIST should be extended to include QUERY_ID (you can get query_id from SHOW commands but not I_S).
      
      The above KILL commands if the QUERY_ID no longer exists on the system, thus you can KILL a SELECT without worrying that it has moved on to creating a new transaction or statement.
      


      from sql_class.h (10.0.4)

      ~ line 2304 - THD class
        /*
          Id of current query. Statement can be reused to execute several queries
          query_id is global in context of the whole MySQL server.
          ID is automatically generated from mutex-protected counter.
          It's used in handler code for various purposes: to check which columns
          from table are necessary for this select, to check if it's necessary to
          update auto-updatable fields (like auto_increment and timestamp).
        */
        query_id_t query_id;
      


      TODO:
      some things that must be done:​

      1) the KILL QUERY_ID 1,2,3,4,5 don't work, that's my first patch with sql_yacc.yy maybe i done something wrong since %expect changed +8 numbers

      2) the QUERY_ID isn't what i want but well worked hehehehe, maybe change the syntax and make it more beautiful

      3) there's some warnings in gcc that must be checked (signed compare with unsigned), and others case) that don't use the right flag (i add a bit = 16 for KILL_QUERY command)

      4) the KILL QUERY_ID <non exists query id>, return a error something like "thread id not found", must be "query id not found"

      5) i don't remember if have other problem...

      1. diff_10_0_4_mdev4911_lex.h
        0.4 kB
        roberto spadim
      2. diff_10_0_4_mdev4911_sql_class.h
        0.3 kB
        roberto spadim
      3. diff_10_0_4_mdev4911_sql_parse.cc
        4 kB
        roberto spadim
      4. diff_10_0_4_mdev4911_sql_yacc.yy
        1 kB
        roberto spadim
      5. diff_10_0_4_sql_show.cc
        5 kB
        roberto spadim
      6. mdev-4911.patch
        112 kB
        Sergey Vojtovich

        Issue Links

          Activity

          Hide
          roberto spadim added a comment -

          nice when possible i will test it
          one point that i didn't found on unit tests, did you tested the kill with many ids?
          example:
          kill query id 1,2,3,4,5

          Show
          roberto spadim added a comment - nice when possible i will test it one point that i didn't found on unit tests, did you tested the kill with many ids? example: kill query id 1,2,3,4,5
          Hide
          Sergey Vojtovich added a comment -

          Pushed to 10.0-base, revision-id: svoj@mariadb.org-20130913161456-g6z5rgwoczfsvdrj

          Show
          Sergey Vojtovich added a comment - Pushed to 10.0-base, revision-id: svoj@mariadb.org-20130913161456-g6z5rgwoczfsvdrj
          Hide
          roberto spadim added a comment -

          hi sergey patch read, just one point (considering that syntax is ok )

          4) the KILL QUERY_ID <non exists query id>, return a error something like "thread id not found", must be "query id not found"

          Show
          roberto spadim added a comment - hi sergey patch read, just one point (considering that syntax is ok ) 4) the KILL QUERY_ID <non exists query id>, return a error something like "thread id not found", must be "query id not found"
          Hide
          Sergey Vojtovich added a comment -

          Ehm, I was going to fix it, but missed somehow. Thanks for pointing it out.

          Show
          Sergey Vojtovich added a comment - Ehm, I was going to fix it, but missed somehow. Thanks for pointing it out.
          Hide
          roberto spadim added a comment -

          no problem

          Show
          roberto spadim added a comment - no problem

            People

            • Assignee:
              Sergey Vojtovich
              Reporter:
              roberto spadim
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 1 day
                1d
                Remaining:
                Time Spent - 30 minutes Remaining Estimate - 7 hours, 30 minutes
                7h 30m
                Logged:
                Time Spent - 30 minutes Remaining Estimate - 7 hours, 30 minutes
                30m