InnoDB auto-inc scalability fixed

InnoDB auto-inc scalability fixed

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (18)

  • k-sport coilovers Reply

    That would be great! At least Innob is doing ways that could possibly solve the problem of their consumers.

    September 26, 2007 at 12:00 am
  • Nicklas Westerlund Reply

    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..

    September 26, 2007 at 4:04 pm
  • mike Reply

    I wonder how hard this would be to port back to a 4.1 release?

    September 26, 2007 at 4:50 pm
  • James Day Reply

    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.

    September 26, 2007 at 5:19 pm
  • InnoDB 的重大修正 at Gea-Suan Lin’s BLOG Reply

    […] MySQL InnoDB çš„ auto-increment 會造成 INSERT 時使用 table-level lock çš„ bug 終於修正 (從 2006 年一月就進 MySQL 回報系統的 bug),下個 5.1 的版本 (預定是 5.1.22) 就會包括在裡面:InnoDB auto-inc scalability fixed。 […]

    September 26, 2007 at 5:40 pm
  • Frank Mashraqi Reply

    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

    September 26, 2007 at 6:39 pm
  • Kevin Burton Reply

    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 🙂

    September 26, 2007 at 10:34 pm
  • Vadim Reply

    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.

    September 27, 2007 at 4:40 am
  • Jay Reply

    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.

    September 27, 2007 at 11:13 am
  • Diamond Notes » Log Buffer #64: a carnival of the Vanities for DBAs Reply

    […] guys over at MySQL Performance Blog – the high performance MySQL gurus – reported that a important bug for was fixed (currently only […]

    September 28, 2007 at 8:58 am
  • Innodb Scaling on MySQL « François Schiettecatte’s Blog Reply

    […] Scaling on MySQL I have run into this bottleneck in InnoDB before and I am glad to see that it was […]

    October 2, 2007 at 6:16 am
  • Sinisa Milivojevic Reply

    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.

    October 3, 2007 at 9:54 am
  • peter Reply

    Sinisa – Which enhancements are you speaking about ?

    October 3, 2007 at 10:04 am
  • InnoDB の AUTO_INCREMENT とロック - METAREAL Reply

    […] InnoDB auto-inc scalability fixed によると最新版の MySQL 5.1.22 […]

    October 7, 2007 at 11:26 pm
  • Hacking to make ALTER TABLE online for certain changes | MySQL Performance Blog Reply

    […] 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 […]

    October 29, 2007 at 5:49 am
  • Блокировки при использовании AUTO_INCREMENT столбцов в MySQL Innodb « Oracle mechanics Reply

    […] масштабируемости, давно уже новостью не является  (см. InnoDB auto-inc scalability fixed, сентябрь 2007). Практический пример типичной проблемы и […]

    June 26, 2009 at 1:20 am
  • Блокирование таблиц при использовании AUTO_INCREMENT столбцов в MySQL Innodb « Oracle mechanics Reply

    […] масштабируемости, давно уже новостью не является  (см. InnoDB auto-inc scalability fixed, сентябрь 2007). Практический пример типичной проблемы и […]

    June 26, 2009 at 1:27 am
  • church of default (I would also have accepted “worse is better”) » Blog Archive » MyISAM is better for write-heavy tables because InnoDB has a full table lock on insert. Reply

    […] supports concurrent inserts, which means you can write to a table like crazy without locking it 2.) innodb prior to 5.1.22 does not have a table lock on insert, but it does have a lock on generating the next entry for an […]

    August 9, 2009 at 1:12 pm

Leave a Reply