MYSQL Optimization Cron

Submitted by andrewrimmer on Tue, 05/17/2011 - 08:31

I saw this PHP script by John Miller at http://www.wmtips.com/php/auto-optimize-mysql-tables-script.htm :

<?

// Change vars as needed here

$server = "localhost";
$user = "mysql_user";
$pwd = "mysql_password";
$dbName = "mysql_dbName";

$link = mysql_connect($server, $user, $pwd);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($dbName, $link);
if (!$db_selected) {
die ('Can\'t use $dbName : ' . mysql_error());
}

// Find all tables in the selected DB

$alltables = mysql_query("SHOW TABLES");

// Process all tables.

while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{

// Optimize them!

mysql_query("OPTIMIZE TABLE '".$tablename."'")
or die(mysql_error());
}
}
mysql_close($link);
?>

and the cron to run it:

echo '#!/bin/sh' > mysql_optimize; echo '/path/to/your/script.php' >> mysql_optimize; chmod 755 mysql_optimize;

Can someone confirm it's ok before I use it?

Status: 
Closed (fixed)

Comments

Submitted by andreychek on Tue, 05/17/2011 - 09:33

Howdy -- I don't see anything that's obviously malicious in there, if that's what you're asking. It appears to loop through each MySQL table in a particular database, and run "OPTIMIZE" on each table.

You may want to take care as to how frequently you run that script, and what time of day it's being run. I believe that "mysql optimize" will lock the tables while it's running, so your application may not be usable during that time.

With that in mind, you may want to run it at off-peak hours.

Submitted by andrewrimmer on Tue, 05/17/2011 - 09:43

Thanks

I have a vbulletin forum that slows in the afternoon. I found optimizing the database helps a lot.

I use this forum quite often. Is it possible to have a section on usable scripts like this that users input?

Submitted by andreychek on Tue, 05/17/2011 - 11:23

I'd suggest using the "Hacks" forum for that. It's designed to hold all the nifty "Tips & Tricks" that Virtualmin users come up with.

After clicking "Forums" above, scroll all the way to the bottom of the page, "Hacks" is the second from the bottom.

Submitted by andrewrimmer on Tue, 05/17/2011 - 11:46

Thanks

appreciate your help