Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values

mysql insert 1 million rows
mysql bulk insert performance
batch insert in java using prepared statement
mysql bulk load
jdbc batch insert performance
insert 10000 rows mysql
mysql inserts per second
mysql bulk loader

I am designing a MySQL database which needs to handle about 600 row inserts per second across various InnoDB tables. My current implementation uses non-batched prepared statements. However, writing to the MySQL database bottlenecks and my queue size increases over time.

The implementation is written in Java, I don't know the version off hand. It uses MySQL's Java connector. I need to look into switching to JDBC tomorrow. I am assuming these are two different connector packages.

I have read the following threads on the issue:

  • Optimizing MySQL inserts to handle a data stream
  • MyISAM versus InnoDB
  • Inserting Binary data into MySQL (without PreparedStatement's)

and from the mysql site:

My questions are:

  • Does anyone have advice or experience on performance differences using INSERTs with prepared statements in batch mode vs. using a single INSERT statement with multiple VALUEs.

  • What are the performance differences between the MySQL Java connector vs. JDBC. Should I be using one or the other?

  • The tables are for archive purposes, and will see ~90% write to ~10% read (maybe even less). I am using InnoDB. Is this the right choice over MyISAM?

Thank you in advance for your help.

DB with best inserts/sec performance?, . with 100 concurrent connections on 10 tables (just some values). Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values. Ask Question. Asked 7 years, 4 months ago. Active 1 year ago. Viewed 21k times. 28. I am designing a MySQL database which needs to handle about 600 row inserts per second across various InnoDB tables.

I know this thread is pretty old, but I just thought I would mention that if you add "rewriteBatchedStatements=true" to the jdbc url when using mysql, it can result in huge performance gains when using batched statements.

MySQL, best way to insert 1000 rows, How can insert 1000 records at a time in MySQL? You can use bulk copy (bcp), insert the data with a series of INSERT statements, use a parameterized prepared statement from the client, or call a stored procedure with the data passed as a parameter. From that stored procedure, you’ll probably want to insert the data into a table by using statements that insert several rows at a time.

Do you have any triggers on any of the affected tables? If not, 600 inserts per second does not look like a lot.

Batch insert functionality from JDBC will issue the same statement multiple times in the same transaction, whereas multi-value SQL will squeeze all values in a single statement. In case of multi-value statement, you will have to construct the insert SQL dynamically and this could be an overhead in terms of more code, more memory, SQL Injection protection mechanism etc. Try the regular batch functionality first, for your workload, it shouldn't be a problem.

If you don't receive data in batches then consider batching it before insert. We use a Queue on separate thread to implement a Producer-Consumer arrangement. In this we hold back inserts till certain time has elapsed or the size of the queue has crossed a threshold.

In case you want the producer to be notified about a successful insert, then some more plumbing is required.

Sometimes just blocking on thread can be more straight forward and practical.

if(System.currentTimeMills()-lastInsertTime>TIME_THRESHOLD || queue.size()>SIZE_THRESHOLD) {
    lastInsertTime=System.currentTimeMills();
    // Insert logic
    } else {
    // Do nothing OR sleep for some time OR retry after some time. 
    }

Comparing multiple rows insert vs single row insert with three data , How can I add multiple values in one column in MySQL? So JDBC batch insert and update does gives us better performance over queries running without batches. One of the important thing which I have not used here is, I have not disabled auto-commit mode. You should always run SQL query with auto-commit mode disabled even with JDBC Batch insert and update example and do commit() explicitly. That will

After some of my own testing Jordan L gave the best tip. I think the execution time Lyubomyr has given for InnoDB non-dirty batch inserts is wrong because he most likely didn't use "rewriteBatchedStatements=true" in the JDBC connection string. Without it batches are worthless. In my own testing the non-dirty batch inserts using prepared statements was even faster than the dirty way of doing it with prepared statements.

Performant Batch Inserts Using JDBC, The performance of 'extract, transform, load' (ETL) processes for large Comparing multiple rows insert vs single row insert with three data of INSERT statements, use a parameterized prepared statement from What's the best way to use statements that insert several rows at a NET · Oracle · MySQL  So first, the basic API to Insert data in database in batches using Java JDBC. Simple Batch. I am calling this a simple batch. The requirement is simple. Execute a list of inserts in batch. Instead of hitting database once for each insert statement, we will using JDBC batch operation and optimize the performance. Consider the following code:

Batch Inserts Using JDBC Prepared Statements, So what can we do to improve the performance of these bulk inserts? a batch insert using a PreparedStatement from the java.sql library. a fairly typical interaction with a MySQL database via a JDBC driver. INSERT statements and convert them to a single multi-value INSERT MySQL: Server vs. If you use INSERT VALUES or INSERT VALUES ROW() with multiple value lists, or INSERT SELECT or INSERT TABLE, the statement returns an information string in this format: Records: N1 Duplicates: N2 Warnings: N3. If you are using the C API, the information string can be obtained by invoking the mysql_info() function.

JDBC Recipes: A Problem-Solution Approach, You instantiate a member of the PreparedStatement class with a SQL (or executes any statement using another Statement or PreparedStatement object). Using a single COPY statement for multiple batch inserts makes loading data Streaming batch inserts improve database performance by allowing parallel processing  Summary: A Java MySQL INSERT example using PreparedStatement. I hope this Java MySQL INSERT example (using a PreparedStatement) makes sense as is.In "real world" Java database programs I almost always use Spring to access a database, but when you're first getting started, I think it's important to see examples like this so you can understand how things work under the covers.

High-speed inserts with MySQL - Benjamin Morel, The first request to an insensitive cursor fetches all the rows and stores them on the client. request generates network traffic; therefore, performance can be very slow. Batch Multiple Update Statements A batch update is a set of multiple update and INSERT statements) submitted to the database for processing as one. Either may be used whether there is a single values list or multiple lists, and regardless of the number of values per list. The affected-rows value for an INSERT can be obtained using the ROW_COUNT() SQL function or the mysql_affected_rows() C API function.

Comments
  • well while using batch insert, you will do this operation in single transaction. In other case you will have transaction per row insert.
  • Maybe the dba.stackexchange would have been a better place for this question.
  • +1 for the research and efforts you have already done though this is your first post.
  • Thanks for the benchmarks, that was the most straightforward answer I could have asked for. I implemented batched prepared inserts today and it worked like a charm!
  • Any idea why batch insert is slower than single inserts on InnoDB?
  • @stracktracer hmmm yes, indeed. It's hard to say, because it probably might be relevant to a specific test case only. Thank you for the notice.
  • This is the first time I have come across the 'rewriteBatchedStatements' setting. With large batch statements this increases the speed by orders of magnitude and is just what I was looking for. See stackoverflow.com/questions/26307760 for more info.
  • Thank you for your advice. I did some research today and created a rudimentary producer-consumer relationship. My data processer works in one thread, adding information to a queue belonging to the mysql inset thread. It seems to work nicely. I was using innodb because there were some important foreign key relations I was going to try to keep. It seems they might not really be necessary in the grad scheme of things, so I might switch to myISAM tomorrow and see how things go.