I was recently discussing innodb_autoinc_lock_mode with some colleagues to address issues at a company I was working with.
innodb_autoinc_lock_mode
This variable defines the lock mode to use for generating auto-increment values. The permissible values are 0, 1 or 2 (for “traditional”, “consecutive” or “interleaved” lock mode, respectively). In most cases, this variable is set to the default of 1.
We recommend setting it to 2 when the BINLOG_FORMAT=ROW. With interleaved, INSERT statements don’t use the table-level AUTO-INC lock and multiple statements can execute at the same time. Setting it to 0 or 1 can cause a huge hit in concurrency for certain workloads.
Interleaved (or 2) is the fastest and most scalable lock mode, but it is not safe if using STATEMENT-based replication or recovery scenarios when SQL statements are replayed from the binary log. Another consideration – which you shouldn’t rely on anyway – is that IDs might not be consecutive with a lock mode of 2. That means you could do three inserts and expect IDs 100,101 and 103, but end up with 100, 102 and 104. For most people, this isn’t a huge deal.
If you are only doing simple inserts, this might not help you. I did a sysbench test on MySQL 5.7 in Amazon RDS with 100 threads and found no difference in performance or throughput between lock modes 1 and 2. It helps the most when you when the number of rows can’t be determined, such as with INSERT INTO…SELECT statements.
You can find a longer form article in the manual, but I highly recommend setting this value to 2 if you are not using STATEMENT-based replication.
What is recommended value of innodb_autoinc_lock_mode in combination with BINLOG_FORMAT=MIXED?
The mixed format uses row-based replication for any statements that are unsafe for statement-based replication, i.e. all of the auto-increment lock modes are safe with this format.