MySQL Optimierung - INSERT DELAYED/UPDATE LOW_PRIORITY

Sie befinden sich: Home > Webmaster News

So heute geht es mal darum ein wenig die MySQL Datenbank Anweisungen zu optimieren. Die Ladegeschwindigkeit von MySQL Befehlen kann über die INSERT DELAYED und die UPDATE LOW_PRIORITY positive Geschwindigkeitsschübe bei der Webseite veranlassen und die Last des MySQL-Servers sehr reduzieren.

Das Problem ist, wenn mehrere hundert Zugriffe gleichzeitig auf eine Tabelle erfolgen, können diese sich gegenseitig stören: Da UPDATE und INSERT bei MySQL eine höhere Priorität als SELECT Anweisungen haben, aber mehr Zeit in Anspruch nehmen und zudem SELECT Anweisungen blockieren, bis sie fertig ausgeführt sind, kann dies bei einem hohem Besucheraufkommen zu einem verlangsamten Seitenaufbau der Website führen oder sogar den Server überlasten.

Deshalb gibt es die Möglichkeit statt dessen unwichtige UPDATE und INSERT warten zu lassen, bis die SELECT Anweisungen durchgeführt worden sind. Wenn Sie die UPDATE und INSERT Anweisungen auf Ihrer Website durchgehen, werden Sie mit großer Wahrscheinlichkeit welche finden, die nicht sofort ausgeführt werden müssten.

Umsetzen können Sie dies mit INSERT DELAYED und UPDATE LOW_PRIORITY. Zur Erläuterung werden dafür hier folgende Anweisungen verwendet, wie sie zum Beispiel in einem Onlineshop vorkommen könnten, in dem User selbst Artikel anbieten (unter der Bedingung, dass der User darüber informiert wird, dass die Übernahme der Eingaben sich verzögern kann).

- Änderung eines angebotenen Artikels: UPDATE `artikeltab` SET `name`='eingabename', `preis`='eingabepreis'...;
- Einstellen eines Artikels: INSERT INTO `artikeltab` (`name`, `preis`...) VALUES ('eingabename', 'eingabepreis'...);

Beachten Sie bitte, dass es sich hierbei lediglich um Beispiele handelt und Sie selbst erwägen müssen, ob das Verzögern gerade dieser UPDATE und INSERT Anweisungen für Ihre eigene Website in Frage kommt oder nicht. So können Sie die Ausführung solcher Anweisungen zurückstellen:

Mit UPDATE LOW_PRIORITY wird ein Datensatz erst aktualisiert, wenn niemand mehr auf die betroffene Tabelle zugreift. Dazu müssen Sie Ihre UPDATE Anweisungen folgendermaßen umändern:

Vorher: UPDATE `artikeltab` SET `name`='eingabename', `preis`='eingabepreis'...;
Nachher: UPDATE LOW_PRIORITY `artikeltab` SET `name`='eingabename', `preis`='eingabepreis'...;

Sie sollten dabei beachten, dass LOW_PRIORITY den Ablauf des PHP Scripts behindern kann, da mysql_query() erst einen Rückgabewert bekommt, sobald das UPDATE durchgeführt wurde. Dies kann man umgehen, indem man das UPDATE im PHP Script an den Schluss setzt.

Es gibt auch die Möglichkeit anstatt die Priorität von UPDATE herunter zu setzen, die der wichtigen SELECT Anweisungen herauf zu setzen. Dies kann jedoch zum einen mehr Arbeit sein, da es meistens mehr SELECT Anweisungen gibt und ein Suchen/Ersetzen im Quellcode nicht ausreicht, weil Sie für jede einzeln entscheiden müssen, ob es sinnvoll ist HIGH_PRIORITY einzusetzen oder nicht. Zum anderen, was noch wichtiger ist, könnten auf diese Weise auch wichtige UPDATE und INSERT Anweisungen zurückgestellt werden.

Wenn Sie diese Möglichkeit dennoch verwenden wollen, dann müssen Sie Ihre Anweisungen wie folgt ändern:

Vorher: SELECT `id`,`name`... FROM `artikeltab`;
Nachher: SELECT HIGH_PRIORITY `id`,`name`... FROM `artikeltab`;

Noch ein kleiner Powertipp in diesem Sinne, verwenden Sie bei allen SELECT Anweisungen den Namen der jeweiligen Spalte. Verzichten Sie auf Abfragen mit SELECT * ...

Auch für INSERT gibt es LOW_PRIORITY, aber hier ist INSERT DELAYED von Vorteil. Die Anweisung wird dabei sofort ausgeführt und der neue Datensatz in eine Warteschlange geschrieben. Dadurch bekommt mysql_query() des PHP Scripts immer sofort den Wert true zurück geliefert und wird somit nicht wie oben beschrieben behindert. Zudem werden die Anweisungen aus der Warteschlange dann gebündelt (also schneller als Einzelanweisungen) abgearbeitet, sobald es keine anderen Zugriffe mehr auf die betroffene Tabelle gibt.

Hierzu müssen Sie Ihre INSERT Anweisungen folgendermaßen ändern:

Vorher: INSERT INTO `artikeltab` (`name`, `preis`...) VALUES ('eingabename', 'eingabepreis'...);
Nachher: INSERT DELAYED INTO `artikeltab` (`name`, `preis`...) VALUES ('eingabename', 'eingabepreis'...);

Hierbei sollten Sie beachten, dass INSERT DELAYED langsamer ist als normale INSERT Anweisungen, da immer erst geprüft werden muss, ob noch andere Zugriffe auf die Tabelle laufen. Zudem müssen Ihre Tabellen mit dem MyISAM-, MEMORY- oder ARCHIVE-Speicher-Engine angelegt worden sein um diese Anweisung durchzuführen. Bei INSERT ... SELECT und INSERT ... ON DUPLICATE KEY UPDATE Anweisungen funktioniert das DELAYED nicht, da es vom Server ignoriert wird.

Damit die Warteschlange nicht zuviel Speicherplatz blockiert, wird über die Variable delayed_queue_size festgelegt, wieviele Datensätze in der Warteschlange stehen dürfen. Zusätzliche INSERT DELAYED Anweisungen müssen dann warten, bis wieder genügend Platz in der Warteschlange zur Verfügung steht. In diesem Fall muss auch das PHP Script auf die Rückmeldung warten. Wenn Sie die betreffende Tabelle mit LOCK TABLES gesperrt haben, dann kommt es bei INSERT DELAYED zu einer Fehlermeldung.

Wenn Sie die oben aufgeführten Anweisungen verwenden, sollten Sie immer bedenken, dass die auf diese Weise hinzugefügten oder geänderten Daten, erst angezeigt werden können, sobald die jeweiligen Anweisungen abgearbeitet worden sind. Wenn immer wieder andere Anweisungen herein kommen, kann es unter Umständen sehr lange dauern, bis die wartenden ausgeführt werden. Also verwenden Sie diese nur, wenn Sie sich sicher sind, dass Sie die betroffenen Daten nicht direkt benötigen. Zudem können die Daten verloren gehen, falls der Server abstürzt, bevor die Anweisungen ausgeführt wurden.

Um zu sehen was der Mysql Server überhaupt macht, kann man mit SHOW FULL PROCESSLIST die gesamte Processlist sich ausgeben lassen. Dies hilft um langsame Abfragen zu finden. In diesem Sinne viel Spaß beim optimieren eurer MySQL Abfragen. Habt Ihr noch Fragen, steht euch unser Forum zur Verfügung.

Eingetragen am 23.01.2011

Schreib ein Kommentar