QUERY_CACHE_SIZE default value too low?

[Steve McLeod]

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. 

[Thomas Mueller]

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

--
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.

[Steve McLeod]

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

[Noel Grandin]

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.

[Steve McLeod]

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.

[Thomas Mueller]

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

[Steve McLeod]

I've attached some screenshots from Java VisualVM CPU sampling. 

On Tuesday, 4 August 2015 07:45:26 UTC+2, Thomas Mueller wrote:

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....@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+unsubscribe@googlegroups.com.

[Noel Grandin]

Is it possible you could share a couple of the queries that are taking this long?
Perhaps privately with Thomas and myself?

[Steve McLeod]

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=?, 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=?where playerid=? and isplaymoney=? and iszoompoker=? and tablesize=? and gametypeid=?

Regards,

Steve

[Noel Grandin]

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.

[Steve McLeod]

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

[Noel Grandin]

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 :)

--
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.

[Steve McLeod]

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

[Thomas Mueller]

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

[Steve McLeod]

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.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[rado]

I would recommend you to try the tomcat connection pool. I think it is available as a separate distributable jar.

[Steve McLeod]

@rado,

Thanks for the suggestion. I tried Tomcat's connection pool, and it is indeed exactly what I need.