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.
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..
I wonder how hard this would be to port back to a 4.1 release?
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.
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
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 🙂
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.
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.
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.
Sinisa – Which enhancements are you speaking about ?
That would be great! At least Innob is doing ways that could possibly solve the problem of their consumers.