I always knew RBR and unindexed tables didn't play along very well, but never realized just how much you can distress a slave can in some cases.
Consider this statement (yeah yeah, i know :)
mysql> delete from t1 order by rand();Query OK, 78130 rows affected (2.61 sec)
t1 has no indexes and is an int field with numbers from 1 to 78130. However, this will cause the slave to re-read entire table for each row deleted! Here it's still running, causing 100% cpu usage:
---TRANSACTION 0 1799, ACTIVE 2390 sec, OS thread id 3672 fetching rows mysql tables in use 1, locked 1 153 lock struct(s), heap size 30704, 78281 row lock(s), undo log entries 35423Number of rows inserted 78130, updated 0, deleted 35423, read 1076560253 0.00 inserts/s, 0.00 updates/s, 17.58 deletes/s, 367099.91 reads/s
Over a billion row reads 40 minutes later and it's not even half done yet.For a large table this could take weeks or years to complete. It would be nice if there was a way to prevent this situation from happening.
5 comments:
Seems pretty obvious to me... don't use tables without a primary key.
Or at least, I prefer to relax this rule to "don't create tables without a primary key if you expect them to ever contain more than one row".
Sometimes it's handy to have single-row (or no-row) tables, they can work without a primary key quite happily.
Even if the table only has one row, you can have a primary key anyway, and have an extra column which is an ENUM with only one value. This stops the table from ever having more than one row (technically, it might end up with two rows if you've not got strict mode on an allow a "blank" value in there)
I have seen cases where the master's update was efficient, but RBR refused to use an index to identify the rows to change on the slave. The hard part about this is that the RBR changes on the slave are not made via a "query" and it's basically a black box -- requires tough things like GDB to figure out what is going on.
We've observed this problem on a table with no PK, but with secondary indexes. The master uses the secondary index for DELETE, but the slave scans the whole table to find the row to delete. Maybe I should fill a bug report about this...
Hit this bug... found the solution here: http://binlogtorelaylog.blogspot.in/2012/08/Batch-operations-in-RBR.html
Post a Comment