I've got a MySQL RDS database that uses pure generated GUIDs for key columns. The current value of the innodb_autoinc_lock_mode is 1, and due to some performance bottlenecks we want to change it 2. However, in MySQL documentation it says that:

If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the source and its replicas. Auto-increment values are not ensured to be the same on the replicas as on the source if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the source and replicas do not use the same lock mode.

Our replication mode is set to Mixed (default in RDS). So what I read here is telling me to keep innodb_autoinc_lock mode to 0 or 1. However the question is: Given that we are NOT using AUTO_INCREMENT columns, what would be the issue with setting innodb_autoinc_lock_mode to 2 with regards to statement-based replication and binlog replay (for recovery) ?

  • If you need any additional information answered, please let me know. – J.D. Dec 14 '20 at 21:46

As far as my understanding goes, if you're truly not using any auto-increment identity fields, then that isn't a concern and you should be ok to switch to innodb_autoinc_lock_mode = 2.

The warning is purely about the fact that auto-increment fields can't have 100% guarantee of staying in sync across replicas when interleaved is set because it allows multiple INSERT statements to occur at the same time so the order of INSERTs (and auto-generation of the IDs) can occur in different orders between replicas and the source.

This article goes into pretty good derail about the different values for that setting and their recommendations on when to use interleaved mode for performance reasons.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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