From: Rohit Kalhans
Date: October 3 2012 8:00am
Subject: Re: execution of the update rows event on a replication slave in
 mysql 5.6.6-m9
List-Archive: http://lists.mysql.com/internals/38590
Message-Id: <506BF0B2.6050102@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Hello Rich,

Please find the answers to your queries inline.

 >Hello,
 >
 >We noticed that the 5.6.6-m9 code that handles the update rows log
 >event on a slave looks as if it no longer fetches the row as MySQL 5.5
 >does (5.5 calls find_row to fetch the row) before calling the storage
 >engine update_row method. Here are some questions:

Don't worry if you cannot find find_row(). This has been replaced by 
three algorithms to find
the row to be updated corresponding to a row in a row event.

 >1. Is it in fact the case that 5.6 does not read the row when
 >executing update log events on the slave?

This is not entirely true. In 5.6 the number of times the rows are read from
the table may or may not be less than that in mysql 5.5. This is due to 
the introduction
of new slave row search algorithms. There are three row search 
algorithms namely
HASH_SCAN (new in mysql-5.6)
TABLE_SCAN (this is the only way of finding rows in 5.5 in a table w/o 
index)
INDEX_SCAN (this is the only way of finding rows in 5.5 in a table with 
index)

These algorithms are configurable in 5.6. see
http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_slave_rows_search_algorithms

A detailed explanation of this can be found here.
http://binlogtorelaylog.blogspot.com/2012/08/Batch-operations-in-RBR.html

 >The log event has the
 >before and after update versions of the row, so reading the row seems
 >redundant.

Yes the log event contains the before and the After Image. but this 
doesnot make the
fetching of the rows from the table redundant. We have to find the 
appropriate row in the table
to apply the after image. I agree that the "primitive" method of finding 
and applying the rows are
costly and often results in O(n^2) complexity which will be resolved by 
using HASH SCAN.

 >2. Why was this change made?

These changes were made to incorporate the HASH SCAN algorithm and 
enable new configurable
slave-row-seach-algorithms options.


 >3. Can the same change be applied to MySQL 5.5? If so, are there any
 >addition changed needed?

Applying these changes in 5.5 is not advised because these are extensive 
changes in how slave applies row events
and is to some extent dependent on some other features in 5.6 namely 
optimized row images etc.


 > Thanks
 > Rich Prohaska



-- 
Cheerio,
R*
-------------------------------------------------
Rohit Kalhans, Software Dev. MySQL Replication
cell: +91-741-152-1946

“Before you criticize someone, you should walk a mile in their shoes. 
That way when you criticize them, you are a mile away from them and you 
have their shoes.”
-Jack Handey