Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Thu, 19 Jun 2008 08:29:56 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Derby Memory Issues in CDC
In-reply-to: <7b813ada0806182036n4d5bc796i147f4aebb4a7b36@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <485A7B74.20506@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <7b813ada0806182036n4d5bc796i147f4aebb4a7b36@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)

Hi Albert,

Some comments inline...

Albert Kam wrote:
> Hello all,
>
> I'm currently developing on CDC 1.0, using Creme JVM, running on HP 
> iPAQ 6965 pocket pc device.
> I'm using Apache Derby as the embedded database for the application.
>
> I notice that the Java app has only about 29MB of memory available, as 
> the rest has been consumed
> by the OS or other things.
>
> Although the RAM is 64MB, when i click on the Start -> Setting -> Memory,
> it says only 48MB memory available, not sure why.
>
> Anyway this means, i gotta know how much memory Apache Derby consumes.
> Here's the Derby version i'm using : derby-10.3.1.4
> Here's my minimum derby.properties configuration :
> derby.storage.pageSize=4096
> derby.storage.pageCacheSize=40
>
> Fortunately i can use Creme monitoring tool to monitor the memory 
> usage at real time.
> I notice that if i start a simple program, just a simple 
> System.out.println, and then the program
> goes to sleep for around 10 secs, i notice the memory used is around 
> 850KB.
>
> Then i tried running a select statement using Apache Derby embedded 
> database, and then goes
> to sleep for around 10 secs, i notice the memory usage is around 10MB ..
> After this, i modify the source to select from around 7 tables, and i 
> notice the memory usage is stable
> around 12MB .. I tried looping this process for around 40 times, and 
> it's still stable around 12 MB
>
> I try with another program that issues delete for all those 7 tables, 
> and the memory usage is around 15MB.
> All these tests are conducted without any GUI or any other things that 
> could consume large memory usage.
> All these tests also use plain Statement, as i was concerned about 
> PreparedStatement that could increase the
> memory consume as they're cached by the Derby. I think, slower is 
> better rather than not be able to run at all
> because of memory limitation.
I don't think that using Statement rather than PreparedStatement is 
going to save any space. Derby caches the compiled query plan regardless 
of what JDBC call was used to compile the plan. The cached plans are 
retrieved using the query text as the key. PreparedStatements actually 
let you save space in the plan cache because PreparedStatements let you 
use ? parameters in your query text--that, in turn, lets Derby re-use 
the same query text (and plan) for multiple queries. For more 
information, please see the section titled "Using the statement cache" 
in the Derby Tuning Guide: http://db.apache.org/derby/docs/10.4/tuning/

>
> My temporary conclusion is this :
> - The attempt to use embedded database for the first time will consume 
> around 9MB of memory
> - The attempts to use the statement (insert, update, select, delete) 
> for the first time increase the memory usage too.
> I dont know if setting the pageSize and pageCacheSize has any effects 
> on this point.
>
> If my conclusion is temporary correct, then the memory usage goes up 
> along with the number of tables i'll operate on,
> and this is very limiting.
It would be interesting to see what classes are filling up your memory 
as you run these experiments. Please share that with the community if 
your Creme monitoring tools give you that insight. I think that Derby 
has been optimized for performance rather than memory usage and it may 
be that memory is being consumed by structures which could be thrown 
away and recreated on memory-constrained devices. We could experiment 
with adding some tracepoints which optimize Derby for memory usage 
rather than performance--but first we would need to know where your 
memory is going.

Thanks,
-Rick
>
> Is there anyway i could limit memory usage ?
>
> Please help ..
>
> Regards,
> Albert Kam
>