Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
Bleeding edge development for OJB v1.1+ is in CVS HEAD, but should also not be broken. OJB_1_0_RELEASE is maintenance for the last release OJB and is thus pre-1.0.4 at the moment. As a rule of thumb, OJB_1_0_RELEASE branch is currently as stable as v1.0.3 plus some bugfixes and new minor improvements like fetchSize hints. I would suggest that you run with a CVS snapshot from the branch instead of patching 1.0.3 with the changes. Regards, Martin ah, ok. I will do that. thank you! regards, andreas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
Andreas Bohnert wrote: I have just run several tests with different hints. It works very well, however, if I don't set a hint, no cursors are used, even with getIteratorByQuery! Good to hear that it's working well! But maybe that's not too bad. If you enable cursors on iterators by default, MySql users could run into troubles. I will put this on the TODO list as it was my intention that getIteratorByQuery should set fetchSize hints of 1 automatically. If that's not working it should either be fixed or backed out (which might be an option considering your MySQL info). just a question: can I apply your patch easily to the 1.0.3 sources? I don't know how stable the current OJB_1_0_RELEASE branch is. Bleeding edge development for OJB v1.1+ is in CVS HEAD, but should also not be broken. OJB_1_0_RELEASE is maintenance for the last release OJB and is thus pre-1.0.4 at the moment. As a rule of thumb, OJB_1_0_RELEASE branch is currently as stable as v1.0.3 plus some bugfixes and new minor improvements like fetchSize hints. I would suggest that you run with a CVS snapshot from the branch instead of patching 1.0.3 with the changes. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
hi martin, I have just run several tests with different hints. It works very well, however, if I don't set a hint, no cursors are used, even with getIteratorByQuery! But maybe that's not too bad. If you enable cursors on iterators by default, MySql users could run into troubles. I just found this on the mysql side: /There are some caveats with this /(using a cursor)/ approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown. Also, any tables referenced by the query that created the streaming result will be locked until all of the results have been read or the connection closed. *http://dev.mysql.com/doc/connector/j/en/cj-implementation-notes.html* / just a question: can I apply your patch easily to the 1.0.3 sources? I don't know how stable the current OJB_1_0_RELEASE branch is. regards, andreas Martin Kalén wrote: Have a look at http://issues.apache.org/jira/browse/OJB-31 This is now fixed (in beta state) in the OJB_1_0_RELEASE branch, where you can set a global max cap hint through the connection-pool descriptor in your repository. All Iterator objects should also set fetchSize hints of 1 internally in OJB -- so please give it a try without setting any hint at first, and see if you get better results automagically. Regards, Martin P.S. This will also be merged with the trunk (v1.1 pre) eventually. I will re-open the issue in JIRA and set the status there once it has been done so you can add yourself as observer of OJB-31 in JIRA to get mail notification on updates. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
Andreas Bohnert wrote: Postgres (for example) don't use cursors on default. The PG jdbc driver loads the entire ResultSet all at once and keeps it in memory! So, even if you do a getIteratorByQuery the memory load on a large resultset is huge! You can get around this, if you set the fetchsize on the jdbc statement for example: stmt = con.createStatement(); stmt.setFetchSize(1); ResultSet rs = stmt.executeQuery(sql); This actually forces the jdbc driver to use a cursor and browse the resultset one by one. There is no possibility to do this with ojb's getIteratorByQuery! Or is there? Have a look at http://issues.apache.org/jira/browse/OJB-31 This is now fixed (in beta state) in the OJB_1_0_RELEASE branch, where you can set a global max cap hint through the connection-pool descriptor in your repository. All Iterator objects should also set fetchSize hints of 1 internally in OJB -- so please give it a try without setting any hint at first, and see if you get better results automagically. Regards, Martin P.S. This will also be merged with the trunk (v1.1 pre) eventually. I will re-open the issue in JIRA and set the status there once it has been done so you can add yourself as observer of OJB-31 in JIRA to get mail notification on updates. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
hello martin, thanks for your quick response!! I will re-run again and check peak JVM heap usage before/after and bring it up on the development list. with 115000 big data objects and fetchSize=0 (=default behavoir=without cursor) 97% memory of my jboss system was used (jboss with -Xmx512m) during the select. with fetchsize=1 it was just 48% Do you see any drawbacks of an OJB-global default of fetchSize=1 for PostgreSQL? Since this is on an OJB global level for PostgreSQL I guess that 1 might be too low to strike a balance between network traffic and memory consumption? It might be possible to use fetch size 1 explicitly only for iterators, yes, even with an iterator, a fetchSize of '1' would be to low in generell. it's hard to find the best setting, but I guess '1000' would be better than '0'. it would be great, if the fetchSize could be set per query or broker. (This should not affect OJB proxy-prefetching-limit since that prefecthing is done one layer above the PostgreSQL JDBC level.) yes, I agree. I will bring the discussion up on the dev-list pointing to this thread and see what concensus is with respect to memory footpring vs network rraffic/JDBC overhead. thank you very much! andreas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
ok, sorry. I see. this was an proposal to set the fetch size! :) Andreas Bohnert wrote: hi jakob, Jakob Braeuchi wrote: hi all, ojb supports proprietary start- and endIndex for queries. this solutions does not use fetch size or limit hints. but this does not affect the way jdbc selects the results and the memory consumption on jdbc side is still the same. or do I miss something? regards, andreas jakob Martin Kalén schrieb: Martin Kalén wrote: It might be possible to use fetch size 1 explicitly only for iterators, I will have a look at this too. This is a bigger refactoring since it affects internal statement creation and would need many internal methods' signature to be expanded with a flag and/or explicit size for fetch size hints. I will bring the discussion up on the dev-list pointing to this thread and see what concensus is with respect to memory footpring vs network traffic/JDBC overhead. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
hi andreas, yes this does not affect the memory footprint of the jdbc part but it prevents unused 'business' objects from being instantiated. jakob Andreas Bohnert schrieb: hi jakob, Jakob Braeuchi wrote: hi all, ojb supports proprietary start- and endIndex for queries. this solutions does not use fetch size or limit hints. but this does not affect the way jdbc selects the results and the memory consumption on jdbc side is still the same. or do I miss something? regards, andreas jakob Martin Kalén schrieb: Martin Kalén wrote: It might be possible to use fetch size 1 explicitly only for iterators, I will have a look at this too. This is a bigger refactoring since it affects internal statement creation and would need many internal methods' signature to be expanded with a flag and/or explicit size for fetch size hints. I will bring the discussion up on the dev-list pointing to this thread and see what concensus is with respect to memory footpring vs network traffic/JDBC overhead. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
Andreas Bohnert wrote: Postgres (for example) don't use cursors on default. The PG jdbc driver loads the entire ResultSet all at once and keeps it in memory! So, even if you do a getIteratorByQuery the memory load on a large resultset is huge! You can get around this, if you set the fetchsize on the jdbc statement for example: stmt = con.createStatement(); stmt.setFetchSize(1); ResultSet rs = stmt.executeQuery(sql); This actually forces the jdbc driver to use a cursor and browse the resultset one by one. There is no possibility to do this with ojb's getIteratorByQuery! Or is there? I had a look around, and you can currently not control this in OJB. However, it is easy enough to issue this in the PostgreSQL-specific platform driver but then it would apply to all created Statement instances within OJB. I will do some performance testing and run the regression test with vs. without different setFetchSize settings in the PostgreSQL platform and see if it qualifies as a new PostgreSQL default behaviour. Any heads-up comments on this? Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
Martin Kalén wrote: Postgres (for example) don't use cursors on default. The PG jdbc driver loads the entire ResultSet all at once and keeps it in memory! So, even if you do a getIteratorByQuery the memory load on a large resultset is huge! You can get around this, if you set the fetchsize on the jdbc statement for example: stmt = con.createStatement(); stmt.setFetchSize(1); ResultSet rs = stmt.executeQuery(sql); This actually forces the jdbc driver to use a cursor and browse the resultset one by one. There was no regression when running the test suite with fetch size=1 (and it actually ran a tiny bit faster, presumably because of less JVM allocations for objects not used in the tests). I will re-run again and check peak JVM heap usage before/after and bring it up on the development list. Do you see any drawbacks of an OJB-global default of fetchSize=1 for PostgreSQL? (This should not affect OJB proxy-prefetching-limit since that prefecthing is done one layer above the PostgreSQL JDBC level.) Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
Martin Kalén wrote: Postgres (for example) don't use cursors on default. The PG jdbc driver loads the entire ResultSet all at once and keeps it in memory! So, even if you do a getIteratorByQuery the memory load on a large resultset is huge! You can get around this, if you set the fetchsize on the jdbc statement for example: stmt = con.createStatement(); stmt.setFetchSize(1); ResultSet rs = stmt.executeQuery(sql); This actually forces the jdbc driver to use a cursor and browse the resultset one by one. There was no regression when running the test suite with fetch size=1 (and it actually ran a tiny bit faster, presumably because of less JVM allocations for objects not used in the tests). I will re-run again and check peak JVM heap usage before/after and bring it up on the development list. I ran PB and ODMG API tests with the old platform (setFetchSize unspecified) vs. setFetchSize(1) as per your recommendation. See: http://people.apache.org/~mkalen/ojb/postgresql-fetchsize-tests.html Since this is on an OJB global level for PostgreSQL I guess that 1 might be too low to strike a balance between network traffic and memory consumption? It might be possible to use fetch size 1 explicitly only for iterators, I will have a look at this too. For normal statements lower fetch size = higher network traffic and more JDBC communication overhead and might not be a good default setting. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
Martin Kalén wrote: It might be possible to use fetch size 1 explicitly only for iterators, I will have a look at this too. This is a bigger refactoring since it affects internal statement creation and would need many internal methods' signature to be expanded with a flag and/or explicit size for fetch size hints. I will bring the discussion up on the dev-list pointing to this thread and see what concensus is with respect to memory footpring vs network traffic/JDBC overhead. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Out of Memory on Large ResultSets/Needed: SetFetchSize Method
hi all, ojb supports proprietary start- and endIndex for queries. this solutions does not use fetch size or limit hints. jakob Martin Kalén schrieb: Martin Kalén wrote: It might be possible to use fetch size 1 explicitly only for iterators, I will have a look at this too. This is a bigger refactoring since it affects internal statement creation and would need many internal methods' signature to be expanded with a flag and/or explicit size for fetch size hints. I will bring the discussion up on the dev-list pointing to this thread and see what concensus is with respect to memory footpring vs network traffic/JDBC overhead. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
