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.

Reply via email to