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] <javascript:>> > 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.
