Hi, I'm sorry, I can only repeat my previous mail... please read the documentation at http://h2database.com/html/performance.html
Regards, Thomas On Thu, Oct 2, 2014 at 12:22 AM, b g <[email protected]> wrote: > Hello, > > My first attempt at making things better was to add indexes to each column > that was being used for SELECTs. This did not improve performance when > measured with my crude time calculations - running on my dev machine. I > will try and get a thread dump while using the indexes out test VM. > > My table - without indexes is: > > > CREATE TABLE IF NOT EXISTS IncomingMessages ( > id INTEGER IDENTITY PRIMARY KEY, > TimeReceived TIMESTAMP, > TimeExpires TIMESTAMP, > Source VARCHAR(256), > Processed BOOLEAN, > Message VARCHAR(1000000), > Properties VARCHAR(32000), > RetryCount INTEGER); > > CREATE INDEX IF NOT EXISTS IDX_PROC ON IncomingMessages (Processed); > CREATE INDEX IF NOT EXISTS IDX_EXP ON IncomingMessages (TimeExpires); > CREATE INDEX IF NOT EXISTS IDX_SRC ON IncomingMessages (Source); > CREATE INDEX IF NOT EXISTS IDX_RCVD ON IncomingMessages (TimeReceived); > CREATE INDEX IF NOT EXISTS IDX_RETRY ON IncomingMessages (RetryCount); > > --- > THESE I DID NOT USE IN THE TESTS BELOW < ---- > --CREATE INDEX IF NOT EXISTS IDX_ID_PROC ON IncomingMessages (id, > Processed); > --CREATE INDEX IF NOT EXISTS IDX_ID_SRC_PROC_EXP_RCVD ON IncomingMessages > (id, Source, Processed, TimeExpires, TimeReceived); > --CREATE INDEX IF NOT EXISTS IDX_SRC_PROC_EXP_RCVD ON IncomingMessages > (Source, Processed, TimeExpires, TimeReceived); > > > Using some for loops, Im inserting, reading, updating and then deleting... > > With only the id as primary index -> > > Took 450 msec to write 1000 messages to incoming store, avg: 0.45 > Took 774 msec to read 1000 messages from incoming store, avg: 0.774 > Took 362 msec to update 1000 messages from incoming store, avg: 0.362 > Took 99 msec to delete 1000 messages from incoming store, avg: 0.099 > ======================================================================== > Took 231 msec to write 2000 messages to incoming store, avg: 0.1155 > Took 1208 msec to read 2000 messages from incoming store, avg: 0.604 > Took 196 msec to update 2000 messages from incoming store, avg: 0.098 > Took 58 msec to delete 2000 messages from incoming store, avg: 0.029 > ======================================================================== > Took 406 msec to write 4000 messages to incoming store, avg: 0.1015 > Took 3444 msec to read 4000 messages from incoming store, avg: 0.861 > Took 160 msec to update 4000 messages from incoming store, avg: 0.04 > Took 85 msec to delete 4000 messages from incoming store, avg: 0.02125 > ======================================================================== > Took 335 msec to write 8000 messages to incoming store, avg: 0.041875 > Took 12283 msec to read 8000 messages from incoming store, avg: 1.535375 > Took 442 msec to update 8000 messages from incoming store, avg: 0.05525 > Took 148 msec to delete 8000 messages from incoming store, avg: 0.0185 > ======================================================================== > Took 289 msec to write 10000 messages to incoming store, avg: 0.0289 > Took 18601 msec to read 10000 messages from incoming store, avg: 1.8601 > Took 278 msec to update 10000 messages from incoming store, avg: 0.0278 > Took 184 msec to delete 10000 messages from incoming store, avg: 0.0184 > ======================================================================== > Took 378 msec to write 12000 messages to incoming store, avg: 0.0315 > Took 170608 msec to read 12000 messages from incoming store, avg: 14.21733 > Took 347 msec to update 12000 messages from incoming store, avg: 0.0289166 > Took 211 msec to delete 12000 messages from incoming store, avg: 0.017583 > > -------------------------------------------------------------------------- > > WITH INDEXES -> > CREATE INDEX IF NOT EXISTS IDX_PROC ON IncomingMessages (Processed); > CREATE INDEX IF NOT EXISTS IDX_EXP ON IncomingMessages (TimeExpires); > CREATE INDEX IF NOT EXISTS IDX_SRC ON IncomingMessages (Source); > CREATE INDEX IF NOT EXISTS IDX_RCVD ON IncomingMessages (TimeReceived); > CREATE INDEX IF NOT EXISTS IDX_RETRY ON IncomingMessages (RetryCount); > > Took 266 msec to write 1000 messages to incoming store, avg: 0.266 > Took 972 msec to read 1000 messages from incoming store, avg: 0.972 > Took 204 msec to update 1000 messages from incoming store, avg: 0.204 > Took 53 msec to delete 1000 messages from incoming store, avg: 0.053 > ======================================================================== > Took 250 msec to write 2000 messages to incoming store, avg: 0.125 > Took 2173 msec to read 2000 messages from incoming store, avg: 1.0865 > Took 191 msec to update 2000 messages from incoming store, avg: 0.0955 > Took 71 msec to delete 2000 messages from incoming store, avg: 0.0355 > ======================================================================== > Took 426 msec to write 4000 messages to incoming store, avg: 0.1065 > Took 7438 msec to read 4000 messages from incoming store, avg: 1.8595 > Took 203 msec to update 4000 messages from incoming store, avg: 0.05075 > Took 104 msec to delete 4000 messages from incoming store, avg: 0.026 > ======================================================================== > Took 487 msec to write 8000 messages to incoming store, avg: 0.060875 > Took 31316 msec to read 8000 messages from incoming store, avg: 3.9145 > Took 447 msec to update 8000 messages from incoming store, avg: 0.055875 > Took 206 msec to delete 8000 messages from incoming store, avg: 0.02575 > ======================================================================== > Took 533 msec to write 10000 messages to incoming store, avg: 0.0533 > Took 54821 msec to read 10000 messages from incoming store, avg: 5.4821 > Took 479 msec to update 10000 messages from incoming store, avg: 0.0479 > Took 254 msec to delete 10000 messages from incoming store, avg: 0.0254 > ======================================================================== > Took 569 msec to write 12000 messages to incoming store, avg: 0.047416 > Took 215022 msec to read 12000 messages from incoming store, avg: 17.9185 > Took 636 msec to update 12000 messages from incoming store, avg: 0.053 > Took 292 msec to delete 12000 messages from incoming store, avg: 0.0243333 > > With more cache size: > jdbc:h2:file:/var/log/XCL/messages_store;CACHE_SIZE=32768 > And the above indexes > > Took 85 msec to write 1000 messages to incoming store, avg: 0.085 > Took 793 msec to read 1000 messages from incoming store, avg: 0.793 > Took 63 msec to update 1000 messages from incoming store, avg: 0.063 > Took 34 msec to delete 1000 messages from incoming store, avg: 0.034 > ======================================================================== > Took 133 msec to write 2000 messages to incoming store, avg: 0.0665 > Took 2112 msec to read 2000 messages from incoming store, avg: 1.056 > Took 110 msec to update 2000 messages from incoming store, avg: 0.055 > Took 49 msec to delete 2000 messages from incoming store, avg: 0.0245 > ======================================================================== > Took 212 msec to write 4000 messages to incoming store, avg: 0.053 > Took 7198 msec to read 4000 messages from incoming store, avg: 1.7995 > Took 213 msec to update 4000 messages from incoming store, avg: 0.05325 > Took 94 msec to delete 4000 messages from incoming store, avg: 0.0235 > ======================================================================== > Took 397 msec to write 8000 messages to incoming store, avg: 0.049625 > Took 31196 msec to read 8000 messages from incoming store, avg: 3.8995 > Took 589 msec to update 8000 messages from incoming store, avg: 0.073625 > Took 189 msec to delete 8000 messages from incoming store, avg: 0.023625 > ======================================================================== > Took 429 msec to write 10000 messages to incoming store, avg: 0.0429 > Took 48873 msec to read 10000 messages from incoming store, avg: 4.8873 > Took 471 msec to update 10000 messages from incoming store, avg: 0.0471 > Took 239 msec to delete 10000 messages from incoming store, avg: 0.0239 > ======================================================================== > Took 658 msec to write 12000 messages to incoming store, avg: 0.05483333 > Took 216026 msec to read 12000 messages from incoming store, avg: 18.002166 > Took 629 msec to update 12000 messages from incoming store, avg: 0.0524166 > Took 298 msec to delete 12000 messages from incoming store, avg: 0.024833 > > > jdbc:h2:file:/var/log/XCL/messages_store;CACHE_SIZE=65536 > And the above indexes.... > > Took 56 msec to write 1000 messages to incoming store, avg: 0.056 > Took 615 msec to read 1000 messages from incoming store, avg: 0.615 > Took 53 msec to update 1000 messages from incoming store, avg: 0.053 > Took 24 msec to delete 1000 messages from incoming store, avg: 0.024 > ======================================================================== > Took 100 msec to write 2000 messages to incoming store, avg: 0.05 > Took 2028 msec to read 2000 messages from incoming store, avg: 1.014 > Took 96 msec to update 2000 messages from incoming store, avg: 0.048 > Took 48 msec to delete 2000 messages from incoming store, avg: 0.024 > ======================================================================== > Took 200 msec to write 4000 messages to incoming store, avg: 0.05 > Took 7055 msec to read 4000 messages from incoming store, avg: 1.76375 > Took 198 msec to update 4000 messages from incoming store, avg: 0.0495 > Took 115 msec to delete 4000 messages from incoming store, avg: 0.02875 > ======================================================================== > Took 536 msec to write 8000 messages to incoming store, avg: 0.067 > Took 30181 msec to read 8000 messages from incoming store, avg: 3.772625 > Took 763 msec to update 8000 messages from incoming store, avg: 0.095375 > Took 205 msec to delete 8000 messages from incoming store, avg: 0.025625 > ======================================================================== > Took 449 msec to write 10000 messages to incoming store, avg: 0.0449 > Took 49610 msec to read 10000 messages from incoming store, avg: 4.961 > Took 520 msec to update 10000 messages from incoming store, avg: 0.052 > Took 258 msec to delete 10000 messages from incoming store, avg: 0.0258 > ======================================================================== > > > On Wednesday, October 1, 2014 9:17:44 AM UTC-7, Thomas Mueller wrote: >> >> Hi, >> >> Do you have indexes for those columns? >> >> Please also read the "performance" documentation on the H2 website. >> >> Regards, >> Thomas >> >> >> >> On Tuesday, September 30, 2014, b g <[email protected]> wrote: >> >>> Hello all, >>> >>> I gathered some thread dumps while observing some poor performance >>> issues. I'm using H2 DB - >>> >>> <groupId>com.h2database</groupId><artifactId>h2</artifactId><version> >>> *1.3.174*</version> >>> Nothing special is on the connection string, >>> <prop key="XCL_Message_Database_Driver">org.h2.Driver</prop> >>> <prop key="XCL_Message_Database_URL">jdbc:h2:file:/var/log/TEST/ >>> messages_store</prop> >>> >>> the application uses a lot of threading to dequeue messages and stuff >>> them into a very simple table. Im getting BLOCKED threads when using >>> executeUpdate >>> >>> "DefaultMessageListenerContainer-4" prio=10 tid=0x00000000599cb800 >>> nid=0x3d0c waiting for monitor entry [0x00002b232080b000] >>> java.lang.Thread.State: BLOCKED (on object monitor) >>> at org.h2.command.Command.executeUpdate(Command.java:246) >>> - waiting to lock <0x0000000680a946b0> (a org.h2.engine.Database) >>> at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal( >>> JdbcPreparedStatement.java:154) >>> - locked <0x0000000680b2fd98> (a org.h2.engine.Session) >>> at org.h2.jdbc.JdbcPreparedStatement.executeUpdate( >>> JdbcPreparedStatement.java:140) >>> at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate( >>> DelegatingPreparedStatement.java:105) >>> at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate( >>> DelegatingPreparedStatement.java:105) >>> at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement( >>> JdbcTemplate.java:817) >>> at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement( >>> JdbcTemplate.java:1) >>> >>> "taskscheduler-1" prio=10 tid=0x00002b2318391000 nid=0x3b77 waiting for >>> monitor entry [0x00002b231e4e8000] >>> java.lang.Thread.State: BLOCKED (on object monitor) >>> at org.h2.command.Command.executeUpdate(Command.java:246) >>> - waiting to lock <0x0000000680a946b0> (a org.h2.engine.Database) >>> at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal( >>> JdbcPreparedStatement.java:154) >>> - locked <0x0000000680a945c8> (a org.h2.engine.Session) >>> at org.h2.jdbc.JdbcPreparedStatement.executeUpdate( >>> JdbcPreparedStatement.java:140) >>> at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate( >>> DelegatingPreparedStatement.java:105) >>> at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate( >>> DelegatingPreparedStatement.java:105) >>> >>> -------------------------------------------------------------- >>> >>> Im finding it occurs in a place in the code with higher frequency, maybe >>> because I have three DELETE statements in a row - It also BLOCKs on some >>> UPDATEs in other sections of code: >>> >>> int r1 = jdbc.update("DELETE FROM Incoming WHERE Processed=True"); >>> int r2 = jdbc.update("DELETE FROM Incoming WHERE TimeExpires<=?", new >>> Object[] { now() }); >>> int r3 = jdbc.update("DELETE FROM Incoming WHERE retryCount>=?", new >>> Object[] { getMaxErrorCountPerMessage() }); >>> >>> >>> -- >>> 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 [email protected]. >>> To post to this group, send email to [email protected]. >>> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
