Optimization INSERT with huge amount of data

Question

I'm using a PreparedStatement in a simple java console application to load Huge amount of data out of an InputStream.

This is the code:

public void readStopTimes(CSVReader reader) throws IOException, SQLException { String insertSql = "INSERT INTO stop_times VALUES (null, ?, ?, ?, ?, ?)"; PreparedStatement statement = db.prepareStatement(insertSql); String [] nextLine; long i = 0; Chronometer chronometer = new Chronometer(); while ((nextLine = reader.readNext()) != null) { if(i++ != 0) { statement.setString(1, nextLine[0]); if(nextLine[1].isEmpty()) statement.setNull(2, Types.TIME); else statement.setTime(2, Time.valueOf(nextLine[1])); if(nextLine[2].isEmpty()) statement.setNull(3, Types.TIME); else statement.setTime(3, Time.valueOf(nextLine[2])); statement.setString(4, nextLine[3]); statement.setInt(5, Integer.parseInt(nextLine[4])); statement.addBatch(); } if(i++ % 1000 == 0) { statement.executeBatch(); } if(chronometer.count() > 5000) { chronometer.restart(); log.debug("Analyzed {} rows", i); } } statement.executeBatch(); db.commit(); }

Every 1000 insertions i'm executing the batch, every 5 seconds i'm printing a log.

From the logs it's evident that this algorithm runs extremely fast at the beginning, counting a total of more than 4 million rows in the first 25 seconds, then it slow down, at the point that in 5 seconds only 2 rows gets added to the batch.

I need to insert more than 5 million rows, do you have a faster alternative?

I don't know the answer, but for test: can you add statement = db.prepareStatement(insertSql); after statement.executeBatch(); ? — Iłya Bursov
– Iłya Bursov, Commented Dec 12, 2013 at 19:44

Trying · Accepted Answer · 2013-12-12 19:10:26Z

disable query cache in mysql
innodb_flush_log_at_trx_commit = 2 or if you can make sure that your mysql does not crash than make it innodb_flush_log_at_trx_commit = 0
if replication is enabled than disable the syncing of bin log by doing sync_binlog = 0

you can try directly putting CSV file to MySql by Load data infile . . . . . command which is pretty fast.

Only valid solution is Load data infile, eventually pre-parsing file with java.

abasterfield · Accepted Answer · 2013-12-12 19:15:22Z

Try comitting just after statement.executeBatch(); instead of just at the end. This should allow the inserts to be flushed as you proceed as mysql is keeping tabs on your inserts so it could roll them all back if necessary.

vivyzer · Accepted Answer · 2013-12-12 19:55:40Z

Try adding data in multiple batches instead of a single batch. Commit after every batch and keep track of processed data. Or you can use a staging table to add the data to the table and once it is done, rename the table, in case of failure, restart or start from a save point.

Collectives™ on Stack Overflow

Optimization INSERT with huge amount of data

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related