Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (athena.apache.org: domain of gosho@oles.biz designates
 87.126.30.246 as permitted sender)
Message-ID: <48B40809.6030304@oles.biz>
Date: Tue, 26 Aug 2008 16:41:29 +0300
From: Georgi Naplatanov <gosho@oles.biz>
User-Agent: Mozilla-Thunderbird 2.0.0.16 (X11/20080724)
MIME-Version: 1.0
To: users@openjpa.apache.org
Subject: Re: slow performance on MySql 5.0
References: <48B3144C.4060800@oles.biz>
 <89c0c52c0808251444l472f56fclf9eb1d799294ab84@mail.gmail.com>
 <48B343EB.7090101@oles.biz>
In-Reply-To: <48B343EB.7090101@oles.biz>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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@oles.biz> 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
>>>
> 
>