使用 ResultSet 时 mysql 内存 (RAM) 使用量增加？

发布于 2024-11-28 01:35:28 字数 1342 浏览 0 评论 0原文

我正在使用 MySQL 和 Java 来 SELECT 大约 50000 条记录。奇怪的是，当我使用 ResultSet 和 next() 方法读取数据时，我发现 Java 应用程序的 RAM 使用量在获取过程中增加。它从 255 MB 开始，增加到 379 MB！我正在使用的代码在这里：

try {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/#mysql50#crawler - used in report?" + "user=root&password=&useUnicode=true&characterEncoding=UTF-8");
    Statement st = conn.createStatement();
    ResultSet rsDBReader = st.executeQuery("SELECT Id, Content FROM DocsArchive");
    while (rsDBReader.next()) {
        int docId = rsDBReader.getInt(1);
        String content = rsDBReader.getString(2);
        . . .
        }
    rsDBReader.close();
    st.close();
    conn.close();
} catch (Exception e) {
    System.out.println("Exception in reading data: " + e);
}

我确信内存使用量是针对 ResultSet 的，而不是程序的其他部分。在这个程序中，我不需要更新记录，所以我想在完成工作后删除每条记录。我的猜测是，已读取的记录不会被删除，并且程序不会释放它们的内存。所以我使用了一些技巧来避免这种情况，例如使用以下代码：

Statement st = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);

st.setFetchSize(500);
rsDBReader.setFetchSize(500);

但它们没有改变任何东西。 :(

所以我需要一些方法来删除（释放）已读取的行的内存。

另一个有趣的点是，即使在完成函数并关闭 ResultSet、Statement 和 Connection 并转到程序的其他部分之后，仍然程序内存使用量并没有减少！谢谢

原文

I am using MySQL and Java to SELECT about 50000 records.
The strange thing is that when I use ResultSet and next() method to read the data, I see that the RAM usage of my java application increases during the fetching. it begins with 255 MB and increases up to 379 MB!
the code I am using is here:

try {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/#mysql50#crawler - used in report?" + "user=root&password=&useUnicode=true&characterEncoding=UTF-8");
    Statement st = conn.createStatement();
    ResultSet rsDBReader = st.executeQuery("SELECT Id, Content FROM DocsArchive");
    while (rsDBReader.next()) {
        int docId = rsDBReader.getInt(1);
        String content = rsDBReader.getString(2);
        . . .
        }
    rsDBReader.close();
    st.close();
    conn.close();
} catch (Exception e) {
    System.out.println("Exception in reading data: " + e);
}

I am sure that the memory usage is for ResultSet, not other parts of the program.
In this program I don't need to update records, so I want to remove every record after finishing the work.
My guess is that the records which have been read, will not be removed and the program doesn't free their memory. so I have used some tricks to avoid this, such as using following code:

Statement st = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);

st.setFetchSize(500);
rsDBReader.setFetchSize(500);

but they didn't change any thing. :(

So I need some method that removes (releases) memory of rows that have been read.

Another interesting point is that even after finishing the function and closing the ResultSet, Statement and Connection, and going to other part of the program, still the program memory usage doesn't decrease!
Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮生面具三千个 2024-12-05 01:35:28

使用 Statement.setFetchSize()< /a> 向驱动程序提供提示，表明它应该流式传输包含特定行数的结果集。据我所知，MySQL Connector-J 驱动程序确实理解提示并流 ResultSet（但在 MySQL 中，这仅限于一次一行）。

默认值为 0，将确保 Connector-J 驱动程序将获取完整的 ResultSet 而无需流式传输。这就是为什么您需要提供一个显式值 - 对于 MySQL，为 Integer.MIN_VALUE。

该语句：

Statement st = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);

不会导致流式传输ResultSet（至少不会自行实现）。它仅仅确保结果集不是“可滚动的”（即只能在向前方向上遍历）并且不是“可更新的”，并且当事务提交时底层游标将被关闭。

如 MySQL 的 JDBC 实现说明中所述，上述语句（不带 ResultSet.CLOSE_CURSORS_AT_COMMIT 参数）必须与Statement.setFetchSize(Integer.MIN_VALUE) 调用使流式传输逐行发生。这种情况下涉及的相关警告也已记录在案。

请注意，MySQL 文档中提到的示例中并未指定游标的可保持性。如果您需要的值与 Connection.getHoldability() 提供的值不同，那么此建议可能不适用。

Use Statement.setFetchSize() to provide a hint to the driver that it should stream the ResultSet for ones containing a certain number of rows. As far as I know, the MySQL Connector-J driver does understand the hint and streams ResultSets (but this is restricted to a row at a time in the case of MySQL).

The default value being 0, will ensure that the Connector-J driver will fetch the complete ResultSet without streaming it. That's why you will need to provide an explicit value - Integer.MIN_VALUE in the case of MySQL.

The statement:

Statement st = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);

does not result in streaming the ResultSet (at least not on it's own accord). It merely ensures that the resultset is not "scrollable" (i.e. can be traversed only in the forward direction) and not "updatable" and the underlying cursor will be closed when the transaction commits.

As noted in the JDBC implementation notes of MySQL, the above statement (without the ResultSet.CLOSE_CURSORS_AT_COMMIT parameter) has to be invoked in conjunction with the Statement.setFetchSize(Integer.MIN_VALUE) invocation for the streaming to occur row by row. The associated caveats involved in such a scenario have been documented as well.

Note that, the holdability of the cursor is not specified in the example mentioned in the MySQL documentation. If you need a value different from the the one provided by Connection.getHoldability(), then again, this advice might not apply.

回复收藏 0 原文