Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts

    Does truncate overwrite free space as well

    Hi, i had someone ask me if truncate table overwrites the free space 256 and i honestly dont know, i looked at the doc for truncate but could not find anything specific. This might be apache setting not a mysql function attribute im thinking. I just dont know?


    I know this next part is more of a apache question but i wanted to try to keep this topic together as much as i could. I did find this as a cron job just in case truncate does not wipe free space but i dont know how accurate this code is.

    http://directedge.us/content/securely-wipe-disks-linux
    Last edited by durangod; 12-16-2012 at 04:29 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,695
    Thanks
    80
    Thanked 4,511 Times in 4,475 Posts
    You mean does it wipe the disk clean? No. Not at all.

    I have done TRUNCATE TABLE on tables with several GIGABYTES of data, and the operation completes almost instantly. No possible way that anything had time to go wipe the disk clean.

    Do you mean does TRUNCATE TABLE give the disk space back to the operating system immediately? That depends on the database type. With MyISAM, yes. Within milliseconds, it seems. You can go look at the actual disk space occupied by MySQL files and see it happen.

    With INNODB, probably not. INNODB tables share disk files and while MySQL can release individual disk blocks, I wouldn't care to bet that it would always do so immediately.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    durangod (12-17-2012)

  • #3
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    Thanks, yeah while it is good news that MyISAM does return the space back to the system almost immediatly i dont think thats gonna be good enough to satisfy this member.

    I was hoping it would or i was hoping there was some kind of option to truncate that would truncate table and make it wipe the data at the same time, but since there is not i will need to look into like the above example and write some code to run a cron or run on damand.

    I also found this but will also do some reading and verify the code does what the it says it does.
    http://www.somacon.com/p336.php

    Thanks again Old Pedant


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •