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: puzzling command options for SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE()
Date Tue, 01 May 2012 15:29:33 GMT
On 5/1/2012 7:22 AM, Rick Hillegas wrote:
> According to the Derby Reference Guide,
> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE() is supposed to return file
> space to the operating system if you invoke it with the trailing
> TRUNCATE_END argument set to 1. I am puzzled by the behavior I am seeing.
>
> I am looking at the repro for DERBY-5234. The heap conglomerate of a
> (soon to be) large table starts out as 8192 bytes long. Over time, the
> heap conglomerate grows to be 42672128 bytes long. Then all of the rows
> are deleted from the table and it is compressed with the following call:
>
> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE( 'APP', 'OPERATIONS', 0, 0,
> 1 )
>
> The heap conglomerate shrinks a little, down to 41857024. The Reference
> Guide suggests that this way of invoking SYSCS_INPLACE_COMPRESS_TABLE()
> might not actually do much. Here's what the Reference Guide says: "To
> return the empty free space at the end of the same table, the following
> call will run much quicker than running all options but will likely
> return much less space." In this situation, that's an understatement. In
> this situation, the meaning of the call seems to be "Run fast but don't
> do what I want."
>
> How is the user supposed to figure out which compression options should
> be turned on? Should we recommend that all options should always be
> turned on? Why would anyone want to run fast but not achieve much?
>
> Thanks,
> -Rick
>
This seems like a bug to me, for the case of deleting all rows.  Is 
there any timing issue between the time the deletes happen and when
you execute the inplace compress, could it be the case that background
work to reclaim space just has not had time to do its job?

The goal of the space reclamation system is to always automatically
in a background thread free any page that only has committed deleted
rows on it.  And once a table only has free pages then I believe the
implace compress with only the shrink option should give back
all the space.

The following JIRA lists the known problems where Derby can "leak"
converting these pages to free pages.
https://issues.apache.org/jira/browse/DERBY-5356

I guess it could also be part of the bug that is causing bad pages,
something with page linkages that also causes inplace to fail.

Mime
View raw message