db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jim Newsham" <jnews...@referentia.com>
Subject tuning/storage questions
Date Fri, 01 Jun 2007 20:49:04 GMT

I'm reading the tuning guide and trying to eke out a little more performance
for our app by tweaking storage parameters.  The properties our database
exhibits which seem potentially of important to storage tuning are:


-          tables with small records (typically 3 fields; usually fixed
size, but some have varchar)

-          few, but large (number of records per) tables

-          frequent inserts

-          once inserted, data will likely not change (it's possible for an
update operation, but in practice may never happen)

-          several indexes per table

-          queries usually involve joins and are usually selective

-          some queries return somewhat large result sets

-          queries currently take longer than we would like


1. Because updates will be very rare, I think it makes sense to set
pageReservedSpace to 0%.  If an update occurs which expands the size of a
row (unlikely but possible), there will be a performance hit (I assume
during both the update and subsequent reads of this record, because the
documentation implies that part of the row will be written to an overflow
page).  But my pages will hold 25% more data, which will be a big win for
the normal case.  Reasonable?


2. Page size is a little less straightforward.  The following imply page
size should be small:


-          not storing large objects

-          small columns/records

-          selective queries


The following imply page size should be large:


-          tables with large numbers of records

-          inserts only (?? The documentation says read-only apps should use
a large page size.  I'm not quite sure of the reasoning for this.  Would
this also apply to an app such as mine which only performs inserts but not


The documentation says large page size for indexes is a big performance
gain.  I was wondering if it makes sense to have small pages for all tables,
and large pages for all indexes.  Does having heterogeneous page sizes have
any adverse effects on the page cache?  Does having mismatched page sizes
between a table and its indexes have any negative impact?


3.  What is the page cache eviction policy?   Is it least-frequently-used?
Do indexes get preferential treatment?  Since some queries pull a large
amount of data, I think this could potentially repopulate the entire cache
with data which may not be used again.  Does this mean it would be wasteful
to increase the page cache size?



Jim Newsham

View raw message