InnoDB auto-inc scalability fixed
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.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
16 Comments
Finally, this is something I have been waiting for for a long time. Unfortunately I will not have the option of migrating to 5.1 in production until Q1/Q2..
Comment :: September 26, 2007 @ 4:04 pm
I wonder how hard this would be to port back to a 4.1 release?
Comment :: September 26, 2007 @ 4:50 pm
Probably not that hard to backport to 4.1 but I would advocate MySQL not doing it. Even 5.0 is pushing the limits for a high risk change that’s a performance improvement. Doubt it’s worthwhile risking breaking things for 5.0 users who need stable production systems.
Comment :: September 26, 2007 @ 5:19 pm
[...] MySQL InnoDB 的 auto-increment 會造成 INSERT 時使用 table-level lock 的 bug 終於修正 (從 2006 年一月就進 MySQL 回報系統的 bug)，下個 5.1 的版本 (預定是 5.1.22) 就會包括在裡面：InnoDB auto-inc scalability fixed。 [...]
Pingback :: September 26, 2007 @ 5:40 pm
A short step for MySQL, a giant leap for mankind
This was much, much, needed.
Imagine, how better the world will be if only MySQL realized the importance of back porting this quickly.
James, I think, a well-tested patch that is back ported will be very worth it. The *thing* is that auto-inc table locking issue makes InnoDB inefficient for OLTP environments where an AUTO-INC column is being used.
Frank
Comment :: September 26, 2007 @ 6:39 pm
Sweet….. Go INNODB!
It’s nice to see these bugs FINALLY being fixed. The thread concurrency stuff in 5.1 looks awesome
Now if we can just get the whole group commit thing sorted out.
And Heikki.. please start assuming you’re on 8 core boxes
Comment :: September 26, 2007 @ 10:34 pm
James,
I know you are acting DBA, and I do not need to tell you what means upgrade of production farm of servers to next major version. So I wonder why you say the patch for 5.0 is not worthwhile. Frankly, we had (and have right now one big) a lot of customers who are hurt by this problem. I believe MySQL Support has much more. So one option for them is the planning to upgrade to 5.1 ? Which even is not in production stage, and actually will be ready for production usage in about 6 month or more after 5.1 is officially released.
Comment :: September 27, 2007 @ 4:40 am
I definitely think this is one of our problems. We always seem to have contention on our database, with locked tables, and we use all innodb tables with auto increment on just about every table. I guess we’ll have to start debating if we move to 5.1.x.
Comment :: September 27, 2007 @ 11:13 am
[...] guys over at MySQL Performance Blog - the high performance MySQL gurus - reported that a important bug for was fixed (currently only [...]
Pingback :: September 28, 2007 @ 8:58 am
[...] Scaling on MySQL I have run into this bottleneck in InnoDB before and I am glad to see that it was [...]
Pingback :: October 2, 2007 @ 6:16 am
A small note on the backporting of this enhancement to 5.0 and 4.1. I have spoken on the subject with Heikki couple of times, and simply put, this is not feasible. This change hardly made to 5.1. Some other nice enhancements are only in 5.2. These are, actually, quite drastic changes in the code and those are not doable in a stable release.
Comment :: October 3, 2007 @ 9:54 am
Sinisa - Which enhancements are you speaking about ?
Comment :: October 3, 2007 @ 10:04 am
[...] InnoDB auto-inc scalability fixed によると最新版の MySQL 5.1.22 [...]
Pingback :: October 7, 2007 @ 11:26 pm
[...] that more and more folks hit the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental [...]
Pingback :: October 29, 2007 @ 5:49 am
[...] масштабируемости, давно уже новостью не является (см. InnoDB auto-inc scalability fixed, сентябрь 2007). Практический пример типичной проблемы и [...]
Pingback :: June 26, 2009 @ 1:20 am
[...] масштабируемости, давно уже новостью не является (см. InnoDB auto-inc scalability fixed, сентябрь 2007). Практический пример типичной проблемы и [...]
Pingback :: June 26, 2009 @ 1:27 am