Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Date: Mon, 10 Mar 2008 15:48:20 +0100
From: =?ISO-8859-1?Q?=D8ystein_Gr=F8vlen?= <Oystein.Grovlen@Sun.COM>
Subject: Not able to get in-place compression to release space to file system
Sender: Oystein.Grovlen@Sun.COM
To: derby-dev@db.apache.org
Message-id: <47D54A34.8070408@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 8BIT
User-Agent: Thunderbird 2.0.0.9 (X11/20071119)


I have been experimenting a bit with in-place compression on a large
table, but I have not been successful in getting it to give back space
to the file system.  I have table that I have done a lot of bulk
changes to over time.  It is now much smaller than it was at its peak
size, but I am not able to reduce the size of the corresponding files
by in-place compression.  My last attempt:

ij> select conglomeratename, isindex, numallocatedpages, numfreepages, 
pagesize, estimspacesaving from new 
org.apache.derby.diag.SpaceTable('T') t order by conglomeratename;
CONGLOMERATENAME 
 
|ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE 
|ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
T 
                                                         |0     |8671 
              |411071              |4096       |1683746816
X_I 
                                                         |1     |757 
              |19535               |4096       |80015360

2 rows selected
ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('OYSTEIN', 'T', 1, 1, 1);
0 rows inserted/updated/deleted
ij> select conglomeratename, isindex, numallocatedpages, numfreepages, 
pagesize, estimspacesaving from new 
org.apache.derby.diag.SpaceTable('T') t order by conglomeratename;
CONGLOMERATENAME 
 
|ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE 
|ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
T 
                                                         |0     |7843 
              |411899              |4096       |1687138304
X_I 
                                                         |1     |757 
              |19479               |4096       |79785984

2 rows selected

As you can see, there is a lot of free pages, both before and after
the compress, and the total number of pages has not been changed.

Some more details:

The table has two columns an integer and a varchar(1024) column.  All
values for the varchar column is the same 640 characters.  (The
varchar column was added by alter table.) I have a unique index on the
integer column.  I have populated the index by repeatedly doubling its
size by 'insert into T select i+k, c from T', where k is the current
number of records in the table.  I have also deleted rows and
reinserted the values, usually the rows with highest integer values,
but at one point I executed 'delete from T where random() > 0.25'.  I
have also reinserted some of the rows that was deleted by that query.
After the random deletions I have several times tried to compress the
table with no luck.  Finally, I deleted all rows with an integer value
higher than 65536, but compress does still not release pages as shown
above.

-- 
�ystein