MySQL Forums
Forum List  »  InnoDB

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

Written By
Re: Database hangs during large inserts or load data infile on innodb table
December 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.