5 messages in com.mysql.lists.javaRe: optimizing many inserts with JDBC...| From | Sent On | Attachments |
|---|---|---|
| Will Glass-Husain | 27 Aug 2003 18:00 | |
| Filip Hanik | 27 Aug 2003 18:08 | |
| Marc Slemko | 27 Aug 2003 18:10 | |
| Will Glass-Husain | 27 Aug 2003 22:33 | |
| "Schäfer, Peter" | 28 Aug 2003 01:07 |
| Subject: | Re: optimizing many inserts with JDBC - RESULTS |
|---|---|
| From: | Marc Slemko (mar...@znep.com) |
| Date: | 08/27/2003 06:10:50 PM |
| List: | com.mysql.lists.java |
On Wed, 27 Aug 2003, Will Glass-Husain wrote:
Two days ago I asked on this mailing list about how to speed the processing of a
high volume of inserts from a Java web application. I made a test program and
got some interesting results I thought I'd share. I'd welcome any comments.
SUGGESTED OPTIMIZATION
The suggested optimization was to make a PreparedStatement that executed an
insert with multiple records on one line, e,g.
INSERT (field1, field2) VALUES (val1a, val1b), (val2a, val2b), (val3a, 3b)
But what should the block size be? In other words, How many records per line?
SUMMARY OF RESULTS
The results showed that the ideal size depended on the database. For MySql
3.23, about 5 was the right number (2900 ms). For MySql 4.0, between 20 and 100
(about 1800 ms). Interestingly, when I converted the tables to InnoDb; my
performance took a huge hit for small block sizes (20x longer!) but I got the
best results with a block size of 1000 records (1300 ms).
Did you make sure to disable autocommit for innodb? That then adds another variable, which is how often you call commit().
If you don't disable autocommit, you will be doing a commit after every statement which limits you to at most a few hundred transactions per second on typical hardware due to seek time and rotational delay of your disk.