Optimize your Tables!

One thing that seems to always irritate me when im trying to organize my backups together is how the file sizes bloat over time. You can, and probably should look into optimizing your tables but for those of you who dont have the time or the will power to care to do it in any other method if you place this function at the top of your automated backup script and simply call it appropriately you will be able to automate the process of optimizing all tables that dont meet your threshold and or maximum free space requirements.

You can just set up a cron job to call your PHP scripts and it should take care of things from there.

$_CONFIG['threshold'] = 0.01;
$_CONFIG['maxfreedata'] = 102400;

function optimizeDatabase($host, $user, $pass, $dbase)
{
	global $_CONFIG;

	$mysqli = new mysqli($host, $user, $pass, $dbase);

	if (mysqli_connect_errno())
	{
		printf("Connect failed: %sn", mysqli_connect_error());
		exit();
	}

	if ($result = $mysqli->query('SHOW TABLE STATUS WHERE '.
		'(Data_free / Data_length) > ' . $_CONFIG['threshold'] .
		' AND Data_free > ' . $_CONFIG['maxfreedata']))
	{
		print 'Optimizing Database: ' . $dbase . "n";
		while ($row = $result->fetch_object())
		{
			printf("%-25s %8d %8dn", $row->Name,
				$row->Data_free, $row->Data_length);
			$mysqli->query('OPTIMIZE TABLE ' . $row->Name);
		}

		$result->close();
	}
	$mysqli->close();
}

optimizeDatabase('localhost', 'User', 'Password', 'Database');

so okay, Gneu updates

I finally set up the automatic backup for every portion of my site, including the wiki and forums. I spent about 4 hours configuring the app to run itself properly and email me like I want it to and so forth, but now its on autopilot so woo hoo!

Secondly, some of you may have already noticed a release was made this past week. I know its simple but I would love to hear your thoughts on the mutator as it is my first and as such is extremely simple.

I have also been doing quite a bit of work on the wiki in the last week. It looks like I am going to be able to get back into my books and tutorial writing sooner than I had expected, and since everything I do is backed up, it will be that much more fun to do it without worrying about my fucking host being dumb again.

=)