memory leak when executing sql query using postgres and java

Question

I need to run some queries using java on postgres DB. The problem is when I execute a query using this code me memory increase and even I close the statement and the result set the memory still increasing. Off course the GC will be executed, but the problem is that sometimes the GC is executed in the middle of a query execution and it change the time execution of my query. How I can solve this problem to prevent GC executing in the middle of SQL query and it is possible to prevent memory leak in my case?

enter image description here

 public long getStatementExecutionTimeInMilliseconds( final String sqlQuery, 
final int fetchSize, final boolean fetchAttributesFlag,
            Integer numberOfSelectedFields) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        int numberOfResult = 0;
        if (numberOfSelectedFields == null) {
            numberOfSelectedFields = getNumberOfSelectedFields(sqlQuery);
        }
        long start = 0;
        try {
            stmt = createStatement (fetchSize);
            stmt.executeQuery (sqlQuery);
            start = System.currentTimeMillis ();
            rs = stmt.executeQuery (sqlQuery);
            while (rs.next ()) {
                if (fetchAttributesFlag) {
                    for (int i = 1; i <= numberOfSelectedFields; i++) {
                        rs.getInt (i);
                    }
                }
            }

            stmt.close ();
        }

        finally {
            DatabaseUtils.tryClose(rs);
            DatabaseUtils.tryClose(stmt);
        }
        //System.out.println (numberOfResult);
        final long executionTimeInMilliseconds = System.currentTimeMillis() - start;


        return executionTimeInMilliseconds;
    }

Why are you executing the statement twice? And why do you think that memory profile is indicative of a memory leak and not just normal system usage? — JonK
– JonK, Commented Aug 28, 2019 at 14:58
i close the statment just to be sure that statement will be released, with the memory even I close the statement and the result set the memory still increase and it happen only when I execute the query. Why the execute(query) consume a lot of memory and didn't release it. — beyfort
– beyfort, Commented Aug 28, 2019 at 15:06
Clearing away old objects is the job of the garbage collector, and it does it whenever it determines that it needs to run. Until it runs the objects will stick around on the heap waiting to be collected. That doesn't mean there's a memory leak. — JonK
– JonK, Commented Aug 28, 2019 at 15:09
Ok. But I need to execute the same query three times for example, the problem is that in some cases the GC will be executed in the middle of the execution which has an impact on the execution time. — beyfort
– beyfort, Commented Aug 28, 2019 at 15:12
Reducing the memory footprint of the SQL execution, while useful, isn't going to prevent the GC from doing full collections, it will only make them less common and so reduce the likelihood that it will happen during a query. You should probably look at the CMS and G1 garbage collectors to see if they work better for you. It's also worth looking into paginating the results that you pull back from the database. Larger pages mean more results held in memory, but fewer round-trips back to the DB to get the next page of data. — JonK
– JonK, Commented Aug 28, 2019 at 15:23

Laurenz Albe · Accepted Answer · 2019-08-28 15:55:31Z

2

The PostgreSQL JDBC driver snarfs the whole result set into RAM by default.

To change that, set the fetch size to a value different from 0. Then cursors will be used, and memory consumption should be stable.

From the documentation:

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);

answered Aug 28, 2019 at 15:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

beyfort Over a year ago

Thanks @laurenz I fixed the fetchsize and tuned it but the consumption still increase. It seems that for each query even you close everything, data still remains in the RAM.

Laurenz Albe Over a year ago

Then that is normal garbage that accumulates throughout the life of a Java program. You can try to program so that you create less garbage, but that only works to a certain extent. You can try to give Java less memory, then the garbage collection will have to be more frequent and might hurt less. Or you switch to a programming language where you can control memory management, like C.

Collectives™ on Stack Overflow

memory leak when executing sql query using postgres and java

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related