Inserts from concurrent threads are slower than Single Thread
Navjot Singh
I am witnessing a weird behavior.
1. I set up a test case and tried to create 200,000 rows using a
single thread (main for loop 200,000 times) to H2. All rows got added
in 135,517 ms. This all happened in Transaction mode using standard
@Transactional (propagation=Propagation.REQUIRED).
Main: 200000 assets inserted in batches of 10000 each completed in
135517 ms.
2. Now, I set up another test case and tried to insert 200,000 rows by
creating 4 threads, which will inserts 50,000 each.
First it failed with error as below.
09-07 18:47:56 jdbc[4]: exception
org.h2.jdbc.JdbcSQLException: Timeout trying to lock table
"TRACKED_ASSET100"; SQL statement:
insert into TRACKED_ASSET100 ( ......)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [50200-157]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.table.RegularTable.doLock(RegularTable.java:506)
at org.h2.table.RegularTable.lock(RegularTable.java:440)
at org.h2.command.dml.Insert.insertRows(Insert.java:123)
at org.h2.command.dml.Insert.update(Insert.java:84)
at org.h2.command.CommandContainer.update(CommandContainer.java:71)
at org.h2.command.Command.executeUpdate(Command.java:212)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
143)
at
org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:
1092)
I removed the @Transactional annotation just to make it work. That's a
question also if concurrent threads who are trying to write in batch
can cause other connections to timeout. I can increase the timeout if
thats the case.
Scenario:1, 50000 assets inserted in batches of 10000 each completed
in 106657 ms
Scenario:3, 50000 assets inserted in batches of 10000 each completed
in 117173 ms
Scenario:4, 50000 assets inserted in batches of 10000 each completed
in 144907 ms
Scenario:2, 50000 assets inserted in batches of 10000 each completed
in 147001 ms
Almost 4 times the time than the first test case.
In both scenarios, I had the exact same configuration for H2
connection.
Running in Mixed Mode but only using from the embedded node.
Exact same data which I am trying to insert.
I used connection pool in both cases.
H2 is being accessed using Spring JDBC API.
One difference though is that in the concurrent test case, I have no
transactional support but I thought it shall remove any overhead and
not add.
Connection Attrs:
/*SQL */SET LOG 0;
/*SQL */SET WRITE_DELAY 5000;
/*SQL */SET CACHE_SIZE 20000;
/*SQL */SET TRACE_LEVEL_FILE 2;
/*SQL */SET DB_CLOSE_DELAY 5;
Are there any special configuation settings if I need to use H2 with
multiple threads. I saw something like MULTI THREADED mode but that is
mentioned as experimental.
I am kind of clueless totally. Please help.
regards
Navjot Singh
Thomas Mueller
It could be anything really, for example maybe there is little memory
available so things get slow. Or the database file is very large, and
you have many indexes.
Usually enabling multi-threading support doesn't increase the throughput.
I suggest to analyze the problem yourself, see
http://h2database.com/html/performance.html#database_performance_tuning
and below.
Regards,
Thomas
Navjot Singh
Yes, I did analyze the issue and I was able to solve the issue by changing
the way my threads were working and at the same time adding MVCC to true.
regards
Navjot Singh
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>