Technical Database Query for jdavis

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?

Thanks,
Fierra,
Admin of the UnaBoard.

Jerry doesn’t get to read the board all the time, so I’m not sure if he’ll see this right away.

I’ll forward it to him. No promises, though.

You might get more satisfaction and quicker answers taking this to the vBulletin’s own board:

vBulletin Community Forum
(http://www.vbulletin.com/forum/index.php)

Hope this helps.

your humble TubaDiva

Thanks for taking the time to do that, Tuba.

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.

I checked with a fellow DBA who has used MySQL and she tells me the same thing that jdavis did.

From the manual:
5.11 DELETE Syntax

<<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.

Could someone maybe provide a URL for this wonderous, mythical UnaBoard I hear so much about?

The Unaboard’s address is http://65.69.77.33/forums/index.php and the staus page is atwww.geocities.com/unaboard.