db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jon Pearson <j...@emnico.com>
Subject Re: Derby Maintenance Activities
Date Thu, 11 Mar 2010 09:10:38 GMT
Hi Bryan,
     thanks for responding :-)

Certainly we have a lot of churn in 2 tables due to periodic updates 
which requires the compression.

We find that performance drops significantly (x10) around 1.5 months and 
running the full compression, which rebuilds the indexes, makes a 
dramatic improvement in performance.

We did try to run the compression online but found that some existing 
compiled statements retained a reference to pages in the pre-compressed 
table resulting in exception whenever they were excecuted.  Since then 
we have had to perform the compression offline resulting in downtime.


On 10/03/2010 16:52, Bryan Pendleton wrote:
>> Does anybody have experience of running statistics or compression 
>> which they can share?
> In my production Derby app, which has run 24x7 for over 4 years, I have
> a few tables which I need to compress, approximately every 6 months.
> I have not found it necessary to update statistics on my tables.
> However, my tables are small and don't change very rapidly, which
> is I think the primary reason that I don't need to compress them or
> recompute their statistics very often.
> I think that if you have highly volatile tables, the need for compression
> and statistics is more substantial.
> I think you should set up a mechanism to monitor the performance of your
> most important transactions, and you should keep an eye on it, and you
> should also periodically run SYSCS_DIAG.SYSCS_SPACE_TABLE, etc., and keep
> an eye on those values, and, over time, you will probably develop an
> intuition about what level of performance degration justifies doing a
> statistics or compression pass.
> thanks,
> bryan

Emnico Services Limited, Dauntsey House, Stonehill Green, Westlea, Swindon SN5 7HB
Company No. 06390113

This message is confidential and may contain privileged information.  If you are not the addressee
indicated in this message (or responsible for delivery of the message to them), you may not
copy or deliver this message to anyone or take any action in reliance on it.  If you have
received this e-mail in error, please delete it and notify the sender as soon as possible.
 The Emnico group of companies do not accept any liability for any harm that may be caused
to the recipient's system or data by this message.  Please carry out virus and other such
checks as you consider appropriate.

View raw message