Langsame MariaDB-SQL-Abfragen im Statistikstatus

Übersicht

Eine der Aufgaben einer Datenbank beim Ausführen einer Abfrage besteht darin, den besten Weg zum Ausführen der Abfrage selbst festzulegen. Während MariaDB (und MySQL) in den meisten Fällen hervorragende Arbeit bei der Optimierung leistet, kann es bei einigen komplexen Abfragen mit einer hohen Anzahl von Joins standardmäßig zu viel Zeit damit verbringen, die Abfrage zu optimieren, anstatt sie tatsächlich auszuführen.

Beispielsweise haben wir bei einer WordPress-basierten Abfrage mit 16 JOIN-Anweisungen eine Ausführungszeit von fast 4 Minuten gesehen mit nur 5000 Zeilen. Das Ausführen eines EXPLAIN für die Abfrage (die die Abfrage nicht wirklich ausführt) führt zu ungefähr demselben Ergebnis, was bedeutet, dass die Verzögerung die Überoptimierung ist nicht die Daten oder die Abfrage selbst.

Dies liegt an der optimizer_search_depth Die Standardeinstellung ist 62. Durch die Reduzierung dieser Zahl auf eine Tiefe von 5 wurde die EXPLAIN-Zeit auf 0,052 Sekunden und die Abfrage selbst auf unter 6 Sekunden reduziert.

Bei dieser speziellen Abfrage bedeutete dies, dass das Ergebnis fast 3900 % langsamer war mit Servervorgaben!

Anleitung

1. Führen Sie die Abfrage mit Ihrer MariaDB-Shell aus und sehen Sie sich dann an, was MariaDB während der Abfrage ist:
   show full processlist;
2. Wenn Sie lange Zeit eine Abfrage mit dem Status „Statistik“ sehen, könnte diese Optimierung anwendbar sein. Zum Beispiel:
   
3. Bestätigen Sie Ihre aktuelle optimizer_search_depth ist eingestellt auf:
   show variables like "optimizer_search_depth";
   Beispielsweise können Sie daher Folgendes sehen:
   +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
4. Dies weist darauf hin, dass auf Ihrem Server immer noch Systemstandardwerte ausgeführt werden.
5. Testen Sie die Einstellung des Servers zur automatischen Optimierung der Tiefe, indem Sie optimizer_search_depth auf null setzen:
   SET SESSION optimizer_search_depth = 0;
6. Führen Sie die langsame SQL-Abfrage erneut aus, um zu bestätigen, dass das Problem behoben wurde.
7. Wenn die aktualisierte Einstellung funktioniert hat, legen Sie sie dauerhaft fest, indem Sie die /etc/my.conf bearbeiten und explizit in [msqld] einstellen Sektion:
   optimizer_search_depth=0
8. Starten Sie MariaDB neu, um sich zu bewerben:
   systemctl restart mariadb

Tipp

Wenn Sie wissen, dass Ihre Datenstruktur sehr kontrolliert ist, können Sie mit der expliziten Einstellung von optimizer_search_depth experimentieren auf einen bestimmten Wert (z. B. 5), um weiter zu reduzieren. Eine explizite Einstellung ist jedoch nicht unbedingt in jedem Fall schneller, also stellen Sie sicher, dass Sie gründlich testen.

Wenn Sie ein Conetix-Kunde mit einem Virtual Private Server sind, bei dem dieses Problem auftritt, können wir Ihnen helfen, die Ursache zu bestätigen und es ohne zusätzliche Kosten für Sie zu beheben. Bitte kontaktieren Sie unser Support-Team für weitere Unterstützung.

Weiterführende Literatur

https://www.percona.com/blog/2012/04/20/joining-many-tables-in-mysql-optimizer_search_depth/

https://mariadb.com/resources/blog/setting-optimizer-search-depth-in-mysql/