How to avoid out of memory heap space error in java when trying to retrieve a million rows from a mysql table?

Question

I get a out of memory heap space error at- oRsSelect = oPrStmt.executeQuery(); This is the function that retrieves a million records from a mysql table called 'snomedinfo_data':

public String getSnomedCodes()
{
    Root oRoot = null;
    JSONObject oJsonSno = null;
    JSONObject oJson = null;
    JSONArray oJsonArr = null;
    ResultSet oRsSelect = null;
    PreparedStatement oPrStmt = null;
    String strSql = null;
    String snomedcode=null;
    String snomeddesc=null;
    String str=null;
    int cStart = 0;

    try {
        oJsonSno = new JSONObject();
        oJson = new JSONObject();
        oJsonArr = new JSONArray();
        oRoot = Root.createDbConnection(null);
//retrieving data from table        
        strSql = "SELECT * FROM snomedinfo_data   ;";

        oPrStmt = oRoot.con.prepareStatement(strSql);


        oRsSelect = oPrStmt.executeQuery();
        while (oRsSelect.next()) {
            snomedcode = Root.TrimString(oRsSelect.getString("conceptid"));
            snomeddesc = Root.TrimString(oRsSelect.getString("term"));
            oJsonSno = new JSONObject();
            oJsonSno.put("snomedcode",snomedcode);
            oJsonSno.put("snomeddesc",snomeddesc);
            oJsonArr.put(oJsonSno);
        }
        oJson.put("status", "success");
        oJson.put("snomeddata", oJsonArr);
        str = oJson.toString(); 
    }
    catch (Exception e) {
        e.printStackTrace();

    }

    finally {

        oRsSelect = Root.EcwCloseResultSet(oRsSelect);

        oPrStmt = Root.EcwClosePreparedStatement(oPrStmt);
        oRoot = Root.closeDbConnection(null, oRoot);
    }

    return str; 
}




I get a out of memory heap space error at- oRsSelect = oPrStmt.executeQuery();

I have tried using -


 strSql = "SELECT * FROM snomedinfo_data   ;";
            oRoot.con.setAutoCommit(false);
            oPrStmt = oRoot.con.prepareStatement(strSql);
            oPrStmt = oRoot.con.prepareStatement(strSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        oPrStmt.setFetchSize(Integer.MIN_VALUE);
            oRsSelect = oPrStmt.executeQuery();

But it still does not work and continues to give the out of memory heapspace error.please help!!

What is being done with that many records? For example, can you push any aggregation in the java code to mysql? If adding more memory is not an option, then consider using [pagination](https://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging) to process a subset of the records at a time so that not all records have to be in memory at the same time. — Andrew S, Aug 02 '18 at 17:08
You're loading all the data into memory. You need to generate the JSON in a streaming fashion. Not knowing which JSON library you're using, it's difficult to suggest how. — Andreas, Aug 02 '18 at 17:17

The Impaler · Answer 1 · 2018-08-02T21:07:09.167

As far as I remember, MySQL does not support result set buffering.

This means MySQL sends you ALL rows back at once and that's it. Theoretically fetchSize is defined to deal with these cases, but it's only a suggestion to the JDBC driver and the RDBMS.

According to the related answer by Gord Thompson, apparently MySQL can adhere to the fecthSize if configured appropriately. This requires to set the parameter useCursorFetch=true in the JDBC connection URL. You'll need to test if your version of MySQL and your version of the JDBC driver abide by it.

Other solutions? Well... I haven't tested these ones but at least I can describe them, so you can see if they are worth the effort:

Use a cursor. Retrieve a cursor instead of a simple SQL select. Then you can retrieve and process rows in smaller chunks.
Use pagination. Some queries naturally support pagination. They need to be ordered by a unique set of columns. This way you execute the query multiple times, so you get 10000 rows at a time only. Big down side: If the data is being constantly modified this can give you an inconsistent result set. The original single SELECT query is fully transactional, while this solution IS NOT. If this is for a nightly process when the table is not modified, then this down side may not be relevant.

The big down side of both solutions is that they require more work. More application effort, more SQL effort, more debugging effort. If it's too much maybe you should consider upgrading to PostgreSQL, DB2, or Oracle, that do implement buffering properly.

Alternatively MariaDB may support it (and is very similar to MySQL) but I wouldn't bet on it.

Yes, MySQL Connector/J does support "streaming" when retrieving a ResultSet. See [this answer](https://stackoverflow.com/a/35412659/2144390) for details. — Gord Thompson, Aug 02 '18 at 18:58
@Gord Thompson - Your test looks promising. I'll test with my local instance since it can be very useful for some queries I have. — The Impaler, Aug 02 '18 at 21:08

score 0 · Answer 2 · answered Aug 02 '18 at 17:17

0

You could simply increase the memory heap size by passing in parameters to the JVM:

-Xmx2G

The "2G" stands for 2GB of memory. You can use ‘G’ for GB, ‘M’ for MB and ‘K’ for KB. Try it out and use the amount you need and have available. You can do some reading on it here

If it is still using a huge load of memory, you could think to think about is to enable streaming results for MySQL queries, so the ResultSet is not fully loaded into the memory at once - At costs of performance, of course:

oPrStmt = oRoot.con.prepareStatement(strSql);
oPrStmt.setFetchSize(Integer.MIN_VALUE); // Code to use minimum fetching size
oRsSelect = oPrStmt.executeQuery();

answered Aug 02 '18 at 17:17

RKrum

298
4
14

Setting `fetchSize` will not help because all objects generated from result set rows are stored in memory in one big collection – Ivan Aug 02 '18 at 17:24
i tried setting Xms2G and iam getting a "unable to load java virtual machine" message when i start eclipse again!! – Vignesh Swaminathan Aug 02 '18 at 17:32
If you are executing from Eclipse, add it in the Run Configurations.. > VM Arguments. The correct would be "-Xmx2G", not "Xms2G" – RKrum Aug 02 '18 at 17:49
@Ivan re: "Setting fetchSize will not help ..." - The *default* behaviour of MySQL Connector/J is to load all ResultSet rows into memory, but `setFetchSize(Integer.MIN_VALUE)` can potentially prevent that. It doesn't necessarily retrieve the rows one at a time, but it does limit the number of rows sent by the server each time it sends a "bunch". See [this answer](https://stackoverflow.com/a/35412659/2144390) for details. – Gord Thompson Aug 02 '18 at 18:57
@GordThompson if results are streamed it is better to process data as you get it not accumulate whole collection like `while (rs.next()) { oJsonArr.put(oJsonSno); }` – Ivan Aug 02 '18 at 19:13
@Ivan - Quite right. It appears that I misinterpreted your comment. Sorry about that. – Gord Thompson Aug 02 '18 at 19:18

How to avoid out of memory heap space error in java when trying to retrieve a million rows from a mysql table?

2 Answers2