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! :)

I think a PHP script might ground to a halt on my database but nice hack.

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.

usefull, thanks! *install*

so this is similer to the repair feature in vb?

thnx for nice hack GamingForce.
but just a small query is this useful for small boards ?
lets say which have 50,000 - 60,000 posts ?

Regards,

how long have you been running it gaming force as i would fear that it would currupt some data one in a freak accident that would cos me :(

to make the script to not stuck the server and the db process, inside the code, just after add this, so it will add a second of pause:

Erm ... that causes unnecessary CPU load (if PHP isn't smart enough to just skip this).

Better use
:)

hum, ya, i forgot sleep()... thanks KirbyDE

thanks :)

installed and clicked

It's useful regardless of the amount of posts your board has.

What is the difference between this and the vbulletin repair/optize function that is built in (other than being automatic of course).

Thanks.

I dont think there is one, at least not that i can tell.

its automatic thats different and it uses the sleep command :D

I'm not too sure of running an optimize table function automatically like this. :) There is a small but real risk of database corruption. Make sure you run a backup script daily as well if you're going to do this before optimizing your tables (something you should always do anyway).

I've never heard of database corruption through the optimize function?

Neither have I - in fact, OPTIMIZE TABLE has REPAIR built-in, so if a table is corrupted this will repair it as well. However, unless you are going to switch off your forums, running OPTIMIZE on a live database runs a small risk of something wrong happening. That's all I'm saying. :)

Erwin is correct. The hack is good and I will run it knowing the dangers. Wouldn't take much to add a repair to it before optimize.

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

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.

Installed...but pixelpunk has a great idea!

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.

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

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

exactly. great point sir!

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.

Ahh cool sounds good then :)

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

Today I wad doing an optimizing/repair from admincp. I had to do it about five or six times since it never repairs completly in one run, and that crashed the database!!! I had to go into phpMYAdmin and repair the user table to get the boards back-up. It was the first time the database had ever crashed while doing a repair/optimizing. It might be the php version. Yesterday I had a Pro upgrade me from the buggy 4.2.2 version to 4.3.3.

Just a quick question - I installed this and ran it, and I got no message just a white screen and my broswer window said "Done". Just making sure it wasn't supposed to give a message when it's done? (Want to make sure it's actually working.) Thanks! :D

PS I ran the file manually because I wanted to close the board while I did it. :)

Hmm. Nice idea, but i get this :

Warning: main(./includes/dboptimize.php): failed to open stream: No such file or directory in /home/minimoto/public_html/vb/admincp/cronadmin.php on line 56

Fatal error: main(): Failed opening required './includes/dboptimize.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/minimoto/public_html/vb/admincp/cronadmin.php on line 56

Any ideas?

Edit : Scrap that found the mistake :

In your install.txt file you have :


5) Change Filename to: ./includes/dboptimize.php

It should be :

5) Change Filename to: ./includes/CRON/dboptimize.php

;)

5) Change Filename to: ./includes/dboptimize.php

should be ./includes/cron/dboptimize.php

Thanks for this hack :)

*installs*

I would like to know the answer to this question too, please. :)

Zachery said there was none built in, but I think thegunowner149 referred to this:

Be carefull running Optimize every day. It's really not needed, but once a week or even once a month. It depends on your boards' traffic.
I see your running the query "OPTIMIZE TABLE" . From the MySQL site:
Click here to read the rest from MySQL 14.5.2.5 OPTIMIZE TABLE Syntax
You may want to set the query to run once a week, or month, by adding the info in to the Scheduled Tasks manager and change the wording from "Daily" to "Weekly" or "Monthly" Database Optimization.
Also, look at your tables over a few days, and you will see the ones that consistantly need optimizing. You'll find that not all of the tables need constant optimizing.
The ones that Gamingforce has on the list of ############ TABLES TO OPTIMIZE ################ are about the most common ones, but you can add and/or delete tables on the list according to your own database usage.
You could also split up the optimizing in to two cron tasks. One for daily (most used tables), and one for weekly/monthly, etc.
John

Is anyone going to answer this question? Is this hack still supported?

no real difference as far as i can see, this just allows for you to do it automatically via vBull's cron scheduler.