[
https://issues.apache.org/jira/browse/DERBY-7109?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Holger Rehn updated DERBY-7109:
-------------------------------
Description:
The SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure seems to be unable to
reclaim any disk space under almost all circumstances. I have a number of
existing DBs that I tried to reorganize to reclaim disk space using the
mentioned procedure. Because that didn't even free the smallest amount of
space, I started a number of tests and it looks like inplace compression simply
doesn't work, at all.
Example:
One of the DBs uses ~37 GB of disk space and the exact same amount after
running SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. After using
SYSCS_UTIL.SYSCS_COMPRESS_TABLE to reorganize the DB it only uses 20,4 GB of
disk space. This might be correct, if I correctly understand how inplace
compression should work - but is not really what I expected. But even after I
completely deleted a number of tables and removed more than 90% of the rows
from all other tables of the original DB,
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was unable to reclaim any disk space.
In contrast, SYSCS_UTIL.SYSCS_COMPRESS_TABLE now reduced the DB size to ~
130MB. To carry this to the extreme, I completely removed any data from all
tables. As expected, procedure SYSCS_UTIL.SYSCS_COMPRESS_TABLE now results in
an empty DB only taking up ~4 MB of disk space, while
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was still unable to reclaim a relevant
amount of disk space (only ~ 4,6GB), leaving a completely empty DB still
occupying more than 32 GB of disk space.
For all the mentioned tests with I set PURGE_ROWS, DEFRAGMENT_ROWS, and
TRUNCATE_END to 1.
was:
The SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure seems to be unable to
reclaim any disk space under almost all circumstances. I have a number of
existing DBs that I tried to reorganize to reclaim disk space using the
mentioned procedure. Because that didn't even free the smallest amount of
space, I started a number of tests and it looks like inplace compression simply
doesn't work, at all.
Example:
One of the DBs uses ~37 GB of disk space and the exact same amount after
running SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. After using
SYSCS_UTIL.SYSCS_COMPRESS_TABLE to reorganize the DB it only uses 20,4 GB of
disk space. This might be correct, if I correctly understand how inplace
compression should work - but is not really what I expected. But even after I
completely deleted a number of tables and removed more than 90% of the rows
from all other tables of the original DB,
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was unable to reclaim any disk space.
In contrast, SYSCS_UTIL.SYSCS_COMPRESS_TABLE now reduced the DB size to ~
130MB. To carry this to the extreme, I completely removed any data from all
tables. As expected, procedure SYSCS_UTIL.SYSCS_COMPRESS_TABLE now results in
an empty DB only taking up ~4 MB of disk space, while
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was still unable to reclaim a relevant
amount of disk space (only ~ 4,6GB), leaving a completely empty DB still
occupying more than 32 GB of disk space.
For all of the tests I
> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE doesn't work as expected
> ----------------------------------------------------------------
>
> Key: DERBY-7109
> URL: https://issues.apache.org/jira/browse/DERBY-7109
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.14.2.0, 10.15.2.0
> Reporter: Holger Rehn
> Priority: Critical
>
> The SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure seems to be unable to
> reclaim any disk space under almost all circumstances. I have a number of
> existing DBs that I tried to reorganize to reclaim disk space using the
> mentioned procedure. Because that didn't even free the smallest amount of
> space, I started a number of tests and it looks like inplace compression
> simply doesn't work, at all.
> Example:
> One of the DBs uses ~37 GB of disk space and the exact same amount after
> running SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. After using
> SYSCS_UTIL.SYSCS_COMPRESS_TABLE to reorganize the DB it only uses 20,4 GB of
> disk space. This might be correct, if I correctly understand how inplace
> compression should work - but is not really what I expected. But even after I
> completely deleted a number of tables and removed more than 90% of the rows
> from all other tables of the original DB,
> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was unable to reclaim any disk space.
> In contrast, SYSCS_UTIL.SYSCS_COMPRESS_TABLE now reduced the DB size to ~
> 130MB. To carry this to the extreme, I completely removed any data from all
> tables. As expected, procedure SYSCS_UTIL.SYSCS_COMPRESS_TABLE now results in
> an empty DB only taking up ~4 MB of disk space, while
> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE was still unable to reclaim a
> relevant amount of disk space (only ~ 4,6GB), leaving a completely empty DB
> still occupying more than 32 GB of disk space.
> For all the mentioned tests with I set PURGE_ROWS, DEFRAGMENT_ROWS, and
> TRUNCATE_END to 1.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
