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
> >>>>>
> >>>
> >>
> >
>
>