You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Lachlan Deck <la...@ish.com.au> on 2006/05/05 02:29:45 UTC

EmbeddedDriver performance help

Hi all,

I've been testing an initial population of a Derby database with some  
30-40000 records and am finding a large performance difference  
between using the EmbeddedDriver vs the ClientDriver i.e., depending  
on whether Derby is run as an embedded or network service for the  
application.

Using the Embedded driver seems to take an order of magnitude longer  
than not (e.g., 2-3 hours vs 15 minutes)!

We'd like to use an embedded Derby for deployment but this is  
unacceptably slow. Does anyone have any ideas on what may be causing  
this?

I'm not that familiar with Cayenne as yet (but am with EOF), so is  
there some way in Cayenne of adjusting the settings at run time (or  
other) for the JDBC connection?

Perhaps Derby's default locking scheme is getting deadlocked  
somewhere or perhaps the Embedded Driver has some problems. Any  
pointers would be greatly appeciated.

Thanks.

Lachlan Deck
lachlan@ish.com.au
+61 2 9550 5001




Re: EmbeddedDriver performance help

Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Lachlan Deck wrote:
> Hi there,
> 
> On 05/05/2006, at 12:15 PM, Jean T. Anderson wrote:
...
>> The 2-3 hours vs 15 minutes is extremely curious.
> 
> 
> Very. I'd hazzard a guess at over-zealous locking/deadlocks. e.g., 
> Cayenne manually using transactions and derby using its own 
> transactions internally by default perhaps...

this might help for isolating any lock problems:

http://db.apache.org/derby/faq.html#debug_lock_timeout

 -jean


Re: EmbeddedDriver performance help

Posted by Lachlan Deck <la...@ish.com.au>.
Hi there,

On 05/05/2006, at 12:15 PM, Jean T. Anderson wrote:

> When using the Derby Network Client, is the client offloaded to  
> another
> machine? or is it running on the same machine as the database?

In both scenarios a single machine is used.

> In embedded mode, are there any whimpers in the derby.log?

No whimpers as such but is it intriguing that for each select it has  
the following cost running as embedded...

2006-05-05 04:00:51,451 GMT Thread[Thread-12,5,main] (XID = 5935),  
(SESSIONID = 2), (DATABASE = directory:myDB), (DRDAID = null),  
Committing
2006-05-05 04:00:51,529 GMT Thread[Thread-12,5,main] (XID = 5935),  
(SESSIONID = 2), (DATABASE = directory:myDB), (DRDAID = null),  
Committing
2006-05-05 04:00:51,568 GMT Thread[Thread-12,5,main] (XID = 5935),  
(SESSIONID = 2), (DATABASE = directory:myDB), (DRDAID = null),  
Committing
2006-05-05 04:00:51,635 GMT Thread[Thread-12,5,main] (XID = 5935),  
(SESSIONID = 2), (DATABASE = directory:myDB), (DRDAID = null),  
Executing prepared statement: SELECT t0.var1, t0.var2 <..> FROM  
Module t0 WHERE t0.someId = ? :End prepared statement with 1  
parameters begin parameter #1: 1587 :end parameter

> --I could guess all night, but it might be faster for you to post to
> derby-user@db.apache.org, and include the sysinfo output.

Sure. Just thought I'd start here in case it was something particular  
about the default settings Cayenne uses to connect with.

> The 2-3 hours vs 15 minutes is extremely curious.

Very. I'd hazzard a guess at over-zealous locking/deadlocks. e.g.,  
Cayenne manually using transactions and derby using its own  
transactions internally by default perhaps...

>  -jean
>
> p.s. I'm on the derby project; haven't actually worked with cayenne  
> yet
> but hope to free up time soon to dig in.

Great.

Lachlan Deck
lachlan@ish.com.au
+61 2 9550 5001




Re: EmbeddedDriver performance help

Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
When using the Derby Network Client, is the client offloaded to another
machine? or is it running on the same machine as the database? In
embedded mode, are there any whimpers in the derby.log?

--I could guess all night, but it might be faster for you to post to
derby-user@db.apache.org, and include the sysinfo output.

The 2-3 hours vs 15 minutes is extremely curious.

 -jean

p.s. I'm on the derby project; haven't actually worked with cayenne yet
but hope to free up time soon to dig in.

Lachlan Deck wrote:
> Hi there,
> 
> On 05/05/2006, at 10:43 AM, Jean T. Anderson wrote:
> 
>> Lachlan Deck wrote:
>>
>>>
>>> I've been testing an initial population of a Derby database with some
>>> 30-40000 records and am finding a large performance difference   between
>>> using the EmbeddedDriver vs the ClientDriver i.e., depending  on 
>>> whether
>>> Derby is run as an embedded or network service for the  application.
>>>
>>> Using the Embedded driver seems to take an order of magnitude longer
>>> than not (e.g., 2-3 hours vs 15 minutes)!
>>
>>
>> fascinating. Normally the embedded driver is faster.
> 
> 
> So I've read.
> 
>> Which client JDBC driver are you using?
> 
> 
> For embedded Derby: org.apache.derby.jdbc.EmbeddedDriver
> For network Derby: org.apache.derby.jdbc.ClientDriver
> 
> Both are using the adaptor:  org.objectstyle.cayenne.dba.derby.DerbyAdapter
> 
>> Which specific version of Derby are you running?  Can you post the
>> output from:
> 
> 
> I've tried both the lastest stable and recent nightly build. No 
> difference.
> 
>> java org.apache.derby.tools.sysinfo
> 
> 
> $ java -cp "<..>lib/derby.jar"  org.apache.derby.tools.sysinfo
> ------------------ Java Information ------------------
> Java Version:    1.5.0_06
> Java Vendor:     Apple Computer, Inc.
> Java home:       /System/Library/Frameworks/JavaVM.framework/Versions/
> 1.5.0/Home
> Java classpath:  "<..>lib/derby.jar:/System/Library/Frameworks/
> JavaVM.framework/Versions/1.5.0/Classes/.compatibility/ 14compatibility.jar
> OS name:         Mac OS X
> OS architecture: ppc
> OS version:      10.4.6
> Java user name:  lachlan
> Java user home:  /Users/lachlan
> Java user dir:   /Users/lachlan
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.5
> --------- Derby Information --------
> JRE - JDBC: J2SE 5.0 - JDBC 3.0
> [<...>lib/derby.jar] 10.1.2.1 - (330608)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> ------------------------------------------------------
> lachlans-imac:~ lachlan$
> 
> Lachlan Deck
> lachlan@ish.com.au
> +61 2 9550 5001
> 
> 
> 


Re: EmbeddedDriver performance help

Posted by Lachlan Deck <la...@ish.com.au>.
Hi there,

On 05/05/2006, at 10:43 AM, Jean T. Anderson wrote:

> Lachlan Deck wrote:
>>
>> I've been testing an initial population of a Derby database with some
>> 30-40000 records and am finding a large performance difference   
>> between
>> using the EmbeddedDriver vs the ClientDriver i.e., depending  on  
>> whether
>> Derby is run as an embedded or network service for the  application.
>>
>> Using the Embedded driver seems to take an order of magnitude longer
>> than not (e.g., 2-3 hours vs 15 minutes)!
>
> fascinating. Normally the embedded driver is faster.

So I've read.

> Which client JDBC driver are you using?

For embedded Derby: org.apache.derby.jdbc.EmbeddedDriver
For network Derby: org.apache.derby.jdbc.ClientDriver

Both are using the adaptor:  
org.objectstyle.cayenne.dba.derby.DerbyAdapter

> Which specific version of Derby are you running?  Can you post the
> output from:

I've tried both the lastest stable and recent nightly build. No  
difference.

> java org.apache.derby.tools.sysinfo

$ java -cp "<..>lib/derby.jar"  org.apache.derby.tools.sysinfo
------------------ Java Information ------------------
Java Version:    1.5.0_06
Java Vendor:     Apple Computer, Inc.
Java home:       /System/Library/Frameworks/JavaVM.framework/Versions/ 
1.5.0/Home
Java classpath:  "<..>lib/derby.jar:/System/Library/Frameworks/ 
JavaVM.framework/Versions/1.5.0/Classes/.compatibility/ 
14compatibility.jar
OS name:         Mac OS X
OS architecture: ppc
OS version:      10.4.6
Java user name:  lachlan
Java user home:  /Users/lachlan
Java user dir:   /Users/lachlan
java.specification.name: Java Platform API Specification
java.specification.version: 1.5
--------- Derby Information --------
JRE - JDBC: J2SE 5.0 - JDBC 3.0
[<...>lib/derby.jar] 10.1.2.1 - (330608)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------
lachlans-imac:~ lachlan$

Lachlan Deck
lachlan@ish.com.au
+61 2 9550 5001




Re: EmbeddedDriver performance help

Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Lachlan Deck wrote:
> Hi all,
> 
> I've been testing an initial population of a Derby database with some 
> 30-40000 records and am finding a large performance difference  between
> using the EmbeddedDriver vs the ClientDriver i.e., depending  on whether
> Derby is run as an embedded or network service for the  application.
> 
> Using the Embedded driver seems to take an order of magnitude longer 
> than not (e.g., 2-3 hours vs 15 minutes)!

fascinating. Normally the embedded driver is faster.

Which client JDBC driver are you using? Derby Network Client? IBM DB2
JDBC Driver?

Which specific version of Derby are you running?  Can you post the
output from:

   java org.apache.derby.tools.sysinfo

 -jean



> We'd like to use an embedded Derby for deployment but this is 
> unacceptably slow. Does anyone have any ideas on what may be causing  this?
> 
> I'm not that familiar with Cayenne as yet (but am with EOF), so is 
> there some way in Cayenne of adjusting the settings at run time (or 
> other) for the JDBC connection?
> 
> Perhaps Derby's default locking scheme is getting deadlocked  somewhere
> or perhaps the Embedded Driver has some problems. Any  pointers would be
> greatly appeciated.
> 
> Thanks.
> 
> Lachlan Deck
> lachlan@ish.com.au
> +61 2 9550 5001
> 
> 
> 


Re: EmbeddedDriver performance help

Posted by Mike Kienenberger <mk...@gmail.com>.
On 5/4/06, Lachlan Deck <la...@ish.com.au> wrote:
> I've been testing an initial population of a Derby database with some
> 30-40000 records and am finding a large performance difference
> between using the EmbeddedDriver vs the ClientDriver i.e., depending
> on whether Derby is run as an embedded or network service for the
> application.
>
> Using the Embedded driver seems to take an order of magnitude longer
> than not (e.g., 2-3 hours vs 15 minutes)!

There's lots of possibilities.   One that comes to mind is that your
embedded database may be hitting a maximum memory size and spending
all of its time swapping/garbage collecting.

I've seen this happen with an embedded HSQLDB database.