Wednesday, March 13, 2013

MIXED mode replication was as following :


There have been many changes in the replication implementation between 5.1 and 5.5.
Many changes have been implemented to address bugs that were discovered.

Yes, I would suggest changing the bin logging format from statement to mixed. 


 Mixed mode works as follows:
Perform binary logging in statement mode
If a statement appears to be unsafe, change to row mode
after finishing the current operation/event fall back to statement

The advantage is that the server can detect statements which can possibly cause
binlogging to fail or create inconsistencies and avoid them by using row based format.

The disadvantage is that as compared to statement based logging, row based logging
is slower and the logs would be bigger in size. But in case where statement based logging
may cause the replication to break, the performance degradation may be acceptable.

Please refer to the following link for further details.

http://dev.mysql.com/doc/refman/5.5/en/binary-log-mixed.html




After upgrading to 5.5 version you will see more messages mentioning that statement-based replication is unsafe for particular queries. As you upgrade to even later version, it may cause more messages as the replication issues with non-deterministic queries become larger in number. So the question is whether mixed mode replication will fix this issue and are there any concerns. This is exactly what mixed mode replication attempts to do for you - offer you the best of both types of replication. When a statement is able to be replicated easily, it will use this format, but if the statement is non-deterministic, that is, may cause a different result to occur in some way on the slave, then it will use row-based replication. To use the mixed mode, you can simply change the binary log format and restart the master for it to take affect.

If you have any concerns about how it works or possible catches, I would recommend the following document:

MySQL Server Variable: binlog_format (Doc ID 1507802.1)


 


When MIXED mode is enabled, then ROW based logging will automatically be done for any statements which would NOT be safe to replicate via statements.

> so I would say MIXED is the way to go for us after upgrading to 5.5

Yes, I would use MIXED or ROW. If you use ROW then you can also safely use these options:
transaction-isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2


You can read more about these options here:
http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode


These options will allow InnoDB to use far fewer locks -- locks necessary to ensure the stabilizability of statement based logs -- namely gap locks, insert intention locks, the table level AUTO_INC lock, etc.

No comments:

Post a Comment