Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Edit:

I realized that if I construct a large query in memory, the speed has increased almost 10 times of magnitude

insert ignore into xxx(col1, col2) values('a',1), values('b',1), values('c',1)...

Edit:

since I have an index on the first column, the insert time creeps up as I insert more. Can I delay the index until the end?

Original:

I'm using the following to batch insert 10 million rows into mysql db(not all at once, since they don't all fit into memory), it's too slow(taking many hours). should I use load file to improve performance? I would have to create a second file to store all the 10 million rows, then load that into db. are there better ways?

PreparedStatement st=con.prepareStatement("insert ignore into xxx (col1, col2) "+
    " values (?, 1)");
Iterator<String> d=data.iterator();

while(d.hasNext()){
    st.clearParameters();
    st.setString(1, (d.next()).toLowerCase());
    st.addBatch();
}
int[]updateCounts=st.executeBatch();
share|improve this question
    
Ten million rows shouldn't be THAT much. Is the MySQL server tuned at all or is it running on default settings? –  Janne Pikkarainen Jan 31 '11 at 8:38
    
default setting since it's my local dev machine, so my batch insert way is fine? –  user12145 Jan 31 '11 at 9:04

2 Answers 2

up vote 1 down vote accepted

You can do a couple of things to speed up the batch insert.

  • Disable any indexes and keys on your table before the upload, this will allow any duplicate rows to be inserted (a bit messy but easy to clean afterwards) but will speed up the insert a big deal

  • Do a batch COMMIT every N rows. If you're using an InnoDB table that works in a fully transactional way just COMMIT every N rows (don't make it too low or too high) so all the transaction is not stored into memory before commiting to disk

Let me know how this tricks work for you!

share|improve this answer
    
how do I disable indexes from my application? –  user12145 Jan 31 '11 at 9:18
    
In order to disable indexes you need to use the ALTER TABLE statement to remove them from the table, then have another ALTER TABLE statement ready for readding them again when you're done. Have a look at dev.mysql.com/doc/refman/5.1/en/alter-table.html –  lynxman Jan 31 '11 at 9:23

I'd suggest inserting into temporary table, w/o indexes at all, then doing insert from it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.