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.

Reply via email to