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.

Answer 1

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.

Answer 2

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

Answer 3

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