Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-3951

GLOBAL STATUS variables drift after rollback

    XMLWordPrintable

    Details

      Description

      Hi,

      all global status variables are counters, values that only ever goes up, however, when executing SHOW GLOBAL STATUS it is possible to hit a data drift.

      An example, for Handler_rollback:

      The first execution of SGS: 16189
      The second execution of SGS: 16194
      The third execution of SGS: 16189

      This is a significant issue because most of alerting/monitoring services assume that values are counters.

      How to repeat:
      1. Create a test table.

       

      mysql> CREATE TABLE `test1` (id int, col1 text); 
      

      2. Fill it with a large amount of data

       

      mysql> INSERT test1 SET id =1, col1=unix_timestamp();
      
      $ for i in $(seq 1 22); do \
       mysql test -e "INSERT INTO test1 SELECT * FROM test.test1"; \
      done;

      3. Run delete queries in a loop:

       

      while /bin/true; do
       mysql -BN -h 127.0.0.1 test -e "BEGIN; DELETE from test1 LIMIT 20000; ROLLBACK" 2>/dev/null
      done;
      

      4. Run kill query in a loop

      while /bin/true; do
       mysql -BN -h 127.0.0.1 test -e "select CONCAT ( \"KILL QUERY \", ID, \";\" ) from information_schema.processlist where info like 'DELETE%';" 2>/dev/null | mysql -BN -h 127.0.0.1 2>/dev/null
      done;

      5. Check GLOBAL STATUS for drift:

       

      #!/bin/bash
      prev=0
      prevprev=0
      declare -A globalstatus
      while /bin/true; do
       while read h v; do
       if [ -z ${globalstatus["${h}_prev"]} ]; then
       globalstatus["${h}_prev"]=$v
       continue
       fi;
      if [ $v -lt ${globalstatus[${h}_prev]} ]; then
       echo "$v < ${globalstatus["${h}_prev"]} ($h)"
       echo ${globalstatus[${h}_prevprev]}
       echo ${globalstatus[${h}_prev]}
       echo $v
       #exit 0
      fi;
      globalstatus["${h}_prevprev"]=${globalstatus["${h}_prev"]}
       globalstatus["${h}_prev"]=$v
       done < <(mysql -BN -h 127.0.0.1 -e "SHOW GLOBAL STATUS LIKE 'Handler%';" 2>/dev/null)
      done;
      

       

      An example output:
      1915 < 1916 (Handler_rollback)
      1915
      1916
      1915

      It is also possible to reproduce the issue without killing a query (only rollback is needed), however, it happens very rarely.

      Suggested fix:
      Values of SHOW GLOBAL STATUS variables should be counters.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              zsolt.parragi Zsolt Parragi
              Reporter:
              iwo.panowicz Iwo Panowicz
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 38 minutes
                  38m

                    Smart Checklist