MySQL Lists are EOL. Please join:

List:MySQL and Java« Previous MessageNext Message »
From:Mark Matthews Date:August 29 2007 10:13pm
Subject:RE: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...
View as plain text  
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> -----Original Message-----
> From: Paul Palaszewski [mailto:paul.palaszewski@stripped] 
> Sent: Monday, August 27, 2007 3:03 PM
> To: java@stripped
> 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.

	-Mark


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

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

Thread
Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7...Paul Palaszewski23 Aug
  • RE: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...Robert DiFalco24 Aug
    • Re: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7...Paul Palaszewski24 Aug
      • Re: Serious OutOfMemory issue while reading withmysql-connector-j-5.0.7 ...Ronald Klop27 Aug
        • Re: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7...Paul Palaszewski27 Aug
          • RE: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...Robert DiFalco27 Aug
            • RE: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...A.J. Blanchard \(ajblanch\)27 Aug
              • Re: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7...Paul Palaszewski27 Aug
                • RE: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...Mark Matthews30 Aug
                  • Re: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7...Paul Palaszewski31 Aug
                    • RE: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...Mark Matthews31 Aug