1

I have 2 servers, one with a Java app using Spring for DB connection handling, and the other with MySQL. Ping delay between both servers is about 100ms.

The Java server is using bulk inserts to DB, 2000 rows at a time. It seems that it's taking 100ms x 2000 inserts = 200,000 ms to do this bulk insert!

Is there a way to minimize this network delay? Does using transactions help in reducing it?

Any ideas are much appreciated.

Amer
  • 263
  • 3
  • 16

2 Answers2

1

Transactions would only give you more overhead. (Though that certainly doesn't mean you shouldn't use them.) I assume doing things in batch might help. That would reduce the overhead from making new network connections for each insert. You might also consider caching things on the Java server when possible; though that would only help for reads, not for writes.

Tim Pote
  • 27,191
  • 6
  • 63
  • 65
  • Every time a query is added to the batch insert, through the same connection, it's still taking 100ms to do it. CONNECT is done once, then 2000 INSERTs are queried, then QUIT. – Amer May 17 '12 at 02:23
  • Try doing it without a batch insert and see the difference. – Tim Pote May 17 '12 at 02:32
1

Like the other answer says, try using a batch. But if the MySQL JDBC driver doesn't optimize them, it won't help. See the answers here for other potential solutions: JDBC batch insert performance

Community
  • 1
  • 1
artbristol
  • 32,010
  • 5
  • 70
  • 103
  • Great link. That's what solved it 'jdbc:mysql://host:3306/db?useServerPrepStmts=false&rewriteBatchedStatements=true'. Now when it sends the 2000 INSERT queries, they are all in 1 query after one another, not 2000 separate queries. Used general_log=1 to diagnose. Now fiddling with persistent connections. Thanks! – Amer May 17 '12 at 12:44