Datenbank optimieren

Aus VMS1 Tutorial

(Unterschied zwischen Versionen)
Wechseln zu: Navigation, Suche
Aktuelle Version (18:15, 28. Feb. 2012) (Bearbeiten) (rückgängig)
K
 
Zeile 89: Zeile 89:
-
Im VMS1 wird die IP Adresse in der Tabelle ''vms_reloads'' als String gespeichert:
+
Im VMS1 wird die IP Adresse in der Tabelle ''vms_reloads'' als [[String]] gespeichert:
`ip` varchar(15) NOT NULL default ' '
`ip` varchar(15) NOT NULL default ' '
Ein Eintrag verbraucht somit im Schnitt 12 Byte (zw. 8 und 16).
Ein Eintrag verbraucht somit im Schnitt 12 Byte (zw. 8 und 16).

Aktuelle Version

Sobald das eigene VMS1 Projekt aus den Kinderschuhen raus ist und man regelmäßig aktive User in Mengen über 100 hat, stellt sich die Frage, wie man die Performance verbessern bzw. erhalten kann. Hauptpunkt dabei ist meist die MySQL-Datenbank. Daher hier eine unvollständige Liste mit Tipps, wie man bei einer Optimierung vorgehen kann.

Inhaltsverzeichnis

Überblick verschaffen

Am besten eignet sich dafür PhpMyAdmin, welches meist schon installiert ist und verwendet werden kann, egal ob Webspace oder eigener Server.

Man wählt oben im Menü den Punkt "Status", eventuell muss man dazu in der Zeile darüber erst auf "Server: SERVERNAME" klicken.

Bild:Phpmyadmin_status.png

Auf der Status Seite bekommt man reichlich Informationen, die hier nicht alle erläutert werden können.

Slow Queries

Ebenfalls oben im Menü findet man den Punkt "Variablen". Dort sucht man nach: long query time. Diesert Wert sollte auf 1 stehen, er legt die Zeit in Sekunden fest, ab der eine Anfrage als "zu lang" angesehen wird. Ist dies nicht der Fall, entweder den Hoster bitten, dass umzustellen, oder selber konfigurieren, siehe dazu ....

Wieder zurück zur Status Seite, dort findet sich eine Zeile Slow_queries, hier sollte auch eine Erklärung danebenstehen. Dieser Wert sollte nach Möglichkeit 0 sein! Ansonsten gilt es nun, die langsamen Anfragen rauszufinden, und zu beseitigen/beschleunigen.

Dazu wird das Slow Query Log benötigt, hier auch entweder an den Hoster wenden, ob dieser das aktivieren / bereitstellen kann, oder aber selber konfigurieren, adss ist an sich schnell gemacht:

Beispiel Debian Server

In der Datei /etc/mysql/my.cnf folgende Zeilen hinzufügen:

 log_slow_queries	= /var/log/mysql/mysql-slow.log
 long_query_time = 1

bzw. sofern vorhanden, aktivieren (Kommentarzeichen davor löschen) oder anpassen, der Pfad ist natürlich immer individuell.

Danach den MySQL Server mit

 /etc/init.d/mysql restart

neustarten.

Wichtig: Das Mitloggen verbraucht auch Performance, daher sollte es immer nur kurze Zeit aktiviert sein.

Damit erzeugt MySQL eine Logdatei, die genau die zu langen Queries auflistet mit einigen Zusatzinformationen, bspw.:

 # Query_time: 2  Lock_time: 0  Rows_sent: 2044  Rows_examined: 33120
 SELECT t1.tan FROM vms_gebuchte_werbung AS t1 LEFT OUTER JOIN vms_reloads AS t2 ON (t2.tan = t1.tan AND (t2.uid = 123456 OR t2.ip =
 INET_ATON('127.0.0.1') ) AND t2.bis >= '1265663704') WHERE t2.tan IS NULL AND t1.werbeart = 'forcedbanner' AND t1.menge > 0 AND 
 t1.status = 1 AND t1.verdienst > 0 AND t1.sponsor != '123456';

So eine Anfrage verlangsamt die Seite natürlich extrem, und belastet die Datenbank. Daher entweder versuchen, durch setzen von Indizes die Abfrage zu optimieren, durch weglassen einiger Bedingungen (im WHERE Teil), eventuell kann sogar die ganze Abfrage aus dem Skript gelöscht werden. Oder, wie im nächsten Punkt beschrieben, die Tabelle(n) optimiert.

Tabellen optimieren

In PHPMyAdmin hat man eine Übersicht über alle Tabellen, dort sollte man

  • auf Überhang prüfen
  • die Anzahl der Einträge im Blick behalten
  • die Größe der Tabellen überwachen

Überhang kann leich durch

 OPTIMIZE TABLE tabellenname;

entfernt werden, dies kann auch via Cronjob regelmäßig für alle Tabellen durchgeführt werden.


Manche Tabellen, wie die Reload Tabelle oder vms_buchungen, haben natürlich viele Einträge. Andere hingegen können regelmäßig geleert werden, Gamelogs bspw. Hier muss je Tabelle und Verwendungszweck individuell entschieden werden, bspw. könnte man die Buchungslisteneinträge auch nach 30 Tagen löschen oder zusammenfassen.

Die Größe einer Tabelle wird aber nicht nur durch die Anzahl der Einträge bestimmt, sondern durch die verwendeten Datentypen je Spalte. So nimmt ein Text (bspw. VARCHAR oder TEXT) mehr Platz weg als eine Zahl (INT). Auch kleine Änderungen (statt INT ein MEDIUMINT) können bei tausenden von Zeilen schon einiges an Platz sparen.

Auch hier muss natürlich von Fall zu Fall entschieden werden, als Anhaltspunkt:

Oft wird die KlammID (=UserID) gespeichert, dazu aber ein INT Feld benutzt. INT belegt 4 Bytes, und speichert Zahlen bis Milliarden. Da KlammIDs aber nur im Bereich von 1 bis 6-stelligen Zahlen liegen, reicht hier ein MEDIUMINT vollkommen aus, dieser belegt nur 3 Bytes.

Einen Überblick bekommt man im Handbuch: [1]

MySQL Analyse

Unter der Struktur Anzeige einer Tabelle befindet sich in PHPMyAdmin ein Link "Tabellenstruktur analysieren". Hier versucht einem MySQL Anhaltspunkte für Optimierungen zu liefern, es sollte aber auf keinen Fall der unter Optimal_fieldtype vorgeschlagene Wert ohne Prüfung übernommen werden!

Bild:Phpmyadmin_analyse.PNG

Indizes setzen

Wenn man zusätzlich zu den zuvor beschriebenen Änderungen an der Konfiguration noch die Zeile

 log-queries-not-using-indexes

hinzufügt, werden auch Anfragen geloggt, welche keine Indizes benutzen. Oft sind diese ineffizient und durchsuchen eine große Zahl an Zeilen. Hier kann es sich lohnen, vorhandene Index Spalten zu prüfen, neue hinzuzufügen, oder auch vorhandene zu kombinieren. Dazu sollte aber ein Grundverständnis vorhanden sein, wie MySQL Indizes benutzt: http://dev.mysql.com/doc/refman/5.1/de/mysql-indexes.html

weitere Informationsquellen

Viel Hilfreiches findet sich in einem Blog, der sich mit MySQL Performance Fragen beschäftigt: http://www.mysqlperformanceblog.com/

Zum Beispiel, wenn man ein recht großes Logfile hat, wird hier ein Skript vorgestellt, welches einem die Auswertung vereinfacht: http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/

noch ein Beispiel: Platz sparen bei IP Adressen

Im VMS1 wird die IP Adresse in der Tabelle vms_reloads als String gespeichert:

 `ip` varchar(15) NOT NULL default ' '

Ein Eintrag verbraucht somit im Schnitt 12 Byte (zw. 8 und 16).

Es gibt aber in MySQL die nützliche Funktion INET_ATON(), die eine IP Adresse in eine Zahl wandelt.

Damit reicht als Datentyp:

 INT UNSIGNED

Ein INT verbraucht nur 4 Byte, hier spart man also Einiges, und Zahlenwerte haben bei der Suche den Vorteil, dass schneller passende Werte gefunden werden können.

Um ein laufendes Projekt umzustellen, muss zuerst jeder Wert geändert werden:

 UPDATE vms_reloads SET ip=INET_ATON(ip);

Dann in allen Dateien, wo die IP in diese Tabelle eingetragen wird:

 ...AND (t2.uid=".$_SESSION['uid']." or t2.ip='".$ip."')...

ändern in:

 ...AND (t2.uid=".$_SESSION['uid']." or t2.ip=INET_ATON('".$ip."'))...

Überwiegend davon betroffen die topframe Dateien und im Ordner content/verdienen.

Danach kann dann der Datentyp der Spalte umgestellt werden. Es empfiehlt sich, die Seite für diese Arbeit kurz in den Wartungsmodus zu setzen, die PHP Dateien kann man ja schon vorbereiten, muss dann nur noch hochladen.

MySQL Server Konfiguration optimieren

Sofern man Zugriff auf die Konfiguration des MySQL Servers hat (Server mit root-Zugriff), kann man mit dem Shell-Skript tuning-primer.sh ([2]) die Statuswerte des MySQL Servers und die aktuellen Einstellungen analysieren lassen und bekommt Vorschläge, welche Werte angepasst werden sollten.

Ein Beispiel für die Ausgabe findet sich im Artikel: Tuning-primer.sh

Persönliche Werkzeuge