mysql Memory (RAM) usage increases while using ResultSet?

Question

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

What you could do to save memory (more time though) is limit the number of results you get per SELECT statement — RMT
– RMT, Commented Aug 4, 2011 at 13:38

Vineet Reynolds · Accepted Answer · 2011-08-04 14:44:26Z

6

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.

edited Aug 4, 2011 at 14:44

answered Aug 4, 2011 at 13:41

Vineet Reynolds

76.9k17 gold badges154 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jarnbjo Over a year ago

No, the MySQL JDBC driver has only a very limited support for controlling the fetch size. The default behaviour is the fetch the entire result of the select at once. To stream the results, the fetch size must be set to Integer.MIN_VALUE and the Statement be created with ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY. Doing this, several further limitations must be accounted for as noted in the documentation: dev.mysql.com/doc/refman/5.0/en/…

Soheil Over a year ago

I have used these codes: st.setFetchSize(500); rsDBReader.setFetchSize(500); But it didn't change any thing

Vineet Reynolds Over a year ago

@Soheil, the docs are fairly clear that 500 is not going to trigger the hint. I would suggest reading them first. If you want a quick answer, using Integer.MIN_VALUE instead of 500 might work.

Soheil Over a year ago

@Vineet Reynolds, you are right. I tested it with Integer.MIN_VALUE. It work.

darioo · Accepted Answer · 2011-08-04 13:40:59Z

2

I'd suggest limiting the amount of rows you retrieve in your query. 50000 is a lot, so why not have a loop that fetches, let's say, 1000 rows every time?

You can achieve this using the limit statement, as described here. It's always best to be pragmatic about amount of data you're processing. Your current select might return 50000 rows today, but what if it grows to one million tomorrow? Your application will choke. So, do your processing step by step.

answered Aug 4, 2011 at 13:40

darioo

47.4k10 gold badges79 silver badges104 bronze badges

Comments

Vladimir Nazarenko · Accepted Answer · 2016-07-15 19:22:54Z

1

Note that there is similar issue with the latest releases of Postgres. In order to achieve cursor processing* you need to disable auto commit on connection connection.setAutoCommit(false) and use single statement in your SQL statement (i.e. statement which contains just one semicolon). It worked for me.

Postgres JDBC documentation

answered Jul 15, 2016 at 19:22

Vladimir Nazarenko

3051 silver badge14 bronze badges

Comments

jarnbjo · Accepted Answer · 2011-08-04 13:41:53Z

-1

What you see is actually expected behaviour and must not necessarily indicate a memory leak. Objects instances are in Java not garbage collected immediately after they have become unreachable and most Java VMs are very reluctant to return once allocated memory back to the operating system.

If you are using a recent version of Oracle's Java VM and really need a more aggressive garbage collector, you can try the G1GC implementation by adding the following arguments to the java command:

-XX:+UnlockExperimentalVMOptions -XX:+UseG1GC

The G1GC garbage collector usually reclaims objects faster than the default garbage collector and unused memory is also freed by the process.

answered Aug 4, 2011 at 13:41

jarnbjo

34.4k7 gold badges75 silver badges95 bronze badges

Collectives™ on Stack Overflow

mysql Memory (RAM) usage increases while using ResultSet?

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related