Boost H2 Performance (Read Only Mode)
Toshiaki Takashima
I want to use H2 database for Read Only Mode
using LinkedTable. So, I tested performance of H2
Server mode and found Multiple access (over 20)
delays the response time.
According to the manual, there is a command "SET MULTI_THREADED"
http://www.h2database.com/html/grammar.html#setmultithreaded
It reads that "Currently, enabling this is experimental only.".
Can I enable it in "Read Only Mode" safely?
I would like to ask if there is a risk when I use multithread mode
in read only mode.
Sincerely,
Thomas Mueller
> I want to use H2 database for Read Only Mode
> using LinkedTable.
> So, I tested performance of H2
> Server mode and found Multiple access (over 20)
> delays the response time.
If the database files are read-only you could open the database in
embedded mode. Each process would open its own database. That way
there is no synchronization.
The newest version can create temporary linked table, this may be
useful if the database is read-only. Local temporary table can be
created in read-only databases, however regular tables and regular
linked tables not.
> According to the manual, there is a command "SET MULTI_THREADED"
> http://www.h2database.com/html/grammar.html#setmultithreaded
>
> It reads that "Currently, enabling this is experimental only.".
> Can I enable it in "Read Only Mode" safely?
Yes, that shouldn't be a problem (I didn't actually test it, but in
theory nothing could go wrong). But using the embedded mode is even
better.
Regards,
Thomas
Toshiaki Takashima
Thank for your advice.
> If the database files are read-only you could open the database in
> embedded mode. Each process would open its own database. That way
> there is no synchronization.
>
connect to embedded database?
Sincerely,
On Sep 30, 5:50 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
> Is there no synchronization even if Each "thread"(not "process")
> connect to embedded database?
You are right, there is synchronization, even though it is not always required.
H2 currently works like this: The first connection to a database opens
it. The second connection will use the same database objects (the
database objects are shared among the sessions that are connected).
Usually this is good because it saves memory: you only need one cache.
But access to the database is also synchronized.
For read-only databases, this synchronization is technically not
required. What about if I add a new feature to open a database in read
only mode, but not share it with other connections? If the same
database is opened from two sessions, it would need more memory, but
it wouldn't be synchronized. What about using a new connection
property PRIVATE for this? So if you want to open a connection to a
read-only database, and you don't want to share the database with
other connections, you would use the database URL
jdbc:h2:~/test;PRIVATE=TRUE. Is this what you are looking for?
Regards,
Thomas
Toshiaki Takashima
> jdbc:h2:~/test;PRIVATE=TRUE. Is this what you are looking for?
>
But Perfomance test in muiti-thread server mode
achieved very good result in my environment.
If multi-thread mode is stable(again, I just only use
H2 Database for executing 'SELECT' statement which
does not need synchronization), I think I do not need
'PRIVATE' option.
Sincerely,
On Oct 1, 6:19 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
>> jdbc:h2:~/test;PRIVATE=TRUE. Is this what you are looking for?
> But Perfomance test in muiti-thread server mode
> achieved very good result in my environment.
>
> If multi-thread mode is stable(again, I just only use
> H2 Database for executing 'SELECT' statement which
> does not need synchronization), I think I do not need
> 'PRIVATE' option.
That's true, when you don't update the table then the multi-threaded
mode should be unproblematic. In any case I will implement the
PRIVATE=TRUE in the next release. I need it to test the new
AUTO_SERVER and AUTO_RECONNECT features, and it turned out it's quite
easy to implement.
Regards,
Thomas
Toshiaki Takashima
> > But Perfomance test in muiti-thread server mode
> > achieved very good result in my environment.
>
to work in read only environment.
I tested as follows :
1. prepared 2 tables in two separated RDBMS.
2. created Linked Table for the 2 tables above.
3. then, excuted Query to H2 which join the 2 tables (left outer join)
from multiple connection(thread)
According to the RDBMS's log, the number of DB connections
did not increase even though MULTI_THREADED was enabled.
Instead, the number of processing queries decreased by 75%!
Let me ask question :
1. I think MULTI_THREADED mode enables multiple concurrent
DB connections to RDBMS which is referenced from LinkedTable.
Am I right?
2. According to the Manual, OPTIMIZED_REUSE_RESULTS option
is enabled by default. Does the OPTIMIZED_REUSE_RESULTS option
decrease the number of queries which are executed from LinkedTable?
Sincerely,
On Oct 2, 5:24 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
> I tested as follows :
> 1. prepared 2 tables in two separated RDBMS.
> 2. created Linked Table for the 2 tables above.
So you have 3 databases? In what database(s) do you use the multi-threaded flag?
> 1. I think MULTI_THREADED mode enables multiple concurrent
> DB connections to RDBMS which is referenced from LinkedTable.
> Am I right?
No. The same linked table (actually the same connection) is never used
concurrently.
> 2. According to the Manual, OPTIMIZED_REUSE_RESULTS option
> is enabled by default. Does the OPTIMIZED_REUSE_RESULTS option
> decrease the number of queries which are executed from LinkedTable?
This option is not related to linked table.
http://www.h2database.com/html/grammar.html#setoptimizereuseresults
The database can't know if the data in the linked database was
changed, so it always have to re-run the query.
Regards,
Thomas
Toshiaki Takashima
> So you have 3 databases? In what database(s) do you use the multi-threaded flag?
>
> > 1. prepared 2 tables in two separated RDBMS.
> > 2. created Linked Table for the 2 tables above.
above.
> No. The same linked table (actually the same connection) is never used
> concurrently.
>
query to the LinkedTable on multiple access was sometimes wrong when
MULTI_THREADED was enabled.
Anyway, Because the response time was shorten, I thought that Low
performance
on over 20 concurrent multiple queries to LinkedTable was caused by
multi thread handling of H2. Why does H2 slow suddenly over 20?
Please give me your advice :
As I have said, I want to use H2 as multi-RDBMS view system. So,
I will only execute "SELECT" query to H2 LinkedTable, not UPDATE
statement.
In that context, I want to boost H2 performance under multiple access
environment.
I will try to use the new feature "OPEN_NEW" option and H2 on memory
mode. If there are other options to boost H2 performance (LinkedTable
access)
on that premise, please let me know.
Sincerely,
On Oct 7, 2:51 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
>
> > I tested as follows :
> > 1. prepared 2 tables in two separated RDBMS.
> > 2. created Linked Table for the 2 tables above.
>
> So you have 3 databases? In what database(s) do you use the multi-threaded flag?
>
> > 1. I think MULTI_THREADED mode enables multiple concurrent
> > DB connections to RDBMS which is referenced from LinkedTable.
> > Am I right?
>
> No. The same linked table (actually the same connection) is never used
> concurrently.
>
> > 2. According to the Manual, OPTIMIZED_REUSE_RESULTS option
> > is enabled by default. Does the OPTIMIZED_REUSE_RESULTS option
> > decrease the number of queries which are executed from LinkedTable?
>
Toshiaki Takashima
> I will try to use the new feature "OPEN_NEW" option and H2 on memory
> mode. If there are other options to boost H2 performance (LinkedTable
> access)
Sincerely,
>
> - Show quoted text -