Emergency

InnoDB auto-inc scalability fixed


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

There was long played scalability issue with InnoDB auto-increment field. For details check Bug 16979. In short words the problem is in case of insert into table with auto-increment column the special AUTO_INC table level lock is obtained, instead of usual row-level locks. With many concurrent inserted threads this causes serious scalability problems, and in our consulting practice we had a lot of customers who was affected by InnoDB auto-inc. For several of them we even advised to replace auto-inc column by that or another solution.
Good news is the bug is fixed. Bad news is it is fixed only 5.1.22, which is not released yet.
I wonder if the fix is going to be ported to 5.0, as I mentioned it affected many production systems and not all of them are ready to upgrade to 5.1.
The interesting also is the fix introduces new system variable innodb_autoinc_lock_mode which determines behavior of InnoDB for tables with autoinc. I do not want to copy-paste MySQL documentation, the very good and informative description of the problem and solution is available here.

Share Button
PREVIOUS POST
NEXT POST


Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.



Tags:

, ,

Categories:
Insight for DBAs


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *