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

LP #903506: RBR + no PK => High load on slave (table scan/cpu) => slave failure

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Oleg Tsarev last update 20-06-2013 06:20:04

      if one runs DML on a table that has no indexes, a full table scan is done.
      with RBR, the slave might need to scan the full table for each row changed.

      consider this on the master with --binlog-format=row :
      delete from t1 order by rand();

      when t1 has N rows, and no primary/unique key,
      the slave must read N rows to process the delete.
      for larger datasets this is unrealistic and will be doomed to fail.

      on master:
      mysql> delete from t1 order by rand();
      Query OK, 78130 rows affected (2.61 sec)

      on slave it takes 78130*78130 row reads to process (still running):
      ---TRANSACTION 0 1799, ACTIVE 731 sec, OS thread id 3672 fetching rows
      mysql tables in use 1, locked 1
      153 lock struct(s), heap size 30704, 78278 row lock(s), undo log entries 10045
      MySQL thread id 5, query id 16 Reading event from the relay log

      Number of rows inserted 78130, updated 0, deleted 10045, read 370899004
      0.00 inserts/s, 0.00 updates/s, 16.20 deletes/s, 600754.85 reads/s

      How to repeat:
      #setup rbr master and slave.
      #run on master:

      drop table if exists t1;
      create table t1(a int)engine=innodb;
      insert t1 values (1),(2),(3),(4),(5);
      set @a=5;
      insert into t1 select (@a:=@a+1) from
      t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7;
      delete from t1 order by rand();

      #watch as slave hits 100% of 1 cpu core and reads billions or rows.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: