openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MiƂosz Tylenda <mtyle...@o2.pl>
Subject Re: slow performance on MySql 5.0
Date Wed, 27 Aug 2008 11:24:49 GMT
Georgi,

Have you compared the actual SQL statements being issued by PostgreSQL and MySQL versions?
If I remember correctly, setting the autoGenerateTestcaseScript=true Connector/J property
will show you all the statements including the implicit ones coming from the driver/transaction
manager. I once was quite surprised how many additional statements were issued by some EJB
2 implementation - commit/rollback, playing with autocommit and transaction isolation. Maybe
this accounts for the difference.

You could also experiment with useServerPrepStmts property.

Regards,
Milosz



> Hello, Kevin.
> 
> > Did you also happen to try the QuerySQLCache option?
> 
> Yes, but this application use something like logical table partitioning
> and retrieves data by queries (depends on application logic, it switch
> from one to another table and it has good SQL/JPQL factory for this
> purpose).
> 
> > What is your goal or target?
> 
> I think that 10-15% performance loss is excellent result at this time.
> 
> To be honest, on PostgreSQL the performance loss is less than 10%.
> I guess that bigger performance loss on MySql is something related to
> prepared statements or retrieving binary data, but it's only my guess.
> 
> > Another area that may be different with any JPA implementation is the
> use of
> > EAGER vs LAZY fetch modes.
> 
> I will think about this and future improvements.
> 
> Thank you again.
> 
> Best regards
> Georgi
> 
> Kevin Sutter wrote:
> > Excellent!
> > 
> > Did you also happen to try the QuerySQLCache option?
> > 
> > What is your goal or target?  Granted, straight JDBC will most likely be
> > better performing in most cases.  But, it does depend on your application's
> > goals.
> > 
> > Another area that may be different with any JPA implementation is the use of
> > EAGER vs LAZY fetch modes.  You need to ensure that the proper configuration
> > is set up for the application usage.  You don't want to be constantly
> > retrieving extra data via the EAGER mode, if that data is never or rarely
> > referenced.  In the same light, if you are constantly accessing related data
> > that is set to LAZY mode, then you are causing extra trips to the database,
> > which affects performance.
> > 
> > Good luck,
> > Kevin
> > 
> > On Tue, Aug 26, 2008 at 8:41 AM, Georgi Naplatanov  wrote:
> > 
> >> Hello, Kevin.
> >>
> >> The implementation with pooling of EntityManager instances is much
> >> faster. With this implementation performance loss on PostgreSQL is less
> >>  than 10%, on MySql between 12-22% for web application which i test.
> >>
> >> Best regards
> >> Georgi
> >>
> >> Georgi Naplatanov wrote:
> >>> Hello, Kevin, thank you for ideas.
> >>>
> >>> I didn't think to pool EntityManager instances. I definitely  will try
> >> it.
> >>> Best regards
> >>> Georgi
> >>>
> >>> Kevin Sutter wrote:
> >>>> Georgi,
> >>>> One of the first areas I would look at is the creation and destruction
> >> of
> >>>> the EntityManagers.  You mention that you are running with an extended
> >>>> context, but does the application create or pool EntityManagers?
> >>  Although
> >>>> our testing has been with IBM databases, we have found that we get the
> >> best
> >>>> performance with the minimum number of EntityManager creations.  If
you
> >> can
> >>>> clear and reuse the EntityManagers, the overall performance will be
> >> better.
> >>>> There is another cache that helps with sql generation as well.  It has
a
> >>>> couple of restrictions, but if the majority of your queries are simple
> >>>> findby operations, this cache will help considerably.  The property
is
> >>>> QuerySQLCache and it is documented in the OpenJPA 1.2.x manual (
> >>>>
> >> http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/manual.html#ref_guide_cache_querysql
> >>>> ).
> >>>>
> >>>> Hope this helps with getting better performance.
> >>>>
> >>>> Kevin
> >>>>
> >>>> On Mon, Aug 25, 2008 at 3:21 PM, Georgi Naplatanov 
> >> wrote:
> >>>>> Hello,
> >>>>>
> >>>>> I'm porting SQL/JDBC web application to JPA and i made some performance
> >>>>> tests with PostgreSQL 8.3.3 and MySql 5.0.51a Community edition,
with
> >>>>> both - SQL/JDBC and OpenJPA implementations of the application.
> >>>>>
> >>>>>                Apache ab       Apache Jmeter
> >>>>> PostgreSQL 8.3   -15%                   -12%
> >>>>> MySql 5.0.51a    -64%                   -27%
> >>>>>
> >>>>> On both tests on PostgreSQL the performance loss is about 15% compared
> >>>>> to pure SQL/JDBC implementation.
> >>>>>
> >>>>> On MySql the performance loss is very big especially on test with
> >> Apache
> >>>>> ab utility.
> >>>>>
> >>>>> In the tests, OpenJPA 1.2.0 is configured with data cache enabled,
> >> query
> >>>>> data cache disabled and query compilation cache - enabled. OpenJPA
> >>>>> operates in extended context with Apache DBCP and statement pooling.
> >>>>>
> >>>>> Is this performance loss on MySql normal ? Does OpenJPA require
some
> >>>>> special configuration for MySql ?
> >>>>>
> >>>>> It is my persistence.xml file.
> >>>>>
> >>>>> >>>>                         value="DriverClassName=com.mysql.jdbc.Driver,
> >>>>>                         Url=jdbc:mysql://localhost/mydb,
> >>>>>                         Username=root,
> >>>>>                         Password=123,
> >>>>>                         maxActive=25,
> >>>>>                         maxWait=25,
> >>>>>                         minIdle=3,
> >>>>>                         maxIdle=25,
> >>>>>                         whenExhaustedAction=block,
> >>>>>                         testOnBorrow=false,
> >>>>>                         testWhileIdle=true,
> >>>>>                         timeBetweenEvictionRunsMillis=3600000,
> >>>>>                         numTestsPerEvictionRun=3,
> >>>>>                         minEvictableIdleTime=1800000,
> >>>>>                         testQuery=select 1,
> >>>>>                         poolPreparedStatements=true"/>
> >>>>>                  >>>>
> >> value="org.apache.commons.dbcp.BasicDataSource"/>
> >>>>> 
> >>>>> 
> >>>>> 
> >>>>> >>>> SoftReferenceSize=0)"/>
> >>>>> 
> >>>>> 
> >>>>> 
> >>>>> >>>>
> >>>>>
> >> value="org.apache.openjpa.jdbc.sql.MySQLDictionary(SupportsSubselect=true)"/>
> >>>>> Best regards
> >>>>> Georgi
> >>>>>
> >>>
> >>
> > 
> 
> 

Mime
View raw message