openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Georgi Naplatanov <go...@oles.biz>
Subject Re: slow performance on MySql 5.0
Date Wed, 27 Aug 2008 12:47:35 GMT
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&amp;cacheCallableStatements=true&amp;cacheServerConfiguration=true&amp;useLocalSessionState=true&amp;elideSetAutoCommits=true&amp;alwaysSendSetIsolation=false&amp;enableQueryTimeouts=false&amp;prepStmtCacheSize=3000&amp;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
>>>>>>>
>>


Mime
View raw message