Kategorien
…und das Leben von SQL geht weiter … jetzt wird es schneller!
Auch wenn es derzeit die neuen und heiß diskutierten Alternativen zu relationalen Datenbanken gibt (siehe Reihe „Ein Leben nach SQL„), wird die Ehe PHP und MySQL sicherlich noch lange halten. Doch oft wird vergessen, dass Ehepartner gleichberechtigt sind und so wird MySQL in Projekten oft nur stiefmütterlich behandelt.
MySQL bietet derzeit sicherlich einen der einfachsten Wege, eine persistente Datenhaltungsschicht aufzubauen. Die freie Datenbank ist schnell, vielseitig, stabil, ausgereift, bei fast jedem Hoster vorinstalliert und sehr gut dokumentiert bzw. kommentiert. Daher ist MySQL für beinahe jedes Projekt bestens geeignet. Auch die meisten PHP Frameworks setzen auf MySQL und bieten eine einfache Integration. Doch gerade diese gute Unterstützung führt häufig zu einer Vernachlässigung der Datenbank. Der folgende Teil soll einen schnellen Überblick über einige grundlegende Optimierungsansätze und häufige Fehlerquellen bei MySQL bzw. RDBMS geben.
Mein Framework erledigt das mit der Datenbank schon
Frameworks, die Datenbanken beinahe komplett kapseln, verleiten leicht dazu, die Datenbank zu vergessen, da man keine oder nur wenig Berührungspunkte mit der Datenbank hat. Für Rapid Application Development ist dies bestens geeignet, doch wer weiß eigentlich, wie das Framework wirklich arbeitet?
Wird eine einfache Abfrage, wie zum Beispiel „Gib mir alle Kunden und ihre Projekte„, über einen schnellen Join realisiert, oder erstellt das Framework für jeden Kunden eine neue Query, die dann die jeweiligen Projekte abfragt?
Wenn in einer Zeile mehrere Spaltenwerte geändert werden, wie im folgenden Codebeispiel gezeigt, wird die gesamte Änderung in einem oder in mehreren UPDATE Statements abgearbeitet?
$datennsatz->change('Spalte1','wert1);
$datennsatz->change('Spalte2','wert1);
$datennsatz->change('Spalte3','wert1);
Resultiert in welchen SQL Statements?Dies sind nur zwei sehr einfache und naheliegende Beispiele, die zeigen, dass diese Kapselung oder Abstraktion der Datenpersistenzschicht je nach Implementierung nicht ganz ohne Nachteile auskommt. Daher ist es umso wichtiger, dass man das verwendete Framework auch etwas unter der Haube kennt.
Die meisten guten Frameworks bieten die Option, sich die eigentlichen SQL Statements ausgeben zu lassen [4,5]. Dies hilft nicht nur das Framework besser zu verstehen und dementsprechend besser damit umzugehen, sondern auch SQL Statement-Sünder zu entlarven.
Meine Datenbank ist so langsam!?
Wenn die Persistenzschicht einmal zu langsam wird und man den Fehler im Programm- und Frameworkteil ausschließen kann, gibt es zwei sehr einfache Möglichkeiten etwas mehr über die Datenbank und ihre Problemfall-Queries zu erfahren:
MySQL Log-Slow-Queries [3]
Dieser Parameter wird in der Konfigurationsdatei von MySQL my.cnf angegeben und definiert eine Log Datei, in die alle Queries geschrieben werden, die zu viel Zeit benötigen.
log-slow-queries = /var/log/mysqld_slow.log
long_query_time = 3In diesem Beispiel werden alle Queries, die über 3 Sekunden zur Ausführung benötigen, aufgezeichnet. Je nach Art der Anwendung sollte der Wert von long_query_time so gewählt werden, dass Ausnahmen leicht gefunden werden, die Datei aber nicht überfüllt wird. Der nächste Schritt, ohne dem eine Log Datei nur bedingt Sinn macht, ist die Kontrolle. Die beste Aufzeichnung ist nutzlos, wenn die Log Dateien nicht regelmäßig überprüft werden.
MySQL Explain [2]
Hat man einmal die „bösen“ Statements festgemacht, können diese mit Hilfe des Befehls explain analysiert werden. Der Befehl zeigt, welchen Ausführungsplan, also die Art der Zugriffe auf die Datenbank, der MySQL Optimierer wählt.
Betrachten wir folgendes Beispiel:
mysql> EXPLAIN SELECT * FROM Projekte WHERE Kunde = 'Maier'
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | projekte | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+Grundsätzlich sollte man vermeiden, sogenannte Full Table Scans zu erreichen. Anfragen, die keinen Index nutzen können, wie in diesem Beispiel, müssen über die gesamte Tabelle (Full Table) den gewünschten Eintrag suchen. In diesem Fall, da in der Spalte key bzw. possible_keys kein Index aufscheint, müssen 100.000 Zeilen durchsucht werden (siehe Spalte rows).
Verwendet eine häufig gestellte Query aus dem Slow-Log keinen Index, muss kontrolliert werden, ob weitere Indizes erzeugt oder alte angepasst werden können, sodass die Query schneller abgearbeitet werden kann.
Index? Habe ich alles, ich habe ja einen Primary Key
Betrachtet man die Datenbankmodellierung vieler Projekte (auch vieler OpenSource Projekte), dann könnte man annehmen, dass Entwickler nur den PrimaryKey (als Form des Index) kennen. Zusätzliche sinnvoll gesetzte Indizes können Datenbanken jedoch um ein Vielfaches beschleunigen, werden jedoch trotzdem nur selten oder rudimentär verwendet.
Genau genommen handelt es sich bei einem PrimaryKey nicht um einen Index. Die meisten Datenbanken, so auch MySQL, legen jedoch automatisch auf den PrimaryKey (eine oder mehrere Spalten) einen Index an. Man spricht daher in diesem Fall auch von einem Primärindex.
Wie beim PrimaryKey, über den sehr oft auf Daten zugegriffen wird, sollten auch andere Spalten einen Index (Sekundärindex) erhalten.
Um das oben angeführte Beispiel zu optimieren, kann zum Beispiel ein Index über die Spalte Kunde angelegt werden:
CREATE INDEX my_index ON Projekte(Kunde);Ein neuerliches Explain zeigt den Unterschied:
mysql> EXPLAIN SELECT * FROM Projekte WHERE Kunde = 'Maier'
+----+-------------+-----------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | projekte | ref | my_index | my_index | 257 | const | 1 | Using where |
+----+-------------+-----------+------+---------------+----------+---------+-------+------+-------------+Mit dem neuen Index müssen nicht mehr alle 100.000 Zeilen durchsucht werden, sondern es kann direkt auf die eine gewünschte Zeile (bei der gilt Kunde=Maier) zugegriffen werden. Dies ist in der Ausgabe von explain an der Spalte rows ersichtlich, die in diesem Beispiel jetzt nur mehr 1 beträgt.
Grundsätzlich sollte jede Spalte, über die oft auf Daten zugegriffen wird (wie Kunde in diesem Beispiel) oder über die ein Join durchgeführt wird, einen Index erhalten. Auch bei sehr kleinen Datenmengen kann dies einen enormen Geschwindigkeitsschub bedeuten, wenn auch die Einfügegeschwindigkeit von neuen Datensätzen durch Indizes etwas sinken kann.
Grundsatzregeln haben aber wie jede Regel Ausnahmen, in diesem Fall sogar sehr viele, doch mehr dazu gibts bei Interesse (Bitte hierzu um Kommentare) in einem nächsten Artikel…oder in den unten angeführten Links.
- Sehr guter Mysql Blog, der aber teilweise technisch in die Tiefe geht – http://www.mysqlperformanceblog.com/
- Das Explain Statement genau erklärt
- MySQL Slow Query Log
- Zend Framework DB Profiler in Verbindung mit FirePHP, dem Firebug Plugin
- MySQL Query Profiler
http://explainextended.com/ ist auch sehr zu empfehlen. Der Typ hats SQL Technisch einfach drauf (nicht nur mysql).
Es gibt Studien, dass 40 Prozent der Performanceprobleme durch schlechte SQL Abfragen bedingt sind. Obwohl sich das meist erst bei größeren Datenmengen bemerkbar macht, sollte es für jeden Entwickler Grund genug sein, sich mit SQL zu beschäftigen und nicht nur auf die Frameworks zu vertrauen.
Was mich mal interessieren würde, man kann einen Index ja auch über mehrere Spalten legen. Welchen Unterschied gibt es da, ob ich nun zwei einzelne Indexe habe oder beide in einem zusammen fasse.
Wie gesagt, Index ist ein Riesen-Thema. Primärindex, Sekundärindex, clustered vs. non-clustered index, index über mehrere spalten usw….also in diese richtung kann man viel erklären. wenn hier bei den lesern interesse besteht können wir gern etwas in dieser richtung machen…
Eigentlich ist der Index etwas, worum sich die Datenbank automatisch kümmern sollte. Und genau daran wird ja gerade intensiv geforscht und gearbeitet.
Die Datenbank soll gefälligst selbst merken welche Spalten sie häufig abfragt und einen Index dort anlegen, wo er benötigt wird und unnötige Indexe (deren Wartung auch Zeit kostet) automatisch löschen.
Hat man eine zentrale Schema-aware DB-API, so wie die an der wir gerade hausintern forschen, kann man das jetzt schon simulieren: einfach protokollieren welche Spalten in einer Where-Klausel verwendet werden und wie oft.
Anschließend kann das Framework aufgrund dieser Daten automatisch die Datenbank anpassen.
Wir wollen das jedenfalls in das quelloffene Yana Framework integrieren als Erweiterung basierend auf PEAR MDB2. Damit kann man dieses Feature nachträglich für MySQL, PostgreSQL und alle anderen unterstützten Datenbanken nachrüsten.
Wir werden anschließend den Code und unsere Ergebnisse veröffentlichen, wie viel Performance sich so herauskitzeln lässt.
Trotzdem: die beste Performanceverbesserung bringt nicht die schnellste Abfrage, sondern die, welche komplett vermieden wird. Zum Beispiel durch intelligentes Caching.
Bei Websphere erledigt das für IBM DB2 der Application Server. Zend würde es im Zend-Server gern machen, hat es aber aktuell noch nicht. Bei MySQL mit Apache steht leider nichts dergleichen in der Pipeline. Sun’s Glassfish sah mal sehr vielversprechend aus, aber da weiß man momentan nicht so recht wie es weiter geht.
Also haben wir das MRU-Caching seinerzeit selbst realisiert: aber den Cache nicht persistiert. Der Overhead ist nicht sehr groß. Zumindest verhindert diese Lösung, das Queries doppelt abgeschickt werden. Ein persistenter Cache bringt zwar deutlich mehr, aber es steigen auch Speicherbedarf, Komplexität und Overhead. Leider kann ich im Moment noch nicht abschließend sagen, ob sich das auf Seite der Anwendung wirklich lohnt.
In jedem Fall hat es sich gelohnt für einzelne häufig benötigte, aufwändig zu initialisierende Objekte. Die Start-Up-Zeit konnte bei uns durch Caching auf ca. 10-30% reduziert werden. Interessanterweise hat Lazy-Loading danach (!) kaum noch etwas zusätzlich gebracht. Allerdings hat es den Speicherbedarf bei unserem sehr großen Projekt sichtbar verringert.
@automatische indexerzeugung:
das gibt es ja bereits bei den großen DB herstellern. ich kenne da auch eine nette geschichte von einer sehr großen versicherung in münchen. die hatten auch sowas und alles lief gut. plötzlich an einem montag waren alle mitarbeiter lahmgelegt, weil die DB plötzlich anfragen gar nicht mehr oder langsam bearbeitete.
das problem waren sehr viele data mining anfragen am wochenende von einem automatischen prozess, der den index optimierer veranlasste, alle indizes umzustellen. montag in der früh kamen die normalen anfrangen, die nicht mehr optimiert durchgeführt wurden -> viele € schaden…
@Tom
automatisch erstellte Indexe? Ja, Faulheit ist sicherlich was feines. Aber man sollte und darf nicht alles automatisieren, was automatisieren ginge – siehe Wolfgangs Beispiel. Faulheit ist nicht immer gut, schon gar nicht da, wo es durchaus unerwartete Probleme geben kann. Es ist immer besser ganz genau zu wissen, was die Maschine macht – statt eine Maschine entscheiden zu lassen, was sie macht.
@Wolfgang
Danke für den super Artikel. Aus dem Grund setz ich eher weniger PHP Frameworks ein, da ich lieber selbst programmiere und da dann auch wirklich weiß, was ich hab. Ich will zwar nicht sagen, dass PHP Frameworks schlecht sind, aber sie lassen den Blick fürs Wesentliche verschwimmen.
Würde mich freuen mehr darüber zu lesen, egal ob es schon durch die von dir angegebenen Punkte schon erklärt wird – dein Artikel liest sich einfach gut und ist leicht verständlich.
Danke!
Hallo,
ich wäre erfreut noch mehr über Datenbankperformance-Optimierung zu lesen.
Danke für den Artikel!
Viele Grüße
Dominik Strauß
Ich schließe mich dem Vorredner an. Interessante Ansätze zur Performance-Optimierung vom MySQL und MySQL-Anfragen sind gern gesehen. Ein Vortrag zu dem Thema auf der PHP Conference in Karlsruhe ging leider in die Hose.
Hinzufügen würde ich noch, dass indizierte Tabellen gewartet werden müssen. So ein Index kann nämlich gern mal kaputt gehen.
BTW, die MySQL C-API kann Dir sagen wann eine Query als langsam erkannt wurde. Diese Funktionalität wird durch ext/mysqli auch PHP-Anwendern zur Verfügung gestellt – http://blog.ulf-wendel.de/?p=272 . Warum ein Teil der von C-API für diese Aufgabe benutzten Konstanten nicht sauber dokumentiert ist, konnte mir das Doku-Team auch nicht sagen…
Verwende als Framework CakePHP und kann sagen, dass dort das Query Building echt gut gelöst ist, aber allgemein kann man sagen, dass eine der größten Vorteile von Cake ORM und Models sind, dass passt alles wunderbar.
@Wolfgang Gassler und @Patrick gedacht ist es zunächst als Vorschlagsfunktion.
Wir wollen, dass der Entwickler einen Belastungstest durchführen kann und anschließend das Programm ihm selbst sagt, wo es gerade weh tut.
Der Vorteil der automatischen Umsetzung liegt aber auf der Hand: der Entwickler kann nicht immer vorhersehen wo die größte Last auftreten wird und wann. Nehmen wir eine Lastspitze auf einem bestimmten Subsystem (vielleicht als Folge einer DOS-Attacke): die Datenbank optimiert sich dann selbst um die Spitze besser bewältigen zu können.
Natürlich muss das System nach der Lastspitze wieder zurück in den Ausgangszustand. Die Frage wie schnell das passiert ist aber nicht Pro- oder Kontra der Technik an sich, sondern eine Frage, wie intelligent die Technik implementiert ist.
Aber das beschriebene Problem hat man auch OHNE die Optimierung. Montagmorgens ist die Datenbank nach den Cronjobs vom Wochenende auch so schon langsamer, weil die Caches ja ebenfalls nicht mehr aktuell sind bzw. die falschen Dinge im Cache liegen.
Die Datenbank muss darauf schnell reagieren können – nichts anderes erwartet man bei anderen Techniken zur Selbstverwaltung auch.
Ein weiteres Beispiel: Wir hatten am WE eine IBM DB2 Datenbank synchronisiert und automatisiert mit neuen Daten befüllt. Der Prozess, der Freitagabend gestartet worden war, lief wider erwarten montags um 8 Uhr immer noch.
Was war passiert? DB2 hat den existierenden Index nicht verwendet weil es keine aktuellen Statistiken hatte. Außerdem hatte es permanent versucht nach jedem neu eingefügten Datensatz alle Indexe zu aktualisieren, was die Zeit für die Inserts und Updates vervielfachte.
Wir mussten die Skripte so anpassen, dass vor dem Cronjob alle Indexe gelöscht und anschließend neu angelegt werden. Danach lief alles wieder normal – zumindest solange der Cronjob nicht wegen eines Fehlers zwischendurch abbrach.
Anderes Beispiel: ein Mitarbeiter hatte am Wochenende ein Data-Mining auf einer PostgreSQL Datenbank durchgeführt via Remote-Zugriff. Dabei hat er eine lang-laufende Abfrage gestartet, die sich in einer Endlosschleife verfangen hatte. Als er merkte, dass etwas schief läuft, hat er den Client einfach geschlossen, die Session auf dem Server aber nicht beendet. Als folge davon lief seine Datenbankabfrage weiter.
Die Deadlock-Erkennung auf PostgreSQL hat versagt. Die Datenbank hat die Query nicht automatisch beendet (Standardkonfiguration). Die laufende Abfrage sperrte mehrere Tabellen. Dadurch wurden am Wochenende die Reports nicht erzeugt. Am Montag morgens stand der ganze Betrieb still, weil die Verkaufszahlen nicht da waren und der Datenbank Anfragen entweder nur träge oder gar nicht entgegen nahm.
In der Folge haben wir PostgreSQL rekonfiguriert und alle Queries, die länger als 6 Stunden liefen, automatisiert abgeschossen. Wenn nun wieder jemand den Server am Freitag lahmlegte so ging uns nur noch das Update vom Samstag verloren, dass dann vom Update am Sonntag überschrieben wurde, sodass Montag wieder alles lief. Zumindest solange niemand den Server am Montag nochmal lahmlegte.
Der Datenbank-Admin wird also in nächster Zeit nicht überflüssig werden. Aber die Arbeit etwas erleichtern wollen wir ihm schon 😉
Und dabei geht es mir vor allem um die Frage: was kann der Entwickler im Vorfeld tun und wie kann die Datenbank-API ihn dabei unterstützen.
@Wolfgang Gassler übrigens: ein Kollege erzählte ein Kunde hatte es schon mal geschafft, dass die eigene IT-Abteilung die Datenbank eines Hochregallagers dazu gebracht hat nach und nach schleichend ihren Bestand zu vergessen.
Nach einer Weile haben die Lagerroboter sich dann einfach geweigert Ware zu holen, weil sie nicht wussten wo die Paletten liegen. Erst nur ein paar Paletten, dann immer häufiger.
Als sie das gemerkt haben mussten sie in der Folge 4 Wochen lang den Betrieb komplett stilllegen, das Lager manuell leer räumen und alles neu einlagern. Die Pointe ist: sie hatten die Software eigentlich deshalb intern entwickelt, um Geld zu sparen, weil ihnen der externe Dienstleister zu teuer war.
@Tom: ja, intelligente software kann fehler machen, menschen können fehler machen -> der idealweg ist das ganze zu vereinen und sich gegenseitig zu helfen. vorschläge und hilfe für den admin finde ich daher sehr gut….
und wenn es auch nur so kleine dinge wie die analyse procedure von mysql sind: „SELECT * FROM my_table PROCEDURE ANALYSE()“
@Tom
> Als sie das gemerkt haben mussten sie in der Folge 4
> Wochen lang den Betrieb komplett stilllegen, das Lager
> manuell leer räumen und alles neu einlagern. Die Pointe
> ist: sie hatten die Software eigentlich deshalb intern
> entwickelt, um Geld zu sparen, weil ihnen der externe
> Dienstleister zu teuer war.
lol. Eine teure Lektion. Aber das wird wohl auch recht häufig passieren, wenn ich bedenke, wie viele Firmen lieber eigenes Personal für Dinge einstellen, die ein Dienstleister womöglich 10x zuverlässiger (da mehr Erfahrung) erledigen kann – auch wenns eventuell erstmal teurer ist als eigenes Personal…
Naja, ich bin mal gespannt, was da noch alles möglich ist. Ich habe, zugegeben, keinerlei derartige Großprojekte bisher gehabt – bin da auch nicht scharf drauf – aber so, wie du des darlegst, Tom, ist das für mich dann jedenfalls auch schlüssig.
Klasse Artikel! Und auch ich würde mich über ein Follow-Up in Sachen Ausnahmen & Co. freuen.
Thanks a lot!