From derby-user-return-13834-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Aug 8 10:46:11 2011 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 3B7A28CDA for ; Mon, 8 Aug 2011 10:46:11 +0000 (UTC) Received: (qmail 17278 invoked by uid 500); 8 Aug 2011 10:46:10 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 16930 invoked by uid 500); 8 Aug 2011 10:46:00 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 16918 invoked by uid 99); 8 Aug 2011 10:46:00 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 08 Aug 2011 10:46:00 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [66.111.4.27] (HELO out3.smtp.messagingengine.com) (66.111.4.27) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 08 Aug 2011 10:45:51 +0000 Received: from compute6.internal (compute6.nyi.mail.srv.osa [10.202.2.46]) by gateway1.messagingengine.com (Postfix) with ESMTP id A8544207EA; Mon, 8 Aug 2011 06:45:29 -0400 (EDT) Received: from web1.messagingengine.com ([10.202.2.211]) by compute6.internal (MEProxy); Mon, 08 Aug 2011 06:45:29 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=message-id:from:to:cc:mime-version :content-transfer-encoding:content-type:references:subject :in-reply-to:reply-to:date; s=smtpout; bh=8a+GqioLYjL76VjWnBUzii Raw28=; b=G5kxrV/HG8YvAoFYqCl5iLIkvvdbbQIeS3kZRj2q33YuHU0ysBVZ5y Mmlwt9hoohA/j8RPmivu762frdHb8DRg/NTpTTQFI7a4Ytg1byrwfFV4ieXp04xq VH++caFXURBHPcjJSFj4O3HQZfE6K34AZ9oL4HHg/s6VkMTJMzbf0= Received: by web1.messagingengine.com (Postfix, from userid 99) id 81DD65E0CD6; Mon, 8 Aug 2011 06:45:29 -0400 (EDT) Message-Id: <1312800329.13528.2160673981@webmail.messagingengine.com> X-Sasl-Enc: dNJ+hrA5wFydLcqB6iN+qPpk+LqnkVhWsILmLdMFeuBT 1312800329 From: "Phil Bradley" To: "Derby Discussion" Cc: Tim.DeMeyer@cegeka.be MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary="_----------=_1312800329135280" X-Mailer: MessagingEngine.com Webmail Interface References: <66E61F081FCB7E479D37FCA3235627D901D79376@sv-int-mail1.cegekanv.corp.local> Subject: Re: Derby eating up disk space + how to trigger ReclaimSpace elegantly In-Reply-To: <66E61F081FCB7E479D37FCA3235627D901D79376@sv-int-mail1.cegekanv.corp.local> Reply-To: philb@tower.ie Date: Mon, 08 Aug 2011 11:45:29 +0100 X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. --_----------=_1312800329135280 MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="us-ascii" Date: Mon, 8 Aug 2011 06:45:29 -0400 X-Mailer: MessagingEngine.com Webmail Interface Hi, For what it's worth, I had an approximately similar experience with approximately similar volumes of data on a desktop app using Derby embedded. I introduced a daily compress of all tables using the following command (for each table): call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('MYSCHEMA', 'MYTABLE', 0) The schedule for this was handled at the application level rather than the derby level. Specifically, we used quartz to do the scheduling. Also, as you mention, this is on a per table basis. I don't believe there is a single command that you can use to compress the whole database so we just use the database metadata to list all tables and apply the procedure to each. Regards, Phil On Fri, 05 Aug 2011 16:35 +0200, "De Meyer Tim" wrote: Hi, We're working on a java webapp and using a derby database (v10.5) in embedded mode. The first version of the application went live about 2 months ago. The application is used to make invoices and we also store the XML of an invoice document (input data for generating a PDF against a template). Today, the database is about 1,7GB on disk. During these 2 months, we've released some minor upgrades, including database migration scripts (like extra tables for new functionality). Now we've noticed that, when we ran our latest upgrade, the database has suddenly shrunk to a size of 600MB. This latest upgrade contained a migration script that dropped a no longer needed column on practically every table. It's after executing this script that the shrink happened. We did some debugging and hit suspend when the disk size started shrinking, it lead us to a Derby class called "ReclaimSpace". The shrink was a bliss, because the customer was already complaining about the large size on disk :-) We're afraid the database will start using up unnecessary space again soon, and of course, we're not going to have a similar migration script in every upgrade. Is there an elegant way to configure derby to do this cleaning continuously, or to let our webapp instruct derby to do some cleaning? We're launching the webapp from within a small java webstart app (we launch a Jetty and attach our war file), so it's even OK for us to write some java code to do it programmatically. We've found this, but it's on a per table basis. [1]http://db.apache.org/derby/docs/10.1/adminguide/cadminspace 21579.html [2]http://db.apache.org/derby/docs/10.5/ref/rrefaltertablecomp ress.html By the way, we're running about 10 of these webapps on different desktop PC's. Each webapp synchronizes its data to a server running a postgresql 9 database. This means that this postgres database accumulates all the data of these 10 webapps (some data is shared, so it's not times ten). The size of this postgres DB is less than 1GB, which we think is surprisingly small compared to the derby DB for one webapp. Any help on all of this would be more than welcome. Kind regards, Tim De Meyer References 1. http://db.apache.org/derby/docs/10.1/adminguide/cadminspace21579.html 2. http://db.apache.org/derby/docs/10.5/ref/rrefaltertablecompress.html --_----------=_1312800329135280 MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset="us-ascii" Date: Mon, 8 Aug 2011 06:45:29 -0400 X-Mailer: MessagingEngine.com Webmail Interface
 
Hi,
 
For what it's worth, I had an approximately similar experience with approximately similar volumes of data on a desktop app using Derby embedded. I introduced a daily compress of all tables using the following command (for each table):
 
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('MYSCHEMA', 'MYTABLE', 0)
 
The schedule for this was handled at the application level rather than the derby level. Specifically, we used quartz to do the scheduling.
 
Also, as you mention, this is on a per table basis. I don't believe there is a single command that you can use to compress the whole database so we just use the database metadata to list all tables and apply the procedure to each.
 
Regards,
Phil
 
 
 
 
 
On Fri, 05 Aug 2011 16:35 +0200, "De Meyer Tim" <Tim.DeMeyer@cegeka.be> wrote:

Hi,

We're working on a java webapp and using a derby database (v10.5) in embedded mode.
The first version of the application went live about 2 months ago.
The application is used to make invoices and we also store the XML of an invoice document (input data for generating a PDF against a template).
Today, the database is about 1,7GB on disk.

During these 2 months, we've released some minor upgrades, including database migration scripts (like extra tables for new functionality).
Now we've noticed that, when we ran our latest upgrade, the database has suddenly shrunk to a size of 600MB.
This latest upgrade contained a migration script that dropped a no longer needed column on practically every table.
It's after executing this script that the shrink happened.
We did some debugging and hit suspend when the disk size started shrinking, it lead us to a Derby class called "ReclaimSpace".

The shrink was a bliss, because the customer was already complaining about the large size on disk :-)
We're afraid the database will start using up unnecessary space again soon, and of course, we're not going to have a similar migration script in every upgrade.
Is there an elegant way to configure derby to do this cleaning continuously, or to let our webapp instruct derby to do some cleaning?
We're launching the webapp from within a small java webstart app (we launch a Jetty and attach our war file), so it's even OK for us to write some java code to do it programmatically.

We've found this, but it's on a per table basis.
http://db.apache.org/derby/docs/10.1/adminguide/cadminspace21579.html
http://db.apache.org/derby/docs/10.5/ref/rrefaltertablecompress.html

By the way, we're running about 10 of these webapps on different desktop PC's.
Each webapp synchronizes its data to a server running a postgresql 9 database.
This means that this postgres database accumulates all the data of these 10 webapps (some data is shared, so it's not times ten).
The size of this postgres DB is less than 1GB, which we think is surprisingly small compared to the derby DB for one webapp.

Any help on all of this would be more than welcome.

Kind regards,

Tim De Meyer

--_----------=_1312800329135280--