In den vorangegangenen Artikeln zur SQL-Optimierung ging es hauptsächlich darum, einzelne Anfragen schneller zu machen. In MySQL können Sie darüber hinaus die Einplanungsprioritäten für Anweisungen manipulieren, sodass Anfragen, die von unterschiedlichen Clients stammen, besser kooperieren können und einzelne Clients nicht längere Zeit ausgesperrt werden. Durch eine Änderung der Prioritäten wird außerdem sichergestellt, dass bestimmte Anfragen schneller ausgeführt werden.
Als Erstes betrachten wir die Standardeinplanung von MySQL und untersuchen dann, welche Möglichkeiten Sie haben, diese Strategie zu beeinflussen. Außerdem wird beschrieben, welche Folgen die Sperrebenen der Tabellen-Handler für den gleichzeitigen Zugriff durch Clients haben.
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |
Einplanungsstrategie
In der folgenden Beschreibung nennen wir einen Client, der eine Suche ausführt (z.B. SELECT), Leser; ein Client, der eine Operation zur Manipulation der Tabelle ausführt (DELETE, INSERT, REPLACE oder UPDATE), ist hingegen ein Schreiber.
Grundsätzlich kann die Einplanungsstrategie von MySQL wie folgt zusammengefasst werden:
-
Schreibanforderungen sollen in der Reihenfolge ausgeführt werden, in der sie ankommen.
-
Schreiboperationen haben eine höhere Priorität als Leseoperationen.
Bei MyISAM- und ISAM-Tabellen wird die Einplanungsstrategie mithilfe von Tabellensperren implementiert. Immer wenn ein Client auf eine Tabelle zugreift, muss dafür eine Sperre angefordert werden. Das kann explizit mit LOCK TABLES erfolgen, aber normalerweise fordert der Sperrmanager des Servers automatisch Sperren an, wenn sie gebraucht werden. Nachdem der Client seine Arbeit mit einer Tabelle abgeschlossen hat, kann die Sperre freigegeben werden. Eine explizit angeforderte Sperre wird mit UNLOCK TABLES freigegeben. Der Server gibt die von ihm angeforderten Sperren automatisch frei.
Exklusiver Zugriff
Ein Client, der eine Schreiboperation ausführt, braucht eine Sperre für den exklusiven Zugriff auf die Tabelle. Die Tabelle befindet sich während der Ausführung der Operation in einem inkonsistenten Status, weil der Datensatz gelöscht, hinzugefügt oder geändert wird und möglicherweise alle Indizes für die Tabelle aktualisiert werden müssen. Wenn man anderen Clients erlaubt, während dieses Zustands auf die Tabelle zuzugreifen, können Probleme auftreten.
Offensichtlich ist es nicht ratsam, zwei Clients gleichzeitig zu gestatten, in die Tabelle zu schreiben, weil die Tabelle damit sehr schnell derart durcheinander geraten würde, dass die darin abgelegten Daten nicht mehr brauchbar wären. Es ist aber auch nicht sinnvoll, einem Client zu erlauben, eine gerade veränderte Tabelle zu lesen, weil sie sich genau zum Zeitpunkt des Lesens ändern könnte und die Ergebnisse damit nicht korrekt wären.
Ein Client, der eine Leseoperation ausführt, braucht eine Sperre, die andere Clients daran hindert, in die Tabelle zu schreiben, sodass diese sich nicht ändert, während sie gelesen wird. Die Sperre muss beim Lesen jedoch nicht exklusiv sein; sie kann anderen Clients erlauben, die Tabelle ebenfalls zu lesen. Beim Lesen wird die Tabelle nicht geändert, es gibt also keinen Grund, warum andere Leser nicht gleichzeitig auf die Tabelle zugreifen sollten.
Anfragemodifikatoren
In MySQL können Sie die Einplanungsstrategie mithilfe verschiedener Anfragemodifikatoren beeinflussen. Einer dieser Modifikatoren ist das Schlüsselwort LOW_PRIORITY für die Anweisungen DELETE, INSERT, LOAD DATA, REPLACE und UPDATE, ein anderer ist das Schlüsselwort HIGH_PRIORITY für SELECT-Anweisungen. Der dritte Modifikator ist das Schlüsselwort DELAYED für INSERT- und REPLACE-Anweisungen.
Das Schlüsselwort LOW_PRIORITY beeinflusst die Einplanung wie folgt: Wenn eine Schreiboperation für eine Tabelle empfangen wird, während diese gelesen wird, wird der Schreiber normalerweise blockiert, bis der Leser fertig ist, denn die Ausführung einer Anfrage wird nicht unterbrochen. Kommt eine weitere Leseanforderung an, während der Schreiber wartet, wird auch der Leser blockiert, weil nach der Standardstrategie Schreiber eine höhere Priorität als Leser haben. Nachdem der erste Leser fertig ist, wird die Anforderung des Schreibers verarbeitet, und nachdem diese abgearbeitet ist, wird die Anforderung des zweiten Lesers verarbeitet.
Hat die Schreibanforderung das Attribut LOW_PRIORITY, dann hat der Schreiber keine höhere Priorität als die Leser. Trifft in diesem Fall eine zweite Leseanforderung ein, während der Schreiber wartet, kann das zweite Lesen vor dem Schreiben ausgeführt werden. Nur wenn keine weiteren Leser anstehen, darf der Schreiber schreiben. Das bedeutet, dass es theoretisch möglich ist, LOW_PRIORITY-Schreibvorgänge für immer zu blockieren. Solange weitere Leseanforderungen ankommen, während noch Leseoperationen ausgeführt werden, erfolgen die neuen Anforderungen vor der Ausführung des LOW_PRIORITY-Schreibvorgangs.
Das Schlüsselwort HIGH_PRIORITY für SELECT-Anfragen verhält sich ganz ähnlich. Damit darf eine SELECT-Anforderung vor einer wartenden Schreiboperation ausgeführt werden, selbst wenn das Schreiben eine normale Priorität besitzt.
DELAYED-Modifikator für INSERT
Der Modifikator DELAYED für INSERT verhält sich wie folgt: Wenn eine INSERT DELAYED-Anforderung für eine Tabelle empfangen wird, stellt der Server die Zeilen in eine Warteschlange und gibt unmittelbar einen Status an den Client zurück, sodass dieser weiterarbeiten kann, noch bevor die Zeilen eingefügt wurden. Solange Leser lesen, werden die Zeilen in der Warteschlange gehalten. Sind keine weiteren Leser vorhanden, beginnt der Server, die Zeilen aus der Warteschlange in die Tabelle einzufügen.
Hin und wieder unterbricht der Server seine Arbeit, um zu prüfen, ob neue Anforderungen eingetroffen sind und wartet. Ist dies der Fall, dann wird die Arbeit mit der Warteschlange unterbrochen, und die Leser können ihre Arbeit fortsetzen. Stehen keine weiteren Leser an, dann werden wieder verzögerte Zeilen eingetragen. Dieser Prozess wird so lange fortgesetzt, bis die Warteschlange leer ist.
LOW_PRIORITY und DELAYED ähneln sich dahingehend, dass beide eine Verschiebung von Zeileneinfügungen ermöglichen, unterscheiden sich allerdings bezüglich ihrer Auswirkungen auf die Clients beträchtlich. LOW_PRIORITY zwingt den Client zu warten, bis die Zeilen eingefügt werden können, während DELAYED es ihm gestattet fortzufahren, während der Server die Zeilen puffert, bis er genug Zeit zur Verarbeitung hat.
Risiken bei INSERT DELAYED
INSERT DELAYED ist praktisch, wenn andere Clients langwierige SELECT-Anweisungen ausführen und sie nicht blockiert werden sollen, bis das Einfügen ausgeführt werden kann. Der Client, der die Anweisung INSERT DELAYED absetzt, kann seine Arbeit schneller wieder fortsetzen, weil der Server die einzufügende Zeile einfach in eine Warteschlange stellt.
Sie sollten jedoch auch einige andere Unterschiede im Verhalten zwischen dem normalen INSERT und INSERT DELAYED kennen. Der Client erhält einen Fehler zurück, falls die INSERT-Anweisung einen Syntaxfehler enthält, aber keine weiteren Informationen, die andernfalls zur Verfügung stehen würden. Beispielsweise können Sie sich nicht darauf verlassen, mit dem Rückgabewert der Anweisung den AUTO_INCREMENT-Wert zu erhalten. Außerdem erhalten Sie auch keinen Zähler für die Anzahl doppelter Werte in eindeutigen Indizes.
Das passiert, weil die Einfügeoperation einen Status zurückgibt, bevor die eigentliche Operation überhaupt stattgefunden hat. Ein weiteres Problem ist, dass Zeilen, wenn sie für INSERT DELAYED-Anweisungen in die Warteschlange gestellt werden und auf das Einfügen warten, bei einem Serverausfall durch Absturz oder Abbruch (mit kill -9) verloren gehen.
Unterstützung der Modifikatoren
Der MyISAM-Handler lässt eine Ausnahme von der allgemeinen Regel zu, dass Leser Schreiber sperren. Wenn nämlich eine MyISAM-Tabelle keine Löcher (d.h. keine gelöschten Reihen) aufweist, dann müssen alle INSERT-Anweisungen Zeilen in jedem Fall am Ende der Tabelle anhängen. Unter diesen Umständen können Clients auch dann neue Zeilen in die Tabelle einfügen, wenn andere Clients Daten aus der Tabelle lesen. Man bezeichnet dies als zeitgleiches Einfügen, denn die Clients können gleichzeitig Daten abrufen, ohne gesperrt zu werden. Wenn Sie diese Funktionalität einsetzen, müssen Sie Folgendes beachten:
-
Verwenden Sie den Modifikator
LOW_PRIORITYnicht in IhrenINSERT-Anweisungen. Andernfalls werden Leser gesperrt, was ein zeitgleiches Einfügen verhindert. -
Leser, die die Tabelle explizit sperren müssen, aber trotzdem zeitgleiches Einfügen erlauben wollen, müssen die Syntax
LOCK TABLES … READLOCALstattLOCK TABLES … READverwenden. Das SchlüsselwortLOCALerlaubt Ihnen, eine Sperre anzubringen, die ein zeitgleiches Einfügen zulässt, weil sie sich nur auf in der Tabelle vorhandene Reihen bezieht und nicht verhindert, dass neue Reihen am Ende hinzugefügt werden.
Die Einplanungsmodifikatoren wurden nicht alle gleichzeitig in MySQL eingeführt. Folgende Tabelle listet die Modifikatoren auf und gibt an, in welcher MySQLVersion sie eingeführt wurden. Sehen Sie hier nach, um festzustellen, welche Möglichkeiten Ihre MySQL-Version unterstützt.
|
Anweisungstyp |
Einführungsversion |
|
|
3.22.5 |
|
|
3.22.5 |
|
|
3.22.15 |
|
|
3.23.0 |
|
|
3.23.38 |
|
|
3.22.8 |
|
|
3.23.11 |
|
|
3.22.5 |
|
|
3.22.15 |
|
|
3.22.9 |
|
|
3.22.5 |
|
|
3.22.5 |
Sperrebenen und Nebenläufigkeit
Die oben beschriebenen Einplanungsmodifikatoren ermöglichen Ihnen die Änderung der Standardeinplanungsrichtlinien. Sie wurden in erster Linie eingeführt, um Probleme zu lösen, die sich in Zusammenhang mit Sperren auf Tabellenebene ergaben, mit deren Hilfe bei ISAM- und MyISAM-Tabellen der gleichzeitige Zugriff verwaltet wird.
Mittlerweile gibt es bei MySQL BDB- und InnoDB-Tabellen, die Sperren auf verschiedenen Ebenen ermöglichen und aufgrund dessen unterschiedliche Leistungscharakteristika aufweisen, was die Verwaltung des gleichzeitigen Zugriffs betrifft. Der BDB-Handler verwendet Sperren auf der Seitenebene, der InnoDBHandler auf Zeilenebene (wenn auch nur im benötigten Maße; oft verwendet InnoDB überhaupt keine Sperren, etwa wenn nur Lesevorgänge erfolgen).
Welche Sperrebene ein Tabellen-Handler verwendet, wirkt sich in hohem Maße auf die Nebenläufigkeit mehrerer Clients aus. Nehmen wir einmal an, zwei Clients wollen jeweils eine Zeile einer gegebenen Tabelle aktualisieren. Um die Aktualisierung durchführen zu können, benötigt jeder der Clients eine Schreibsperre. Bei einer MyISAM-Tabelle wird der Handler für den ersten Client eine Tabellensperre vornehmen, d. h., der zweite Client wird gesperrt, bis der erste seine Arbeit beendet hat.
Bei BDB-Tabellen können gleichzeitige Verbindungen eher vorhanden sein, da die Aktualisierungen gleichzeitig erfolgen können, sofern die betreffenden Zeilen nicht auf der gleichen Seite vorhanden sind. Bei InnoDB-Tabellen ist die Situation sogar noch besser: Die Aktualisierungen können gleichzeitig erfolgen, solange nicht beide Clients ein und dieselbe Zeile aktualisieren.
Deadlocks
Allgemein ergibt sich daraus, dass Nebenläufigkeit bei Clients umso besser unterstützt wird, je niedriger die Sperrebene ist, denn umso mehr Clients können eine Tabelle gleichzeitig verwenden (sofern sie auf unterschiedliche Teile dieser Tabelle zugreifen). Praktisch ergibt sich daraus, dass verschiedene Tabellentypen unterschiedlich gut für verschiedene Anfragemischungen geeignet sind:
-
ISAM- und MyISAM-Tabellen sind beim Abrufen von Daten extrem schnell. Allerdings kann die Verwendung von Sperren auf Tabellenebene in Umgebungen mit gemischten Abruf- und Aktualisierungsaktionen zum Problem werden. Dies gilt insbesondere dann, wenn das Abrufen sehr zeitaufwändig ist; unter diesen Bedingungen kann es ziemlich lange dauern, bis Updates umgesetzt werden.
-
BDB- und InnoDB-Tabellen bieten eine bessere Performance, wenn Aktualisierungen häufig auftreten. Da die Sperre auf der Seiten- bzw. Zeilenebene erfolgt, ist der Teil der Tabelle, der vorübergehend nicht zugänglich ist, kleiner. Auf diese Weise werden Wartezeiten reduziert und gleichzeitige Verbindungen optimiert.
Die Sperre auf Tabellenebene hat einen Vorteil gegenüber den feinkörnigeren Sperrebenen, was die Verhinderung von Deadlocks angeht; diese können nämlich bei einer Tabellensperre gar nicht erst auftreten. Der Server bestimmt, welche Tabellen benötigt werden, indem er die Anfrage überprüft und alle betroffenen Tabellen umgehend sperrt. Bei InnoDB- und BDB-Tabellen können Deadlocks auftreten, weil die Handler nicht alle zu Beginn einer Transaktion benötigten Sperren vornehmen; stattdessen werden die Sperren umgesetzt, wenn sie während der Abarbeitung der Transaktion für notwendig befunden werden.
Es ist möglich, dass zwei Anfragen Sperren vornehmen und dann versuchen, weitere Sperren umzusetzen, die jedoch die Freigabe bereits gesperrter Elemente voraussetzen. Das bedeutet, dass jeder Client eine Sperre vorgenommen hat, die aufgehoben werden muss, damit der jeweils andere Client fortfahren kann. Dies ist eine Sackgasse, und der Server muss eine der Transaktionen abbrechen. Bei BDB-Tabellen können Sie die Entstehung solcher Deadlocks verhindern, indem Sie die Tabellensperre explizit mithilfe von LOCK TABLES vornehmen, denn der BDB-Handler verarbeitet solche Sperren. Bei InnoDB-Tabellen funktioniert das nicht, denn der Handler ignoriert Sperren, die mit LOCK TABLES gesetzt wurden.
Ausblick
Dieser Teil der Serie hat sich mit der manuellen Priorisierung von Lese- und Schreiboperationen und dem passenden Table Locking beschäftigt. Im nächsten und auch letzten Teil der SQL-Serie werden Optimierungen besprochen, die den Server selbst betreffen daher speziell für Administratoren geeignet sind. (mzu)
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |