Hi Sam, Thanks for the info and the suggestions.
* My h2 config is the result of much time spent configuring, measuring, configuring, measuring, etc. * It's common for my users to have a million CLOBS (TEXT fields), and I found through measuring that there was an adverse performance effect on inserts if I used the default max_length_inplace_lob setting. * The JVM heap (-DXms64M -Xmx384M ) and H2 cache are also finely tuned from months of iterative improvements, and tested on hundreds of systems due to my very supportive key users. I am, however, always looking for ways to improve the tuning. * My transactions are small, just a few queries at a time. * I didn't know about max_memory_rows and will try this for tuning. But none of this fits the problems I'm seeing: When a machine loses power unexpectedly, for example, sometimes that leaves the database in an unusable state. The table which generates the Exception I showed in the original post is a fact table in a star schema: CREATE TABLE PlayerSummary ( playerid INTEGER NOT NULL, dayid INTEGER NOT NULL, isplaymoney BOOLEAN NOT NULL, tableSize INTEGER NOT NULL, gametypeid INTEGER NOT NULL, positionid INTEGER NOT NULL, stakelevelid INTEGER NOT NULL, tournamentid INTEGER NOT NULL, playerName VARCHAR(32) NOT NULL, istournament BOOLEAN NOT NULL, isHero BOOLEAN NOT NULL, buyin INTEGER, casinoid INTEGER NOT NULL, startTime DATETIME NOT NULL, takeInCents INTEGER, showdowntakeInCents INTEGER, nonshowdowntakeInCents INTEGER, takeInBigBlinds DOUBLE, timesseen INTEGER, flopseen INTEGER, vpip INTEGER, preflopraised INTEGER, postflopbet INTEGER, postflopraised INTEGER, postflopcalled INTEGER, postflopfolded INTEGER, postflopbetorraised INTEGER, postFlopCallFoldBetOrRaise INTEGER, blindStealAttempt INTEGER, blindStealAttemptOpportunity INTEGER, checkRaised INTEGER, checkRaisedOpportunity INTEGER, wentToShowdown INTEGER, wonAtShowdown INTEGER, wonWithoutShowdown INTEGER, threeBetPreflop INTEGER, threebetPreflopOpportunity INTEGER, blindStealDefenseOpportunityOnBigBlind INTEGER, calledPreflopRaise INTEGER, calledPreflopRaiseOpportunity INTEGER, continuationBetPreflop INTEGER, continuationBetPreflopOpportunity INTEGER, foldedToBlindStealOnBigBlind INTEGER, foldedToContinuationBet INTEGER, foldedToContinuationBetOpportunity INTEGER, foldedToPreflopThreeBet INTEGER, foldedToPreflopThreeBetOpportunity INTEGER, PRIMARY KEY (playerid,dayid,isplaymoney,tableSize,gametypeid,positionid,stakelevelid,tournamentid) ); CREATE INDEX PlayerSummary_tournamentid_idx ON PlayerSummary (tournamentid); CREATE INDEX ps_idx_1 on playersummary (dayid,isplaymoney,istournament,ishero,positionid,stakelevelid,tablesize,gametypeid); CREATE INDEX ps_idx_2 on playersummary (playerid,isplaymoney,istournament,tablesize); The exception happens on a SELECT statement. Thanks again for your help. Regards, Steve McLeod On Jan 12, 4:33 pm, Sam Van Oort <[email protected]> wrote: > Hi Steve, > > A couple general suggestions: > > Increase the amount of heap memory given to Java (using -xmx) without > changing other settings. This gives it a bit more wiggle room for > operations requiring more memory. > > Reduce max_length_inplace_lob to something more reasonable, such as > 4096. 64 kB is quite excessive and may cause excessive memory use in > some cases (specifically with large transactions). > > Reduce max_memory_rows (important if combined with the above) -- think > about how much memory your data will use, and make sure this will use > less than 1/4 the memory available for H2. > > Try to break large transactions into several smaller transactions if > possible. H2 currently has memory problems on transactions which > touch more than 100,000 rows. This should be fixed in the next > couple builds, but in the meantime, consider switching to the old page > store (append ;page_store = false to your URL when creating the > database and connecting). > > To help us correct any bugs in H2, please provide any of the following > information you can: a heap dump in cases when it runs out of memory, > a trace file containing the operations being performed when memory > problems occur, information about DB structure and row counts when > the problem occurs, specific queries that trigger problems. > > Cheers, > Sam Van Oort > (Junior programmer, H2 project) > > On Jan 12, 5:24 am, Steve McLeod <[email protected]> wrote: > > > > > Using databases created with 1.2.124 in a desktop app, I'm > > occasionally getting error reports from users with this exception: > > > org.h2.jdbc.JdbcSQLException: General error: > > "java.lang.ArrayIndexOutOfBoundsException: 0"; SQL statement: > > select sum(takeinbigblinds) as takeinbigblinds from PlayerSummary f > > where f.isHero=true and f.isplaymoney=? and f.istournament=? and > > f.dayid>=? and f.dayid<? and f.tableSize=? and f.gametypeid=? and > > buyin=? [50000-124] > > at org.h2.message.Message.getSQLException(Message.java:111) > > at org.h2.message.Message.convert(Message.java:288) > > at org.h2.message.Message.convert(Message.java:249) > > at org.h2.command.Command.executeQueryLocal(Command.java:144) > > at org.h2.command.Command.executeQuery(Command.java:123) > > at org.h2.jdbc.JdbcPreparedStatement.executeQuery > > (JdbcPreparedStatement.java:98) > > at org.springframework.jdbc.core.JdbcTemplate > > $1.doInPreparedStatement(JdbcTemplate.java:648) > > at org.springframework.jdbc.core.JdbcTemplate.execute > > (JdbcTemplate.java:591) > > ... 11 more > > Caused by: java.lang.ArrayIndexOutOfBoundsException: 0 > > at org.h2.index.PageDataLeaf.getRowAt(PageDataLeaf.java:310) > > at org.h2.index.PageDataLeaf.getRow(PageDataLeaf.java:427) > > at org.h2.index.PageDataNode.getRow(PageDataNode.java:284) > > at org.h2.index.PageDataNode.getRow(PageDataNode.java:284) > > at org.h2.index.PageDataIndex.getRow(PageDataIndex.java:377) > > at org.h2.index.PageDataIndex.getRow(PageDataIndex.java:366) > > at org.h2.table.TableData.getRow(TableData.java:116) > > at org.h2.index.PageBtreeIndex.getRow(PageBtreeIndex.java:284) > > at org.h2.index.PageBtreeCursor.get(PageBtreeCursor.java:46) > > at org.h2.index.IndexCursor.get(IndexCursor.java:146) > > at org.h2.table.TableFilter.getValue(TableFilter.java:650) > > at org.h2.expression.ExpressionColumn.getValue > > (ExpressionColumn.java:168) > > at org.h2.expression.Comparison.getValue(Comparison.java:215) > > at org.h2.expression.ConditionAndOr.getValue > > (ConditionAndOr.java:86) > > at org.h2.expression.Expression.getBooleanValue(Expression.java: > > 188) > > at org.h2.command.dml.Select.queryGroup(Select.java:309) > > at org.h2.command.dml.Select.queryWithoutCache(Select.java:551) > > at org.h2.command.dml.Query.query(Query.java:236) > > at org.h2.command.CommandContainer.query(CommandContainer.java: > > 82) > > at org.h2.command.Command.executeQueryLocal(Command.java:142) > > ... 15 more > > > I'm unable to reproduce, but in at least one case I know this was > > preceded by a user's MacBook running out of power. > > > The database url is: > > jdbc:h2:~/ > > database;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database > > .DatabaseListener';MAX_LENGTH_INPLACE_LOB=65536;COMPRESS_LOB=DEFLATE;CACHE_ > > SIZE=65536 > > > Any suggestions? > > > Thanks > > > Steve
-- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
