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: question about deleting clob should reclaim disk space like blob
Date Thu, 13 May 2010 22:01:06 GMT
what I was suggesting is that results might be better understood if
you tracked space in the log directory separately from the rest of
the database.

Lily Wei wrote:
> Hi Mike:
> 
>      Thank you so much for explaining the behavior to me.
> 
> I do check the database size including seg0 directory. And, it is 
> understandable that the default for the files in the log directory 
> represents a piece of an ever appended transaction log. I believe with 
> my test data, I might not have any checkpoint happen yet. If I execute 
> the compress comment, I do see the space return back to the OS. (Notice 
> the database size is 11494963 and 1987577)
> 
> The database size: 11494963
> 
> delete rows from TAB
> 
> *****spaceTable before 
> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','TAB',1,1,1)
> 
> CONGLOMERATENAME
> 
>                             |ISIND&|NUMALLOCATEDPAGES   
> |NUMFREEPAGES        |NUMUNFILLEDPAGES    |P
> 
> AGESIZE   |ESTIMSPACESAVING
> 
> ----------------------------------------------------------------------------------------------------
> 
> ----------------------------------------------------------------------------------------------------
> 
> -------------------------------
> 
> TAB
> 
>                             |0     |9                   
> |184                 |1                   |3
> 
> 2768      |6029312
> 
> *****spaceTable after 
> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','TAB',1,1,1)
> 
> CONGLOMERATENAME
> 
>                             |ISIND&|NUMALLOCATEDPAGES   
> |NUMFREEPAGES        |NUMUNFILLEDPAGES    |P
> 
> AGESIZE   |ESTIMSPACESAVING
> 
> ----------------------------------------------------------------------------------------------------
> 
> ----------------------------------------------------------------------------------------------------
> 
> -------------------------------
> 
> TAB
> 
>                             |0     |1                   |0              
>      |1                   |3
> 
> 2768      |0
> 
> *****spaceTable after SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TAB',1)
> 
> CONGLOMERATENAME
> 
>                             |ISIND&|NUMALLOCATEDPAGES   
> |NUMFREEPAGES        |NUMUNFILLEDPAGES    |P
> 
> AGESIZE   |ESTIMSPACESAVING
> 
> ----------------------------------------------------------------------------------------------------
> 
> ----------------------------------------------------------------------------------------------------
> 
> -------------------------------
> 
> TAB
> 
>                             |0     |2                   
> |0                   |1                   |3
> 
> 2768      |0
> 
> The database size: 1987577
> 
>  
> 
> I will test the drop table and hopefully create test data that trigger 
> check point happens.
> 
>  
> 
> So far, all of these behaviors are expected behavior.
> 
>  
> 
> Thanks,
> 
> Lily
> 
> 
>   
> 
> ------------------------------------------------------------------------
> *From:* Mike Matrigali <mikem_app@sbcglobal.net>
> *To:* derby-dev@db.apache.org
> *Sent:* Wed, May 12, 2010 2:47:37 PM
> *Subject:* Re: question about deleting clob should reclaim disk space 
> like blob
> 
> My comments apply to database size as is measured by the files stored
> in the seg0 directory.  It looks like your test includes the log 
> directory.  Space reclamation of files in the log directory is 
> orthogonal to space reclamation in the seg0 directory and trying
> to understand the sum of these 2 is confusing.
> 
> The default for the files in the log directory is that each file 
> represents a piece of an ever appended transaction log.  When a
> checkpoint happens (internal systems determine when this happens),
> then the system figures out which of the older files are no longer
> needed to properly recover and roll forward the current transactions
> and those are deleted.  If the system has enabled the backup with
> rollfoward option then these files are not deleted until the user
> executes an explicit new backup command that allows the older files
> to be deleted.
> 
> The expected behavior of files in the seg0 directory is that space is
> never returned to the OS after deleting rows in a table, until the user
> executes one of the compress commands.  Deleted row space may be used
> by subsequent inserts to the same table.  Also not part of this test, 
> but space of dropped tables will also be returned to the OS.
> 
> 
> Lily Wei wrote:
>  > Hi There:
>  >
>  >  I am seeing delete clob data from a table does not reclaim the disk 
> space. The same behavior does not happen to blob. For Blob, after 
> delete, the database size will decrease. However, for Clob, after 
> delete, the database size remaind the same. If we check the compression 
> space after delete clob using SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE, 
> the size is smaller after delete clob rows. And, this is the same with blob.
>  >
>  >    Is this a bug or expected behavior?
>  >
>  >    I am attaching the repro for you to review.
>  >
>  >    Note the output indicate the databaseSize() did not change after 
> delete clob data from TAB table:
>  >
>  > ===========================
>  > The database size: 11494963
>  > delete rows from TAB
>  > The database size: 11494963
>  >
>  >
> 
> 


Mime
View raw message