RBR + no PK => High load on slave (table scan/cpu) => slave failure
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Invalid
|
Medium
|
Unassigned | ||
5.1 |
Won't Fix
|
Undecided
|
Unassigned | ||
5.5 |
Triaged
|
Medium
|
Unassigned | ||
5.6 |
Invalid
|
Medium
|
Unassigned |
Bug Description
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)
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.
Related branches
Changed in percona-server: | |
assignee: | nobody → Oleg Tsarev (tsarev) |
tags: | added: cr i20448 |
Upstream bug is claimed to be fixed in 5.5.19 and 5.6.4. Needs re-verification.