We were wondering over on the UnaBoard, since you run with the same database, if you were aware of whether MySQL will automatically recover space when posts, threads, or other data is deleted.
That is to say, if you deleted 10% of the posts from the SDMB, does the database software manage space such that it would shrink by (roughly) that amount? Or would you need to rebuild the database to recover the space, as with Sybase SQL Anywhere? If this is the case, are there specific tools that you use instead to recover the space and compact the database?
I don’t believe MySQL will automatically recover space when you delete posts, threads, etc. My understanding, which is not that definitive, is MySQL file size can automatically grow larger but has to be manually shrunk.
We don’t do too many deletes (in comparison to the large size of the board) so it’s not something we do on a regular basis. I’m aware of two methods for manually shrinking down the size of the underlying MySQL files after a deletion of a large number of records. You can do an ‘OPTIMIZE TABLE <table>’ command in the interactive MySQL tool or you can use the ‘isamchk’ command which is a command line tool provided with the MySQL system.
<<Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See section 4.5.1 OPTIMIZE TABLE Syntax and section 4.4.6.10 Table Optimization.>>
Arnold, jdavis,
Thank you both for that, that was very thorough and very helpful. I’ll look into whether we need to do it and into which option is better for us in case we decide to save disk space, as will Una.
Thanks again,
Fierra.