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

GLOBAL STATUS variables drift after rollback

    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.

        Smart Checklist

          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