db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: How to best constrain database space with many records being inserted and deleted
Date Wed, 07 Jul 2010 18:09:04 GMT
Hi Brett,

You are on the right track with the PURGE_ROWS option.

PURGE_ROWS will find the pages which have only deleted rows on them
and will make those pages available for future insertions. The
available pages will not be returned to the OS, but instead will be
used by Derby when more rows need to be inserted in that table. From
your original question, I think that is what you are looking for.

Hope this helps,
Mamta

On Tue, Jul 6, 2010 at 12:21 PM, Bergquist, Brett <BBergquist@canoga.com> wrote:
> This is the schema of the table in question:
>
>
>
>     CREATE TABLE NPARESULTS
>
>     (
>
>         ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>
>         REPORTKEY CHAR(56) NOT NULL,
>
>         MASTERIP CHAR(4) FOR BIT DATA NOT NULL,
>
>         BOOTCOUNT INTEGER NOT NULL,
>
>         TESTRESULTID BIGINT NOT NULL,
>
>         PROFILEREFID INTEGER NOT NULL,
>
>         ADDRESSREFID INTEGER NOT NULL,
>
>         STARTDATETIME TIMESTAMP NOT NULL,
>
>         ACCURACYLEVEL SMALLINT NOT NULL,
>
>         RESULTFLAG SMALLINT NOT NULL,
>
>         PACKETSSENT INTEGER NOT NULL,
>
>         ROUNDTRIPPACKETS INTEGER NOT NULL,
>
>         DROPPEDPACKETS INTEGER NOT NULL,
>
>         OUTOFORDERPACKETS INTEGER NOT NULL,
>
>         MINROUNDTRIPLATENCY INTEGER NOT NULL,
>
>         MAXROUNDTRIPLATENCY INTEGER NOT NULL,
>
>         TOTALROUNDTRIPLATENCY BIGINT NOT NULL,
>
>         AVGROUNDTRIPLATENCY INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE1 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE2 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE3 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE4 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE5 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE6 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE7 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE8 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE9 INTEGER NOT NULL,
>
>         LATENCYBUCKETVALUE10 INTEGER NOT NULL,
>
>         JITTERMEASUREMENT INTEGER NOT NULL,
>
>         MINLOCALREMOTEJITTER INTEGER NOT NULL,
>
>         MAXLOCALREMOTEJITTER INTEGER NOT NULL,
>
>         TOTALLOCALREMOTEJITTER BIGINT NOT NULL,
>
>         AVGLOCALREMOTEJITTER INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE1 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE2 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE3 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE4 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE5 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE6 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE7 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE8 INTEGER NOT NULL,
>
>         LOCALREMOTEJITTERBUCKETVALUE9 INTEGER NOT NULL,
>
>         MINREMOTELOCALJITTER INTEGER NOT NULL,
>
>         MAXREMOTELOCALJITTER INTEGER NOT NULL,
>
>         TOTALREMOTELOCALJITTER BIGINT NOT NULL,
>
>         AVGREMOTELOCALJITTER INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE1 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE2 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE3 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE4 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE5 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE6 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE7 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE8 INTEGER NOT NULL,
>
>         REMOTELOCALJITTERBUCKETVALUE9 INTEGER NOT NULL,
>
>         CIRCUIT1REFID INTEGER NOT NULL,
>
>         CIRCUIT2REFID INTEGER NOT NULL
>
>     );
>
>
>
> So would this type of schema trigger this kind of problem?  Also, what does
> the “PURGE_ROW” option do?
>
>
>
>
>
> From: Kathey Marsden [mailto:kmarsdenderby@sbcglobal.net]
> Sent: Tuesday, July 06, 2010 2:26 PM
> To: derby-user@db.apache.org
> Subject: Re: How to best constrain database space with many records being
> inserted and deleted
>
>
>
> On 7/6/2010 11:09 AM, Bergquist, Brett wrote:
>
> I  What I am seeing is that the database is growing and it does not seem to
> be reusing the deleted space.  Should it be?  The records being inserted are
> exactly the size of the records being deleted.
>
>
>
> The known issues in this area are:
> https://issues.apache.org/jira/browse/DERBY-4057
> https://issues.apache.org/jira/browse/DERBY-4055
>
> For DERBY-4055 a possible workaround is here:
> https://issues.apache.org/jira/browse/DERBY-4055?focusedCommentId=12680196&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12680196
> Also synchronizing table access has helped some users.
>
> You can verify if you are seeing DERBY-4055 by running with a SANE build and
> putting
> derby.debug.true=DaemonTrace
>
> in your derby.properties.
>
> All that said, I have heard anecdotal reports that there may be another
> bug.  If you can come up with a reproduction that is not either of these
> issues, we would appreciate it.
>
> Thanks
>
> Kathey

Mime
View raw message