H2 poor performance?

[Francesco Chicchiriccò <il...@apache.org>]

Hi all,
I am currently facing some strange behavior with in-memory H2.

In my project - Syncope Open Source IdM [1] - we recently moved from
Hibernate to OpenJPA: everything is now working and I can safely say
that the porting is functionally complete.

Our source code does some integration tests with Tomcat 7 and an
in-memory H2 instance: unfortunately H2 performs really bad in such
circumstances. Only consider that all tests take 128 secs to run, while
same tests take only 96 secs with PostgreSQL and 77 with MySQL.

When running the same test suite against last Hibernate-based build, it
turns out instead that H2 is faster than MySQL and PostgreSQL (as I
would expect given the small test dataset).

Is there anything special I should take care of when using in-memory H2?

TIA.
Regards.

[1] http://www.syncope-idm.org

-- 
Francesco Chicchiriccò

Apache Cocoon Committer and PMC Member
http://people.apache.org/~ilgrosso/

Re: H2 poor performance?

[Francesco Chicchiriccò <il...@apache.org>]

On 26/01/2012 13:22, Prashant Bhat wrote:
> Hi,
>
> Did you try the DB_CLOSE_DELAY connection parameter
> jdbc:h2:mem:test;DB_CLOSE_DELAY=-1 as mentioned in [1].
>
> I've not tested this myself but this seems to be the preferred option in
> such cases.

Hi Prashant,
I was already using ';DB_CLOSE_DELAY=1000': unfortunately
';DB_CLOSE_DELAY=-1' did not change things.

Thanks anyway.
Regards.

> [1] http://www.h2database.com/html/features.html#in_memory_databases
>
> Regards,
> Prashant
-- 
Francesco Chicchiriccò

Apache Cocoon Committer and PMC Member
http://people.apache.org/~ilgrosso/

Re: H2 poor performance?

[Prashant Bhat <pr...@gmail.com>]

Hi,

Did you try the DB_CLOSE_DELAY connection parameter
jdbc:h2:mem:test;DB_CLOSE_DELAY=-1 as mentioned in [1].

I've not tested this myself but this seems to be the preferred option in
such cases.

[1] http://www.h2database.com/html/features.html#in_memory_databases

Regards,
Prashant



On Thu, Jan 26, 2012 at 6:49 PM, Francesco Chicchiriccò <ilgrosso@apache.org
> wrote:

> On 25/01/2012 18:48, Kevin Sutter wrote:
> > That is interesting data, Francesco.  Since the actual query processing
> > seems to take less time with H2 (vs MySQL), but the overall time of the
> > test takes more time with H2 (vs MySQL), my next guess is with Connection
> > management.  Maybe the cost of creating Connections is much more
> expensive
> > with H2.  I would also lump in the creation of the EntityManagers, but
> that
> > should be constant between H2 and MySQL.  Except for the creation and
> > management of the database Connection.
> >
> > So, you could try a couple of things.  One thing would be to utilize DBCP
> > for pooling your Connections [1].  OpenJPA does not provide connection
> > pooling.  We rely on DBCP or an Application Server to provide that level
> of
> > function.  After the initial creation of the Connections, then the
> pooling
> > and sharing of connections would be a constant between H2 and MySQL.
> >
> > Depending on your test application, the other thing you might try is the
> > use of the ConnectionRetainMode set to "always" [2].  If you are
> constantly
> > deleting and re-creating EMs, then this property may not be of use.  But,
> > if you are using a single EM for your tests, then keeping a single
> > Connection around should help with your overall through-put.
>
> Hi Kevin,
> unfortunately applying either (or both) indications does not affect
> significantly the overall test execution time (from 117 secs to 110, at
> most).
>
> At this point I don't know how to proceed further with investigations:
> after all it's all test performance :-(
>
> Thanks anyway: I'll let you know if I eventually find something.
>
> Regards.
>
> > [1]
> >
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_integration_dbcp
> > [2]
> >
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_dbsetup_retain
> >
> > 2012/1/25 Francesco Chicchiriccò <il...@apache.org>
> >
> >> On 24/01/2012 15:54, Kevin Sutter wrote:
> >>
> >>> First of all, Congratulations on your migration from Hibernate to
> OpenJPA.
> >>>  I'm glad to hear that things worked out well for you during this
> effort.
> >>>
> >> Hi Kevin,
> >> thanks for your answer.
> >>
> >>
> >>  As far as H2 performance...  My guess is that the H2 Dictionary may not
> >>> have been kept up to date with improvements to H2 features and SQL
> >>> improvements.  These dictionaries have been maintained as needs arise
> and
> >>> as expertise provides input and feedback.  For example, our DB2
> customers
> >>> provide input for the DB2 dictionary, Oracle users provide input for
> the
> >>> Oracle dictionary, etc.  I looked back on my notes and I don't see an
> >>> expert identified for H2.  Would you like to provide that service?  :-)
> >>>
> >> Well, I am not an H2 expert at all: I am just using it for integration
> >> tests...
> >> Anyway, if I ever find something interesting on this topic, be sure that
> >> I'll do my best for providing an adequate patch ;-)
> >>
> >>
> >>
> >>  Since the performance of MySQL and Postgres seem to be acceptable, I
> would
> >>> assume that the deficiencies are probably localized to the dictionary.
>  I
> >>> would start by turning on Trace while running your test bucket.  The
> full
> >>> Trace output will show the timings for executing the various SQL.
>  Maybe
> >>> you'll find SQL being generated that is not efficient for H2.  You
> could
> >>> also review the H2 Dictionary and see if some of the basic properties
> may
> >>> not be set correctly for H2.  Maybe comparing MySQL and/or Postgres
> >>> dictionaries and trace output with H2's.
> >>>
> >> Following your advices, I've put log4jdbc [2] at work and found that,
> >> quite interestingly, the total time spent in querying H2 is quite less
> than
> >> the total time spent in querying MySQL.
> >> Since, as said before, the total time taken by tests is less with MySQL
> >> than with H2, I think that there must be some kind of latency.
> >>
> >> Does this say something more?
> >>
> >> Thanks.
> >> Regards.
> >>
> >>
> >>  2012/1/24 Francesco Chicchiriccò<ilgrosso@apache.**org<
> ilgrosso@apache.org>
> >>>  Hi all,
> >>>> I am currently facing some strange behavior with in-memory H2.
> >>>>
> >>>> In my project - Syncope Open Source IdM [1] - we recently moved from
> >>>> Hibernate to OpenJPA: everything is now working and I can safely say
> >>>> that the porting is functionally complete.
> >>>>
> >>>> Our source code does some integration tests with Tomcat 7 and an
> >>>> in-memory H2 instance: unfortunately H2 performs really bad in such
> >>>> circumstances. Only consider that all tests take 128 secs to run,
> while
> >>>> same tests take only 96 secs with PostgreSQL and 77 with MySQL.
> >>>>
> >>>> When running the same test suite against last Hibernate-based build,
> it
> >>>> turns out instead that H2 is faster than MySQL and PostgreSQL (as I
> >>>> would expect given the small test dataset).
> >>>>
> >>>> Is there anything special I should take care of when using in-memory
> H2?
> >>>>
> >>>> TIA.
> >>>> Regards.
> >>>>
> >>>> [1] http://www.syncope-idm.org
> >>>>
> >>> [2] http://code.google.com/p/**log4jdbc/<
> http://code.google.com/p/log4jdbc/>
> --
> Francesco Chicchiriccò
>
> Apache Cocoon Committer and PMC Member
> http://people.apache.org/~ilgrosso/
>
>

Re: H2 poor performance?

[Francesco Chicchiriccò <il...@apache.org>]

On 26/01/2012 11:49, Francesco Chicchiriccò wrote:
> [...]
>
> At this point I don't know how to proceed further with investigations:
> after all it's all test performance :-(
>
> Thanks anyway: I'll let you know if I eventually find something.

This problem seems to be solved now: [1] (my last message).

[1]
http://openjpa.208410.n2.nabble.com/OpenJPA-2-2-0-SNAPSHOT-amp-Glassfish-3-1-1-td7227299.html

-- 
Francesco Chicchiriccò

Apache Cocoon Committer and PMC Member
http://people.apache.org/~ilgrosso/

Re: H2 poor performance?

[Francesco Chicchiriccò <il...@apache.org>]

On 25/01/2012 18:48, Kevin Sutter wrote:
> That is interesting data, Francesco.  Since the actual query processing
> seems to take less time with H2 (vs MySQL), but the overall time of the
> test takes more time with H2 (vs MySQL), my next guess is with Connection
> management.  Maybe the cost of creating Connections is much more expensive
> with H2.  I would also lump in the creation of the EntityManagers, but that
> should be constant between H2 and MySQL.  Except for the creation and
> management of the database Connection.
>
> So, you could try a couple of things.  One thing would be to utilize DBCP
> for pooling your Connections [1].  OpenJPA does not provide connection
> pooling.  We rely on DBCP or an Application Server to provide that level of
> function.  After the initial creation of the Connections, then the pooling
> and sharing of connections would be a constant between H2 and MySQL.
>
> Depending on your test application, the other thing you might try is the
> use of the ConnectionRetainMode set to "always" [2].  If you are constantly
> deleting and re-creating EMs, then this property may not be of use.  But,
> if you are using a single EM for your tests, then keeping a single
> Connection around should help with your overall through-put.

Hi Kevin,
unfortunately applying either (or both) indications does not affect
significantly the overall test execution time (from 117 secs to 110, at
most).

At this point I don't know how to proceed further with investigations:
after all it's all test performance :-(

Thanks anyway: I'll let you know if I eventually find something.

Regards.

> [1]
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_integration_dbcp
> [2]
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_dbsetup_retain
>
> 2012/1/25 Francesco Chicchiriccò <il...@apache.org>
>
>> On 24/01/2012 15:54, Kevin Sutter wrote:
>>
>>> First of all, Congratulations on your migration from Hibernate to OpenJPA.
>>>  I'm glad to hear that things worked out well for you during this effort.
>>>
>> Hi Kevin,
>> thanks for your answer.
>>
>>
>>  As far as H2 performance...  My guess is that the H2 Dictionary may not
>>> have been kept up to date with improvements to H2 features and SQL
>>> improvements.  These dictionaries have been maintained as needs arise and
>>> as expertise provides input and feedback.  For example, our DB2 customers
>>> provide input for the DB2 dictionary, Oracle users provide input for the
>>> Oracle dictionary, etc.  I looked back on my notes and I don't see an
>>> expert identified for H2.  Would you like to provide that service?  :-)
>>>
>> Well, I am not an H2 expert at all: I am just using it for integration
>> tests...
>> Anyway, if I ever find something interesting on this topic, be sure that
>> I'll do my best for providing an adequate patch ;-)
>>
>>
>>
>>  Since the performance of MySQL and Postgres seem to be acceptable, I would
>>> assume that the deficiencies are probably localized to the dictionary.  I
>>> would start by turning on Trace while running your test bucket.  The full
>>> Trace output will show the timings for executing the various SQL.  Maybe
>>> you'll find SQL being generated that is not efficient for H2.  You could
>>> also review the H2 Dictionary and see if some of the basic properties may
>>> not be set correctly for H2.  Maybe comparing MySQL and/or Postgres
>>> dictionaries and trace output with H2's.
>>>
>> Following your advices, I've put log4jdbc [2] at work and found that,
>> quite interestingly, the total time spent in querying H2 is quite less than
>> the total time spent in querying MySQL.
>> Since, as said before, the total time taken by tests is less with MySQL
>> than with H2, I think that there must be some kind of latency.
>>
>> Does this say something more?
>>
>> Thanks.
>> Regards.
>>
>>
>>  2012/1/24 Francesco Chicchiriccò<il...@apache.org>
>>>  Hi all,
>>>> I am currently facing some strange behavior with in-memory H2.
>>>>
>>>> In my project - Syncope Open Source IdM [1] - we recently moved from
>>>> Hibernate to OpenJPA: everything is now working and I can safely say
>>>> that the porting is functionally complete.
>>>>
>>>> Our source code does some integration tests with Tomcat 7 and an
>>>> in-memory H2 instance: unfortunately H2 performs really bad in such
>>>> circumstances. Only consider that all tests take 128 secs to run, while
>>>> same tests take only 96 secs with PostgreSQL and 77 with MySQL.
>>>>
>>>> When running the same test suite against last Hibernate-based build, it
>>>> turns out instead that H2 is faster than MySQL and PostgreSQL (as I
>>>> would expect given the small test dataset).
>>>>
>>>> Is there anything special I should take care of when using in-memory H2?
>>>>
>>>> TIA.
>>>> Regards.
>>>>
>>>> [1] http://www.syncope-idm.org
>>>>
>>> [2] http://code.google.com/p/**log4jdbc/<http://code.google.com/p/log4jdbc/>
-- 
Francesco Chicchiriccò

Apache Cocoon Committer and PMC Member
http://people.apache.org/~ilgrosso/

Re: H2 poor performance?

[Kevin Sutter <kw...@gmail.com>]

That is interesting data, Francesco.  Since the actual query processing
seems to take less time with H2 (vs MySQL), but the overall time of the
test takes more time with H2 (vs MySQL), my next guess is with Connection
management.  Maybe the cost of creating Connections is much more expensive
with H2.  I would also lump in the creation of the EntityManagers, but that
should be constant between H2 and MySQL.  Except for the creation and
management of the database Connection.

So, you could try a couple of things.  One thing would be to utilize DBCP
for pooling your Connections [1].  OpenJPA does not provide connection
pooling.  We rely on DBCP or an Application Server to provide that level of
function.  After the initial creation of the Connections, then the pooling
and sharing of connections would be a constant between H2 and MySQL.

Depending on your test application, the other thing you might try is the
use of the ConnectionRetainMode set to "always" [2].  If you are constantly
deleting and re-creating EMs, then this property may not be of use.  But,
if you are using a single EM for your tests, then keeping a single
Connection around should help with your overall through-put.

Good luck,
Kevin

[1]
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_integration_dbcp
[2]
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_dbsetup_retain

2012/1/25 Francesco Chicchiriccò <il...@apache.org>

> On 24/01/2012 15:54, Kevin Sutter wrote:
>
>> First of all, Congratulations on your migration from Hibernate to OpenJPA.
>>  I'm glad to hear that things worked out well for you during this effort.
>>
>
> Hi Kevin,
> thanks for your answer.
>
>
>  As far as H2 performance...  My guess is that the H2 Dictionary may not
>> have been kept up to date with improvements to H2 features and SQL
>> improvements.  These dictionaries have been maintained as needs arise and
>> as expertise provides input and feedback.  For example, our DB2 customers
>> provide input for the DB2 dictionary, Oracle users provide input for the
>> Oracle dictionary, etc.  I looked back on my notes and I don't see an
>> expert identified for H2.  Would you like to provide that service?  :-)
>>
>
> Well, I am not an H2 expert at all: I am just using it for integration
> tests...
> Anyway, if I ever find something interesting on this topic, be sure that
> I'll do my best for providing an adequate patch ;-)
>
>
>
>  Since the performance of MySQL and Postgres seem to be acceptable, I would
>> assume that the deficiencies are probably localized to the dictionary.  I
>> would start by turning on Trace while running your test bucket.  The full
>> Trace output will show the timings for executing the various SQL.  Maybe
>> you'll find SQL being generated that is not efficient for H2.  You could
>> also review the H2 Dictionary and see if some of the basic properties may
>> not be set correctly for H2.  Maybe comparing MySQL and/or Postgres
>> dictionaries and trace output with H2's.
>>
>
> Following your advices, I've put log4jdbc [2] at work and found that,
> quite interestingly, the total time spent in querying H2 is quite less than
> the total time spent in querying MySQL.
> Since, as said before, the total time taken by tests is less with MySQL
> than with H2, I think that there must be some kind of latency.
>
> Does this say something more?
>
> Thanks.
> Regards.
>
>
>  2012/1/24 Francesco Chicchiriccò<il...@apache.org>
>> >
>>
>>  Hi all,
>>> I am currently facing some strange behavior with in-memory H2.
>>>
>>> In my project - Syncope Open Source IdM [1] - we recently moved from
>>> Hibernate to OpenJPA: everything is now working and I can safely say
>>> that the porting is functionally complete.
>>>
>>> Our source code does some integration tests with Tomcat 7 and an
>>> in-memory H2 instance: unfortunately H2 performs really bad in such
>>> circumstances. Only consider that all tests take 128 secs to run, while
>>> same tests take only 96 secs with PostgreSQL and 77 with MySQL.
>>>
>>> When running the same test suite against last Hibernate-based build, it
>>> turns out instead that H2 is faster than MySQL and PostgreSQL (as I
>>> would expect given the small test dataset).
>>>
>>> Is there anything special I should take care of when using in-memory H2?
>>>
>>> TIA.
>>> Regards.
>>>
>>> [1] http://www.syncope-idm.org
>>>
>> [2] http://code.google.com/p/**log4jdbc/<http://code.google.com/p/log4jdbc/>
>
>
> --
> Francesco Chicchiriccò
>
> Apache Cocoon Committer and PMC Member
> http://people.apache.org/~**ilgrosso/<http://people.apache.org/~ilgrosso/>
>
>

Re: H2 poor performance?

[Francesco Chicchiriccò <il...@apache.org>]

On 24/01/2012 15:54, Kevin Sutter wrote:
> First of all, Congratulations on your migration from Hibernate to OpenJPA.
>   I'm glad to hear that things worked out well for you during this effort.

Hi Kevin,
thanks for your answer.

> As far as H2 performance...  My guess is that the H2 Dictionary may not
> have been kept up to date with improvements to H2 features and SQL
> improvements.  These dictionaries have been maintained as needs arise and
> as expertise provides input and feedback.  For example, our DB2 customers
> provide input for the DB2 dictionary, Oracle users provide input for the
> Oracle dictionary, etc.  I looked back on my notes and I don't see an
> expert identified for H2.  Would you like to provide that service?  :-)

Well, I am not an H2 expert at all: I am just using it for integration 
tests...
Anyway, if I ever find something interesting on this topic, be sure that 
I'll do my best for providing an adequate patch ;-)


> Since the performance of MySQL and Postgres seem to be acceptable, I would
> assume that the deficiencies are probably localized to the dictionary.  I
> would start by turning on Trace while running your test bucket.  The full
> Trace output will show the timings for executing the various SQL.  Maybe
> you'll find SQL being generated that is not efficient for H2.  You could
> also review the H2 Dictionary and see if some of the basic properties may
> not be set correctly for H2.  Maybe comparing MySQL and/or Postgres
> dictionaries and trace output with H2's.

Following your advices, I've put log4jdbc [2] at work and found that, 
quite interestingly, the total time spent in querying H2 is quite less 
than the total time spent in querying MySQL.
Since, as said before, the total time taken by tests is less with MySQL 
than with H2, I think that there must be some kind of latency.

Does this say something more?

Thanks.
Regards.

> 2012/1/24 Francesco Chicchiriccò<il...@apache.org>
>
>> Hi all,
>> I am currently facing some strange behavior with in-memory H2.
>>
>> In my project - Syncope Open Source IdM [1] - we recently moved from
>> Hibernate to OpenJPA: everything is now working and I can safely say
>> that the porting is functionally complete.
>>
>> Our source code does some integration tests with Tomcat 7 and an
>> in-memory H2 instance: unfortunately H2 performs really bad in such
>> circumstances. Only consider that all tests take 128 secs to run, while
>> same tests take only 96 secs with PostgreSQL and 77 with MySQL.
>>
>> When running the same test suite against last Hibernate-based build, it
>> turns out instead that H2 is faster than MySQL and PostgreSQL (as I
>> would expect given the small test dataset).
>>
>> Is there anything special I should take care of when using in-memory H2?
>>
>> TIA.
>> Regards.
>>
>> [1] http://www.syncope-idm.org
[2] http://code.google.com/p/log4jdbc/

-- 
Francesco Chicchiriccò

Apache Cocoon Committer and PMC Member
http://people.apache.org/~ilgrosso/

Re: H2 poor performance?

[Kevin Sutter <kw...@gmail.com>]

First of all, Congratulations on your migration from Hibernate to OpenJPA.
 I'm glad to hear that things worked out well for you during this effort.

As far as H2 performance...  My guess is that the H2 Dictionary may not
have been kept up to date with improvements to H2 features and SQL
improvements.  These dictionaries have been maintained as needs arise and
as expertise provides input and feedback.  For example, our DB2 customers
provide input for the DB2 dictionary, Oracle users provide input for the
Oracle dictionary, etc.  I looked back on my notes and I don't see an
expert identified for H2.  Would you like to provide that service?  :-)

Since the performance of MySQL and Postgres seem to be acceptable, I would
assume that the deficiencies are probably localized to the dictionary.  I
would start by turning on Trace while running your test bucket.  The full
Trace output will show the timings for executing the various SQL.  Maybe
you'll find SQL being generated that is not efficient for H2.  You could
also review the H2 Dictionary and see if some of the basic properties may
not be set correctly for H2.  Maybe comparing MySQL and/or Postgres
dictionaries and trace output with H2's.

Good luck,
Kevin

2012/1/24 Francesco Chicchiriccò <il...@apache.org>

> Hi all,
> I am currently facing some strange behavior with in-memory H2.
>
> In my project - Syncope Open Source IdM [1] - we recently moved from
> Hibernate to OpenJPA: everything is now working and I can safely say
> that the porting is functionally complete.
>
> Our source code does some integration tests with Tomcat 7 and an
> in-memory H2 instance: unfortunately H2 performs really bad in such
> circumstances. Only consider that all tests take 128 secs to run, while
> same tests take only 96 secs with PostgreSQL and 77 with MySQL.
>
> When running the same test suite against last Hibernate-based build, it
> turns out instead that H2 is faster than MySQL and PostgreSQL (as I
> would expect given the small test dataset).
>
> Is there anything special I should take care of when using in-memory H2?
>
> TIA.
> Regards.
>
> [1] http://www.syncope-idm.org
>
> --
> Francesco Chicchiriccò
>
> Apache Cocoon Committer and PMC Member
> http://people.apache.org/~ilgrosso/
>
>