… is bad for InnoDB, but this is what I need for my setup (I still can have synthetic auto_inc PK, but in… –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max-requests=0 –num-threads=32 –rand-type=pareto run and InnoDB options… #####plugin options innodb_read_io_threads = 16 innodb_write_io_threads = 4 innodb_io_capacity = 4000 innodb_use_native_aio=0 #not innodb options (fixed…
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 usual… 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 8 Comments
….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…(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT…
Post: Drilling down to the source of the problem
November 8, 2008 By 7 Comments
… of time being… many of which SHOW INNODB STATUS showed as waiting on famous Innodb AUTO-INC lock. That would be an easy excuse… mostly selects which were stuck waiting. The next symptom was AUTO-INC lock which again was a false one – because single insert… 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…` int(6) NOT NULL auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB To remove auto_increment, we (1) create table…
Post: Fix of InnoDB/XtraDB scalability of rollback segment
January 18, 2009 By 9 Comments
… innodb_buffer_pool_size=6G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=128M innodb_thread_concurrency=0 innodb_extra_rsegments…-updates –oltp-non-index-updates –oltp-nontrx-mode select –oltp-auto-inc off –oltp-connect-delay 10000 –oltp-user-delay-min 0…
Comment: InnoDB auto-inc scalability fixed
October 29, 2007 By 18 Comments
[...] 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 [...]
Comment: InnoDB auto-inc scalability fixed
June 26, 2009 By 18 Comments
[...] маÑштабируемоÑти, давно уже новоÑтью не ÑвлÑетÑÑ (Ñм. InnoDB auto-inc scalability fixed, ÑентÑбрь 2007). ПрактичеÑкий пример типичной проблемы и [...]
Comment: InnoDB auto-inc scalability fixed
June 26, 2009 By 18 Comments
[...] маÑштабируемоÑти, давно уже новоÑтью не ÑвлÑетÑÑ (Ñм. InnoDB auto-inc scalability fixed, ÑентÑбрь 2007). ПрактичеÑкий пример типичной проблемы и [...]
Comment: InnoDB auto-inc scalability fixed
October 7, 2007 By 18 Comments
[...] InnoDB auto-inc scalability fixed ã«ã‚ˆã‚‹ã¨æœ€æ–°ç‰ˆã® MySQL 5.1.22 [...]
