1

I am trying to synchronize data from one database to another in MySQL, using Java.
To improve performance, I try to use MySQL bulk insert syntax: 

insert into table xxx(...) values (row1...) (row2...).

The problem is, how many rows should I insert at a time, so as not to violate the MySQL's max_allowed_packet setting?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Eric Liu
  • 11
  • 1
  • 3
    given that we know neither the average size of your rows, nor the value that your `max_allowed_packet` is set to, an answer is impossible. – pala_ Apr 20 '15 at 02:15
  • how do i know the average size of the rows? I am actually working on a general sync program that could be used to sync different kind of tables. so the average size cannot be pre-determined. – Eric Liu Apr 20 '15 at 03:13
  • yes. it can. `show table status`. or you just track it as you build the query and compare it to the max packet size. – pala_ Apr 20 '15 at 03:36
  • Why use Java at all? MySQL already does replication, and it already has dump and load facilities. – user207421 Apr 20 '15 at 04:33
  • @pala_ so I should just measure the sql size and compare it againt the max packet size? what about prepared statement? – Eric Liu Apr 20 '15 at 05:09

1 Answers1

0

You don't need to do those calculations yourself because MySQL Connector/J will take care of the details if you

  • use rewriteBatchedStatements=true in your connection URL
  • use addBatch() and executeBatch().

For more details see my other answer here.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418