Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 17 Feb 2011 21:48:12 +0000 (UTC)
From: "Myrna van Lunteren (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1650648927.213.1297979292061.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] Updated: (DERBY-1213) create new space reclamation test
 based on functionality removed from access.sql test
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-1213?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Myrna van Lunteren updated DERBY-1213:
--------------------------------------

    Issue & fix info: [Newcomer]

> create new space reclamation test based on functionality removed from access.sql test
> -------------------------------------------------------------------------------------
>
>                 Key: DERBY-1213
>                 URL: https://issues.apache.org/jira/browse/DERBY-1213
>             Project: Derby
>          Issue Type: Improvement
>          Components: Store, Test
>    Affects Versions: 10.2.1.6
>            Reporter: Mike Matrigali
>            Priority: Minor
>              Labels: derby_triage10_8
>
> In order to get rid of the inconsistent  regression test results reported in DERBY-990 and DERBY-1210, removed the following 
> from the access.sql script.  This methodology has been problematic and multiple "hacks" have been put in to try and get the
> results reproducible.  The results are very timing dependent on background threads.   The test probably should be rewritten
> as a .java program and at the very least report the differences in space management in the error (rather than the lazy approach
> used because logic was not possible in a .sql script).
> Here is the original scripting:
> --- buddy test case for reclaiming deleted rows during split.
> --- test space reclamation using the SpaceTable VTI.
> ---    o insert bunch of rows with sequential keys.
> ---    o create non-unique index.
> ---    o check space usage for index using SpaceTable VTI
> ---    o delete every other one less than 1000 - this will make normal post
> ---      commit not fire as no page should have all deleted rows (< 1000 avoids
> ---      possible case where last page only has 1 row).
> ---    o commit
> ---    o now reinsert rows into the "holes" which before the fix would cause
> ---      splits, but now will force reclaim space and reuse existing space in
> ---      btree.
> ---    o redo delete, commit and insert several times over
> ---    o check space usage for index using SpaceTable VTI is same as above
> --- set page size to default.
> -call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
> -call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize',
> '1');
> -
> -autocommit off;
> -commit;
> -drop table foo;
> -drop table foo2;
> -
> --- create and load a table with values from 1024 down to 1,
> -create table foo (a int, b char(200), c int);
> -
> -insert into foo values (1024, 'even', 0);
> -insert into foo        (select foo.a - 1, 'odd' , 1 from foo);
> -
> -insert into foo (select foo.a - 2,   foo.b, foo.c from foo);
> -insert into foo (select foo.a - 4,   foo.b, foo.c from foo);
> -insert into foo (select foo.a - 8,   foo.b, foo.c from foo);
> -insert into foo (select foo.a - 16,  foo.b, foo.c from foo);
> -insert into foo (select foo.a - 32,  foo.b, foo.c from foo);
> -insert into foo (select foo.a - 64,  foo.b, foo.c from foo);
> -insert into foo (select foo.a - 128, foo.b, foo.c from foo);
> -insert into foo (select foo.a - 256, foo.b, foo.c from foo);
> -insert into foo (select foo.a - 512, foo.b, foo.c from foo);
> -
> --- insert into the "holes", but different keys (even2 instead of even)
> -create table foo2 (a int, b char(200), c int);
> -insert into foo2 (select * from foo);
> -delete from foo2 where foo2.c = 1;
> -delete from foo2 where foo2.a >= 1000;
> -
> --- create "packed" index.
> -create index a on foo (a, b);
> -commit;
> -
> -create table spaceused (a int);
> -
> --- check space usage on the index
> -insert into spaceused
> -select numallocatedpages
> -from new org.apache.derby.diag.SpaceTable('FOO') t
> -where conglomeratename = 'A';
> -
> --- delete every other row
> -delete from foo where foo.c = 0 and foo.a < 1000;
> --- turn all the deletes into "committed deletes"
> -commit;
> --- wait for post commit on heap deleted rows to finish, so that there is
> --- no interference between post commit heap processing, and btree reclamation
> --- during split.
> -CALL WAIT_FOR_POST_COMMIT();
> -
> --- reinsert
> -insert into foo (select * from foo2);
> -commit;
> --- do the above several times
> -delete from foo where foo.c = 0 and foo.a < 1000;
> -commit;
> --- wait for post commit on heap deleted rows to finish, so that there is
> --- no interference between post commit heap processing, and btree reclamation
> --- during split.
> -CALL WAIT_FOR_POST_COMMIT();
> -
> -insert into foo (select * from foo2);
> -delete from foo where foo.c = 0 and foo.a < 1000;
> -commit;
> --- wait for post commit on heap deleted rows to finish, so that there is
> --- no interference between post commit heap processing, and btree reclamation
> --- during split.
> -CALL WAIT_FOR_POST_COMMIT();
> -
> -insert into foo (select * from foo2);
> -delete from foo where foo.c = 0 and foo.a < 1000;
> -commit;
> --- wait for post commit on heap deleted rows to finish, so that there is
> --- no interference between post commit heap processing, and btree reclamation
> --- during split.
> -CALL WAIT_FOR_POST_COMMIT();
> -
> -insert into foo (select * from foo2);
> -delete from foo where foo.c = 0 and foo.a < 1000;
> -commit;
> --- wait for post commit on heap deleted rows to finish, so that there is
> --- no interference between post commit heap processing, and btree reclamation
> --- during split.
> -CALL WAIT_FOR_POST_COMMIT();
> -
> -insert into foo (select * from foo2);
> -delete from foo where foo.c = 0 and foo.a < 1000;
> -commit;
> --- wait for post commit on heap deleted rows to finish, so that there is
> --- no interference between post commit heap processing, and btree reclamation
> --- during split.
> -CALL WAIT_FOR_POST_COMMIT();
> -
> -
> -insert into foo (select * from foo2);
> -
> -commit;
> -
> --- check space usage on the index
> -insert into spaceused
> -select numallocatedpages
> -from new org.apache.derby.diag.SpaceTable('FOO') t
> -where conglomeratename = 'A';
> -
> --- should only be 1 distinct row in this table since space used by the
> --- index should be the same before and after
> -select count(distinct a)
> -from spaceused;
> -
> --- cleanup
> -drop table foo;
> -drop table foo2;
> -drop table spaceused;

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira