MySQL Inserting large data sets from file with Java

Question

I need to insert about 1.8 million rows from a CSV file into a MySQL database. (only one table)

Currently using Java to parse through the file and insert each line.

As you can imagine this takes quite a few hours to run. (10 roughtly)

The reason I'm not piping it straight in from the file into the db, is the data has to be manipulated before it adds it to the database.

This process needs to be run by an IT manager in there. So I've set it up as a nice batch file for them to run after they drop the new csv file into the right location. So, I need to make this work nicely by droping the file into a certain location and running a batch file. (Windows enviroment)

My question is, what way would be the fastest way to insert this much data; large inserts, from a temp parsed file or one insert at a time? some other idea possibly?

The second question is, how can I optimize my MySQL installation to allow very quick inserts. (there will be a point where a large select of all the data is required as well)

Note: the table will be eventually droped and the whole process run again at a later date.

Some clarification: currently using ...opencsv.CSVReader to parse the file then doing an insert on each line. I'm concating some columns though and ignoring others.

More clarification: Local DB MyISAM table

Answer 1

You should really use LOAD DATA on the MySQL console itself for this and not work through the code...

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

If you need to manipulate the data, I would still recommend manipulating in memory, rewriting to a flat file, and pushing it to the database using LOAD DATA, I think it should be more efficient.

Answer 2

Wouldn't it be faster if you used LOAD DATA INFILE instead of inserting each row ?

Answer 3

Tips for fast insertion:

• Use the LOAD DATA INFILE syntax to let MySQL parse it and insert it, even if you have to mangle it and feed it after the manipulation.

• Use this insert syntax:

  insert into table (col1, col2) values (val1, val2), (val3, val4), ...

• Remove all keys/indexes prior to insertion.

• Do it in the fastest machine you've got (IO-wise mainly, but RAM and CPU also matter). Both the DB server, but also the inserting client, remember you'll be paying twice the IO price (once reading, the second inserting)

Answer 4

I'd probably pick a large number, like 10k rows, and load that many rows from the CSV, massage the data, and do a batch update, then repeat until you've gone through the entire csv. Depending on the massaging/amount of data 1.8 mil rows shouldn't take 10 hours, more like 1-2 hours depending on your hardware.

edit: whoops, left out a fairly important part, your con has to have autocommit set to false, the code I copied this from was doing it as part of the GetConnection() method.

    Connection con = GetConnection();
con.setAutoCommit(false);
         try{
          PreparedStatement ps = con.prepareStatement("INSERT INTO table(col1, col2) VALUES(?, ?)");
          try{
           for(Data d : massagedData){
            ps.setString(1, d.whatever());
                                        ps.setString(2, d.whatever2());
                                            ps.addBatch();
           }
           ps.executeBatch();
          }finally{
           ps.close();
          }
         }finally{
          con.close();
         }

Answer 5

Another idea: do you use a PreparedStatement for inserting your data with JDBC ?

Answer 6

I would run three threads...

1) Reads the input file and pushes each row into a transformation queue 2) Pops from the queue, transforms the data, and pushes into a db queue 3) Pops from the db queue and inserts the data

In this manner, you can be reading data from disk while the db threads are waiting for their IO to complete and vice-versa

Answer 7

If you're not already, try using the MyISAM table type, just be sure to read up on its shortcomings before you do. It is generally faster than the other types of tables.

If your table has indexes, it is usually faster to drop them then add them back after the import.

If your data is all strings, but is better suited as a relational database, you'll be better off inserting integers that indicate other values rather than storing a long string.

But in general, yes adding data to a database takes time.

Answer 8

Depending on what exactly you need to do with the data prior to inserting it your best options in terms of speed are:

Parse the file in java / do what you need with the data / write the "massaged" data out to a new CSV file / use "load data infile" on that.

If your data manipulation is conditional (e.g. you need to check for record existence and do different things based on whether it's an insert or and update, etc...) then (1) may be impossible. In which case you're best off doing batch inserts / updates.
Experiment to find the best batch size working for you (starting with about 500-1000 should be ok). Depending on the storage engine you're using for your table, you may need to split this into multiple transactions as well - having a single one span 1.8M rows ain't going to do wonders for performance.

Answer 9

Your biggest performance problem is most likely not java but mysql, in particular any indexes, constraints, and foreign keys you have on the table you are inserting into. Before you begin your inserts, make sure you disable them. Re-enabling them at the end will take a considerable amount of time, but it is far more efficient than having the database evaluate them after each statement.

You may also be seeing mysql performance problems due to the size of your transaction. Your transaction log will grow very large with that many inserts, so performing a commit after X number of inserts (say 10,000-100,000) will help insert speed as well.

From the jdbc layer, make sure you are using the addBatch() and executeBatch() commands rather on your PreparedStatement rather than the normal executeUpdate().

Answer 10

Are you absolutely CERTAIN you have disabled auto commits in the JDBC driver?

This is the typical performance killer for JDBC clients.

Answer 11

This is an interesting read: http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html

Answer 12

You can improve bulk INSERT performance from MySQL / Java by using the batching capability in its Connector J JDBC driver.

MySQL doesn't "properly" handle batches (see my article link, bottom), but it can rewrite INSERTs to make use of quirky MySQL syntax, e.g. you can tell the driver to rewrite two INSERTs:

INSERT INTO (val1, val2) VALUES ('val1;, 'val2'); 
INSERT INTO (val1, val2) VALUES ('val3;, 'val4');

as a single statement:

INSERT INTO (val1, val2) VALUES ('val1;, 'val2'), ('val3','val4'); 

We did this for a bulk insert investigation of our own: it made an order of magnitude of difference. Used with explicit transactions as mentioned by others and you'll see a big improvement overall.

The relevant driver property setting is:

jdbc:mysql:///<dbname>?rewriteBatchedStatements=true

See: A 10x Performance Increase for Batch INSERTs With MySQL Connector/J Is On The Way