Nightly Database Optimizer

[ Feature Description ]
This is a simple, very easy to install hack that automatically optimizes your database daily! The optimizer uses MySQL's 'optimize table' function. In simple terms, you can look at the optimize command as a hard drive defragmenter. It reclaims lost space and defragments the database file.

By default, this modification runs the optimize script nightly at 3:22 AM (which statistically on my board is the least busiest time). You can run it weekly, monthly or whenever you want by changing the settings under the Scheduled Task Manager.

The script includes all the tables that get heavy daily activity. You can easily add other tables if you wish by opening up the dboptimize.php file and simply adding extra table names to the end of the list.

[ Version Fix Log ]
1.00 -> 1.01 - July 28, 2004

• Added sleep code that adds a pause after each database process. (Thanks KirbyDE)

Enjoy!

[Gamingforce]

Okay I did a fun stress test earlier today running the Optimize command for 20,000 times on the heavily accessed/updated USER table with 200+ concurrent users on my board and no problems at all.

TCS: Repair is built into Optimize. No need to run both.

[pixelpunk]

what would it take for this script to turn the board off between time A and time B and possibly give the admin the choice to run it on certain days?

It'd be much like eBay (among other sites) closing for maintainence.

[TheComputerGuy]

Installed...but pixelpunk has a great idea!

[Gamingforce]

There is no need to close the board. It's just downtime for your users. vBulletin's CRON system doesn't allow you to select more than one day a week, but I can work around that by having the script run daily, but instead run the code within it only on certain days. I'll look into that.

[TheComputerGuy]

Originally Posted by Gamingforce

There is no need to close the board. It's just downtime for your users.

True, I just thought since it would be a dead time on the board anyways...it would no matter

[Gamingforce]

If it's a dead time, then why close it?

[TheComputerGuy]

Originally Posted by Gamingforce

If it's a dead time, then why close it?

exactly. great point sir!

[pixelpunk]

Statistics can't predict exactly when someone will post during your boards 'dead time.'

There is still that risk of the database becoming corrupt because someone's inserting data into it while it's optimizing so why not kill it 100% just to be safe.

Still a kickin-rad hack though! Thanks GamingForce.

[Ghostsuit]

Originally Posted by Gamingforce

The optimization happens at off peak times and content is still accessible during the optimization process. I have a board with almost a million posts and it works fine.

Ahh cool sounds good then

[Gamingforce]

Originally Posted by pixelpunk

Statistics can't predict exactly when someone will post during your boards 'dead time.'

There is still that risk of the database becoming corrupt because someone's inserting data into it while it's optimizing so why not kill it 100% just to be safe.

Still a kickin-rad hack though! Thanks GamingForce.

I really do think it's 100% safe because MySQL locks the table from updates/inserts while it's optimizing it.