[h2] QUERY_CACHE_SIZE default value too low?
Hi all, I was doing some application profiling today. I found that in our real-world scenario of loading a database using many different very long SQL statements, a significant amount of H2's CPU time was being spent in org.h2.command.Parser.initialise(). Further investigation revealed that by default, H2 caches only 8 prepared statements in a least recently used cache. Use 9 or more prepared statements repeatedly - as our application does - and you lose all benefit of the prepared statement cache. The effect is the same as if the query cache was set to 0, except for the continual overhead of cache churn. I performed my profiling again, this time with QUERY_CACHE_SIZE set to 100. This time I managed to run 40% more queries in the same timeframe, measured over several minutes of sustained inserts, updates, selects, and merges. I proposed that the value for QUERY_CACHE_SIZE should by default be significantly higher. -- 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. For more options, visit https://groups.google.com/d/optout.
Re: [h2] QUERY_CACHE_SIZE default value too low?
Hi, If you use PreparedStatement, and the re-use them, within the same connection, you should be fine, even without the query cache. The cache query cache is only for those cases where you use Statement instead of PreparedStatement, or if you re-create the PreparedStatement each time. That includes the case if you use a simple connection pool. Could explain what you do, maybe with a simple code example? Regards, Thomas On Sun, Aug 2, 2015 at 2:43 PM, Steve McLeod steve.mcl...@gmail.com wrote: Hi all, I was doing some application profiling today. I found that in our real-world scenario of loading a database using many different very long SQL statements, a significant amount of H2's CPU time was being spent in org.h2.command.Parser.initialise(). Further investigation revealed that by default, H2 caches only 8 prepared statements in a least recently used cache. Use 9 or more prepared statements repeatedly - as our application does - and you lose all benefit of the prepared statement cache. The effect is the same as if the query cache was set to 0, except for the continual overhead of cache churn. I performed my profiling again, this time with QUERY_CACHE_SIZE set to 100. This time I managed to run 40% more queries in the same timeframe, measured over several minutes of sustained inserts, updates, selects, and merges. I proposed that the value for QUERY_CACHE_SIZE should by default be significantly higher. -- 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. For more options, visit https://groups.google.com/d/optout. -- 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. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: H2 Database Engine: New version 1.4.188 available
Hi, Sorry, I originally forgot to update the website. But this should be done now. Regards, Thomas On Sat, Aug 1, 2015 at 2:35 PM, 이정주 fwy...@treesoft.co.kr wrote: Hi Thank you for the new release. Website yet seem unchanged :) -- 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. For more options, visit https://groups.google.com/d/optout. -- 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. For more options, visit https://groups.google.com/d/optout.
