Re: [h2] QUERY_CACHE_SIZE default value too low?
@rado, Thanks for the suggestion. I tried Tomcat's connection pool, and it is indeed exactly what I need. On Saturday, 8 August 2015 10:04:46 UTC+2, rado wrote: I would recommend you to try the tomcat connection pool. I think it is available as a separate distributable jar. -- 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?
I would recommend you to try the tomcat connection pool. I think it is available as a separate distributable jar. -- 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 Thomas,
Thanks for the tip. I spent time today trialling various connection pools
with H2. Surprisingly, nothing I found compares with using H2's own query
cache + connection pool for both speed and ease.
On Thursday, 6 August 2015 20:01:47 UTC+2, Thomas Mueller wrote:
Hi,
Did you try using a more advanced connection pool? One that re-uses
prepared statements. The H2 one is really simple and prevents that.
Regards,
Thomas
On Thursday, August 6, 2015, Steve McLeod steve.mcl...@gmail.com wrote:
Noel, I think you are right. I use this pattern for each query:
public void insertARow(int x) {
String sql = insert into yada yada yada;
try (Connection conn = getConnectionFromConnectionPool();
PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, x);
statement.executeUpdate();
}
}
It is based on keeping the database as unlocked as possible, in my
multi-threaded app. I may need to change the pattern a bit.
I deduced that Parser.initialize, for an SQL statement with n characters
* creates an array of n+1 ints
* an array of n+1 chars
* calls String.getChars() , which in turn calls System.arraycopy() for
n+1 characters
* calls new String() , which in turn calls System.arraycopy() for n+1
characters
All of these result in memory that escapes the method, so will be created
on the JVM's heap.
Although this should all be blindingly fast, the fact that the rest of H2
is so fast, like you said, makes this show up. I think I was seeing this,
because for an SQL statement with 3000 characters, being performed 10,000
times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being
allocated on the heap. And indeed, in my profiling, I noticed a lot of
churn on the heap.
Cheers,
Steve
On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:
The thing is, I don't think there is a problem. I think that your code
is not caching PreparedStatement 's properly, and the rest of H2 is so
fast, that the only thing left in the profile is the parser initialisation
:)
On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com wrote:
Hi Noel,
I've actually solved this problem of PreparedStatement caching for my
app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping
solve the bigger issue of why it seems to take a comparatively significant
time to create a PreparedStatement.
Cheers,
Steve
On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:
Thanks, I'll have a look tomorrow at them in detail.
Tell me, how often is JdbcConnection@preparedStatement called compared
to how many times you execute a query?
If it's every time, it means that your PreparedStatement caching is
not working, which would indicate a problem with
your connection pool, or something similar.
--
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...@googlegroups.com.
To post to this group, send email to h2-da...@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.
--
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.
[h2] QUERY_CACHE_SIZE default value too low?
Hi,
Did you try using a more advanced connection pool? One that re-uses
prepared statements. The H2 one is really simple and prevents that.
Regards,
Thomas
On Thursday, August 6, 2015, Steve McLeod steve.mcl...@gmail.com wrote:
Noel, I think you are right. I use this pattern for each query:
public void insertARow(int x) {
String sql = insert into yada yada yada;
try (Connection conn = getConnectionFromConnectionPool();
PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, x);
statement.executeUpdate();
}
}
It is based on keeping the database as unlocked as possible, in my
multi-threaded app. I may need to change the pattern a bit.
I deduced that Parser.initialize, for an SQL statement with n characters
* creates an array of n+1 ints
* an array of n+1 chars
* calls String.getChars() , which in turn calls System.arraycopy() for n+1
characters
* calls new String() , which in turn calls System.arraycopy() for n+1
characters
All of these result in memory that escapes the method, so will be created
on the JVM's heap.
Although this should all be blindingly fast, the fact that the rest of H2
is so fast, like you said, makes this show up. I think I was seeing this,
because for an SQL statement with 3000 characters, being performed 10,000
times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being
allocated on the heap. And indeed, in my profiling, I noticed a lot of
churn on the heap.
Cheers,
Steve
On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:
The thing is, I don't think there is a problem. I think that your code is
not caching PreparedStatement 's properly, and the rest of H2 is so fast,
that the only thing left in the profile is the parser initialisation :)
On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com wrote:
Hi Noel,
I've actually solved this problem of PreparedStatement caching for my
app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping
solve the bigger issue of why it seems to take a comparatively significant
time to create a PreparedStatement.
Cheers,
Steve
On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:
Thanks, I'll have a look tomorrow at them in detail.
Tell me, how often is JdbcConnection@preparedStatement called compared
to how many times you execute a query?
If it's every time, it means that your PreparedStatement caching is not
working, which would indicate a problem with
your connection pool, or something similar.
--
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...@googlegroups.com.
To post to this group, send email to h2-da...@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.
--
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?
Noel, I think you are right. I use this pattern for each query:
public void insertARow(int x) {
String sql = insert into yada yada yada;
try (Connection conn = getConnectionFromConnectionPool();
PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, x);
statement.executeUpdate();
}
}
It is based on keeping the database as unlocked as possible, in my
multi-threaded app. I may need to change the pattern a bit.
I deduced that Parser.initialize, for an SQL statement with n characters
* creates an array of n+1 ints
* an array of n+1 chars
* calls String.getChars() , which in turn calls System.arraycopy() for n+1
characters
* calls new String() , which in turn calls System.arraycopy() for n+1
characters
All of these result in memory that escapes the method, so will be created
on the JVM's heap.
Although this should all be blindingly fast, the fact that the rest of H2
is so fast, like you said, makes this show up. I think I was seeing this,
because for an SQL statement with 3000 characters, being performed 10,000
times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being
allocated on the heap. And indeed, in my profiling, I noticed a lot of
churn on the heap.
Cheers,
Steve
On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:
The thing is, I don't think there is a problem. I think that your code is
not caching PreparedStatement 's properly, and the rest of H2 is so fast,
that the only thing left in the profile is the parser initialisation :)
On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com
javascript: wrote:
Hi Noel,
I've actually solved this problem of PreparedStatement caching for my app
by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping solve
the bigger issue of why it seems to take a comparatively significant time
to create a PreparedStatement.
Cheers,
Steve
On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:
Thanks, I'll have a look tomorrow at them in detail.
Tell me, how often is JdbcConnection@preparedStatement called compared
to how many times you execute a query?
If it's every time, it means that your PreparedStatement caching is not
working, which would indicate a problem with
your connection pool, or something similar.
--
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...@googlegroups.com javascript:.
To post to this group, send email to h2-da...@googlegroups.com
javascript:.
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] QUERY_CACHE_SIZE default value too low?
Is it possible you could share a couple of the queries that are taking this long? Perhaps privately with Thomas and myself? On 2015-08-05 02:34 PM, Steve McLeod wrote: I've attached some screenshots from Java VisualVM CPU sampling. -- 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?
Noel, I've sent a comprehensive list of queries to you privately. The three most-executed queries are: select lastseen from player where playerid=? insert into TournamentPlayerHand (gameid, playerid, playername, seatnumber, stakelevelid, positionid, tournamentid, buyin, entries, casinoid, gametypeid, tableid, tablename, dayid, isplaymoney, iszoompoker, isHero, startTime, tableSize, takeInCents, showdowntakeInCents, nonshowdowntakeInCents, takeInBigBlinds, downcard1, downcard2, downcard3, downcard4, flop1, flop2, flop3, turn, river, handTypeId, numPlayersSittingIn, allInStreetId, equityPercentage, equityValueDiffInCents, rakePaid, timesseen, flopseen, vpip, preflopraised, unopenedpreflopraised, unopenedpreflopraisedopportunity, postflopbet, postflopraised, postflopcalled, postflopfolded, postFlopCallFoldBetOrRaise, postflopbetorraised, blindStealAttempt, blindStealAttemptOpportunity, checkRaised, checkRaisedOpportunity, wentToShowdown, wonAtShowdown, wonWithoutShowdown, threeBetPreflop, threeBetPreflopOpportunity, blindStealDefenseOpportunityOnBigBlind, calledPreflopRaise, calledPreflopRaiseOpportunity, continuationBet_flop, continuationBetOpportunity_flop, continuationBetTurn, continuationBetTurnOpportunity, foldedToBlindStealOnBigBlind, foldedToContinuationBet, foldedToContinuationBetOpportunity, foldedToContinuationBetOnTurn, foldedToContinuationBetOnTurnOpportunity, foldedToPreflopThreeBet, foldedToPreflopThreeBetOpportunity, fourBetPreflop, fourBetPreflopOpportunity, foldedToPreflopFourBet, foldedToPreflopFourBetOpportunity, timesseen_bb, timesseen_sb, timesseen_btn, timesseen_co, timesseen_mp, timesseen_ep, vpip_bb, vpip_sb, vpip_btn, vpip_co, vpip_mp, vpip_ep, pfr_bb, pfr_sb, pfr_sb_btn, pfr_co, pfr_mp, pfr_ep, uopfr_bb, uopfr_sb, uopfr_btn, uopfr_co, uopfr_mp, uopfr_ep, uopfr_opportunity_bb, uopfr_opportunity_sb, uopfr_opportunity_btn, uopfr_opportunity_co, uopfr_opportunity_mp, uopfr_opportunity_ep, betorraisedflop, betorraisedturn, betorraisedriver, flopCallFoldBetOrRaise, turnCallFoldBetOrRaise, riverFlopCallFoldBetOrRaise, donkbet_flop, donkbet_flop_opportunity, donkbet_turn, donkbet_turn_opportunity, donkbet_river, donkbet_river_opportunity, squeezeBetPreflop, squeezeBetPreflopOpportunity, threebet_bb, threebet_sb, threebet_btn, threebet_co, threebet_mp, threebet_ep, threebet_opportunity_bb, threebet_opportunity_sb, threebet_opportunity_btn, threebet_opportunity_co, threebet_opportunity_mp, threebet_opportunity_ep, fourbet_bb, fourbet_sb, fourbet_btn, fourbet_co, fourbet_mp, fourbet_ep, fourbet_opportunity_bb, fourbet_opportunity_sb, fourbet_opportunity_btn, fourbet_opportunity_co, fourbet_opportunity_mp, fourbet_opportunity_ep, checkRaisedFlop, checkRaisedFlopOpportunity, checkRaisedTurn, checkRaisedTurnOpportunity, checkRaisedRiver, checkRaisedRiverOpportunity) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) update TournamentPlayerTableSizeSummary set lastSeen=?, takeincents=?, showdowntakeInCents=?, nonshowdowntakeInCents=?, takeinbigblinds=?, rakePaid=?, timesseen=?, flopseen=?, vpip=?, preflopraised=?, unopenedpreflopraised=?, unopenedpreflopraisedopportunity=?, postflopbet=?, postflopraised=?, postflopcalled=?, postflopfolded=?, postFlopCallFoldBetOrRaise=?, postflopbetorraised=?, blindStealAttempt=?, blindStealAttemptOpportunity=?, checkRaised=?, checkRaisedOpportunity=?, wentToShowdown=?, wonAtShowdown=?, wonWithoutShowdown=?, threeBetPreflop=?, threeBetPreflopOpportunity=?, blindStealDefenseOpportunityOnBigBlind=?, calledPreflopRaise=?, calledPreflopRaiseOpportunity=?, continuationBet_flop=?, continuationBetOpportunity_flop=?, continuationBetTurn=?, continuationBetTurnOpportunity=?, foldedToBlindStealOnBigBlind=?, foldedToContinuationBet=?, foldedToContinuationBetOpportunity=?, foldedToContinuationBetOnTurn=?, foldedToContinuationBetOnTurnOpportunity=?, foldedToPreflopThreeBet=?, foldedToPreflopThreeBetOpportunity=?, fourBetPreflop=?, fourBetPreflopOpportunity=?, foldedToPreflopFourBet=?, foldedToPreflopFourBetOpportunity=?, timesseen_bb=?, timesseen_sb=?, timesseen_btn=?, timesseen_co=?, timesseen_mp=?, timesseen_ep=?, vpip_bb=?, vpip_sb=?, vpip_btn=?, vpip_co=?, vpip_mp=?, vpip_ep=?, pfr_bb=?, pfr_sb=?, pfr_sb_btn=?, pfr_co=?, pfr_mp=?, pfr_ep=?, uopfr_bb=?, uopfr_sb=?, uopfr_btn=?, uopfr_co=?, uopfr_mp=?, uopfr_ep=?, uopfr_opportunity_bb=?, uopfr_opportunity_sb=?, uopfr_opportunity_btn=?, uopfr_opportunity_co=?, uopfr_opportunity_mp=?, uopfr_opportunity_ep=?, betorraisedflop=?, betorraisedturn=?,
Re: [h2] QUERY_CACHE_SIZE default value too low?
Thanks, I'll have a look tomorrow at them in detail. Tell me, how often is JdbcConnection@preparedStatement called compared to how many times you execute a query? If it's every time, it means that your PreparedStatement caching is not working, which would indicate a problem with your connection pool, or something similar. -- 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 Noel, I've actually solved this problem of PreparedStatement caching for my app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping solve the bigger issue of why it seems to take a comparatively significant time to create a PreparedStatement. Cheers, Steve On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote: Thanks, I'll have a look tomorrow at them in detail. Tell me, how often is JdbcConnection@preparedStatement called compared to how many times you execute a query? If it's every time, it means that your PreparedStatement caching is not working, which would indicate a problem with your connection pool, or something similar. -- 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?
The thing is, I don't think there is a problem. I think that your code is not caching PreparedStatement 's properly, and the rest of H2 is so fast, that the only thing left in the profile is the parser initialisation :) On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve.mcl...@gmail.com wrote: Hi Noel, I've actually solved this problem of PreparedStatement caching for my app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping solve the bigger issue of why it seems to take a comparatively significant time to create a PreparedStatement. Cheers, Steve On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote: Thanks, I'll have a look tomorrow at them in detail. Tell me, how often is JdbcConnection@preparedStatement called compared to how many times you execute a query? If it's every time, it means that your PreparedStatement caching is not working, which would indicate a problem with your connection pool, or something similar. -- 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] QUERY_CACHE_SIZE default value too low?
Hi, Could you share some profiling data please? Parser.initialize() should be very fast. It only loops over the SQL statement, that should be the fastest part of parsing. Regards, Thomas On Monday, August 3, 2015, Steve McLeod steve.mcl...@gmail.com wrote: I use org.h2.jdbcx.ConnectionPool. However my software is a desktop app, with all JDBC activity taking place on one dedicated thread, so it might in effect work always use the same single connection. And yes I do have a lot of complex queries. On Monday, 3 August 2015 14:00:57 UTC+2, Noel Grandin wrote: On 2015-08-03 11:26 AM, Steve McLeod wrote: Consider this issue solved for me - I wanted to make sure that for the longer term a bigger query cache was contemplated. A related part of this issue is that Parser.prepareCommand(String); is costly for large SQL statements. In particular the Parser.initialize(String) method seems to be a CPU hog. When I say large SQL statement I mean 3000 characters, and 150 parameters. I attempted to do some profiling to find if there were any simple optimisations available, but alas the Parser.initialize() method is complex and not easy to profile in more detail. How many connections do you open? If you have a lot of very complex queries, the right answer may be that we need to share the PreparedStatement cache across sessions. -- 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 javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.com javascript:_e(%7B%7D,'cvml','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] QUERY_CACHE_SIZE default value too low?
Hi Thomas, Consider this issue solved for me - I wanted to make sure that for the longer term a bigger query cache was contemplated. A related part of this issue is that Parser.prepareCommand(String); is costly for large SQL statements. In particular the Parser.initialize(String) method seems to be a CPU hog. When I say large SQL statement I mean 3000 characters, and 150 parameters. I attempted to do some profiling to find if there were any simple optimisations available, but alas the Parser.initialize() method is complex and not easy to profile in more detail. Regards, Steve McLeod On Sunday, 2 August 2015 20:14:30 UTC+2, Thomas Mueller wrote: 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@gmail.com javascript: 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...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.com javascript:. 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] QUERY_CACHE_SIZE default value too low?
On 2015-08-03 11:26 AM, Steve McLeod wrote: Consider this issue solved for me - I wanted to make sure that for the longer term a bigger query cache was contemplated. A related part of this issue is that Parser.prepareCommand(String); is costly for large SQL statements. In particular the Parser.initialize(String) method seems to be a CPU hog. When I say large SQL statement I mean 3000 characters, and 150 parameters. I attempted to do some profiling to find if there were any simple optimisations available, but alas the Parser.initialize() method is complex and not easy to profile in more detail. How many connections do you open? If you have a lot of very complex queries, the right answer may be that we need to share the PreparedStatement cache across sessions. -- 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?
I use org.h2.jdbcx.ConnectionPool. However my software is a desktop app, with all JDBC activity taking place on one dedicated thread, so it might in effect work always use the same single connection. And yes I do have a lot of complex queries. On Monday, 3 August 2015 14:00:57 UTC+2, Noel Grandin wrote: On 2015-08-03 11:26 AM, Steve McLeod wrote: Consider this issue solved for me - I wanted to make sure that for the longer term a bigger query cache was contemplated. A related part of this issue is that Parser.prepareCommand(String); is costly for large SQL statements. In particular the Parser.initialize(String) method seems to be a CPU hog. When I say large SQL statement I mean 3000 characters, and 150 parameters. I attempted to do some profiling to find if there were any simple optimisations available, but alas the Parser.initialize() method is complex and not easy to profile in more detail. How many connections do you open? If you have a lot of very complex queries, the right answer may be that we need to share the PreparedStatement cache across sessions. -- 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.
[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.
