db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: tuning/storage questions
Date Fri, 01 Jun 2007 21:06:59 GMT


Jim Newsham wrote:
>  
> 
> 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:
> 
I'll answer below, but what I usually say in these cases is that you
are starting too low.  I would not expect more than a 10% change for
any of these low level stuff.  I would look more at the queries that
are going to slow and see if the right indexes exist for these queries.
Often you can get orders of magnitude changes in this area.

also you don't say if you are using embedded vs. network.  Returning 
large result sets is likely to go much faster in embedded vs. network.
>  
> 
> -          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?
should be completely reasonable for tables with only char colums.   And 
probably reasonable for varchar columns.  Your interpretation of impact 
is right on.  Derby, as zero-admin should already optimize the space for
all fixed length column tables, but it doesn't.  I just filed  	 
DERBY-2746 for this feature - you may want to vote on it or help do the 
work to implement it if you think it
would be useful.
> 
>  
> 
> 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 updates?)
> 
>  
> 
> 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?
There may be a garbage collection impact on the cache.  The current 
cache handles heterogeneous page sizes fine, but when replacing a page
where the size is the same it reuses a page size array, but if it is 
different it throws away the old array and allocates a new one.
> 
>  
> 
> 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?
it is basically lru, so if your queries are using indexes the index 
pages probably stay in cache.  But a scan not using the indexes that is
bigger than cache may throw out everything.
> 
>  
> 
> Thanks,
> 
> Jim Newsham
> 

Mime
View raw message