JDBC批量插入OutOfMemoryError

问题描述

我编写了一个方法 insert()，其中我尝试使用 JDBC Batch 将 50 万条记录插入 MySQL 数据库:

I have written a method insert() in which I am trying to use JDBC Batch for inserting half a million records into a MySQL database:

public void insert(int nameListId, String[] names) {
    String sql = "INSERT INTO name_list_subscribers (name_list_id, name, date_added)" +
        " VALUES (?, ?, NOW())";
    Connection conn = null;
    PreparedStatement ps = null;

    try {
        conn = getConnection();
        ps = conn.prepareStatement(sql);

        for (String s : names ) {
            ps.setInt(1, nameListId);
            ps.setString(2, s);
            ps.addBatch();
        }

        ps.executeBatch();

    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        closeDbResources(ps, null, conn);
    }
}

但是每当我尝试运行此方法时，都会出现以下错误:

But whenever I try to run this method, I get the following error:

java.lang.OutOfMemoryError: Java heap space
    com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.<init>(ServerPreparedStatement.java:72)
    com.mysql.jdbc.ServerPreparedStatement.addBatch(ServerPreparedStatement.java:330)
    org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:171)

如果我用 ps.executeUpdate() 替换 ps.addBatch() 并删除 ps.executeBatch()，它工作正常，虽然需要一些时间.如果您知道在这种情况下使用 Batch 是否合适，请告诉我，如果合适，那么为什么它会给出 OurOfMemoryError?

If I replace ps.addBatch() with ps.executeUpdate() and remove ps.executeBatch(), it works fine, though it takes some time. Please let me know if you know if using Batch is appropriate in this situation, and if it is, then why does it give OurOfMemoryError?

谢谢

推荐答案

addBatch 和 executeBatch 为您提供了执行批量插入的机制，但您仍然需要执行自己批处理算法.

addBatch and executeBatch give you the mechanism to perform batch inserts, but you still need to do the batching algorithm yourself.

如果您像现在这样简单地将每条语句堆到同一个批处理中，那么您将耗尽内存.您需要每 n 条记录执行/清除批处理.n 的值由您决定，JDBC 无法为您做出决定.批量越大，事情就会越快，但太大，你会遇到内存不足，事情会变慢或失败.这取决于你有多少内存.

If you simply pile every statement into the same batch, as you are doing, then you'll run out of memory. You need to execute/clear the batch every n records. The value of n is up to you, JDBC can't make that decision for you. The larger the batch size, the faster things will go, but too large and you'll get memory starvation and things will slow down or fail. It depends how much memory you have.

例如，从批量大小 1000 开始，然后尝试不同的值.

Start off with a batch size of 1000, for example, and experiment with different values from there.

final int batchSize = 1000;
int count = 0;
for(String s : names ) {
   ps.setInt(1, nameListId);
   ps.setString(2, s);
   ps.addBatch();

   if (++count % batchSize == 0) {
      ps.executeBatch();
      ps.clearBatch(); //not sure if this is necessary
   }
}
ps.executeBatch();   // flush the last few records.

这篇关于JDBC批量插入OutOfMemoryError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！