… 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… 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…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
….1.22 InnoDB used a method to access that counter values called “Traditional”. This one uses a special table lock called AUTO-INC that remains until the end of the query or transaction. Because of this, two queries can’t have the AUTO-INC lock…/REPLACE uses a light-weight mutex instead of a table lock on AUTO-INC. We have recovered the concurrency and the performance but…
Post: Drilling down to the source of the problem
November 8, 2008 By 7 Comments
… which SHOW INNODB STATUS showed as waiting on famous Innodb AUTO-INC lock. That would be an easy excuse to blame everything on this lock, suggest… AUTO-INC lock which again was a false one – because single insert slows down a lot of inserts pile up waiting on the AUTO-INC lock… long letting hundreds of AUTO-INC insert to pile up. This was also very interesting case about optimal innodb_thread_concurrency. I continue…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…, but works well with both – InnoDB and MyISAM. Now 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 becomes…=InnoDB To remove auto_increment, we (1) create table with the same layout but without auto_increment, (2) flush tables with read lock, (3…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…: 2.365434 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count(*) from auto_inc; Compare….001414 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note that…
Comment: InnoDB auto-inc scalability fixed
September 26, 2007 By 18 Comments
… 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: InnoDB auto-inc scalability fixed
September 26, 2007 By 18 Comments
[...] MySQL InnoDB çš„ auto-increment æœƒé€ æˆ INSERT 時使用 table-level lock çš„ bug çµ‚æ–¼ä¿®æ£ (從 2006 年一月就進 MySQL å›žå ±ç³»çµ±çš„ bug)，下個 5.1 的版本 (é 定是 5.1.22) 就會包括在裡é¢ï¼šInnoDB auto-inc scalability fixed。 [...]
