Author: Skinny Vinny
Modification Description: Adds automated mysql table optimization. Optimizes ALL tables of connected database, including custom mod tables and site tables from phpBB integration.
Modification Version: 1.0.1
Requirements: This modification is for boards/sites running on mysql databases only! Other dbs require a different modification. Built in safety to only run on mysql installations.
Modification Download: http://svmods.com/download/svmods_optim ... tabase.zip
I happened to notice the 'phpBB optimization' mod in these forums and thought that although their idea/intention was good, their application of such a mod was incorrect. There's no reason this can't be automated and encompass the entire database.
This is another hook. Well, kind of: it doesn't 'hook' into anything, but being named as a hook and placed in the hook directory, the script is included in phpBB execution. It only runs on cron execution (when IN_CRON defined), and defaults to only running once a week.
The hook builds a list of every table in your database, and optimizes them all. The functionality of your site/boards are not interrupted during optimization: the db reads from the temp tables optimization creates during execution, and update/insert queries should pend completion. At most, activity during optimization may result in a few second delay in loading. This is only when the optimization query is executing.
Update, just adding text to explain mysql optimization:
If you wish to change the frequency after installation, simply update the config table (find config name svmods_optimize_db_freq) to the number of days you wish between optimization. Very large and high traffic boards may wish to do this daily. Smaller boards far less frequently.OPTIMZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space.
OPTIMIZE TABLE works by making a temporary copy of the original table. The old table is copied to the new table (without the unused rows), then the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirected to the new table without any failed updates. While OPTIMIZE TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready.
Should any issues arise from the use of the hook as scripted, please let me know immediately so that I can address them. Please give feedback regardless, as I believe little testing is required beyond my own tests and it should be safe to move this along to an rc.