Tabelle posts bläht sich auf - OPTIMIZE TABLE

[Ottokar]

phpbb 3.0.4
mysql 5.0.24
Die Tabelle posts war bei 6414 records 20 MB groß, nach manuellem OPTIMIZE TABLE nur noch 16,2 MB.
Normalerweise sollte mysql freigegebenen Speicher doch überschreiben, das scheint aber mit der Tabelle posts nicht zu funktionieren - sofern es sich um solchen handelt.
Nach 3 Tagen und 500 Einträgen (mehr als vorher) konnte ich die Tabelle mit OPTIMIZE TABLE um 4 MB verkleinern.
Da hierbei zuvor bzw. zwischenzeitlich keine Daten gelöscht wurden, frage ich mich außerdem, woher der viele freie Platz in der Tabelle kommt. Das kann doch nur bedeuten, dass die Tabelle bei der Nutzung massiv fragmentiert, was wiederum meine erste Frage beantwortet.
Das bedeutet aber, dass bei einem gut besuchten Forum die Tabelle alle paar Tage manuell defragmentiert werden muss um zu verhindern, dass diese sich unnötig aufbläht/fragmentiert was zwangsläufig zu Performanceverlust führt. Das kann es doch aber wohl nicht sein?

Weis jemand, was da die Ursache und die Lösung ist?

[Miriam]

Die Ursache könnte evtl. Dein Hoster beantworten. Die Lösung wäre ein CRON Job, der die Tabellen optimiert.

[Ottokar]

Die Ursache könnte evtl. Dein Hoster beantworten.

Worauf willst du hinaus?

Die Lösung wäre ein CRON Job, der die Tabellen optimiert.

'Ne Idee, wie ich den in das Forum integriere?

[Miriam]

zu 1. Ich weiss nicht, warum die Tabellen immer wieder solch eine großen Überhang haben.
zu 2. mach es als externen CRON Job z.B. alle 24h.

[Ottokar]

Mangels Hilfe habe ich mir nun selbst geholfen und eine php-Datei programmiert, die unabhängig vom Forum zur Datenbankwartung dient.
Damit kann man:
- die Größe der Tabellen und der Datenbank ermitteln (keine Wartungsfunktion),
- diese optimieren, d.h. defragmentieren, Daten neu ordnen, Leerräume entfernen und damit verkleinern,
- diese analysieren, d.h. damit werden die in der Tabelle verwendeten Schlüssel analysiert und aktualisiert,
- diese auf Fehler überprüfen und Fehler beheben, sofern möglich.

Die Auswahl der Wartungsfunktionen erfolgt über sog. Radio-Buttons, wobei jeweils nur immer eine auswählbar ist. Um die gewählte Wartungsfunktion auszuführen, müssen zuvor Passwort und Benutzername der Datenbank eingegeben werden, wie man sie vom Provider erhalten bzw. selbst eingerichtet hat (Tip: Passwort und Benutzername der Datenbank stehen in der Datei config.php im Hauptverzeichnis von phpbb). Das schützt vor unerlaubtem Zugriff. Zu beachten ist dabei, dass, während eine Wartungsfunktion ausgeführt wird, die jeweils betroffene Tabelle der Datenbank, bzw. die Datenbank komplett, gesperrt ist, d.h. man sollte z.B. eine Optimierung nur dann durchführen, wenn sich entweder kein User oder nur wenige im Forum angemeldet haben, damit Beeinträchtigungen weitestgehend vermieden werden. Die komplette Sperrung des Forums ist dabei i.d.R. unnötig.

Die Datei heist 'db.php' und muss im Hauptverzeichnis des Forums liegen.
Die Funktionsweise ist übrigends sowohl unabhängig von der verwendeten phpbb Version als auch von phpbb selbst, allerdings auf mysql Datenbanken begrenzt. Verwendet wird das Stylesheet des Themes subsilver2, dieses sollte also vorhanden sein - zumindest die zugehörige css-Datei.

Die Nutzung erfolgt auf eigene Gefahr!

Hier der Quellcode:

Code: Alles auswählen

<?php
// db.php
// PHP-Datei zur Datenbankwartung für phpbb und mysql
// Copyright © Ottokar
// E-Mail: fruechtchen.ottokar@gmx.net

$forumname = 'Forum';  // hier 'Forumnamen' eingeben; here insert 'Forumname'

// ab hier nichts mehr verändern; from this point you don't chance anything
// Meldungstexte dürfen übersetzt werden; you can only translate messages

$db_mode = $_POST['db_mode'];
$username = $_POST['username'];
$password = $_POST['password'];
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'config.'.$phpEx);

if(($db_mode != 'analyze') && ($db_mode != 'optimize') && ($db_mode != 'repair') && ($db_mode != 'check') && ($db_mode != 'size')) {
	$db_mode = 'no';
}
else{
	if(($username != $dbuser) || ($password != $dbpasswd)) {
		$db_mode = 'passwd';
	}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" dir="ltr" lang="de" xml:lang="de">
<head>

<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<meta http-equiv="content-language" content="de" />
<meta http-equiv="content-style-type" content="text/css" />
<meta http-equiv="imagetoolbar" content="no" />
<meta name="resource-type" content="document" />
<meta name="distribution" content="global" />
<meta name="copyright" content="" />
<meta name="keywords" content="" />
<meta name="description" content="" />
<meta name="robots" CONTENT="noindex, nofollow">

<title><?php echo ($forumname); ?> &bull; Datenbankwartung</title>

<link rel="stylesheet" href="<?php echo ($phpbb_root_path); ?>styles/subsilver2/theme/stylesheet.css" type="text/css" />
<link rel="shortcut icon" type="image/x-icon" href="<?php echo ($phpbb_root_path); ?>favicon.ico" />

<script language="JavaScript" type="text/javascript">
<!--
function send() {
	document.getElementById("send").style.display = "block";
}
function checked(i) {
	document.forms[0].elements[i].checked = true;
}
-->
</script>
</head>

<body class="ltr">
<a name="top"></a>

<div id="wrapcentre">
<table border="0" cellpadding="5" cellspacing="1" width="100%" class="tablebg" align="center"><tbody>
  <tr> 
    <th colspan="3" class="thHead"><?php echo ($forumname); ?> &bull; Datenbankwartung</th>
  </tr>
  <tr> 
    <td align="left" valign="top" width="10%" class="row1"></td>
    <td align="center" valign="top" width="80%" class="row2">

<?php
$msg1='<p><br /><br /><b>Der Befehl \''.$db_mode.'\' wurde ausgef&uuml;hrt. Bitte kontrollieren sie das Ergebnis auf Fehler.</b></p><br />';

switch ($db_mode)
{
	case 'size':
			size();
			echo ($msg1);
			break;
			
	case 'analyze': 
	case 'check':
	case 'optimize':
	case 'repair':
			dbcheck();
			echo ($msg1);
			break;

	case 'passwd': 
			echo "<p><br /><br /><span style='color: red; font-weight: bold;'>Benutzername und/oder Passwort falsch oder nicht angegeben.</span></p>";
			break;
			
	case 'no': 
			echo "<p><br /><br />Es wurde keine Funktion gew&auml;hlt.<p>";
			break;
			
	default: 
			break;
}

function dbcheck() {
	global $db_mode;
	global $dbhost;
	global $dbuser;
	global $dbpasswd;
	global $dbname;
	$checkmode = strtoupper($db_mode);
	$mysql = mysql_connect($dbhost,$dbuser,$dbpasswd);
	$db_selected = mysql_select_db($dbname, $mysql);
	$tables = mysql_query("SHOW TABLES", $mysql);
	echo('<table cellspacing="2" cellpadding="10" align="center">');
	echo('<tr><td align="left"><p><b>Tabelle &nbsp; &nbsp; </b></p></td><td align="left"><p><b>Operation &nbsp; &nbsp; </b></p></td><td align="left"><p><b>Ergebnistyp &nbsp; &nbsp; </b></p></td><td align="left"><p><b>Ergebnis &nbsp; &nbsp; </b></p></td></tr>');
	while ($row = mysql_fetch_row($tables))
	{
		$sql = "$checkmode TABLE ".$row[0];
		$result = mysql_query($sql, $mysql);
		if ( !$result )
		{
			echo ('Fehler Nr. '.mysql_errno() . ": " . mysql_error());
		}
		else
		{
		echo('<tr><td align="left">');
		echo($row[0]);
		echo('</td><td align="left">');
		echo(mysql_result($result, 0, 1));
		echo('</td><td align="left">');
		echo(mysql_result($result, 0, 2));
		echo('</td><td align="left">');
		echo(mysql_result($result, 0, 3));
		echo('</td></tr>');
		}
	}
	echo('</table>');
}

function size(){	
	global $dbhost;
	global $dbuser;
	global $dbpasswd;
	global $dbname;
	$mysql = mysql_connect($dbhost,$dbuser,$dbpasswd);
	$db_selected = mysql_select_db($dbname, $mysql);
	
	if( $query = @mysql_query("SHOW TABLE STATUS") )
	{
		$curSize = 0;
		echo('<table cellspacing="2" cellpadding="10" align="center">');
		echo('<tr><td align="left"><p><b>Tabelle &nbsp; &nbsp; </b></p></td><td align="right"><p><b>Gr&ouml;&szlig;e in kB</b></p></td><td align="right"><p><b> &nbsp; &nbsp; Gr&ouml;&szlig;e in MB</b></p></td></tr>');
		while( $result = @mysql_fetch_array($query) )
		{
			echo('<tr><td align="left">');
			echo($result["Name"]);
			echo('</td><td align="right">');
			echo(round(($result["Data_length"] + $result["Index_length"]) / 1024, 2).' kB');
			echo('</td><td align="right">');
			echo(round(($result["Data_length"] + $result["Index_length"]) / 1048576, 2).' MB');
			echo('</td></tr>');
			$curSize += $result["Data_length"] + $result["Index_length"];
		}
 			echo('<tr><td align="left"><p><b>Gesamt:</b></p></td><td align="right">');
			echo('<p><b>'.round($curSize / 1024, 2).' kB</b></p>');
			echo('</td><td align="right">');
			echo('<p><b>'.round($curSize / 1048576, 2).' MB</b></p>');
			echo('</td></tr></table>');
	}
	else {
		echo('<p><b>Gr&ouml;&szlig;enberechnung konnte nicht durchgef&uuml;hrt werden.</b></p>');
	}
} 
 
?>
<br /><br />
<form method="post" action="./db.php">
	<table class="tablebg" cellspacing="1" width="100%">
		<tbody><tr>
		<td class="row1" align="center">
			<table cellspacing="1" align="center"><tbody>
				<tr><td align="left">
					<p>&nbsp;</p>
				</td></tr>
				<tr><td align="left">
					<p><input type="radio" name="db_mode" value="size">&nbsp; Datenbankgr&ouml;&szlig;e ermitteln <span style="font-size:87.5%">(size)</span></p>
				</td></tr>
				<tr><td align="left">
					<p><input type="radio" name="db_mode" value="optimize">&nbsp; Tabellen optimieren  <span style="font-size:87.5%">(optimize)</span><b><a href="javascript:checked('1');" onclick="this.blur();" title="defragmentieren, Daten neu ordnen, Leerr&auml;ume entfernen" style="cursor:help;text-decoration:none;"><sup>&nbsp;i&nbsp;</sup></a></b></p>
				</td></tr>
				<tr><td align="left">
					<p><input type="radio" name="db_mode" value="analyze">&nbsp; Tabellen analysieren <span style="font-size:87.5%">(analyse)</span><b><a href="javascript:checked('2');" onclick="this.blur();" title="analysiert & aktualisiert die Schl&uuml;ssel der Tabellen" style="cursor:help;text-decoration:none;"><sup>&nbsp;i&nbsp;</sup></a></b></p>
				</td></tr>
				<tr><td align="left">
					<p><input type="radio" name="db_mode" value="check">&nbsp; Tabellen &uuml;berpr&uuml;fen <span style="font-size:87.5%">(check)</span><b><a href="javascript:checked('3');" onclick="this.blur();" title="&uuml;berpr&uuml;ft Tabellen auf Fehler" style="cursor:help;text-decoration:none;"><sup>&nbsp;i&nbsp;</sup></a></b></p>
				</td></tr>
				<tr><td align="left">
					<p><input type="radio" name="db_mode" value="repair" title="(repariert fehlerhafte Tabellen)">&nbsp; Tabellen reparieren<sup>&nbsp;</sup><span style="font-size:87.5%">(repair)</span></p>
				</td></tr>
				<tr><td align="left">
				<p>&nbsp;</p>
				</td></tr>
	</tbody></table>
	<p><span class="genmed">Benutzername:</span> <input name="username" size="10" type="text">&nbsp; <span class="genmed">Passwort:</span> <input name="password" size="10" type="password">&nbsp; &nbsp; <input class="btnmain" value="Ausf&uuml;hren" type="submit" onclick="send();"><br /><br /></p>
	</td></tr>
	<tr><td id="send" align="center" style="display:none">
		<p><br /><b>Ihre Anfrage wird ausgef&uuml;hrt, bitte warten ...</b></p><br />
	</td></tr>
	</tbody></table>
</form>
<br />
	</td>
	<td align="left" valign="top" width="10%" class="row1"></td>
  </tr>
</tbody></table>

<br />

<table border="0" cellpadding="5" cellspacing="1" width="100%">
  <tr>
    <td align="center">
	<span style="text-align:center;">
	[ <a href="<?php echo ($phpbb_root_path); ?>" title= "zum Forum" onmouseover="status='zum Forum';return true;" onmouseout="status='';return true;">zum Forum</a> ]
	</span>
    </td>
  </tr>
</table>
<br /><br />

</div>

</body>
</html>

[Meyer83]

kann jemand diese art der SQL & Tabellen optimieren gut heißen bzw verwendet dies der ein oder andere???

möcht mich auch nciht immer über MyAdmin einloggen um die überhänge zu optimieren.

[Dr.Death]

Vom Grundsatz ist die Datei ok, allerdings bietet sie die Möglichkeit zu einem Brute Force Attack auf die Datenbank.

Es ist keine Sicherung für mehrfach falsch eingegebenes Passwort vorhanden.

Besser wäre es, wenn man dieses Script als ACP Modul entwirft und umprogrammiert.
Dann kann nur noch derjenige das Script ausführen, der auch Adminstriven Zugang hat.

[Meyer83]

ok danke dann werd ich wo lieber die umständliche art machen.