MySQL Bugs: #3569: OutOfMemoryError

Bug #3569	OutOfMemoryError
Submitted:	26 Apr 2004 15:18	Modified:	21 Dec 2004 0:00
Reporter:	Alexander Petrov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	3.0.11	OS:	Windows (windows XP/ Linux mandrake)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
  The sequence :
ResultSet result;
some stuff here...
int i=0;
while (true)
{ 
 result.movetoinsertrow();
 result.putString(1,i+"");
 result.insertRow();
 ++i;

}

This sequense leads to OutOfMemoryError. The resultSet is Scroll Sensitive and Updateable. This sequense has been tested on 3 windows machines and one linux. The same sequens was tested true Odbc in microsoft acces and jconnector thrue ODBC the result was correnct, no errors reported.

The number of properly inserted record before OutOfMemoryError depend on available memory. On a 700mhz pentium with 256 ram bug comes about 100 000 records.

 
 

How to repeat:
The sequence :
ResultSet result;
some stuff here...
int i=0;
while (true)
{ 
 result.movetoinsertrow();
 result.putInt(1,i);
 result.insertRow();
 ++i;

}

The driver needs to store the result set in memory when you use it in this manner. You need to increase the heap size of your JVM to account for the amount of rows you will insert using updatable result sets.

If you are inserting large amounts of data, it is much more efficient to just use regular "INSERT" statements.

Dear Mark,
I stil do not agree with u. Let's say we have  an empty table with 100 fields ot char(10) this makes 100bytes.
I set -Xms256 -Xms512   And I recieved OutOfMemoryError on a machin with 128ram after 140000 inserts. Excuse me my program works correctly with  tree other drivers. What's so special about this one?
 Anyway what should I do to make updateable resultset working? It seams that enlarging heap is only temporaly solution.

> I stil do not agree with u. Let's say we have  an empty table with 100
> fields ot char(10) this makes 100bytes.
> I set -Xms256 -Xms512   And I recieved OutOfMemoryError on a machin
> with 128ram after 140000 inserts. 

There is overhead above and beyond just the data that is stored for _every_ row, so there is more memory used then you are accounting for. There is also memory allocated for network communications for _every_ row, which can depending on how your JVM is doing GC will not be reclaimed unless you have the 'headroom' to do it.

> Excuse me my program works correctly
> with  tree other drivers. What's so special about this one?

You're not using the best API for getting the job done. Updatable result sets are _not_ intended to be used to update thousands of rows. Each row update will require a round-trip to the database..>Are you absolutely sure you want to be doing this? A standard multiple-value INSERT statement will be orders-of-magnitude faster.

>  Anyway what should I do to make updateable resultset working? It seams
> that enlarging heap is only temporaly solution.

Which three other drivers are you refering to? ODBC doesn't support 'updatable result sets', and I'm not sure what 'jconnector thrue
ODBC' means.

Until MySQL has server-side cursors, updatable result sets are not going to be appropriate for your case (and even when server-side cursors exist, I would still argue that updatable result sets most likely won't be appropriate for this kind of usage).

I use the jdbc-odbc bridge driver and it suports updatable resultset. I've also tried MySQL ODBC 3.51 driver and NetDirect driver for Microsoft Access. They all support updatable resultsets and my program runs correctly. I'm not sure that simple insert statements are faster than resultset. I have tested the speed of both methods through jdbc-odbc bridge and NetDirect driver and the result is resultset is aproximatly 2 times faster than simple insert.
  As a conclusion I think that the memory managment of connector/j is not quite well made since it allows such errors.

Unfortunately, Connector/J is not designed to be used in the way you are using it. It is optimized for a use-case of small to medium size result sets. The design that is used is by some called 'disconnected' result sets, i.e. server resources/locks, etc. are freed before a return from a Statement.execute*() method call because the driver reads all rows. This design allows for maximum concurrency in an application, which is what the majority of users and customers are asking for.

Because of this design decision there is a memory overhead for every row you create with insertRow(), because it has to be stored in memory on the client side. 

When not using updatable result sets, you can use 'forward-only' result sets to get around this issue and only store one row at a time client side. However, until the MySQL server itself has updatable cursors, large updatable result sets can not be correctly nor efficiently implemented in the JDBC driver. Therefore I'm turning this bug report into a deferred feature request, contingent on the server supporting the functionality that is needed to do this correctly and efficiently.

Have you tried actually accessing the result data on the insert row when using the JDBC:ODBC driver? My tests show that you can't unless you re-open the result set (so the functionality there can't be considered complete).

As far as INSERT performance goes, I just benchmarked your case using 'extended insert' syntax, and received around 750 rows inserted per-second on my laptop (which would be much higher on 'server' class hardware). I find it hard to believe that using updatable result sets that will issue one query per row inserted will reach that level of performance, no matter how they're implemented.

Dear Mark,
thank you very much for your support. I tested the 'extended insert syntax' and I find it very usefull and fast (approximately 2-3 times faster than result set). Now I see why result set is not made for the way I use it. Anyway there is something good in result set: you don't need to construct the sql statement, and you can put and get directly. Result set is also binary data, and if it is possible to send only once a binary packet it would be quite fast. 
  I have tried accessing the result data on the insert row when using
the JDBC:ODBC driver and the answer is: No, that kind of action is not supported.
  Once again thank you very much for the fast and quite in time answeres I've learned a lot of thing i didn't know before.