… 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…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
…dpe01:/etc/mysql# sysbench –test=oltp –oltp-auto-inc=off –oltp-test-mode=nontrx –oltp-nontrx-mode=update_key –mysql-user=root –mysql-password=”" –…cluster_name=DPE binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 wsrep_sst_auth=root:
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; Now consider the following sequence of events. (root@…being defined first. So, MySQL checks our INSERT, sees that the next auto-inc value is available, and claims…be. 1500 queries per second all trying to lock and update the same row is not …
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: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… transactions Using auto_inc on the id column Threads started! Screen1: root@localhost:/var/lib/mysql# rm ib* root@localhost:/var/lib/mysql# Now… until InnoDB flushes all pages. To stop write activity we can either stop application or lock tables: mysql> flush tables with read lock; Query…
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: Percona XtraDB Cluster reference architecture with HaProxy
February 14, 2013 By 63 Comments
…driver=mysql –mysql-engine-trx=yes –mysql-table-engine=innodb –mysql-host=haproxy –mysql-port=3307 –mysql-user=sbtest –mysql-password… Using “BEGIN” for starting transactions Using auto_inc on the id column Maximum …ERROR : Deadlock found when trying to get lock; try restarting transaction So I …
