List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:July 30 2012 4:18pm
Subject:Re: How often the slave to pull data from master?
View as plain text  
Hello David,

On 7/30/2012 11:46 AM, David Lerer wrote:
> Thanks Shawn. This is very useful.
> Could you shed some light on how rolled-back transactions (or not-yet-committed
> transactions for that matter) are treated as far as the binary logs?  Are these updates
> actually go to the binary logs, thus trigger replication to the salve?
> Thanks, David.

There are two scenarios to worry about

1) This transaction involves no other storage engine except InnoDB.

2) This transaction involves any other storage engine as well as InnoDB.

In both cases, they start out the same. A temporary binary log cache is 
generated to keep track of the transaction as it occurs. All events are 
recorded to this cache which may become a temporary file if you exceed 
certain limits.

In the event of a COMMIT, this separate cache (or file) is appended to 
the normal binary log file at the position that corresponds to the 
moment where the COMMIT occurs.

In the event of a ROLLBACK, there is a difference. In scenario 1) where 
all changes are transacted, the cache is just dropped. As there were no 
changes to the actual data, nothing needs to enter the binary log.  In 
scenario 2), however, permanent changes to the data exist on the master. 
In this case, the entire cache is appended (as normal) but the last 
command is the ROLLBACK. This applies the non-transacted changes to the 
slave while removing the transacted changes from the slave thus keeping 
the two systems in sync.

Beginning with 5.5.9, we created a separate cache specifically for the 
non-transacted events to reduce our replication overhead. Only those 
statements will be transmitted in the event of a ROLLBACK.

http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Binlog_cache_disk_use

Regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
How often the slave to pull data from master?Zhigang Zhang26 Jul
  • Re: How often the slave to pull data from master?Johan De Meersman26 Jul
  • Re: How often the slave to pull data from master?Keith Murphy26 Jul
    • RE: How often the slave to pull data from master?Rick James26 Jul
      • Re: How often the slave to pull data from master?Shawn Green26 Jul
        • RE: How often the slave to pull data from master?Zhigang Zhang29 Jul
          • Re: How often the slave to pull data from master?Reindl Harald29 Jul
          • Re: How often the slave to pull data from master?Shawn Green30 Jul
            • RE: How often the slave to pull data from master?David Lerer30 Jul
              • Re: How often the slave to pull data from master?Shawn Green30 Jul
                • RE: How often the slave to pull data from master?David Lerer31 Jul
                • RE: How often the slave to pull data from master?Rick James1 Aug
                  • Re: How often the slave to pull data from master?Shawn Green1 Aug
                    • RE: How often the slave to pull data from master?Rick James1 Aug
  • Re: How often the slave to pull data from master?Keith Murphy29 Jul