Re: [h2] Re: LocalResult hold by PreparedStatement consumes a lot of Memory
probably what is easier to set QUERY_CACHE_SIZE to zero: https://www.h2database.com/javadoc/org/h2/engine/DbSettings.html#QUERY_CACHE_SIZE -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: LocalResult hold by PreparedStatement consumes a lot of Memory
One of test cases I have written recently has failed with OOM. It
actually tests some SELECT logic loading "heavy" records with large binary
objects (geometries). Investigation that I have done showed exactly the
described below case: prepared statements (every SELECT in my test
produces a bit different SQL query) are cached (Apache DBCP2 library is
used for connection pooling with an option *ds.setPoolPreparedStatements(true);
*) and they hold last result set - (field "lastResult" of Query class
that is extended by Select class).
The chain is:
JdbcPreparedStatement -> CommandInterface (CommandContainer) -> Prepared
(Select -> Query) -> LocalResult (Query#lastResult).
So, prepared statements caching is ON in connection pool,
OPTIMIZE_REUSE_RESULTS option has default value, lot of unique SQL queries
that leads to continuous caching of prepared statements with complete
result set hold in memory until connection is closed or prepared statements
are released from connection's cache. We do not know how PS caching is
configured in general way.
My conclusion is that cleaning of this internally cached result set is a
must after we know that logic around PS is complete and goes to the
cache. I understand why result set is cached within Query object. We
need clearly distinguish cases when last result is still needed and when
it's not needed anymore for sure.
I suppose PreparedStatement.clearParameters() is exactly the moment when
we can safely get rid of the "last result".This method is also called
by DBCP2 before PS goes to the cache.
Prepared class gets empty method that can be overrided by subclasses:
public void clearCachedResults(){
}
Query class overrides it:
@Override
public void clearCachedResults() {
closeLastResult();
lastResult = null;
}
CommandInterface gets a method as well:
public void clearCachedResults();
CommandContainer implements it as:
public void clearCachedResults(){
prepared.clearCachedResults();
}
and JdbcPreparedStatement has additional logic to call
command.clearCachedResults();
inside of its #clearParameters().
Last result is cleaned when it's not needed, changes are minimal, test
case now runs successfully, additional debugging prooves cleaning of
results and GC.
If an approach is accepted I can make change request in GIT. May be naming
of methods could be better, let's think.
Vitali.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
[h2] Re: LocalResult hold by PreparedStatement consumes a lot of Memory
Hi, Is this a (known) bug (which might be fixed in a newer version)? To find out, you could check the change log at http://www.h2database.com/html/changelog.html - but it's probably easier if you test it. Is 1.3.172 running in jboss 7.1.1.Final? I never heard it doesn't run, and wouldn't know why it shouldn't. Regards, Thomas On Thu, May 30, 2013 at 4:45 PM, Florian Schurke flobin...@googlemail.comwrote: Hi all, we are using a H2 database (1.3.161) embedded in jboss 7.1.1.Final. We wanted to cache the prepared queries but without caching the result of it. Therefore we set OPTIMIZE_REUSE_RESULTS=0. But unfortunately there is another reference-chain from the PreparedStatement to its result. This yields in keeping all the big results in memory. My questions are: - Is this a (known) bug (which might be fixed in a newer version)? - Is 1.3.172 running in jboss 7.1.1.Final? So may I replace the jar with the newer one? Thank you for your help, Flo *Detailed information:* The jboss-configuration is: datasources datasource jndi-name=java:/TestDS pool-name=TestDS enabled=true use-java-context=true connection-urljdbc:h2:mem:test;REFERENTIAL_INTEGRITY=0;LOG=0;MULTI_THREADED=1;OPTIMIZE_REUSE_RESULTS=0;DB_CLOSE_DELAY=-1;QUERY_CACHE_SIZE=0;DEFAULT_LOCK_TIMEOUT=1000/connection-url driverh2/driver transaction-isolationTRANSACTION_READ_COMMITTED/transaction-isolation pool min-pool-size1/min-pool-size max-pool-size12/max-pool-size prefilltrue/prefill /pool timeout blocking-timeout-millis3/blocking-timeout-millis idle-timeout-minutes1/idle-timeout-minutes /timeout statement prepared-statement-cache-size100/prepared-statement-cache-size share-prepared-statementstrue/share-prepared-statements /statement /datasource drivers driver name=h2 module=com.h2database.h2 xa-datasource-classorg.h2.jdbcx.JdbcDataSource/xa-datasource-class /driver /drivers /datasources The reference-chain is the following: org.h2.jdbc.JdbcPreparedStatement.command - org.h2.command.CommandContainer.prepared - org.h2.command.dml.Select.topTableFilter - org.h2.table.TableFilter.cursor - org.h2.index.IndexCursor.cursor - org.h2.index.ViewCursor.result - org.h2.result.LocalResult containig all the data of the result Maybe result in ViewCursor should be set to null after usage? -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
LocalResult hold by PreparedStatement consumes a lot of Memory
Hi all, we are using a H2 database (1.3.161) embedded in jboss 7.1.1.Final. We wanted to cache the prepared queries but without caching the result of it. Therefore we set OPTIMIZE_REUSE_RESULTS=0. But unfortunately there is another reference-chain from the PreparedStatement to its result. This yields in keeping all the big results in memory. My questions are: - Is this a (known) bug (which might be fixed in a newer version)? - Is 1.3.172 running in jboss 7.1.1.Final? So may I replace the jar with the newer one? Thank you for your help, Flo *Detailed information:* The jboss-configuration is: datasources datasource jndi-name=java:/TestDS pool-name=TestDS enabled=true use-java-context=true connection-urljdbc:h2:mem:test;REFERENTIAL_INTEGRITY=0;LOG=0;MULTI_THREADED=1;OPTIMIZE_REUSE_RESULTS=0;DB_CLOSE_DELAY=-1;QUERY_CACHE_SIZE=0;DEFAULT_LOCK_TIMEOUT=1000/connection-url driverh2/driver transaction-isolationTRANSACTION_READ_COMMITTED/transaction-isolation pool min-pool-size1/min-pool-size max-pool-size12/max-pool-size prefilltrue/prefill /pool timeout blocking-timeout-millis3/blocking-timeout-millis idle-timeout-minutes1/idle-timeout-minutes /timeout statement prepared-statement-cache-size100/prepared-statement-cache-size share-prepared-statementstrue/share-prepared-statements /statement /datasource drivers driver name=h2 module=com.h2database.h2 xa-datasource-classorg.h2.jdbcx.JdbcDataSource/xa-datasource-class /driver /drivers /datasources The reference-chain is the following: org.h2.jdbc.JdbcPreparedStatement.command - org.h2.command.CommandContainer.prepared - org.h2.command.dml.Select.topTableFilter - org.h2.table.TableFilter.cursor - org.h2.index.IndexCursor.cursor - org.h2.index.ViewCursor.result - org.h2.result.LocalResult containig all the data of the result Maybe result in ViewCursor should be set to null after usage? -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
