java out of memory Exception (jdbc)

Question

I am trying to read serialized objects from mysql database in a loop and perform some operations on it in java. I have written the following function for returning me the object from ResultSet the object.

public static MyObj deSerializeCacheTagInfo(ResultSet res
    ) throws SQLException, IOException, ClassNotFoundException 
{
    byte[] buf = res.getBytes(3);
    ObjectInputStream objectIn = null;
    if (buf != null)
        objectIn = new ObjectInputStream(new ByteArrayInputStream(buf));
    MyObj info = (MyObj)objectIn.readObject();
    return info;
}

When I run this code, it gives me an out of memory exception. I searched around a bit and realized it could be because result set is large and it is kept in memory, so I tried fetching some 50 rows at a time.

But that doesn't seem to help either.

On profiling with visualvm, it reports that all the space is being hogged by byte[] objects. But I am not entirely sure what's going wrong.

Could you use [`getBinaryStream`](http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBinaryStream(int)) instead of [`getBytes`](http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBytes(int))? — MadProgrammer, Oct 08 '12 at 22:21
the size of the blob is between 2-3 MB. However in one query I fetch only 100 rows and the memory used up by the code is over 2 GB when it goes out of memory. — nik-v, Oct 09 '12 at 11:04

score 3 · Answer 1 · answered Apr 12 '13 at 21:38

By default MySQL JDBC driver fetches the complete ResultSet into memory.

You can change this to a streamed fetch with something like this:

Statement st = connIn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

score 0 · Accepted Answer · answered Oct 08 '12 at 22:28

0

Try to add objectIn.close(); before returning, it may help

answered Oct 08 '12 at 22:28

Aubin

14,617
9
61
84

I had high hopes after realizing I had missed out on closing the stream, but it doesn't seem to help. Any other ideas? – nik-v Oct 09 '12 at 00:31
This was required but in my case the reason was I wasn't closing the resultset after I had extracted all rows and simply reusing it again, which led to memory leak, as Munesh pointed out in the comments above. – nik-v Oct 09 '12 at 19:09

score 0 · Answer 3 · edited May 23 '17 at 12:14

0

You can increase the heap space by using the option -mx256m for 256mb heap space or -mx512m for 512mb and so on.

Search for increasing heap space by setting VM arguments on net.

this might help

Increase heap size in Java

edited May 23 '17 at 12:14

Community

1
1

answered Oct 11 '12 at 12:40

Bhavik Shah

5,125
3
23
40

That there is a memory leak was evident. I wanted ideas on where it could be, which Munesh answered in comments above. – nik-v Oct 12 '12 at 14:18

java out of memory Exception (jdbc)

3 Answers3