Java Machine crashes for too big MySQL result.

Question

I am running out of memory when I am trying to read a huge list. This is the code that crashes the server

query = "SELECT * FROM huge_list ORDER BY id ASC";
statement = this.database.createStatement();
results = statement.executeQuery(query);

This is the error

Exception in thread "Thread-2" java.lang.OutOfMemoryError: Java heap space

the error points to the results line. Any advice on how to avoid that? Perhaps load the list using Limit? Could the size of the list cause an error?

Yes, either add LIMIT constraint or provide Java VM more memory with -XMm (or something like that) flag — kirilloid, May 04 '14 at 06:19

score 1 · Answer 1 · answered May 04 '14 at 06:19

You could may change the memory settings of your jvm, this could fix your problem but in general you should not load all data at once. Better load always let's say 100 rows and query then the next 100 rows.

Just for security I would store the smallest ID and query that again with limit the next brunch of rows. So this will not break your logic if new rows are added.

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

I believe you should be streaming the MySQL results. See Reading large amount of records MySQL into Java

Your other option would be to increase the maximum RAM allowed by the JVM, but that's probably not what you want to do.

edited May 23 '17 at 11:50

Community

1
1

answered May 04 '14 at 06:23

Christopher D. Long

326
2
8

score 0 · Answer 3 · answered May 04 '14 at 06:25

Try to modify the query to return only those records which you are displaying/processing to the user. You can use limit by clause. One more thing state your column names, SELECT * isn't really needed, are you really processing all the cols that your query returns?

Java Machine crashes for too big MySQL result.

3 Answers3