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.

Answer 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)

Answer 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.

Answer 3

Try splitting your data into a number of result sets. Think about what you want to do with the data once you got it back from the database. The result set is too large to fit in heap space.

Answer 4

Must the whole result from the query be in memory? If yes then you should buy more RAM. If not, then partitionning the problem will be the proper solution. You can even let the database consolidate the data for you in many cases.

If your solution to a given problem doesn't scale well with the amount of data present you will return to this problem even by extending your RAM. So the really good solution is to avoid the situation at all.

Answer 5

Is Lazy Load applicable? Can you use an upper limit to bound the results of a piece-wise query?

Answer 6

I discovered that the MySql driver has a memory leak. I use it with tomcat in a connection pool setup. Loads of JDBCResultSet and StatementImpl objects are in the heap. Setting the maxAge in the datasource configuration (context.xml) in tomcat helped.