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