Pruneterrain.php

From OpenSimulator

Jump to: navigation, search


Description

Currently, the terrain revision table in the mysql database gets too big after a while(more than 1gb is no exception), so backups slow down greatly. This script prunes the revisions per region, thus limiting the maximum space which this table occupies.

Usage

Save the script in your scripts directory, make it executable(chmod +x) and fire up like:

./pruneterrain.php sqlhostname sqluser sqlpw sqldb prunelimit

The prunelimit cuts off every revision after the amount you give(1-x).

Souce

#!/usr/bin/php
<?php
//First do some basic parameter checking
if (sizeof($argv)!=6) {
echo "\nInvalid amount of parameters!\nUsage:".$argv[0]." host user pw db limit\n";
die;
}
else {
	foreach($argv as $arg){
		if(empty($arg)){
		echo "\nInvalid parameter input!\nUsage:".$argv[0]." host user pw db limit\n";
		die;
		}
	}
$host = $argv[1];
$username = $argv[2];
$password = $argv[3];
$database = $argv[4];
	if(intval($argv[5]) > 0) {
	$prunelimit = $argv[5];
	}
	else {
	echo "Enter a valid Prune-limit!(> 1). You don't want to loose your work do you?\n";
	die;
	}
}
 
$connect = mysql_connect($host, $username, $password);
	if (!$connect) {
	die('Could not connect: ' . mysql_error());
	}
	mysql_select_db($database);
	//Get unique regions from terrain table
	$query = "SELECT uuid,regionName from regions";
	$result = mysql_query($query);
	if(!$result) die("Query Failed.");
	//For every unique Region
	$tempspace = 0;
	$space = 0;
	while ($row = mysql_fetch_assoc($result)) {
	$query = "SELECT RegionUUID,Revision from terrain WHERE RegionUUID = '".ereg_replace("-", "", $row['uuid'])."' ORDER BY Revision DESC";
	$nextresult = mysql_query($query);
	//Get the amount of revisions
	$revnum = mysql_num_rows($nextresult);
		//If revision amount exceeds the prune-limit
		if($revnum > $prunelimit) {
		$tempspace = (($revnum - $prunelimit) / 2);
		echo "Freeing ".$tempspace."MB of data from ".$row['regionName']."\n";
		mysql_data_seek($nextresult, ($prunelimit));
			if($lastrow = mysql_fetch_assoc($nextresult)) {
			$query = "DELETE FROM terrain WHERE Revision <= '".$lastrow['Revision']."'";
			mysql_query($query) or die (mysql_error());
			$space += $tempspace;
			}
		}
 
	}
	echo "Totally removed ".$space."MB of terrain-revision data...\n";
	mysql_close($connect);
?>
Personal tools
General
About This Wiki