Hilfe, wo sind meine Rabatte?

OXID Enterprise Edition bietet vielfältige Möglichkeiten, Rabatte einzusetzen

Rabatte können für jeden Subshop separat konfiguriert werden und sie können an Subshops vererbt werden. Rabatte können auf eine Auswahl von Ländern eingeschränkt werden, sie können auch auf einen Teil des Sortiments eingeschränkt werden (wenn das nicht der Fall ist, gelten sie für den gesamten Warenkorb). Und schließlich können Rabatte in Bezug auf Benutzer bzw. Benutzergruppen eingeschränkt werden.

OXID Professional Services wurde vomPartner W&Coum Unterstützung gebeten, weil in einem ihrer Projekte die Rabattberechnung nicht oder nur erratisch funktionionierte.

Das Problem mit der Optimierung derived_merge

Es hat sich herausgestellt, dass beim Einsatz einer OXID Enterprise Edition zusammen mit MySQL 5.7 die Rabatte nicht funktionieren, wenn die Konfigurationsmöglichkeiten für Rabatte ausgereizt werden. Der Shop berechnet einen Rabatt einfach nicht, obwohl er laut Konfiguration berechnet werden sollte.

Das Problem ist hier dieOptimierung derived_merge, die bei MySQL 5.7 zu einer fehlerhaften Ausführung bestimmter Abfragen führen kann. Dies zu ermitteln, hat uns beträchtlich Nerven und Mühe gekostet.

Wir wandten uns unsererseits an denHosting-Partner qwertikound gaben ihnen den zusätzlichen Hinweis, dass der Fehler nicht auftritt, wenn wir alle Optimizer-Switches auf 'off' stellten mittels

Dies empfanden wir als Holzhammer-Methode mit unübersehbaren Auswirkungen auf die Datenbank-Performance.

Sehr bald lieferte qwertiko uns jedoch eine wesentlichweniger invasive Lösung. Es reicht nämlich,nur den Optimizer-Switch derived_merge auf 'off' zu stellen. Diese Optimierung kann laut Dokumentation (Optimizing Derived Tables and View References with Merging or Materialization) auch auf Abfragen mit Views angewendet werden.

Ein Artikel bei Percona "Why Optimization derived_merge can Break your Queries" beschreibt, dass diese Optimierung dazu führen kann, dass die durch den Optimizer umgeschriebene Abfrage nicht mehr die gleichen Ergebnisse produziert.

Eine bleibende Lehre ist ein weiteres Werkzeug für unseren Werkzeugkasten, das wir diesem Percona-Artikel entnehmen konnten. Es gibt nämlich eine einfacheMöglichkeit, in das Wirken des Optimizers Einblick zu nehmenund diese Möglichkeit wollen wir in diesem Artikel besprechen.

DerJob des Optimizers ist es, eine Datenbankabfrage so umzuschreiben, dass sie performanter ausgeführt werden kann.Doch was macht der Optimizer?

Das EXPLAIN-Statement

Das EXPLAIN-Statement gibt Einblick, wie eine Abfrage ausgeführt wird und lässt zu, die Performance einer Abfrage zu beurteilen. Wir wollen das mit einer vereinfachten Version der problematischen Abfrage zeigen (vereinfacht in der Hinsicht, dass lediglich die Gruppenzuordnung der Rabatte vorkommt).

Die Abfrage sieht so aus:

EXPLAIN mit derived_merge=on

Ein EXPLAIN mit derived_merge=on liefert

Wir wollen uns nun nicht den 5 Ergebniszeilen zuwenden, sondern einen Blick auf die Zusammenfassung werfen. Dort steht, dass EXPLAIN 5 Ergebniszeilen und 4 Warnungen liefert. Diese Warnungen sind der Schlüssel zu einem tieferen Verständnis des Optimizers.

Mittels SHOW WARNINGS zeigt MySQL nämlich das Ergebnis der Arbeit des Optimizers:

Abfrage nach Formatierung

Schön formatiert (und die Select-Felder durch * ersetzt) sieht die Abfrage so aus:

EXPLAIN mit derived_merge=off

Wenn wir derived_merge auf 'off' stellen, liefert EXPLAIN Folgendes:

Abfrage nach erneuter Formatierung

Wir erhalten mittels SHOW WARNINGS (wiederum ohne Select-Felder und formatiert):

Die so umgeschriebene Abfrage liefert nun unabhängig von der Einstellung von derived_merge immer das gewünschte Ergebnis. Abgesehen von der Auflösung der View (falls derived_merge=on) fällt auf, dass jeweils aus 'SELECT (IF (EXISTS' ein 'IF (EXISTS (SELECT' erzeugt wird.

Zusammenfassung und Handlungsempfehlung

Nach diesem Einblick in die Arbeit des Optimizers wollen wir es nicht unterlassen, zu betrachten, wieviel besser die mittels derived_merge=on produzierte Abfrage arbeiten würde. Sie würde lediglich 124 statt 24200 Zeilen untersuchen! Das ist immerhin ein Faktor 200. Das hilft natürlich nicht, wenn das Ergebnis nicht stimmt. Aber es zeigt dennoch eindrucksvoll, was der Optimizer leisten kann. Von daher kann der Verzicht auf bestimmte Optimierungen nur als Krücke betrachtet werden, die im Fall eines Shops mit nicht allzu viel Aufrufen durchaus akzeptabel sein kann. Im Fall von deutlicher Last auf dem Shop ist aber auf jeden Fall eine Version des Datenbankservers zu empfehlen, mit der der Bug behoben ist.