-
Task
-
Resolution: Done
-
Critical
-
6.0.0.Final
-
None
Iterating over a huge ResultSet with MySQL seems to keep all the rows in memory: https://community.jboss.org/message/854402
We should investigate whether we can limit the memory usage to a fixed amount and whether this affects other drivers as well.
[ISPN-3937] Investigate the memory usage of JDBC cache stores when processing a large result set
It looks like setting the ```MIN_VALUE``` is not enough (http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html). However, after reading the docs, the disadvantages are worrying me a little... any other option?
Unless it was fixed in a recent driver release, setting useCursorFetch=true won't work! Be cautious.
I'd say we should set MIN_VALUE by default if it's MySQL.
The fetch size is already exposed in the JDBC store configuration, so a user could work around the issue with storeBuilder.table().fetchSize(Integer.MIN_VALUE). MySQL 5.0.2+ also has a useCursorFetch=true connection property that allows the driver to use any fetch size, not just Integer.MIN_VALUE: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
It would be nice to have a better behaviour by default, but I'm not sure what would be the best choice. If the connection property really works, I think we could require the user to set it, and warn on startup if it's not set.
We need to also look at the other tricks Hibernate uses in its SpecializedMySqlStringCacheStore: http://pastebin.com/4w0mAf65
E.g. there is another issue to use REPLACE INTO for MySQL: https://issues.jboss.org/browse/ISPN-686
Use
ResultSet.setFetchSize(Integer.MIN_VALUE)
deactivate's MySQL's "smart" behaviour
Setting the result set type and concurrency shouldn't be a problem.
Are you worried about the lock on the table being held while we are processing the results? process() doesn't do that much work on the caller's thread, so our iteration over the result set should be only slightly slower than the driver's fetching of the entire result set.