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, -Rick Any information will be greatly appreciated. Brett
RE: Question about TRUNCATE TABLE and freeing disk space
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.hille...@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, -Rick Any information will be greatly appreciated. Brett
Re: Question about TRUNCATE TABLE and freeing disk space
Bergquist, Brett bbergqu...@canoga.com writes: immediately brought this down to 46Gb of disk space. I do not have a checkpoint in my utility application. You can call it from ij or via JDBC: CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE() http://db.apache.org/derby/docs/10.7/ref/rrefcheckpointdbproc.html Dag
RE: Question about TRUNCATE TABLE and freeing disk space
My point Dag is that even though I did not checkpoint the database, Derby released the disk space back to the OS (Solaris 10) when I dropped the table, so my experience is not matching what Rick had mentioned in that the disk space would not be released until a checkpoint. My experience is saying that the disk space was released immediately. Is there some implicit checkpoint being done? Note that my utility is connecting to the database using the network server mode and the database was not shutdown before the space was released back to the OS. -Original Message- From: Dag H. Wanvik [mailto:dag.wan...@oracle.com] Sent: Tuesday, March 29, 2011 10:32 AM To: derby-dev@db.apache.org Subject: Re: Question about TRUNCATE TABLE and freeing disk space Bergquist, Brett bbergqu...@canoga.com writes: immediately brought this down to 46Gb of disk space. I do not have a checkpoint in my utility application. You can call it from ij or via JDBC: CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE() http://db.apache.org/derby/docs/10.7/ref/rrefcheckpointdbproc.html Dag
Re: Question about TRUNCATE TABLE and freeing disk space
In all these experiments it is important to note when a commit of the transaction is done. It is probably the case that autocommit is assumed and each statement being discussed is a separate transaction. But thought I would just raise the issue, derby definitely can not release the space until the transaction doing the truncate commits. This is why the extra files are created on truncate and drop, so that if necessary we can abort the change by reverting back to the saved files. The cleanup of these extra files happen at checkpoint time as that is when we match up these objects with the transactions that created them and if those transactions are committed or aborted then we can go ahead and do the cleanup. checkpoints happen automatically by the system at various times including: o clean shutdown o recurring while running based on amount of data logged o when requested explicitly using procedure call Maybe in your system with the system running flat out you are automatically generating the checkpoints in the background and this is the difference between what you and rick are seeing. /mikem Bergquist, Brett wrote: My point Dag is that even though I did not checkpoint the database, Derby released the disk space back to the OS (Solaris 10) when I dropped the table, so my experience is not matching what Rick had mentioned in that the disk space would not be released until a checkpoint. My experience is saying that the disk space was released immediately. Is there some implicit checkpoint being done? Note that my utility is connecting to the database using the network server mode and the database was not shutdown before the space was released back to the OS. -Original Message- From: Dag H. Wanvik [mailto:dag.wan...@oracle.com] Sent: Tuesday, March 29, 2011 10:32 AM To: derby-dev@db.apache.org Subject: Re: Question about TRUNCATE TABLE and freeing disk space Bergquist, Brett bbergqu...@canoga.com writes: immediately brought this down to 46Gb of disk space. I do not have a checkpoint in my utility application. You can call it from ij or via JDBC: CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE() http://db.apache.org/derby/docs/10.7/ref/rrefcheckpointdbproc.html Dag
RE: Question about TRUNCATE TABLE and freeing disk space
Very good explanation! Thank you very much. -Original Message- From: Mike Matrigali [mailto:mikem_...@sbcglobal.net] Sent: Tuesday, March 29, 2011 12:59 PM To: derby-dev@db.apache.org Subject: Re: Question about TRUNCATE TABLE and freeing disk space In all these experiments it is important to note when a commit of the transaction is done. It is probably the case that autocommit is assumed and each statement being discussed is a separate transaction. But thought I would just raise the issue, derby definitely can not release the space until the transaction doing the truncate commits. This is why the extra files are created on truncate and drop, so that if necessary we can abort the change by reverting back to the saved files. The cleanup of these extra files happen at checkpoint time as that is when we match up these objects with the transactions that created them and if those transactions are committed or aborted then we can go ahead and do the cleanup. checkpoints happen automatically by the system at various times including: o clean shutdown o recurring while running based on amount of data logged o when requested explicitly using procedure call Maybe in your system with the system running flat out you are automatically generating the checkpoints in the background and this is the difference between what you and rick are seeing. /mikem Bergquist, Brett wrote: My point Dag is that even though I did not checkpoint the database, Derby released the disk space back to the OS (Solaris 10) when I dropped the table, so my experience is not matching what Rick had mentioned in that the disk space would not be released until a checkpoint. My experience is saying that the disk space was released immediately. Is there some implicit checkpoint being done? Note that my utility is connecting to the database using the network server mode and the database was not shutdown before the space was released back to the OS. -Original Message- From: Dag H. Wanvik [mailto:dag.wan...@oracle.com] Sent: Tuesday, March 29, 2011 10:32 AM To: derby-dev@db.apache.org Subject: Re: Question about TRUNCATE TABLE and freeing disk space Bergquist, Brett bbergqu...@canoga.com writes: immediately brought this down to 46Gb of disk space. I do not have a checkpoint in my utility application. You can call it from ij or via JDBC: CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE() http://db.apache.org/derby/docs/10.7/ref/rrefcheckpointdbproc.html Dag
