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: neutral (nike.apache.org: local policy)
Message-ID: <4B98B38E.9070000@emnico.com>
Date: Thu, 11 Mar 2010 09:10:38 +0000
From: Jon Pearson <jonp@emnico.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
 rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: Derby Maintenance Activities
References: <4B8678F1.7080709@emnico.com> <4B8B9978.2060808@emnico.com>
 <4B97C050.8080808@emnico.com> <4B97CE34.3000203@gmail.com>
In-Reply-To: <4B97CE34.3000203@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.


Jon

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.