Batch insertions getting slow over time

I'm inserting about 400,000 rows into a MySQL table with
PreparedStatement. To do so, I'm inserting them in batches of 100 rows
each. At the start, they are fast (0.01 to 0.02 seconds per batch).
but after a while, they start getting slow. First, I'd see a single
batch that'd like 50 seconds in the middle of others which take just
0.02 seconds. Then, they start to average about 2-3 seconds.

I checked two explanations for this behavior. First, the table is
getting bigger so insertions are expected to get slower (there's an
index on the table). But I was able to rule this out by restarting my
program and inserting new rows into the table (without clearing it).
Inserting the new rows are fast.

Second possible cause is that other things in my program are slowing
things down. I was able to rule this out by running my program with
the exception of PreparedStatement.executeBatch() commented out. In
other words, everything except the actual insertion into the table.
This ran without any slowness.

So, I'm starting to think PreparedStatement.executeBatch() is doing
something funky. The really slow batches (50 seconds) look like it's
some sort of memory paging issue because the CPU usage is 0% during it.
Though when I observe the memory usage of the program during
execution, it doesn't seem that weird.. Another reason could be that
the table's getting locked for some weird reason during all those
insertions.

Anyway, can someone shed some light on this? It's my first time using
MySQL with Java and I'm at a loss here. Thanks!

.

Relevant Pages

  • PHP, mysql, and escaping characters
    ... and the use of addcslashes. ... MySQL requires only that backslash and the quote ... In addition to the qutoes, backslashes, and nulls, real escape string ... "Inserting a large value into a BLOB column is no different than ...
    (comp.lang.php)
  • Re: Help with this code - parse insert into Mysql
    ... product information by pulling 3 fields and inserting values into my ... As hard as the devel team has tried the command-line functionality of PHP is, in my opinion, not fully finished and leaves me wanting in most cases when I use it solely as command-line script. ... if you can clarify the purpose of the script and the desired result there maybe a possibility that there is a better solution to using MySQL. ...
    (comp.lang.php)
  • Re: Help with this code - parse insert into Mysql
    ... product information by pulling 3 fields and inserting values into my ... As hard as the devel team has tried the command-line functionality of PHP is, in my opinion, not fully finished and leaves me wanting in most cases when I use it solely as command-line script. ... if you can clarify the purpose of the script and the desired result there maybe a possibility that there is a better solution to using MySQL. ...
    (comp.lang.php)
  • Re: Simple question about an insert query...
    ... My application performs inserting into the 2 table. ... I have to do everythings in one transaction.. ... Consider a transaction where the first insert statement creates an ID that I need for two other queries in the same transaction. ... I'm using j2ee with the latest mysql connector on mysql5. ...
    (comp.lang.java.programmer)
  • Re: mySQL question
    ... I am not sure where/how you are inserting the different records, ... > I guess someone who can deal with SQL Server can also deal with mySQL. ... > the last field of the record, it automatically jump to the next record, in ...
    (microsoft.public.sqlserver.programming)