Large JDBC ResultSet Passed to Another thread doesn't release JVM memory

Question

I have a program that issues a query that returns roughly 150k datapoints in a result set for each of 30 queries. I'm trying to clean up the memory from processing which pushes the JVM up to 130MB RAM usage. There are two threads, one that issues the database query and gets the results and another that does the processing of the results.

The first thread takes the result set and deep copies it into a List>, which is put onto a shared Queue between the threads. The second thread takes off of this and then sets it to null. (Currently for memory usage testing). The first thread surrounds the DB call with a finally that sets the statement and result set to null. Commenting out the queue and 2nd thread, memory is 70MB usage. But when I put it back in, memory stays at 130MB usage. Any ideas why it doesn't go back down to 70?

 Statement stmt = null;
        ResultSet rs = null;
        try
        {
            if (conn.isValid(DB_TIMEOUT))
            {

                String query = msg.getQuery();
                Object request = msg.getRequest();

                // errorLog.debug("Query Issued to Trend Database: " + query);
                stmt = conn.createStatement();
                if (!query.isEmpty())
                {
                    stmt.execute(query);
                    rs = stmt.getResultSet();
                }
                TrendApp.resultSetProcQueue.put(new ResultData((jTrendDataRequest) request, resultSetToArrayList(rs)));
} catch (SQLException e)
        {
            errorLog.error("Failed to issue database command: " + e);
        } finally
        {
            if (stmt != null)
            {
                try
                {
                    stmt.close();
                } catch (SQLException e)
                {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (rs != null)
            {
                try
                {
                    rs.close();
                } catch (SQLException e)
                {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

On the processing side:

while (true)
    {
        ResultData result = null;
        try
        {
            try
            {
                result = (ResultData) TrendApp.resultSetProcQueue.take();

            } catch (InterruptedException e)
            {
                errorLog.error("Unable to fetch message from Data Adapter processing queue.");
            }
} finally
        {
            if (result != null)
            {
                result.setData(null);
                result = null;
            }
        }

    }

Here is the resultSet to ArrayList conversion

public List<HashMap<String, Object>> resultSetToArrayList(ResultSet rs) throws SQLException
{
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();

    while (rs.next())
    {
        HashMap<String, Object> row = new HashMap<String, Object>(columns);
        for (int i = 1; i <= columns; ++i)
        {
            row.put(md.getColumnName(i), rs.getObject(i));
        }
        list.add(row);
    }
    return list;
}

You need to read up on Java GC. Basically, depending on the JVM settings, Java will use as much memory as it could and only when it think it running low then it call GC to clean up. To reclaim the memory footprint you need to call explicit GC (System.gc()). You can do this manually via JMX console. Again, you need to read up on this. — Minh Kieu, Jun 30 '17 at 16:20
Yeah I thought it might be doing that. It is just that it is a very small footprint system so I need to keep usage as low as possible. (Just as long as it's not something that I am explicitly doing or forgetting to clear that is causing memory buildup) — Tacitus86, Jun 30 '17 at 16:21
" To reclaim the memory footprint you need to call explicit GC" -- Note that calling `System.gc()` suggests to the JVM that it should clean up. The JVM probably will, but may defer. See https://stackoverflow.com/questions/66540/when-does-system-gc-do-anything — bradimus, Jun 30 '17 at 16:23
Is there something I can look at in my googling with these JVM settings to say, "Keep Memory as low as possible" with regard to the GC? — Tacitus86, Jun 30 '17 at 16:23
jvm setting: `-Xmx` https://stackoverflow.com/questions/1493913/how-to-set-the-maximum-memory-usage-for-jvm — bradimus, Jun 30 '17 at 16:25
You can read up on ParallelGC and ParallelOldGC. This GC will constantly be running and will clean up and freed up unused objects. — Minh Kieu, Jun 30 '17 at 16:25
`Xmx` and `Xms` set the limits on the heap size, not the stack. — bradimus, Jun 30 '17 at 16:29
Well explicitly calling the GC lowered it to 100MB from 130 at least. Still not sure where the rest is going. — Tacitus86, Jun 30 '17 at 16:42
Why two threads? There is no apparent advantage. Do it all in one and lose the `List`. That will certainly save memory. — user207421, Jun 30 '17 at 22:42
EJP, this is part of a much larger program. I paired it down because this is the portion that is causing the large memory usage and it would have filled the question with a lot of unnecessary explanation. To keep it simple, its a data trending program with 3 threads, one that reads requests from the client, one that services the requests and one that replies. — Tacitus86, Jul 01 '17 at 23:43

score 0 · Answer 1 · edited Jul 13 '17 at 18:32

The VM will attempt to retain your peak size. Java performs better with more memory and does a good job of cleaning up when it needs to.

For your specific problem (Limiting total memory use), just set -Xmx to the max you want it to allocate rather than trying to manually clean up variables. Java will do the rest for you. It really is not a good idea to try to allow some kind of extra allocation for a "Peak" usage then force it back down.

What you do have to worry about is memory continually growing. A profiler can tell you if objects aren't being released, but you can do your own mini-memory check by calling System.gc() twice then printing out the amount of used memory (total memory - free memory, there isn't an actual "used"). If you check this number after every iteration of a memory-intensive process it should not grow--it should hover around a specific value.

The gc calls needed to get accurate memory size will shrink your memory usage and therefore you do NOT want to do it in production. It will mess up Java's whole memory allocation/optimization system.

Large JDBC ResultSet Passed to Another thread doesn't release JVM memory

1 Answers1