db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: How to best constrain database space with many records being inserted and deleted
Date Tue, 06 Jul 2010 19:21:15 GMT
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