RE: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> -----Original Message-----
> From: Paul Palaszewski [mailto:paul.palaszewski@bdm-systems.com]
> Sent: Monday, August 27, 2007 3:03 PM
> To: java@lists.mysql.com
> Subject: Re: Serious OutOfMemory issue while reading with
> mysql-connector-j-5.0.7 ...
>
> A.J. Blanchard (ajblanch) schrieb:
> > Hum,
> >
> > I had this problem long ago on a much older version of
> drivers/engine. It amounted to the client side trying to read
> in the entire ResultsSet into the local JVM. Turned out we
> had to limit the ResultSet size to 30,000 some odd size to
> prevent the OOM Exception (LIMIT).
> >
> > Recently, we have been able to at least get the row count
> and seems the data was streaming. We did nothing special (in
> options) to achieve this. But that was pre-5.x. I have not
> run this specific test lately.
> >
> > Just to see if you can avoid the OOM, try LIMIT and heap up
> with the popular -Xmx512M (if I recall the syntax correctly).
> >
> > A. J.
> >
> Thanks for your feedback. I'll try some 3.x drivers ... I've
> tried to avoid that, since 5.x drivers handled several things
> like stmt.cancel() or meta data character enconding better,
> but we still have the work arounds in our database abstraction layer.
>
> The exception occured with a table that has 200+ columns and
> ~3 mio records. Sure, if we read less data or add more
> memory, it will work somehow - but then, what's the point in
> using mysql as database at all, if it's driver can not handle data?
>
> Adding memory is not an option. The application is a
> datawarehouse which runs on many platforms and is highly
> optimized. I mean it has a lot real good optimizations
> regarding speed and memory footprint. Under load it usually
> uses only 32-64M - so I definitely won't add 512mb to that to
> get troubles with our customers and encounter another
> exception with 6 mio recs.
>
> @Mark/MySQL .. has anyone already looked at the issue? what's
> the status?

Hi Paul,

The root of the issue is that there is a "clash" with how MySQL's network protocol works, and the JDBC spec.

The JDBC specification says that applications can open as many result sets as they want on a given connection. MySQL only allows one open result set per connection. If you want to issue another query on a given connection while a result set is still open, you have to read all of the rows first. (it's a request/response protocol)

Given that 98% of the applications that use MySQL are OLTP-ish (or they can use the LIMIT clause to window their data), we made the optimization to read in all of the results for a query before returning to the client application with our JDBC driver. For the overwhelming majority of our users this is an optimization, because their result sets are small (a few hundred rows at most), it reduces network traffic (because there are no "acks" or "send me more rows" calls) and it also ends up freeing locks quicker (this is an optimization that _many_ databases use, it's usually called "firehose" mode), and doesn't use complicated, error-prone threadsafe bookeeping to ensure that only one result set is actively being used by a given connection.

For those very few cases where one needs access to millions of rows, I suggest using the MySQL-specific method of "streaming" a result rather than server-side cursors if you can. Server-side cursors in 5.0 (and 5.1) _always_ materialize a temporary table and read from that, which can have I/O overhead. If have "plugins" or database-awareness in your application, and only need to have one result set open per connection, then you can use the "streaming" mode, which you can do by either setting the fetch size to Integer.MIN_VALUE on the statement, and asking for FORWARD_ONLY cursors, or by casting to a com.mysql.jdbc.Statement and calling enableStreamingResults().

I'm a little bewildered at why cursors aren't working in your situation, since that should be mostly transparent to your application, and they're working for others. What _exact_ version of the driver are you using? You said you saw things setup correctly in the debugger, I'm wondering where you had breakpoints? If I were debugging this myself, I'd set one in ServerPreparedStatement.serverExecute(), walk through the execution of the statement, and also look at the RowData instance that the result set holds, it should be a "CursorRowProvider". If you're not getting ServerPreparedStatements from Connection.prepareStatement(), or not getting CursorRowProviders, then I'd say somewhere there's a disconnect between the JDBC configuration parameters your setting for your application and them getting set internally in the driver.

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

        -Mark

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)

iD8DBQFG1e9ztvXNTca6JD8RAjjqAJ9Ny6mlTlNosdNjikOIRRMTTPtHsACeMz7k pxKNyRqyhaIlRQG7oV4rK0k=
=GHAp
-----END PGP SIGNATURE-----

-- 
MySQL Java Mailing List
For list archives: 
http://lists.mysql.com/java
To unsubscribe:    
http://lists.mysql.com/java?unsub=lists@pantek.com