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 Lily Wei <li...@yahoo.com> on 2010/05/12 20:21:58 UTC
question about deleting clob should reclaim disk space like blob
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
Re: question about deleting clob should reclaim disk space like blob
Posted by Lily Wei <li...@yahoo.com>.
Thank you so much, Mike.
I track the disk space with <databasename>/seg0 director only. The behavior is action as expected. Disk space remains the same after deleting rows for table that has clob column. And, disk space return to OS after dropping the table.
I am trying to see the behavior with proper recover and roll forward
the current transactions. I am following the functionTests/tests/store/RecoveryAfterBackup.java.
Lily
________________________________
From: Mike Matrigali <mi...@sbcglobal.net>
To: derby-dev@db.apache.org
Sent: Thu, May 13, 2010 3:01:06 PM
Subject: Re: question about deleting clob should reclaim disk space like blob
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 <mi...@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
> >
> >
>
>
Re: question about deleting clob should reclaim disk space like blob
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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 <mi...@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
> >
> >
>
>
Re: question about deleting clob should reclaim disk space like blob
Posted by Lily Wei <li...@yahoo.com>.
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 <mi...@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
>
>
Re: question about deleting clob should reclaim disk space like blob
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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
>
>