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 <gosho@(protected):
>
>> 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 <gosho@(protected)>
>> 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.
>>>>>
>>>>> <property name="openjpa.ConnectionProperties"
>>>>>                 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"/>
>>>>>            <property name="openjpa.ConnectionDriverName"
>>>>>
>> value="org.apache.commons.dbcp.BasicDataSource"/>
>>>>> <property name="openjpa.QueryCompilationCache" value="true"/>
>>>>> <property name="openjpa.QueryCache" value="false"/>
>>>>> <property name="openjpa.RemoteCommitProvider" value="sjvm"/>
>>>>> <property name="openjpa.DataCache" value="true(CacheSize=2000,
>>>>> SoftReferenceSize=0)"/>
>>>>> <property name="openjpa.FetchBatchSize" value="100"/>
>>>>> <property name="openjpa.ConnectionRetainMode" value="on-demand"/>
>>>>> <property name="openjpa.FlushBeforeQueries" value="true"/>
>>>>> <property name="openjpa.jdbc.DBDictionary"
>>>>>
>>>>>
>> value="org.apache.openjpa.jdbc.sql.MySQLDictionary(SupportsSubselect=true)"/>
>>>>> Best regards
>>>>> Georgi
>>>>>
>>>
>>
>