db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Question about TRUNCATE TABLE and freeing disk space
Date Tue, 29 Mar 2011 13:47:45 GMT
I appreciate your taking the time to help me out here Rick.  

I am not seeing the same thing in production with Derby 10.5.x in regards to the space being
released back to the file system however.  I currently have a single table that contains all
of the records and one problem is that I cannot delete records fast enough without affecting
insert rate and also it does not free enough pages for space to be re-used efficiently so
the database is growing without bounds.   Compact is no help as the database needs to run
24/7 with no breaks for maintenance and compact locks up the table.

That being said, I wrote a utility application for emergency maintenance that creates a new
table that is a mirror of the existing table, copies 5 days worth of records to it, drops
the original table, and renames the new table back to the original name and re-creates the
constraints and indexes.  This has the immediate effect of releasing the spaces that was allocated
to the original table back to the file system.  Last night this was run on a database that
was taking 186Gb of disk space and immediately brought this down to 46Gb of disk space.  I
do not have a "checkpoint" in my utility application.

I will run this again to verify what I am seeing, but I believe this to be true.

Right now we are running 10.5.x in production, so I guess I will have to do a test setup with
10.7.x and experiment as well.  I was just hoping that it was something missing in the documentation
on the effect of TRUNCATE TABLE.

-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@oracle.com] 
Sent: Tuesday, March 29, 2011 9:25 AM
To: derby-dev@db.apache.org
Subject: Re: Question about TRUNCATE TABLE and freeing disk space

On 3/29/11 5:02 AM, Bergquist, Brett wrote:
> I have to do a poor man's partitioning because I have a table where 
> records are constantly being added (right now about 1.7 million per 
> day) and I need to purge older records out. My plan is to partition 
> incoming records into separate tables for a week and then to drop a 
> week's worth of data by dropping and recreating a table. I also have a 
> view that consolidates the data across the various weeks back into a 
> single view.
> I see the TRUNCATE TABLE command available in Derby 10.7 and was 
> wondering if it has the same ability to free disk space as dropping 
> and recreating a table or if it will still suffer the problem where 
> the disk space is still allocated until a compact is run? It would be 
> nice to use the TRUNCATE TABLE because I would not have to destroy and 
> re-create my view but if it does not have the same benefits, I will go 
> with the dropping and recreating the table and view.
Hi Brett,

I ran the following experiments.

o I created an empty database. This left me with 71 files in seg0.

o I created two tables and put a row in each one. This bumped the number 
of seg0 files up to 73.

o I truncated the first table. This bumped the number of seg0 files up 
to 74.

o I dropped the second table. This did not alter the number of seg0 
files: the count remained 74.

o I recreated the second table. This bumped the file count to 75.

o I compressed the first table. This bumped the file count to 76.

o I compressed the second table. This bumped the file count to 77.

o I checkpointed the database. This dropped the file count down to 73.

I believe the following is true:

1) When you TRUNCATE a table, you get a new, empty file just like you do 
when you drop and recreate a table. From the point of view of running 
subsequent queries, TRUNCATE table is equivalent to DROP/CREATE.

2) However, the old file is still hanging around (just as it is when you 
drop a table). The space is not reclaimed from the file system.

3) Table compression also creates a new file and does not delete the old 
file. Table compression does not release space to the file system--this 
operation actually claims more space.

4) Space is reclaimed from the file system when you perform a checkpoint.

Hope this pushes the discussion forward,
> Any information will be greatly appreciated.
> Brett

View raw message