db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-1187) defragment of inplace compress pass for described dataset is not freeing up empty pages.
Date Tue, 02 May 2006 07:26:47 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1187?page=all ]

Mike Matrigali updated DERBY-1187:
----------------------------------

    Fix Version: 10.1.3.0
                 10.1.2.4

backported fix from trunk to 10.1 line :

r398761 | mikem | 2006-05-01 18:27:21 -0700 (Mon, 01 May 2006) | 11 lines

DERBY-1187

backported fix for DERBY-1187 from trunk to 10.1 branch, targeted for the
10.1.3 release.  Original comments from fix (392439):
Defragment pass was not being committed properly, so when rows at
front of table were being deleted and freeing pages the defragment
pass did not move the rows to the free pages.  Longer term the
defragment pass should commit more often, see DERBY-1188.
Added extra test to OnlineCompressTest.java for this case.

> defragment of inplace compress pass for described dataset is not freeing up empty pages.
> ----------------------------------------------------------------------------------------
>
>          Key: DERBY-1187
>          URL: http://issues.apache.org/jira/browse/DERBY-1187
>      Project: Derby
>         Type: Bug

>     Versions: 10.1.1.0, 10.2.0.0, 10.1.2.0, 10.1.1.1, 10.1.1.2, 10.1.2.1, 10.1.2.2, 10.1.2.3
>     Reporter: Mike Matrigali
>     Assignee: Mike Matrigali
>     Priority: Minor
>      Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.4

>
> For the following script defragment pass is not freeing up the free pages:
> drop table t1;
> create table t1 (i integer primary key, j integer, c char(200));
> insert into t1 values (1, 1, 'a');
> insert into t1 (select t1.i + 1,    t1.j + 1,    t1.c from t1);
> insert into t1 (select t1.i + 2,    t1.j + 2,    t1.c from t1);
> insert into t1 (select t1.i + 4,    t1.j + 4,    t1.c from t1);
> insert into t1 (select t1.i + 8,    t1.j + 8,    t1.c from t1);
> insert into t1 (select t1.i + 16,   t1.j + 16,   t1.c from t1);
> insert into t1 (select t1.i + 32,   t1.j + 32,   t1.c from t1);
> insert into t1 (select t1.i + 64,   t1.j + 64,   t1.c from t1);
> insert into t1 (select t1.i + 128,  t1.j + 128,  t1.c from t1);
> insert into t1 (select t1.i + 256,  t1.j + 256,  t1.c from t1);
> insert into t1 (select t1.i + 512,  t1.j + 512,  t1.c from t1);
> delete from t1 where i < 512;
> select
>     cast(conglomeratename as char(12)) as tabname,
>     isindex,
>     cast(numallocatedpages as int) as alloc,
>     numfreepages,
>     cast(numunfilledpages as int) as unfilled,
>     pagesize,
>     estimspacesaving
> from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
> select
>     cast(conglomeratename as char(12)) as tabname,
>     isindex,
>     cast(numallocatedpages as int) as alloc,
>     numfreepages,
>     cast(numunfilledpages as int) as unfilled,
>     pagesize,
>     estimspacesaving
> from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
> select
>     cast(conglomeratename as char(12)) as tabname,
>     isindex,
>     cast(numallocatedpages as int) as alloc,
>     numfreepages,
>     cast(numunfilledpages as int) as unfilled,
>     pagesize,
>     estimspacesaving
> from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
> Here is example output from a run on the trunk:
> ij> drop table t1;
> 0 rows inserted/updated/deleted
> ij> create table t1 (i integer primary key, j integer, c char(200));
> 0 rows inserted/updated/deleted
> ij> insert into t1 values (1, 1, 'a');
> 1 row inserted/updated/deleted
> ij> insert into t1 (select t1.i + 1,    t1.j + 1,    t1.c from t1);
> 1 row inserted/updated/deleted
> ij> insert into t1 (select t1.i + 2,    t1.j + 2,    t1.c from t1);
> 2 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 4,    t1.j + 4,    t1.c from t1);
> 4 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 8,    t1.j + 8,    t1.c from t1);
> 8 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 16,   t1.j + 16,   t1.c from t1);
> 16 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 32,   t1.j + 32,   t1.c from t1);
> 32 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 64,   t1.j + 64,   t1.c from t1);
> 64 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 128,  t1.j + 128,  t1.c from t1);
> 128 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 256,  t1.j + 256,  t1.c from t1);
> 256 rows inserted/updated/deleted
> ij> insert into t1 (select t1.i + 512,  t1.j + 512,  t1.c from t1);
> 512 rows inserted/updated/deleted
> ij> delete from t1 where i < 512;
> 511 rows inserted/updated/deleted
> ij> select
>     cast(conglomeratename as char(12)) as tabname,
>     isindex,
>     cast(numallocatedpages as int) as alloc,
>     numfreepages,
>     cast(numunfilledpages as int) as unfilled,
>     pagesize,
>     estimspacesaving
> from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
> TABNAME     |ISIND&|ALLOC      |NUMFREEPAGES        |UNFILLED   |PAGESIZE   |EST
> IMSPACESAVING
> --------------------------------------------------------------------------------
> -----------------
> SQL060406034|1     |7          |0                   |0          |4096       |0
> T1          |0     |60         |9                   |0          |4096       |368
> 64
> 2 rows selected
> ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
> 0 rows inserted/updated/deleted
> ij> select
>     cast(conglomeratename as char(12)) as tabname,
>     isindex,
>     cast(numallocatedpages as int) as alloc,
>     numfreepages,
>     cast(numunfilledpages as int) as unfilled,
>     pagesize,
>     estimspacesaving
> from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
> TABNAME     |ISIND&|ALLOC      |NUMFREEPAGES        |UNFILLED   |PAGESIZE   |EST
> IMSPACESAVING
> --------------------------------------------------------------------------------
> -----------------
> SQL060406034|1     |7          |0                   |1          |4096       |0
> T1          |0     |69         |0                   |0          |4096       |0
> 2 rows selected
> ij>

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message