db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Iterate over big table...
Date Fri, 19 May 2006 20:13:23 GMT
yves pielusenet wrote:

>I have such a table :
>   data BLOB NOT NULL
>This table should have millions of records. I have to iterate over all
>What is the best way (I don't want to have a outOfMemoryException) :
>for(int i=0; i<nbRows; i++){
>    theData = select data from data where numvign=i;
>    doSomething(theData);
>or :
>alldata = select data from data order by numvign;
>   doSomething(allData.next());
>When I do a big select, are all selected rows into memory or derby
>access to database when it necessary ?
>thanks :)
Hi -
With the proper Derby pageCache and JVM maxHeap settings you should not 
get OutOfMemory (OOM) exceptions when using Derby.  Determining the 
exact, best setting requires iterative testing on your application using 
different settings.  I've included guidelines below on how to balance 
these factors for a system.  The default pageCache setting of Derby 
works well for databases that do not make heavy use of LOB data (thus 
utilizing many 32 K pages rather than the standard 4 K pages).  When you 
select  from the above table you will be using all 32 K pages and should 
compute the proper setting for pageCache and maxHeap to obtain a ratio 
on 16:1 or higher.  I have not heard of OOM exceptions happening in 
systems using a 20:1 ratio or higher.

Here's the basic information you need to know:
Avoinding  OOM exceptions means balancing three things:
     How much of the  physical memory of the machine you can allocate to 
the JVM.
          IMPORTANT: The JVM must fit in physical memory.
     The maximum heap size allowed for the jvm (-Xmx )
     The size of the Derby/Cloudscape pageCache (pageCache * avg. pageSize)

The JVM maximum Heap size (maxHeap) is set using the java parameter 
"-Xmx#m" where # is the maximum number of Mb to allocate to the heap 
(for example java -Xmx128m - for a 128 Mb maxHeap).
The Derby pageCache size is set using the property 
derby.storage.pageCacheSize. pageCache and is specified in database 
pages (which can be of various sizes: 4K for standard tables, 32K for 
tables with LOBs). 

The following sizing method computes the number of pages to allocate to 
the pageCache as a ratio of the JVM maximum Heap size.  This ratio for a 
default implementation of Derby is 16:1 (default sizes:  pageCache of 
1000 pages, only 4 K pages in the database running in a 64 Mb JVM) and 
can be calculated using the following formula:  JVM maxHeap / ( (avg. 
page size) * pageCache) / pageCache) [ 64M / ( (1000*4)/1000 ].  If you 
are getting OOM exceptions in Derby it is likely that your 'avg. page 
size' is greater than 4 K.

View raw message