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

Hi Mark!

Big thanks for your detailed response. I've tested with Connector/J 
5.0.3 + 5.0.7 and debugged 5.0.7.

Regarding my debug session. The OutOfMemoryError-Stacktrace showed the 
execution path up to the readAllResults-method which lead me to 
com.mysql.jdbc.PreparedStatmenet#executeQuery and 
#createStreamingResultSet() , which decides, if all results have to be 
read at once or if a streaming resultset should be used.

    protected boolean createStreamingResultSet() {
        return ((this.resultSetType == java.sql.ResultSet.TYPE_FORWARD_ONLY)
                && (this.resultSetConcurrency == 
java.sql.ResultSet.CONCUR_READ_ONLY) && (this.fetchSize == 
Integer.MIN_VALUE));
    }

So I've set a breakpoint at com.mysql.jdbc.PreparedStatmenet line 1448, 
which is in the executeQuery() right before createStreamingResultSet() 
is called. There I could very, that setting useCursorFetch=true did also 
set useServerPrepStmts=true. My resultSetType was TYPE_FORWARD_ONLY and 
the concurrency CONCUR_READ_ONLY - but my fetchSize was 0 or 50 - 
depending on the test-run where I had some with fetchSize set to 50 and 
some, where I did not set it.

As you suggested, setting fetchSize to Integer.MIN_VALUE fixed the 
OutOfMemoryException. During my first the debug session I've seen the 
MIN_VALUE, but I've had a look at the javadoc for setFetchSize which 
states ...
     * @exception SQLException if a database access error occurs, or the
     *        condition 0 <= <code>rows</code> <= 
<code>this.getMaxRows()</code>
     *        is not satisfied.
     * @since 1.2

So I would never have tried setting something which should cause a 
SQLException. I've assumed, that the MIN_VALUE is only used internally.
Does anyone have ideas, how this could be made more intuitive without 
breaking compatibility?

Further, as you explained, copying the data to a temp table is really 
slow and it only works, as long as there is enough disk space for the 
temp table.

Hm ... up to now I really like working with mysql's myisam tables since 
they are usually really fast. I almost understand the necessity to copy 
data to a temp table on the server to provide some kind of isolation for 
the cursor. Yet I thought that mysql with myisam tables does not provide 
any transaction handling and therefor no isolation - which was fine for us.

I will tell my customers, that they may need more disk space when they 
want to import from mysql and that it's as fast, as it can get. What are 
the chances, that cursors are enhanced on the server?

Best regards,
Paul
> 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 ge
 tting set internally in the driver.
>
> 	-Mark
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (MingW32)
>
> iD8DBQFG1e9ztvXNTca6JD8RAjjqAJ9Ny6mlTlNosdNjikOIRRMTTPtHsACeMz7k
> pxKNyRqyhaIlRQG7oV4rK0k=
> =GHAp
> -----END PGP SIGNATURE-----
>
>
>   


-- 
--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/
_/   BDM Business Data Management GmbH
_/
_/   Firmenbuch: FN 242059w
_/   Gerichtsstand: Landesgericht Wiener Neustadt  
_/   UID: ATU57685758
_/
_/   Grenzgasse 111, Objekt 9/4
_/   2340 Mödling
_/   Tel,Fax: +43 2236 46 393
_/
_/   Ing. Paul Palaszewski, (Hons) B.Sc.
_/   Geschäftsführer
_/   Mobil: +43(650) 37 36 030
_/   E-Mail: [email protected]
_/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 


-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]