… MySQL 5.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: InnoDB auto-inc scalability fixed
September 26, 2007 By 18 Comments
… 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… system variable innodb_autoinc_lock_mode which determines behavior of InnoDB for tables with autoinc. I do not want to copy-paste MySQL documentation…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might….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
…, needed. Imagine, how better the world will be if only MySQL realized the importance of back porting this quickly. James, I… 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
Post: Drilling down to the source of the problem
November 8, 2008 By 7 Comments
… INNODB STATUS showed as waiting on famous Innodb AUTO-INC lock. That would be an easy excuse to blame everything on this lock, suggest upgrading to MySQL… 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
… 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… sec) — 2. mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) — 3. mysql> ^Z [1]+ Stopped mysql test # mv…
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。 [...]
