Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of francois.orsini@gmail.com
 designates 66.249.82.192 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=p3JYCSkTeCExBBMo9uJoYyv3J03aHA3Y/3dtSNqRqkZEmXSK+EEAY9c9M+wOOblCqNDfI03PekBO9B+LBHAfhSaXW7LxOcx4KtIpXpwVGWhuVWoQV1cGK9GZV1WuA1eG/T0jeCjDYpia5W8M8yAqeE/vNVM2/SkdKmgFQRtswKc=
Message-ID: <7921d3e4050816114857d30c61@mail.gmail.com>
Date: Tue, 16 Aug 2005 11:48:05 -0700
From: Francois Orsini <francois.orsini@gmail.com>
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: [jira] Created: (DERBY-510) DERBY-132 resolved ? Table not
 automatically compressed
In-Reply-To: <43021907.3060006@sbcglobal.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <1422164751.1124196655093.JavaMail.jira@ajax.apache.org>
	 <43021907.3060006@sbcglobal.net>

Maybe like having somewhat of an "HouseKeeper" module servicing
"tasks/chores" with one of them being data compression, triggered
during Derby "idle" times...configuration settings properties could
let the service chore know which table to compress, if not all the
them...just some thoughts...

--francois

On 8/16/05, Mike Matrigali <mikem_app@sbcglobal.net> wrote:
> Full compression of derby tables is not done automatically, I
> am looking for input on how to schedule such an operation.  An
> operation like this is going to have a large cpu, i/o, and
> possible temporary disk space impact on the rest of the server.
> As a zero admin db I think we should figure out some way to
> do this automatically, but I think there are a number of
> applications which would not be happy with such a performance
> impact not under their control.
>=20
> My initial thoughts are to pick a default time frame, say
> once every 30 days to check for table level events like
> compression and statistics generation and then execute the operations
> at low priority.  Also add some sort of parameter so that
> applications could disable the automatic background jobs.
>=20
> Note that derby does automatically reclaim space from deletes
> for subsequent inserts, but the granularity currently is at
> a page level.  So deleting every 3rd or 5th row is the worst
> case behavior.  The page level decision was a tradeoff as
> reclaiming the space is time consuming so did not want to
> schedule to work on a row by row basis.  Currently we schedule
> the work when all the rows on a page are marked deleted.
>=20
> Volker Edelmann (JIRA) wrote:
>=20
> > DERBY-132 resolved ? Table not automatically compressed
> > --------------------------------------------------------
> >
> >          Key: DERBY-510
> >          URL: http://issues.apache.org/jira/browse/DERBY-510
> >      Project: Derby
> >         Type: Bug
> >     Versions: 10.1.1.0
> >  Environment: JDK 1.4.2, JDK 1.5.0
> > Windows XP
> >     Reporter: Volker Edelmann
> >
> >
> >  I tried a test-program that repeatedly inserts a bunch of data into 1 =
table and repeatedly deletes a bunch of data.
> >
> >     // table is not empty  when test-program starts
> >      derby.executeSelect("select count(*) c from rclvalues");
> >
> >    TestQueries.executeBulkInsertAnalyst(derby.getConnection(), 2000000)=
; // insert 2.000.000 rows
> >         derby.executeDelete("delete from rclvalues where MOD(id, 3) =3D=
 0");
> >    TestQueries.executeBulkInsertAnalyst(derby.getConnection(), 1000000)=
;
> >         derby.executeDelete("delete from rclvalues where MOD(id, 5) =3D=
 0");
> >
> >      derby.executeSelect("select count(*) c from rclvalues");
> >
> > At the end of the operation, the table contains approximately the same =
number of rows. But the size of the database has grown from
> > 581 MB to 1.22 GB. From the description of item DERBY-132, I hoped that=
 Derby does the compression now ( version 10.1.X.X.).
> >
> >
>