openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prashant Bhat <prashant...@gmail.com>
Subject Re: H2 poor performance?
Date Thu, 26 Jan 2012 12:22:39 GMT
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ò <ilgrosso@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/
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message