Distributed database insertion speed is very slow

Question

@Test
public void transaction() throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        String sql = "insert into `1` values(?, ?, ?, ?)";
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        conn.setAutoCommit(false);
        for(int i = 1; i <= 10000; i++){
            ps.setObject(1, i);
            ps.setObject(2, 10.12345678);
            ps.setObject(3, "num_" + i);
            ps.setObject(4, "2021-12-24 19:00:00");
            ps.addBatch();
        }
        ps.executeBatch();
        ps.clearBatch();
        conn.commit();
    } catch (Exception e) {
        conn.rollback();
        e.printStackTrace();
    }finally {
        JDBCUtils.closeResources(conn, ps);
    }
}

When setAutoCommit = true, local MySQL and distributed MySQL insert speeds are very slow.

When I set the transaction to commit manually, just like the code above, the local MySQL speed has increased a lot, but the insertion speed of distributed MySQL is still very slow.

Is there any additional parameters I need to set?

Not related to your question, but don't use things like `JDBCUtils.closeResources`, but instead switch to using [try-with-resources](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html). — Mark Rotteveel, Dec 25 '21 at 11:36
@asd21d32a In your configuration [mysqld] section, innodb_change_buffer_max_size=50 # from 25 (percent) for improved INSERT rate per second. SHOW FULL PROCESSLIST; to monitor when the instance has completed adjustment, then do your inserts and put it back to 25 percent for typical processing speed. — Wilson Hauck, Dec 28 '21 at 01:30

Stephen C · Accepted Answer · 2021-12-28T02:10:18.920

Setting parameters probably won't help (much).

There are a couple of reasons for the slowness:

With autocommit=true you are committing on every insert statement. That means the each new row must be written to disk before the database server returns the response to the client.
With autocommit=false there is still a client -> server -> client round trip for each insert statement. Those round trips add up to a significant amount of time.

One way to make this faster is to insert multiple rows with each insert statement, but that is messy because you would need to generate complex (multi-row) insert statements.

A better way is to use JDBC's batch feature to reduce the number of round-trips. For example:

  PreparedStatement ps = c.prepareStatement("INSERT INTO employees VALUES (?, ?)");

  ps.setString(1, "John");
  ps.setString(2,"Doe");
  ps.addBatch();

  ps.clearParameters();
  ps.setString(1, "Dave");
  ps.setString(2,"Smith");
  ps.addBatch();

  ps.clearParameters();
  int[] results = ps.executeBatch();

(Attribution: above code copied from this answer by @Tusc)

If that still isn't fast enough, you should get even better performance using MySQL's native bulk insert mechanism; e.g. load data infile; see High-speed inserts with MySQL

For completeness, I am adding this suggestion from @Wilson Hauck

"In your configuration [mysqld] section, innodb_change_buffer_max_size=50 # from 25 (percent) for improved INSERT rate per second. SHOW FULL PROCESSLIST; to monitor when the instance has completed adjustment, then do your inserts and put it back to 25 percent for typical processing speed."

This may increase the insert rate depending on your table and its indexes, and on the order in which you are inserting the rows.

But the flip-side is that you may be able to achieve the same speedup (or more!) by other means; e.g.

by sorting your input so that rows are inserted in index order, or
by dropping the indexes, inserting the records and then recreating the indexes.

You can read about the change buffer here and make your own judgements.

If you use `autocommit=false`, you _must_ remember to follow the batch with `COMMIT`. (I don't know how to say that in JDBC.) — Rick James, Dec 27 '21 at 19:57
That is orthogonal to the main topic of this question. But if you look at the OP's question, it is already doing this. (Obviously the code in my answer is not complete, but IMO it doesn't need to be to illustrate the "batch" approach.) — Stephen C, Dec 28 '21 at 01:36
There are two ways to "batch". One is with a transaction, as they were doing; the other was to have multiple rows in a single `INSERT` statement. — Rick James, Dec 28 '21 at 07:00
Thanks Rick. I have already noted that: *"One way to make this faster is to insert multiple rows with each insert statement, but that is messy because you would need to generate complex (multi-row) insert statements."*. (Mentioning autocommit with respect to that (inferior) method would also be (IMO) superfluous.) — Stephen C, Dec 28 '21 at 08:24

Distributed database insertion speed is very slow

1 Answers1