You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Øystein Grøvlen <Oy...@Sun.COM> on 2008/03/10 15:48:20 UTC
Not able to get in-place compression to release space to file system
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
Re: Not able to get in-place compression to release space to file
system
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Mike Matrigali wrote:
> Is the data such that you can post the database? If so does the db
> zip to a managable size?
Yes to both. I accidentally deleted the database, but I had zipped a
previous version of the database that I think will reproduce the
problem. However, it was created before feature freeze so I am not
currently able to access it with the trunk version. I will figure out
how to get around that, make a jira, and upload the database.
>
> Have your recent attempts at in-place compress been single user?
Yes.
>
> What version are you using?
Trunk, but before version was bumped to 10.5.
>
--
Øystein
Re: Not able to get in-place compression to release space to file
system
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Is the data such that you can post the database? If so does the db
zip to a managable size?
Have your recent attempts at in-place compress been single user?
What version are you using?
Øystein Grøvlen wrote:
> Øystein Grøvlen wrote:
>>
>> 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:
>
> I have now deleted all records in the table, but inplace compress does
> still not release any pages:
>
> 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 |1
> |419741 |4096 |1719259136
> X_I |1 |131
> |20105 |4096 |82350080
>
> 2 rows selected
>
>
Re: Not able to get in-place compression to release space to file
system
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Øystein Grøvlen wrote:
>
> 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:
I have now deleted all records in the table, but inplace compress does
still not release any pages:
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 |1
|419741 |4096 |1719259136
X_I
|1 |131
|20105 |4096 |82350080
2 rows selected
--
Øystein