Re: Insert speed (JDBC and MySQL)

Mark Matthews wrote: 
John wrote:

Mark Matthews wrote:


John wrote:


Hi,

I am writing a program that needs to typically carry out around 100,000
INSERTs to a mysql database. I would normally load the data using a text
file, but in this case, there is the occasional query required (evenly
spread through, and dependent on, the INSERTs).

I had the program connecting to a remote database and was achieving
around 47ms per INSERT. I thought that connecting to the database
locally would improve the speed, but it is unchanged at around 47ms.

This compares (very) unfavourably with the 0.3 ms that can be achieved
by piping a text file of sql into the command line tool.

Does anyone know how to improve the performance of these INSERTs?

Similarly, what speeds can be achieved for INSERTS using JDBC? (using
one sensible machine only).

Thanks,

John 


John,

How are your inserts crafted? Without knowing that, you're comparing
apples and oranges.

I can load 100 thousand rows using JDBC in just under a second over
100mbit ethernet via batched inserts (i.e. of the form INSERT INTO foo
VALUES (...), (...), (...)), so you've definitely got something not
efficient with the way you're doing it.

-Mark 

Hi Mark,

I am using one Connection object.

For each INSERT, I obtain a statement using createStatement(), then call
executeUpdate("queryString") on that statement. This takes the 47ms.

90% of the INSERT statements would be of the form "INSERT INTO
table_name VALUES(data, data)".

I am quite reluctant to combine multiple INSERTS into one batch, because
if there is an error with an update I would like the program to detect
this, log it, and take corrective action.

If there is a way of improving performance without batching the inserts
or combining multiple queries into one statement that would be ideal.
(I'm hoping that I am doing something simple inefficiently and there is
some easy way of speeding it up.)

Thanks,

John 


John,

Well, for one, you don't need to create a statement for each insert,
that can make a lot of garbage. You can create it once and re-use it.

Other than sending things in bulk, you're going to run into the issue of
round-trip time for each insert, which is what is killing you now
(although 47ms per INSERT makes it sound like something goofy is going
on with your configuration). Normally the round-trip time for network
communiction in the JDBC driver is on the order of microseconds (i.e.
can't be measured with System.currentTimeMillis()).

Without seeing the table(s) in question, your MySQL configuration, and
some of your code, I can't be much more help than that, sorry :(

-Mark 


Thanks for your help. You eliminated a lot of the possibilities for me.

You mentioned that queries should execute in micro-s rather than ms, so I tried using the command-line tool. It turned out that the time was 0.05s on the CLI (about the same as the 47ms I was seeing). So it wasn't JDBC's fault after all.

After a bit of digging, I saw that you can use either InnoDB or MyISAM table types.

Switching to MyISAM gave an average insert time of 0.3 micro seconds (including all sorts of other inefficient application code). That's good enough for me!

John
.

Relevant Pages