SQL-Fehler häufen sich – Forenoptimierung benötigt

[typoagrafka]

Es geht um das Forum http://busenfreundinnen.net - läuft derzeit noch auf der phpBB-Version 3.0.7PL1 mit einigen MODs (u. a. Portal, AutoMOD, Tapatalk, Statistics, Support Toolkit, Manage Attachments) und Snippets, die ich auch zum Teil selbst gebastelt habe.
Ein paar Eckdaten:
Benutzeranzahl: ca. 5800 (ich möchte aber viele inaktiven löschen, diese Zahl wird sich also deutlich verkleinern - auf ca. 1500-2000 denke ich)
Beiträgeanzahl: ca. 330000
Dateianhänge: fast 46000 Stück
Datenmenge der hochgeladenen Dateien: fast 8 GB
Datenbankgröße: ca. 750 MB
Anzahl Unterforen: ca. 500
Datenbank-Server: MySQL(i) 5.1.49-3~bpo50+1-log
PHP: 5.3.16

Seit Monaten bleibt die Besucheranzahl laut Google Analytics konstant. Trotzdem häufen sich mySQL-Fehler. Ich habe schon einen Serverupgrade gemacht - vor ein paar Monaten. Dann war Ruhe. Leider nicht für sehr lange. Ich habe keine Ahnung, auf welche Auslastung phpBB vorbereitet ist – kann man es irgendwo herausfinden?

Es stellt sich für mich die Frage, in wie fern diese Mengen an Dateianhängen und Unterforen sowie Beiträgen "schuldig" sind daran, dass es zu diesen hohen Auslastung der Datenbank kommt. Wir sind als Team dabei, derzeit das Forum zu optimieren und aufzuräumen, möchten es aber sinnvoll gestalten und nicht unnötig informative Fotos oder sonstige Inhalte löschen.

Ich möchte auch phpBB auf den neuesten Stand bringen, allerdings zuerst das Forum ein bisschen "abspecken", weil es derzeit höchstens problematisch ist - selbst das Herunterladen vom Backup aus dem Server dauerte gestern 8 Stunden (!).

Ich suche nach guten Optimierungstipps, vielleicht auch Links, wo ich so was finde. Einen weiteren Serverupgrade kann ich mir derzeit nicht leisten, derzeit wollen wir deshalb am Forum arbeiten.

Ist es möglich, bereits hochgeladene Bilder zu verkleinern?
Ich habe auch die Einstellungen für die Bilder jetzt geändert (die Dateien sind zu 99% Bilder), so dass sie bestimmte Maße nicht überschreiten sollten, aber es scheint nicht einzugreifen. Ich habe trotzdem geschafft, ein größeres Bild hochzuladen. Woran kann es liegen?

Gibt es irgendwelche Tools, um es den Benutzern einfacher zu machen, Bilder hochzuladen, so dass diese nicht so groß werden?
Und auch igendwelche MODs (außer dem o. g. Manage Attachment Module) zum Verwalten von bereits hochgeladenen Dateien? Dieser MOD ist nicht schlecht, aber hat nicht alles, was ich bräuchte, um so große Menge an Dateianhängen zu verwalten, z. B. Filtern nach Unterforum. Wir sind bereit, viel davon zu löschen, aber manuell Tausende Threads durchzugehen wäre sehr aufwändig.

Ich wäre daher dankbar für Tipps und Hilfe,

Joanna

[Miriam]

Schon gelesen: Klick?
Zum Thema: MYSQL Server has gone way -> Klack. Darin wird auch von Fehlern in der Anwendungslogik gesprochen. Solltest Du evtl. ein "problematisches" Snippet kreiert haben?

[Mahony]

Hallo
Es könnte unter anderem an eurem Statistics-MOD liegen. Derartige MODs sind oftmals sehr Performance-lastig.

Aktiviere mal in der config.php den Debug-Modus und schau dann unter EXPLAIN (im Footer des Forums) nach, was eine hohe Auslastung verursacht.

Lies dir dazu auch mal diese Dokumentation durch EXPLAIN-Syntax (Informationen über ein SELECT erhalten)

Wichtig sind hierbei die folgenden Aussagen:

# Using filesort

MySQL muss einen zusätzlichen Durchlauf vornehmen, um zu ermitteln, wie die Datensätze in sortierter Reihenfolge abgerufen werden können. Diese Sortierung erfolgt, indem alle Datensätze entsprechend dem Join-Typ überprüft und Sortierschlüssel sowie der Zeiger auf den Datensatz für alle Datensätze gespeichert werden, die der WHERE-Klausel entsprechen. Die Schlüssel werden dann sortiert und die Datensätze entsprechend in sortierter Reihenfolge abgerufen. Siehe auch Abschnitt 7.2.12, „ORDER BY-Optimierung“.

Wollen Sie Ihre Abfragen so schnell wie möglich machen, dann sollten Sie nach den Extra-Werten Using filesort und Using temporary suchen.

Bei den Abfragen, wo using filesort angezeigt wird, bedeutet dies, dass kein Index für die Sortierung verwendet werden kann und MySQL sortiert dann recht aufwendig eben über dieses filesort.

Tipps bei lahmendem Board:
Tipp 1: - Ab und zu mal die Tabellen in der Datenbank optimieren.
Tipp 2: - viewtopic.php?f=93&t=181801 (Optimierung des CSS-Parsings in phpBB3)
Tipp 3: - Deaktiviere IP gegen Schwarze DNS-Liste prüfen (unter Allgemein - Sicherheit im ACP) - falls aktiviert.
Tipp 4: - Deaktiviere Rekompilieren veralteter Style-Komponenten: (unter Allgemein - Serverlast im ACP) - falls aktiviert.

Hier noch etwas Lesestoff zum Thema Datenbank-Optimierung
1. SQL-Optimierung: Indizes richtig einsetzen
2. SQL-Optimierung: Indexnutzung in Queries manuell steuern
3. SQL-Optimierung: Tabellen und Spalten anpassen
4. SQL-Optimierung: Daten effizient in Tabellen einfügen
5. SQL-Optimierung: Manuelles Priorisieren und Sperren
6. SQL-Optimierung: Server richtig konfigurieren

Gibt es irgendwelche Tools, um es den Benutzern einfacher zu machen, Bilder hochzuladen, so dass diese nicht so groß werden?

Da kann ich das folgende Snippet empfehlen Serverseitiges image resize beim Upload.
Damit werden zumindest die hoch geladenen Bilder Serverseitig auf die festgelegte Größe herunter gerechnet und gespeichert. Das Snippet nimmt das hoch geladene File, verkleinert es, und legt es anschließend ab.
Das original File wird anschließend verworfen. Das funktioniert allerdings nur bei Bildern die nach dem Einbau des Snippets hoch geladen werden. Für die Verkleinerung bereits hoch geladener Bilder ist mir momentan kein MOD bekannt (was aber nicht unbedingt heißen muss, dass es sowas nicht gibt).


Grüße: Mahony

[typoagrafka]

Ich danke Euch sehr für alle Tipps. Ich schaue es mir alles genau durch und werde mich dann wieder melden.

[Mahony]

Hallo

Ist es möglich, bereits hochgeladene Bilder zu verkleinern?

Es gäbe da eine Möglichkeit, die Fotos mittels irfanview ( http://www.irfanview.de/ ) per Stapelverarbeitung zu bearbeiten (verkleinern).

Da die Fotos auf dem Server aber verschlüsselte Dateinamen haben, benötigt man noch einige kleine Umwege.

1. Zunächst muss man die Tabelle phpbb_attachments nach den verschlüsselten (physical_filename) und den original (real_filename) - Namen abfragen und sich das ganze als Liste, mit dem Trennzeichen | , ausgeben lassen.

2. Anhand dieser Liste kann man mittels dem Tool Bulk Rename Utility ( http://www.bulkrenameutility.co.uk/Main_Intro.php ) die Fotos in die Original-Namen umbenennen (wichtig, da Windows nach den Dateiendungen geht).

3. Jetzt kann man mittels irfanview seine Fotos bearbeiten/verkleinern lassen.

4. Nachdem die Fotos bearbeitet wurden, müssen diese wieder,mittels dem Tool Bulk Rename Utility , in die verschlüsselten Bezeichnungen umbenannt werden, damit sie vom Forum ausgelesen werden können.

5. Die Fotos können jetzt wieder hoch geladen werden und sollten vom Forum wieder ausgelesen werden können.

6. Eventuell muss die Dateigröße in bytes noch ermittelt und in die Tabelle phpbb_attachments (Spalte filesize ) eingetragen werden.

ACHTUNG: Bilder (Fotos) müssen immer im Binär-Modus hoch oder herunter geladen werden!

Eine genaue Anleitung dazu kann ich, falls Interesse daran besteht, demnächst posten.


Grüße: Mahony

[Mahony]

Hallo
Soo...und hier kommt schon einmal das PHP-Script um die verschlüsselten und die Original-Namen auslesen zu können. Das Script muss als irgendeinname.php (irgendeinname kann von euch entsprechend geändert werden) gespeichert und in den Foren-root hoch geladen werden. Ausgelesen werden die Formate .jpg .jpeg und .png

Code: Alles auswählen

<?php
// Benötigte Dateien und Variablen von phpBB 
define('IN_PHPBB', true);
$phpbb_root_path = './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);


// Session auslesen und Benutzer-Informationen laden
$user->session_begin();  // Session auslesen
$auth->acl($user->data); // Benutzer-Informationen laden
$user->setup();          // Sprachvariablen laden
//Beginn - ist das Mitglied nicht angemeldet, wird er auf die Login-Box weiter geleitet
if ($user->data['user_id'] == ANONYMOUS)
{
    login_box();
}
//Ende - ist das Mitglied nicht angemeldet, wird er auf die Login-Box weiter geleitet

//Beginn - ist das Mitglied kein Administrator oder globaler Moderator, bekommt er die Fehlermeldung angezeigt.
if ($auth->acl_get('a_') || $auth->acl_getf_global('m_'))
{
}

else
    {
    trigger_error('Du bist nicht berechtigt, diese Seite aufzurufen!');
    die;
    }
//Ende - ist das Mitglied kein Administrator oder globaler Moderator, bekommt er die Fehlermeldung angezeigt.

//Beginn - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen - jpg-Fotos
$sql = "SELECT `physical_filename`, `real_filename` FROM `phpbb_attachments` WHERE `real_filename` LIKE \"%.jpg\"";
//Ende - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen - jpg-Fotos

//Beginn - Das Ergebnis wird in eine Variable gepackt.
$result = mysql_query ($sql); 
//Ende - Das Ergebnis wird in eine Variable gepackt.

//Wir geben das Ergebnis mittels einer While-Schleife aus.
while ($row = mysql_fetch_assoc($result)) 
{
echo "<pre>";
echo $row["physical_filename"] .  "|" ;
echo $row["real_filename"];
echo "</pre>";

};

//Beginn - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen - jpeg-Fotos
$sql = "SELECT `physical_filename`, `real_filename` FROM `phpbb_attachments` WHERE `real_filename` LIKE \"%.jpeg\"";
//Ende - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen  - jpeg-Fotos

//Beginn - Das Ergebnis wird in eine Variable gepackt.
$result1 = mysql_query ($sql); 
//Ende - Das Ergebnis wird in eine Variable gepackt.

//Wir geben das Ergebnis mittels einer While-Schleife aus.
while ($row = mysql_fetch_assoc($result1)) 
{
echo "<pre>";
echo $row["physical_filename"] .  "|" ;
echo $row["real_filename"];
echo "</pre>";

};


//Beginn - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen - png-Fotos
$sql = "SELECT `physical_filename`, `real_filename` FROM `phpbb_attachments` WHERE `real_filename` LIKE \"%.png\"";
//Ende - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen - png-Fotos

//Beginn - Das Ergebnis wird in eine Variable gepackt.
$result2 = mysql_query ($sql); 
//Ende - Das Ergebnis wird in eine Variable gepackt.

//Wir geben das Ergebnis mittels einer While-Schleife aus.
while ($row = mysql_fetch_assoc($result2)) 
{
echo "<pre>";
echo $row["physical_filename"] .  "|" ;
echo $row["real_filename"];
echo "</pre>";

};
?>

Zugriff auf das Script haben nur globale Moderatoren und Administratoren.


Grüße: Mahony

[Miriam]

Ausgelesen werden die Formate .jpg .jpeg und .png

Was ist mit TIF, TIFF, PNG & GIF (nur um hier mal die vordefinierten zu nennen)?
Was passiert mit den selbstdefinierten Bilddatei-Erweiterungen, die evtl. im ACP definiert wurden?
Was ist, wenn das Tabellen-Präfix nicht phpbb_ ist?

Mein Vorschlag:

Code: Alles auswählen

<?php
// Benötigte Dateien und Variablen von phpBB 
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);

//Beginn - ist das Mitglied nicht angemeldet, wird er auf die Login-Box weiter geleitet
if ($user->data['user_id'] == ANONYMOUS)
{
    login_box();
}
//Ende - ist das Mitglied nicht angemeldet, wird er auf die Login-Box weiter geleitet

//Beginn - ist das Mitglied kein Administrator oder globaler Moderator, bekommt er die Fehlermeldung angezeigt.
if (!$auth->acl_get('a_') && !$auth->acl_getf_global('m_'))
{
    trigger_error('Du bist nicht berechtigt, diese Seite aufzurufen!');
    die;
}
//Ende - ist das Mitglied kein Administrator oder globaler Moderator, bekommt er die Fehlermeldung angezeigt.

//Beginn - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen - jpg-Fotos
$sql = 'SELECT a.physical_filename, a.real_filename
        FROM ' . ATTACHMENTS_TABLE . ' a
        WHERE a.extension
            IN (
                SELECT e.extension
                FROM ' . EXTENSIONS_TABLE . ' e, ' . EXTENSION_GROUPS_TABLE . ' eg
                    WHERE e.group_id = eg.group_id
                    AND eg.group_name = \'IMAGES\'
                )';
//Ende - Abfrage nach den verschluesselten und den zugehoerigen Original-Foto-Namen - jpg-Fotos
$result = $db->sql_query($sql); 

//Wir geben das Ergebnis mittels einer While-Schleife aus.
$i = 0;
while ($row = $db->sql_fetchrow($result)) 
{
    $i++;
    print '<pre>';
    print $row['physical_filename'] .  '|' ;
    print $row['real_filename'];
    print '</pre>';
};
if (!$i) print 'Nichts gefunden.'; //Vllt gibt's ja auch nichts zu finden.
?>

(Nein, die SQL Abfrage ist nicht UMIL compliant - sollte aber für MySQL passen.)

[Mahony]

Hallo
Mein Script habe ich extra so übersichtlich gestaltet (und kommentiert), damit auch Leute die keine Ahnung von Programmieren haben es anpassen können. Somit kann jeder das Script auf seine Bedürfnisse anpassen.

Bei deinem Script werden einfach alle Bildformate ausgelesen, was aber nicht immer erwünscht ist. Die Wahrscheinlichkeit, dass User zu große Fotos als gif-Datei hoch laden ist Beispielsweise eher gering.

Nunja...jetzt haben wir eben beide Varianten

Grüße: Mahony

[Miriam]

... denn doppelt hält besser