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
Subject
Views
Written By
Posted
6810
December 13, 2006 09:55AM
2582
December 13, 2006 10:08AM
3315
December 19, 2006 06:58AM
Re: Database hangs during large inserts or load data infile on innodb table
2964
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.