Hello, Milosz. > Have you compared the actual SQL statements being issued by PostgreSQL and MySQL versions?
I switched to traditional join syntax and performance is better. <property name="openjpa.jdbc.DBDictionary" value="JoinSyntax=traditional"/> SQL statements are very similar compared to SQL and JPA implementations excepts number of fields which are selected and some brackets in the where clause. In the MySql logs are a lot of SET autocommit=1 SET autocommit=0 and i still not finding a way to remove them. > You could also experiment with useServerPrepStmts property. I did it, but it is slower. It is the used JDBC URL - jdbc:mysql://localhost/ocmsjpa?cachePrepStmts=true&cacheCallableStatements=true&cacheServerConfiguration=true&useLocalSessionState=true&elideSetAutoCommits=true&alwaysSendSetIsolation=false&enableQueryTimeouts=false&prepStmtCacheSize=3000&prepStmtCacheSqlLimit=1000 I have to find a way to remove "SET autocommit" calls and to tune fetch modes and fetch groups before compare performance between SQL and JPA implementations again. Best regards Georgi Miłosz Tylenda wrote: > 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 >>>>>>> >>
