Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a typical master-slave MySQL setup. Slave is in test mode currently. Master is heavily used.

I am noticing something that looks like a potential race-like condition on the slave. On occasion, because of heavy use on the master, I will get 2 entries in the Master binlog with the same timestamp on a very heavily used table.

On the slave, the relay log is identical to the binlog on the Master so I know that part is working properly. However, when these statements are replayed I end up with a 1062 Duplicate error condition.

When I check the slave tables I get the first statement replayed properly but only part of the second. The binlog format is currently mixed.

I am considering testing the setup with row-based instead of mixed mode replication as I have read that it is suppose to help with such a condition - I have not used RBR myself before.

  • Has anyone else had similar experiences?
  • Do you have a resolution to this condition?
  • Since RBR can have an impact on performance, is that significant from your experience?

Here were my settings:

  • MySQL ver is 5.5.13
  • Sync_binlog=1
  • innodb_flush_log_at_trx_commit=1

Thanks

share|improve this question
add comment

2 Answers 2

Replication is always serialized to prevent the race-conditions you are speaking about. See this stackoverflow post for my explanation of InnoDB locking. Some important notes:

  • RBR with row-based replication and read-committed isolation level sets fewer locks than InnoDB would have previously.

  • We should be moving to a world with RBR anyway, because this will open more doors for parallel replication threads (by working out dependencies between transactions).

To answer your last question, I use RBR in production, and I haven't seen any major performance hit. We do generate a lot more binary log data however, since the events logged contain whole rows.

share|improve this answer
add comment

RBR basically updates the slave with the entire row as it is after it has been inserted/updated and updates using the primary key of the table.

The advantages to this over statement is if the master server runs a statement which requires some sort of calculation or if it has a non-deterministic function or sub query in it.

With the former, you do not need to perform any calculation on the slave as you are only updating the row after the calculation has been applied to it.

With the latter, it may resolve race-conditions if the non-deterministic function or sub-query ran at a time when the result would be different then the time when it is run on the slave. So you can use this to keep your data consistent with these types of queries.

Also, it is important to note that you can use RBR in a session (if you are using MIXED binlog_format on your server) for part of your application that you feel has these types of statements (add to that if you create your own temp table and need the slave to do the same). You can do SET SESSION binlog_format = 'ROW'; in your application code for that specific (and potentially problematic) part of your code.

The disadvantages are if on the master, you run one statement that updates 100,000 rows. To update the slave, you just pass that one statement very quickly.

In my opinion, in transactions-based applications (where everything is just 1-2 inserted rows), then RBR can be advantageous. But in any case, you should test it out for yourself.

share|improve this answer
    
It does sound like those particular statements are going to SBR and that CAN have errors on the slave when you combine 'right' timing with a more lenient transaction isolation level. RBR is more consistent. One note I will add though, and this should come out in the testing, is that RBR needs significantly more disk space for its bin logs than SBR. the order of magnitude depending on your average row sizes. So this consistent behaviour might come at the expense of more hardware or less days in bin logs for you. –  TechieGurl Oct 10 '11 at 15:03
    
I do think it is a timing-type of issue. The issue occurs only when there is heavy activity on the master and timestamps are the same in the bin logs. I have been experimenting with RBR and it looks promising so far. Needs more testing however. I can live with bigger bin logs at this point. –  PDI Oct 10 '11 at 23:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.