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 vom Partner W&Co um 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 die Optimierung 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 den Hosting-Partner qwertiko und gaben ihnen den zusätzlichen Hinweis, dass der Fehler nicht auftritt, wenn wir alle Optimizer-Switches auf 'off' stellten mittels


set global optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off';

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

Sehr bald lieferte qwertiko uns jedoch eine wesentlich weniger 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 einfache Möglichkeit, in das Wirken des Optimizers Einblick zu nehmen und diese Möglichkeit wollen wir in diesem Artikel besprechen.

Der Job 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:


SELECT 
    *
FROM
    oxv_oxdiscount_3_de
WHERE
    (oxv_oxdiscount_3_de.oxactive = 1
        OR (oxv_oxdiscount_3_de.oxactivefrom < '2021-07-28 17:14:00'
        AND oxv_oxdiscount_3_de.oxactiveto > '2021-07-28 17:14:00'))
        AND (SELECT 
            IF(EXISTS( SELECT 
                            1
                        FROM
                            oxobject2discount,
                            oxv_oxgroups_de
                        WHERE
                            oxv_oxgroups_de.oxid = oxobject2discount.oxobjectid
                                AND oxobject2discount.OXDISCOUNTID = oxv_oxdiscount_3_de.OXID
                                AND oxobject2discount.oxtype = 'oxgroups'
                        LIMIT 1),
                    EXISTS( SELECT 
                            oxobject2discount.oxid
                        FROM
                            oxobject2discount
                        WHERE
                            oxobject2discount.OXDISCOUNTID = oxv_oxdiscount_3_de.OXID
                                AND oxobject2discount.oxtype = 'oxgroups'
                                AND oxobject2discount.OXOBJECTID IN ('oxidnewcustomer' , 'b846c39ca20caf6be1e84b5db78df31a')),
                    1)
        )
ORDER BY oxv_oxdiscount_3_de.oxsort;

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:


SELECT 
    *
FROM
    `oxid`.`oxdiscount`
        JOIN
    `oxid`.`oxdiscount2shop` `t2s`
WHERE
    ((`oxid`.`oxdiscount`.`OXMAPID` = `oxid`.`t2s`.`OXMAPOBJECTID`)
        AND (`oxid`.`t2s`.`OXSHOPID` = 3)
        AND ((`oxid`.`oxdiscount`.`OXACTIVE` = 1)
        OR ((`oxid`.`oxdiscount`.`OXACTIVEFROM` < '2021-07-28 17:14:00')
        AND (`oxid`.`oxdiscount`.`OXACTIVETO` > '2021-07-28 17:14:00')))
        AND IF(EXISTS( SELECT 
                1
            FROM
                `oxid`.`oxobject2discount`
                    JOIN
                `oxid`.`oxgroups`
            WHERE
                ((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
                    AND (`oxid`.`oxgroups`.`OXID` = `oxid`.`oxobject2discount`.`OXOBJECTID`)
                    AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxid`.`oxdiscount`.`OXID`))),
        EXISTS( SELECT 
                `oxid`.`oxobject2discount`.`OXID`
            FROM
                `oxid`.`oxobject2discount`
            WHERE
                ((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
                    AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxid`.`oxdiscount`.`OXID`)
                    AND (`oxid`.`oxobject2discount`.`OXOBJECTID` IN ('oxidnewcustomer' , 'b846c39ca20caf6be1e84b5db78df31a')))),
        1))
ORDER BY `oxid`.`oxdiscount`.`OXSORT`

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):


SELECT 
    *
FROM
    `oxid`.`oxv_oxdiscount_3_de`
WHERE
    (((`oxv_oxdiscount_3_de`.`OXACTIVE` = 1)
        OR ((`oxv_oxdiscount_3_de`.`OXACTIVEFROM` < '2021-07-28 17:14:00')
        AND (`oxv_oxdiscount_3_de`.`OXACTIVETO` > '2021-07-28 17:14:00')))
        AND IF(EXISTS( SELECT 
                1
            FROM
                `oxid`.`oxobject2discount`
                    JOIN
                `oxid`.`oxv_oxgroups_de`
            WHERE
                ((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
                    AND (`oxid`.`oxobject2discount`.`OXOBJECTID` = `oxv_oxgroups_de`.`OXID`)
                    AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxv_oxdiscount_3_de`.`OXID`))),
        EXISTS( SELECT 
                `oxid`.`oxobject2discount`.`OXID`
            FROM
                `oxid`.`oxobject2discount`
            WHERE
                ((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
                    AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxv_oxdiscount_3_de`.`OXID`)
                    AND (`oxid`.`oxobject2discount`.`OXOBJECTID` IN ('oxidnewcustomer' , 'b846c39ca20caf6be1e84b5db78df31a')))),
        1))
ORDER BY `oxv_oxdiscount_3_de`.`OXSORT`

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.


Schreibe einen Kommentar

* Pflichtfelder


Kommentare

09.09.2021 | Yannic Groß

An diesem Problem bzw. an der Lösung sieht man schön, dass die Zeit von MySQL 5.7 vorbei ist. Den normalen Support hat Oracle schon letztes Jahr eingestellt und EOL ist irgendwann im Oktober 2023. MySQL wird leider etwas stiefmütterlich von Oracle behandelt und man sollte die Chance nutzen auf MariaDB umzusteigen - wenn es die Applikation erlaubt. Zum Glück unterstützt Oxid MariaDB und ist damit für die Zukunft gerüstet. Yannic Groß qwertiko GmbH

Tel. +49 761 36889 0
Mail. [email protected]