Re: Slow performance with OpenJPA when selecting from a ManyToMany relation.

[Paul Copeland]

Two cents - It would be useful to examine the use cases that benefit 
from a JPA implementation having its own integrated connection pool.  
Most J2EE containers already have abstractions for connection pool 
resources.  Also, long running connections have other issues with 
concurrency and locking beyond worrying about connection pool 
starvation.  The tradeoffs on a case by case basis can only be 
understood at the application level.  For instance, a long running batch 
program that runs occasionally may not even use the same pool that 
interactive processes use.

- Paul
On 3/21/2009 8:18 AM, Michael Dick wrote:
Hi Nitish,

I'm not sure there is a best answer. In this case it's a bit of a tradeoff.
If you're not actively driving work on a connection why would one want to
hold on to a connection? Say you have long lived EntityManagers which use
resource local transactions. If we maintain the connection until the
EntityManager is closed then the connection pool size is 1:1 with the number
of EMs (which might be 1 per thread). Right now we're taking the stance that
the connection pool is better able to load balance if we return the
connection to the pool immediately.

With JTA the scenario changes a bit. In my experience with JTA aware
connection pools the connection doesn't really go back to the pool when we
(the app) close it. Rather it's reserved for future use in the same JTA tran
(this might be implementation specific though). In which case the cleanup
which causes a lot of the overhead wouldn't even happen until the JTA tran
completes.

I'm less familiar with Spring transactions (suppose I have some reading to
do).

I'm not ready to say either approach is right or wrong. It really depends on
your use case. By default we try to keep the number of connections low and
avoid contention for database resources which makes sense for bigger
applications. Smaller ones might prefer setting retainMode=transaction or
always.

What this is pointing out to me is that there's some benefit in having an
integrated connection pool in a JPA provider. I believe someone posted about
the idea a few weeks ago but I don't remember what came of it - based on
this thread I think it's worth revisiting.

-mike



  
Hi Michael,
      The connection retain mode property makes the numbers almost same,
but I am a little confused. Shouldnt this be the default behavior of Entity
Manager? Typically I would have the entity manager tied up to the
transaction (JTA or Spring), so I would expect Entity Manager to hold the
connection always.

Thanks and Regards,
Nitish Kumar

-----Original Message-----
From: Michael Dick [mailto:michael.d.d...@gmail.com]
Sent: Sat 3/21/2009 1:55 AM
To: users@openjpa.apache.org
Subject: Re: Slow performance with OpenJPA when selecting from a ManyToMany
relation.

Hi all,

As Paul pointed out privately I didn't indicate which versions of OpenJPA I
was using. I've been testing with 1.2.0, 1.2.1 and 2.0.0-SNAPSHOT
primarily.
I also ran with 1.3.0-SNAPSHOT, and 1.0.3 for comparison - there wasn't
much
difference though so reduced the scope to 1.2 and trunk.

The testcase uses a single EntityManager instance to issue a batch of
findBy
operations. In OpenJPA each findBy (or any query for that matter) obtains a
connection from the connection pool and returns it after getting the
results. So we're spending a lot of time moving the connection to and from
the pool (some cleanup is done along the way).

Fortunately this behavior can be configured with the
openjpa.ConnectionRetainMode property. Setting it to "always" causes the
EntityManager to hold on to a single connection until the EntityManager
closes. Obviously this setting introduces the possibility of exhausting the
connection pool if num_entitymanagers > max_connections, but for this
benchmark it's safe to try.

Setting ConnectionRetainMode gave OpenJPA equivalent times on my laptop at
100 - 100,000 iterations. In addition I removed the
openjpa.jdbc.SynchronizeMappings property from the example (it's extraneous
once the tables are created anyway).Another option I enabled that made some
difference was preparedStatementCaching in dbcp. I'm assuming EclipseLink
has some pstmt caching as well, but that could be faulty - in which case
I'll disable it in dbcp.

Here's the entire set of properties I'm using :
       properties.put("openjpa.Log", "DefaultLevel=FATAL");
       properties.put("openjpa.RuntimeUnenhancedClasses", "unsupported");
       properties.put("openjpa.ConnectionRetainMode", "always");
       properties.put("openjpa.ConnectionDriverName",
           "org.apache.commons.dbcp.BasicDataSource");

       properties.put("openjpa.ConnectionProperties", String.format(
           "DriverClassName=%s, Url=%s, username=%s, password=%s,"
               + " MaxActive=%s, MaxIdle=%s, MinIdle=%s, MaxWait=%s"
               + ", poolPreparedStatements=true"
               , JDBC_DRIVER, JDBC_URL, JDBC_USER,
           JDBC_PASSWORD, MAX_CON, MIN_CON, MIN_CON, "1000"));

       EntityManagerFactory factory =
           Persistence.createEntityManagerFactory("OpenJPAPU", properties);

MIN_CON = 1, MAX_CON=10.

Shubbis, could you try running something similar and see if you get the
same
results?

-mike

On Fri, Mar 20, 2009 at 8:46 AM, Michael Dick <michael.d.d...@gmail.com
    
wrote:
      
Hi, I took a quick run with the source code from the RAR Shubbis attached
earlier (thanks BTW).

The SQL we execute for this findBy is SELECT t0.warehouseName FROM
Warehouse t0 WHERE t0.warehouseNr = ?. Pretty basic, and I doubt
      
EclipseLink
    
is doing better (certainly not 3x) based solely on the SQL.

So I started digging deeper and on my laptop (not to be confused with any
sort of "real" benchmark) there's a sweet spot around 100 iterations.
      
Under
    
100 OpenJPA is faster. Between 100 and 125 they're comparable, and over
      
125
    
iterations EclipseLink starts pulling ahead.

Environment :
* Entities were enhanced by the PCEnhancer tool prior to running the
      
tests.
    
* Connection pooling is enabled for EclipseLink and OpenJPA with roughly
the same settings. EclipseLink's pool and commons-dbcp weren't an easy
      
1:1
    
match, so I might have some investigation to do there.
* MySQL Connector for Java v 5.1.7.
* MySQL database running locally, Version: 5.0.67-0ubuntu6
* Tests executed in Eclipse, YMMV outside of Eclipse.
* Sun JDK 5 java full version "1.5.0_15-b04"

I have done a lot of hacking about with the sample application but I
      
don't
    
think I've violated the intent of the exercise. I'll upload the app to a
jira shortly.

The relevant code is in my pastebin at these links :
persistence.xml : http://miked.pastebin.com/m490814b7
test01.java : http://miked.pastebin.com/m7d3df62f
WarehouseDAO.java : http://miked.pastebin.com/m49ab9a0e

I highlighted the changed lines in WarehouseDAO, but missed it on the
others (too many lines to highlight accurately.

I'm still looking, but thought this was worth sharing in case someone
      
else
    
sees something I've missed.

-mike



On Thu, Mar 19, 2009 at 10:53 AM, Paul Copeland <t...@jotobjects.com
wrote:

      
At one point in this thread it was mentioned that the benchmark ran much
faster on a home computer than on an office computer and the reason for
        
the
    
difference was not obvious.  Was that difference explained yet?

What version of OpenJPA is the test using?

- Paul


On 3/19/2009 7:44 AM, Kevin Sutter wrote:

        
Shubbis and Nitish,
Thanks for your efforts.  So, to clarify -- all implementations are
          
using
    
similar configurations (ie. connection pooling, caching, enhancement,
etc)?
But, the OpenJPA performance is still 3 times slower than the
competitors?
In all of the scenarios?  Or, just with this ManyToMany scenario?  I
would
expect some overhead as compared to iBatis and/or straight JDBC, but
OpenJPA
should be competitive (and beat) the Hibernates and EclipseLinks...
          
 Very
    
frustrating.  When we do our comparisons with the industry benchmarks
(Trade
and SpecJApp), OpenJPA is extremely competitive.

I have not closely examined your benchmark project, so I don't know how
it
compares to Trade and/or SpecJApp work loads.  Any thoughts on this
topic?

One other thought...  Just to prove that the enhancement processing is
being
done and you're not falling into the sub-classing support, could you
          
run
    
with the following property?  This will cause your application to
error-off
if your Entities are not byte-code enhanced.  We will not fall into the
sub-classing support which greatly affects the performance.

<property name="openjpa.RuntimeUnenhancedClasses"
   value="warn"/>

It really seems that you are trying to do a fair comparison, and I
greatly
appreciate your efforts.  The last time one of these comparisons was
posted,
the benchmark code and process was flawed.  So, I am pleased to see the
efforts associated with this exercise.

Our performance lead is out having a baby, so we haven't been able to
          
dig
    
into your benchmark to the extent that we would like.  If we can verify
that
the enhancement processing is happening, that would be good input.
 Thanks
for your patience.  What kind of timeframe are you under for posting
          
this
    
benchmark?

Thanks,
Kevin

On Thu, Mar 19, 2009 at 9:05 AM, Shubbis <marius.jo...@broadpark.no>
wrote:



          
Since we decided to go with vanilla installations of alle the
            
frameworks
    
we
have not added the connection pool feature to OpenJPA, until now.

The results are sadly not that great. Yes, it's faster and it doesn't
run
out of connections like before, BUT it's still 3, yes, -three- times
slower
than Hibernate, EclipseLink, iBatis and regular JDBC when persisting
entities with many relations.

Clearly this is not the kind of results I was hoping for and I'm quite
perplexed as to what to do.

Shubbis


Nitish Kumar wrote:


            
Hi subbis,
     If I let the iteration loop over 5000, I get that exception, It
seems (I am not sure) openjpa is creating a new connection and after
              
a
    
while mysql runs out of connection. I tried the same code and
              
iteration
    
loop with a connection pool and it works fine. That should get you
moving as of now, till someone from Open JPA team looks into the
              
issue.
    
Thanks and Regards,
Nitish Kumar



              
--
View this message in context:


            
http://n2.nabble.com/Slow-performance-with-OpenJPA-when-selecting-from-a-ManyToMany-relation.-tp2466994p2503084.html
    
Sent from the OpenJPA Users mailing list archive at Nabble.com.




            

          
        

-------------------------------------------------------------------------------------------------------------------------
"The information contained in this e-mail transmission is confidential and
may be privileged. It is intended only for the
addressee(s) stated above. If you are not an addressee, any use,
dissemination, distribution, publication, or copying of
the information contained in this e-mail is strictly prohibited. If you
have received this e-mail in error, please
immediately notify us by telephone (+91 80 6618 6555), or e-mail the sender
and delete the e-mail from your system.
If you do not want to receive our emails please let us know so that we may
delete you from our email list. Proteans
Software Solutions and its parent group ("CAMO Group") do not accept
liability for damage caused by this email, and may
monitor email traffic."

-------------------------------------------------------------------------------------------------------------------------