…. There are people to tell you you just should use auto_increment_increment and auto_increment_offset and you would not have any conflicts, I… to 2 masters at once. This can be solved with auto_increment_increment and auto_increment_offset, however a lot of other things can’t… silently out of sync. So back of our topic of auto_increment_increment and auto_increment_offset – besides being able to avoid some problems they…
Post: Conflict Avoidance with auto_increment_increment and auto_increment_offset
January 12, 2011 By 2 Comments
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
…_id: CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb; # each insert does one operations to get the… table The testing options are: Option 1 – auto_commit each statement Option 2 – auto_commit each statement Option 1 – nest each loop… you have already started modifying data before you need an auto_increment number but you do not want to commit yet. Full…
Post: Sharing an auto_increment value across multiple MySQL tables (revisited)
October 26, 2010 By 7 Comments
A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few…
Post: InnoDB auto-inc scalability fixed
September 26, 2007 By 18 Comments
… 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… had a lot of customers who was affected by InnoDB auto-inc. For several of them we even advised to replace…
Post: Find where your data is replicated, win a Percona Live ticket
October 21, 2011 By Leave a Comment
… status 0 seconds behind, running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 1.0.6-unknown +- [redacted… status 0 seconds behind, running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 1.0.6-unknown +- [redacted… status 0 seconds behind, running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 1.0.6-unknown +- [redacted…
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 being… situation: CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT… – INT UNSIGNED. Do the math. The maximum value for an auto-increment INT UNSIGNED is 4294967295. Divide that by 1500 qps and…
Comment: Conflict Avoidance with auto_increment_increment and auto_increment_offset
November 1, 2011 By 2 Comments
I think is better use auto_increment_increment , auto_increment_offset. auto_increment_increment = 2 // number of masters auto_increment_offset = 2 // id of master example if have 3 servers…,10,etc.. auto_increment_increment = 3 auto_increment_offset = 1 for server 2, this server generate ids 2,5,8,11,etc.. auto_increment_increment = 3 auto_increment_offset = 2…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… files. When and how it works: auto_increment (removing). Let’s have a simple table with auto_increment we want to get rid of…` int(6) NOT NULL auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB To remove auto_increment, we (1) create table with the same layout but without auto_increment, (2) flush tables with read…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… buffer. The most efficient approach here is not to use auto_increment but use certain partitioned sequences, for example you can have…) has pretty much table level locks when it comes to auto_increment columns, which is however completely separate problem which can be… many auto_increment already gives us what we want – if we access “recent” items more frequently and data set is large auto_increment would…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…. Improved Auto Increment handling Yet another known Innodb problem is table locks which are taken for statement duration for Inserts with auto-increment column… count exact number of auto increment values needed or if we get behavior change by having potential “holes” when auto increment values are assigned… number of rows in auto-increment batch insert is not known as we do not have to allocate sequential auto increment values if row…
