Running 2 webservers myself, both very intense in SQL services (what server isn’t any more?), I found that SQL can be ugly, it can be just downright ugly, especially when NOT optimized on a frequent basis. Today, I’ll go through a very simple trick and script to optimize your SQL databases. Doing this saves me 10-20 meg a day in backups.

Tomorrow, how to create daily SQL backups OUTSIDE of your control panel, and restore from them if necessary!

First, we need a script. This script will be a php script, used to determine the databases, and whatnot. This is a php script, so create a .php file on your server (as root), and put these contents in there:

#!/usr/local/bin/php
$myuser=”root”;
$mypass=”insertyourrootpasswordhere”;
$myhost=”localhost”;
$query = “SHOW DATABASES”;
$mtime = microtime();
$mtime = explode(” “,$mtime);
$mtime = $mtime[1] + $mtime[0];
$start_time = $mtime;

$dbh=mysql_connect (”$myhost”, “$myuser”, “$mypass”) or die(mysql_error());
$result = mysql_query($query) or die (”Error in query: $query. “.mysql_error());
$count = 0;
while ($row = mysql_fetch_array($result)) {
$count = $count + 1;
$thisdb=$row[0];
cleanup($thisdb);

}
function cleanup($dbname)
{
check_exists($dbname);
global $myuser, $mypass, $myhost;
$dbh=mysql_connect (”$myhost”, “$myuser”, “$mypass”) or die(mysql_error());
mysql_select_db ($dbname) or die(mysql_error());
$db_clean = $dbname;
$tot_data = 0;
$tot_idx = 0;
$tot_all = 0;
$local_query = ‘SHOW TABLE STATUS FROM ‘.$dbname;
$result=mysql_query($local_query) or die(mysql_error());
if (mysql_num_rows($result)) {
while ($row = mysql_fetch_array($result)) {
$tot_data = $row['Data_length'];
$tot_idx = $row['Index_length'];
$total = $tot_data + $tot_idx;
$total = $total / 1024 ;
$total = round ($total,3);
$gain= $row['Data_free'];
$gain = $gain / 1024 ;
$total_gain += $gain;
$gain = round ($gain,3);
$local_query = ‘OPTIMIZE TABLE ‘.$row[0];
$resultat=mysql_query($local_query);
if ($gain == 0) {

} else {

}
}
}

while ($row = mysql_fetch_row($result)) {
$histo += $row[0];
$cpt += 1;
}
}
function check_exists($dbname)
{
global $myuser, $mypass, $myhost;
$dbh=mysql_connect (”$myhost”, “$myuser”, “$mypass”) or die(mysql_error());
mysql_select_db ($dbname) or die(mysql_error());
}
function microtime_diff($a, $b) {
list($a_dec, $a_sec) = explode(” “, $a);
list($b_dec, $b_sec) = explode(” “, $b);
return $b_sec – $a_sec + $b_dec – $a_dec;
}
$mtime = microtime();
$mtime = explode(” “,$mtime);
$mtime = $mtime[1] + $mtime[0];
$end_time = $mtime;
$total_time = ($end_time – $start_time);
$total_time = substr($total_time,0,5);
print”Processing Time: $total_time seconds\n”;
?>

One thing here:

You’ll notice that this uses ROOT priviledges, so I’d suggest you encrypt this script. ioncube does a remarkably good per-script encoding online. Usually, they want (about) .10, which is incredibly reasonable!

SO, now that we have our script, what on earth are we going to do it? Using it is the most important part, right? Let’s get to that:

You’ll need to change the permissions on the script to what you like them to be, but it should (note: shoudl) at minimum be executable

chmod u+x ./scriptname.php

And we’re done

Now, let’s call the script to make sure that it works right

./scriptname.php

If it doesn’t work right, well, there’s usually a problem. Firstly, make sure that php is correct, and in the correct location. From bash:

which php

If it’s not in /usr/local/bin/php (which most are), then adjust the first line of the script
Secondly, maybe you need to update php? That one, I’m not going to cover in a tutorial as it CAN break things. Feel free to Contact Me though for a very well priced update
Thirdly, hey, things happen. If it still doesn’t work, after you’ve updated php AND checked the location, then please, feel free to reply with your problem and we can see if we can’t get it working together.

Now, it works. Let’s tell the system to do it automatically. Again, from bash:

crontab -e

Add the following to your cron job:

30 12 * * * /path/to/scriptname.php > /dev/null 2>&1

THIS will set the cron job to run @ 1230 every day, and then you will have optimized tables.

Thoughts, comments, ideas? As always, put ‘em here.