@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?

CodePudding user response：

Setting parameters probably won't help (much).

There are a couple of reasons for the slowness:

1. 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.

2. 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