Nightly Database Optimizer

[Gamingforce]

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

[Ghostsuit]

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

[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.

[Phalynx]

usefull, thanks! *install*

[Skyline_GT]

so this is similer to the repair feature in vb?

[SaN-DeeP]

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,

[sabret00the]

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 :(

[nexialys]

to make the script to not stuck the server and the db process, inside the code, just after $tempcount++;
add this, so it will add a second of pause:
for ($i=0; $i < 1000; $i++) {
// do nothing, 1000 times
}

[Andreas]

$tempcount++;
add this, so it will add a second of pause:
for ($i=0; $i < 1000; $i++) {
// do nothing, 1000 times
}

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

Better use

sleep(1);


:)

[nexialys]

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

[Mickie D]

thanks :)

installed and clicked

[Gamingforce]

1.00 -> 1.01 - July 28, 2004

Added sleep code that adds a pause after each database process. (Thanks KirbyDE)
To update, simply upload the new dboptimize.php script found in the zip attachment in the first post of this thread.

[Gamingforce]

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,It's useful regardless of the amount of posts your board has.

[Zoints]

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

Thanks.

[Zachery]

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.

[Highlander]

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

[Erwin]

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

[Gamingforce]

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

[Erwin]

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

[tcs]

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.

[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]

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]

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]

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]

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.

[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! :D

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]

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 (http://www.mysql.com/) 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 (http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html)
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]

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:

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

[neocorteqz]

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.

[barnest2]

Exactly what I've been looking for!

[TwinsX2Dad]

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.

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