Batch Insertions Using Prepared Statements in Jdbc with Mysql are dead slow
I am trying to Insert 300 Milion Records. The record is split into 29 tables
I tried with 2000 and 1000 records per batch the time take for insertion are 20 and 10 mins respectively
only one table has 20 columns remaining all table will have columns from 3 to 6
Java Code reading 10000 rows in 5 sec but batch Insertions are taking 90 mins of time
I am Working on Windows 7 with 4gb of ram
basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.1/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/"
My suggestion would be to
DROP all of the tables' indexes,
INSERT the rows, and then recreate the indexes. A database can insert data much faster if it doesn't need to update indexes for each row inserted. And an index can be created from scratch faster than by building it with a "random" sequence of inserts.
It may also improve things if you can sort the rows of the respective tables into primary key order, and insert them in that order.
Finally, batching is a trade-off between the costs of lots of little requests and the costs of doing large / long transactions. Try experimenting with batch sizes that are smaller and large than those you are currently using.