… I need for my setup (I still can have synthetic auto_inc PK, but in that case I still will need SECONDARY…=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max-requests=0…
Post: InnoDB auto-inc scalability fixed
September 26, 2007 By 18 Comments
There was long played scalability issue with InnoDB auto-increment field. For details check Bug 16979. In short words … is in case of insert into table with auto-increment column the special AUTO_INC table level lock is obtained, instead of usual… 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 8 Comments
… called AUTO-INC that remains until the end of the query or transaction. Because of this, two queries can’t have the AUTO-INC… a light-weight mutex instead of a table lock on AUTO-INC. We have recovered the concurrency and the performance but with… OK, 0 rows affected (0.00 sec) Now check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo…
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… mostly selects which were stuck waiting. The next symptom was AUTO-INC lock which again was a false one – because single insert… the original insert is taking so long letting hundreds of AUTO-INC insert to pile up. This was also very interesting case…
Comment: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… when these tables were small and first created to use auto-inc primary key. At the time (over 2 years ago), I… these tables get parallel inserts, I’m getting lots of auto-inc waits. Now that these tables are huge, I’ve got… PC software. If this works, then I can push the auto-inc‘ing up into my application and relieve MySQL of this…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… majority of the database was a single table with an auto-incrementing integer PK and a secondary UNIQUE KEY. The queries… situation: CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED… first. So, MySQL checks our INSERT, sees that the next auto-inc value is available, and claims it, but then it checks…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental becomes… of: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB… 1062 (23000): Duplicate entry ’0′ for key 1 Unfortunately, adding auto_increment does not work that way. Enum values (add and…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
… –num-threads=32 –init-rng=on –max-requests=0 –oltp-auto-inc=off –max-time=3000 run Results become as follows: Summary… as follows: root@dpe01:/etc/mysql# sysbench –test=oltp –oltp-auto-inc=off –oltp-test-mode=nontrx –oltp-nontrx-mode=update_key…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
June 19, 2012 By 8 Comments
… you wanted the Insert to happen but with the next auto-inc number. I have a situation where I have a multi… because I want the row inserted, just with a different auto-inc value that isn’t a duplicate.
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_examined: 655360 use test; SET timestamp=1325145746; select count(*) from auto_inc; Compare that to Percona Server with log_slow_verbosity=full… # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note that logging all queries in this fashion as opposed…
