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!

[nintendo]

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.

[Sadie Frost]

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!

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

[manguish]

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?

[manguish]

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

[venomx]

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

should be ./includes/cron/dboptimize.php

[Megareus Rex]

Thanks for this hack

*installs*

[Loki12]

Originally Posted by thegunowner149

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

Thanks.

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:

[wirewolf]

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:

"OPTIMIZE 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 and to defragment the data file.

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it's not likely that you need to do this more than once a week or month and only on certain tables.

OPTIMIZE TABLE works as follows:
* If the table has deleted or split rows, repair the table.
* If the index pages are not sorted, sort them.
* If the statistics are not up to date (and the repair couldn't be done by sorting the index), update them.

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

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

[Loki12]

Originally Posted by Loki12

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:

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

[neocorteqz]

Originally Posted by Loki12

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.