Java Heap Memory error

Question

I am getting this error:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1585)
    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1409)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2886)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:476)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2581)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1757)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2171)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1476)
    at DBase.connect(automateExport.java:31)
    at automateExport.main(automateExport.java:10)

I tried to increase the heap memmory space by opening eclipse.ini file and then changing

-Xms 256m and -Xmx 512m

But that did not help. I tried 512m and 1024m but that ended up giving error: Failed to start JVM and eclipse did not open up.

I tried doing the same on cmd line:

java -Xms 256m and -Xmx 512m

and also eclipse -vmargs -Xms 256m and -Xmx 512m But still no help. I am basically creating a JDBC connection to query the database for very large set of records. Please help me.

Solution 1

When you query returns very large sets, the mysql driver will by default load the entire result set in memory before giving you control back. It sounds like you're just running out of memory, so increase the memory furter, e.g. -Xmx1024M

The other alternative might be to enable streaming results on your MySQL queries- this prevents the entire ResultSet to be loaded into memory all at once. This can be done by doing

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, 
           java.sql.ResultSet.CONCUR_READ_ONLY);//These are defaults though,I believe
stmt.setFetchSize(Integer.MIN_VALUE);

(Note that anything else but Integer.MIN_VALUE has no effect on the MySQL driver)

Solution 2

You don't need to worry about -Xms too much - that's the initial Heap size on startup.

-Xmx is the max Heap size. Try increasing it until either you don't get the OutOfMemory exception anymore, or until you run out of memory.

If you run out of memory, you can't load the entire ResultSet at once, and you'll need to apply the approaches that others have mentioned.