Skip navigation links

MySQL Forums :: InnoDB :: Database hangs during large inserts or load data infile on innodb table


Advanced Search

Re: Database hangs during large inserts or load data infile on innodb table
Posted by: James Day ()
Date: December 20, 2006 05:59AM

In addition to Marko's suggestion, if you are not loading the records in primary key order, try sorting them before loading - it can make a huge difference. If your machine has enough RAM you could also temporarily make the InnoDB buffer pool larger, but don't make it so large that swapping starts.

If SHOW ENGINE INNODB STATUS shows that the dirty pages percentage is above 90%, try inserting some 20 second delays in your load process to give the server some time to catch up, perhaps 25 seconds after every batch of 10000 rows and a commit. This will give the server a chance to do dirty page saving to disk and get some insert buffer merging work done. Adjust the delay time so that the merged records count is only a few hundred thousand records below the rows inserted count.

If you don't have a convenient way to sort the records you could load them into MyISAM, use ALTER TABLE ... ORDER BY to sort by the primary key and then ALTER TABLE ... engine=innodb.

James Day
Support Engineer, MySQL AB

Options: ReplyQuote


Subject Views Written By Posted
Database hangs during large inserts or load data infile on innodb table 3174 Ramam Pullella 12/13/2006 09:55AM
Re: Database hangs during large inserts or load data infile on innodb table 1548 Ramam Pullella 12/13/2006 10:08AM
Re: Database hangs during large inserts or load data infile on innodb table 2227 Marko Mäkelä 12/19/2006 06:58AM
Re: Database hangs during large inserts or load data infile on innodb table 1787 James Day 12/20/2006 05:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.