Home › Blogs › pulsar's blog

MySQL, Large Result Sets and OutOfMemory related Headaches

Wed, 09/16/2009 - 21:55 — pulsar

Just in case you run into OutOfMemory Exceptions while requesting a large data chunk from the MySQL: the JDBC driver will load ALL (yes, ALL) rows before passing it to your fancy, agile and low-footprint routine. Tweaking the fetchSize property of a statement won't do any good either... well, not without some voodoo. So, here is how you can get the JDBC driver to get you a nice and tight StreamingResultSet:

  1. Connection connection = dataSource.getConnection();
  2. Statement st = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
  3. st.setFetchSize(Integer.MIN_VALUE); // Inter.MIN_VALUE <- and ONLY this value, 1,5 or 100 won't fix your problem.
  4. ResultSet rs = st.executeQuery("select * from someREALLYHugeTable");

Oh, and you clowns out there saying "this is normal, just bump up the memory settings for your JVM" - are you NUTS!? Or do you just like your applications exploding out of nowhere after being in production for some time?

Your rating: None Average: 3.5 (2 votes)

Comments

Thu, 09/24/2009 - 08:02 — tu (not verified)

for the bug see

for the bug see http://bugs.mysql.com/bug.php?id=18148

you could implement paging:

  1. int count = select count(*)
  2. for (int i = 0; i < count/100 +1 ; i++){
  3.       rs = select * Limit i,100
  4. }

Thu, 09/24/2009 - 11:55 — pulsar
pulsar's picture

sure I could

But wouldn't you consder this being a workaround for a much deeper problem / issue? Not all queries being affected by that default behaviour will be that simple. What I am saying is that you should be very aware of this issue.

It gets even more confusing when you read the JDK's notes about a Result-Set (http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html):

"A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row."

This one indicates that the specification of the JDBC framework is exactly the opposite to the MySQL JDBC driver's behaviour. What I find even more confusing ist the setFetchSize Propoerty which has no effect with the default MySQL JDBC Settings (http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#setFetch...):

"Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. The default value is set by the Statement object that created the result set. The fetch size may be changed at any time."

So, works as designed but not specified, eh?

Thu, 09/24/2009 - 13:49 — tu (not verified)

just for the sake of

just for the sake of argument:

it states

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.

( http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html)
So it is a table, not a single row or FetchSize() numbered rows. It is a representation of your result set. How the result set is implemented internally is not specified.


This one indicates that the specification of the JDBC framework is exactly the opposite to the MySQL JDBC driver's behaviour.

No, it is as specified. Cursor is only a pointer to the current row. The restriction to move only forward does not prevent returning the full dataset at once.
As for the fetch size:

Gives the JDBC driver a hint as to the number of rows

( http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html)
It is only a hint. You are free to follow it or not.

So the driver works as specified.


What I am saying is that you should be very aware of this issue.

Right!

Perhaps what is is missing in the spec for DatabaseMetaData is

  1. public boolean supportsFetchSize()

and in ResultSet
  1. TYPE_STREAMING

My suggestion is really only a workaround, and is not appropiate in all cases, and sometimes not even possible (e.g. in Transaction with non-repeatable/dirty read, or where the limit-clause is not supported from db).

But you can be sure how much data will come, before it is coming.