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!

[barnest2]

Exactly what I've been looking for!

[TwinsX2Dad]

Originally Posted by Gamingforce

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

You are 100% correct. No danger in optimizing while the board is live UNLESS you were also manually changing the DB - even then, the risk is so slight as to not be concerned.

Originally Posted by Loki12

Is anyone going to answer this question?

There is no difference between this process and the vB optimize/repair feature, which is just an overlay to doing it directly in your DB, except that this is automatic.

When running a DB process like this, you can choose 'repair' which simply fixes corrupted tables, which are repairable. You can also choose 'optimize' which first repairs (if needed), then optimizes (removes dupes and unneeded entries). You can always use 'optimize' unless you're manually editing the DB and trying to find out what is happening.

This hack uses the 'optimize' command. I guess if you were to need to manually work your DB, a process few have a handle on, you could disable this hack, do your work, then enable it when all is done.

I've run automated DB optimization scripts for a long time - on vB (or IPB) databases, as well as DBs for other purposes and I've never had a problem. The difference between those scripts and this one is that this is housed within vB and is free.

So, go ahead and use it. It is a good thing.

[dwh]

As stated earlier, the docs say it isn't needed to run this that often.But what definitely does benefit the server is running analyze.
http://dev.mysql.com/doc/mysql/en/analyze-table.html

In the optimize section they said optimize is mapped to analyze. Idon't know if that means analyze is done along with optimize, butanalyze can by itself make mysql do a quicker job returning data injoins and the difference can at times be dramatic.

[Elfo King]

Is possible to modify it for repair and optimize for all table ?

Thx !

[Borimikan]

sows this work for 3.5.3?

[Fenriz]

Is it possible to make the same for 3.6.8? Please......