I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that … used: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… seeing periodic drops in performance which might be the result of the insert buffer filling up and then decreasing performance until some space… lets you accelerate the rate at which insert buffer pages are flushed to disk (innodb_ibuf_accel_rate) which might be interesting…. Mark Callaghan over at Facebook has seen big performance difference on insertion rate when increased insert buffer flushing was enabled (using the FB…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… locked. Then, we try to insert another value on the second session: transaction2 > START TRANSACTION; transaction2 > INSERT INTO t VALUES(26); ERROR… insertion of data to other sessions. How to troubleshoot gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB… gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways: 1- Change…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
… AWS MySQL RDS, InnoDB engine. Our requirement is to insert data in same table and read from same table. insert into … SAME_TABLE….85 GB). I wrote separate script to split the same insert in 8 chunks then it took 10min. Question : Can 14min… be reduce further? Is there anyway I can improve the performance? We can use OUTFILE method as suggested by Peter because…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…–single-transaction to get consistent backup for their Innodb tables without making database read only. In … In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary … this table and so data which was inserted after start of transaction (by ALTER TABLE statement…
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 11 Comments
… surprising. Stats about Innodb Insert Merge Buffer are available in SHOW INNODB STATUS output: ————————————- INSERT BUFFER AND ADAPTIVE HASH …performance stats, for example to understand when insert buffer merge will be completed. The “inserts” is number of inserts to insert…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
… memory-to-disk threshold, as famously happens in B-Tree inserts (see InnoDB vs TokuDB for example). This doesn’t mean that… a solely in-memory bottleneck to solely on-disk. The insert performance is quite variable, more so than I would like to… them. Finally, a single-threaded insert workload is not very revealing. To understand the sustained write performance of an RDS instance, we…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
Everyone using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such… correct, however there a notable exception – INSERT INTO table1 SELECT * FROM table2. This statement will perform locking read (shared locks) for table2… tests to see how well it performs 
One more thing to keep into account – INSERT … SELECT actually performs read in locking mode and…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… the innodb_autoinc_lock_mode to 0. But with a loss of performance and concurrency. How can I solve this problem for INSERT IGNORE? As I informed you before, it is not documented that INSERT… (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex…
Comment: To UUID or not to UUID ?
June 8, 2010 By 28 Comments
…-D3B6181E9833 1438CCD7-7348-11DF-807F-D4B6181E9833 To improve InnoDB insert performance, primary keys should be inserted with least changing bits first, correct? I wrote…
