db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Does in-place compress really defragment?
Date Mon, 03 Apr 2006 17:55:38 GMT
there is a lot more work that could be done on the inplace compress,
my guess is that the defragment part is not finding space on the 1/3
empty pages to move the rows to.  The defragment loop basically takes
rows at the "end" of the table and inserts them where the existing
technology tells them there is space (after being reset to look from
beginning rather than end).  It is true that after the defragment
run the only space that can be returned to the OS is the free pages
at the "end", but the defragment run tries to move rows from end to
beginning.

There are a lot of smarter thing that could be done, creating an
in memory data structure with efficient access to number of free
bytes per page could then be used to do a better job of filling
"half" empty pages.

I am most concerned about 7 to 8, that should reduce space assuming
the 171 rows don't all fit on a page.

one question, what sizes of rows are you using (basically are there
actually 300 characters in your "c" column), for quick tests I always
use char columns as they use whatever space is declared no matter
the data.  Varchar uses variable length storage.  It would be 
interesting to throw in a select from the space table vti after every
statement to see what is going on - see
opensource/java/testing/org/apache/derbyTesting/functionTests/tests/store/SpaceTable.sql 

test for some usage.

For major space changes in the table I would recommend the offline
compress, it guarantees to reclaim ALL possible space in tables and
indexes and has the benefit of probably getting better clustering
on disk.

Øystein Grøvlen wrote:
> I tried an experiment with on-line compress and it seems like no space
> is freed unless I delete records at the end of the heap:
> 
> 1. Deleted every third record of a table
> 2. Inline compress with purge&defragment. File size did not change
> 3. Deleted every second of the remaining records
> 4. Inline compress with purge&defragment. File size did not change
> 5. Deleted the last third of the remaining records
> 6. Inline compress with purge&defragment. File size reduced by 1/3. 
> 7. Deleted first half of the remaining records
> 8. Inline compress with purge&defragment. File size did not change
> 
> Is this how it is supposed to be?  I would have thought that each
> compress would defragment the table and free space, but it seems like
> only empty space at the end of a table is freed.  Trace of what I did
> below.  (There are 1536 records in t.  The records have primary keys
> in range [0,1535] and was inserted in sorted order on primary key.
> For all records j==mod(i,3).)
> 
> --
> Øystein
> 
> ij> create table t1 (i integer primary key, j integer, c varchar(300));
> 0 rows inserted/updated/deleted
> ij> insert into t1 select * from t;
> 1536 rows inserted/updated/deleted
> ij> delete from t1 where j=1;
> 512 rows inserted/updated/deleted
> ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
> 0 rows inserted/updated/deleted
> ij> delete from t1 where j=2;
> 512 rows inserted/updated/deleted
> ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
> 0 rows inserted/updated/deleted
> ij> delete from t1 where i > 1024;
> 170 rows inserted/updated/deleted
> ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
> 0 rows inserted/updated/deleted
> ij> delete from t1 where i < 512;
> 171 rows inserted/updated/deleted
> ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
> 0 rows inserted/updated/deleted
> ij> 
> 
> 
> 


Mime
View raw message