Laufzeit-Wunder

[Robert]

Hallo zusammen,

ich habe einen Query, der ca. 10 Zeilen innerhalb von ca. 3 Sekunden ermittelt. Wenn ich darauf 2 Werte in jeder Zeile vergleiche, benötigt der Query 5 Stunden.

Hier der Query:

select *
from (
     select s.Zeitpunkt_GMT as Zeitpunk, s.Wert, v.Verbrauch, (select sum(Verbrauch) from haus.Strom_Verbrauch where Zeitpunkt_GMT <= Zeitpunk) as Stromverbrauch
      from haus.strom as s, haus.Strom_Verbrauch as v
      where s.OBIS = '1-0:1.8.0*255' and s.Zeitpunkt > now() - interval 1 hour and s.Zeitpunkt_GMT = v.Zeitpunkt_GMT
      order by s.Zeitpunkt_GMT desc
     ) as a
where Wert <> Stromverbrauch"

Das ist der "komplizierte" Query-Teil, der nur 3 Sekunden benötigt.

     select s.Zeitpunkt_GMT as Zeitpunk, s.Wert, v.Verbrauch, (select sum(Verbrauch) from haus.Strom_Verbrauch where Zeitpunkt_GMT <= Zeitpunk) as Stromverbrauch

      from haus.strom as s, haus.Strom_Verbrauch as v

      where s.OBIS = '1-0:1.8.0*255' and s.Zeitpunkt > now() - interval 1 hour and s.Zeitpunkt_GMT = v.Zeitpunkt_GMT

      order by s.Zeitpunkt_GMT desc

Ein Vergleich darauf von zwei Werten führt zu dieser unglaublichen Laufzeitveränderung.

Den Query starte ich auf einer MariaDB 10.5.15.

Hat jemand eine Erklärung für mich? Aktuell löse ich es über eine simple Aufteilung.

Danke!

VG
Robert

 

[akretschmer]

wild guess: wenn lahm materialisiert die DB zuerst deinen CROSS-JOIN, weil er die versteckte JOIN-Condition zu spät bzw. nicht erkennt. Hätte MySQL einen besseren Planner und ein aussagefähiges EXPLAIN wäre dies besser erkennbar bzw. vermeidbar. You get what you paid for.

 

[dabadepdu]

ich rate mal, es läuft auf einem Raspberry Pi oder so und es gibt einigermaßen viele Datensätze.
Alle "Denkarbeit" läuft mangels Hirn (RAM) im Bauch (Festplatte).
Aber man braucht nicht zu raten, mit Explain kann man sich anzeigen lassen, was geschieht.

Wenn man eine wenig begabte DB einsetzt, darf man aber auch keine (Laufzeit)Wunder erwarten.
Dann muss man selbst anhand des Ausführungsplans optimieren und Indizierung und Abfrage Aufbau anpassen.

Man muss also das tun, was der Query Optimizer eigentlich erledigen sollte.
Und ich rate auch mal wild: Wenn Du sehr viele Daten hast und eine leistungsschwache Anlage, kann es nicht besonders schnell werden. Aber wenn Du in der Abfrage die vielen Daten auf den Bestand aus der letzten Stunde einschränkst, sind es vielleicht erheblich weniger Daten. Nur die DB hat's nicht geschnallt.

Was oftmals auch schlecht optimiert wird, sind inline Queries innerhalb der Select Clause wie das hier:

(select sum(Verbrauch) from haus.Strom_Verbrauch where Zeitpunkt_GMT <= Zeitpunk)

Das wäre sozusagen die SQL Manifestation der berühmten PHP If Schleife.

 

[Thallius]

Hast du mal versucht das select * durch konkrere spaltennamen zu ersetzen?

 

[akretschmer]

Hast du mal versucht das select * durch konkrere spaltennamen zu ersetzen?

dürfte keinen Unterschied machen hier.

 

[Georg V.]

@Robert kannst Du uns die Anzahl der Datensätze sagen, die in den Tabellen existieren. Denn bei den 10 Ergebnissätzen ist eher die Lesezeit der Daten relevant als die Transportzeit. Gibt es noch mehr als die genannten Attribute.

Du verwendest den Rechner allein, auf den die Datenbank läuft? (Vermutlich lokale Installation auf einen Laptop, oder?) Oder anders gefragt, ist das Szenario reprodzierbar? Oder ist es doch ein Kleinstrechner?

Handelt es sich bei dem Fehler im Statement um ein Kopierfehler? (Statt

where Wert <> Stromverbrauch"

hätte ich eher

where Wert <>‘Stromverbrauch‘

erwartet.

Und falls dieses Fragen nicht weiterhelfen, dann wäre trotz es überflüssigen Produktbashings die Explain Ausgaben sinnvoll.

 

[akretschmer]

Handelt es sich bei dem Fehler im Statement um ein Kopierfehler? (Statt
hätte ich eher
erwartet.

Nope. Stromverbrauch wurde im Subselect als Aliasname verwendet, die Syntax ist korrekt.

 

[Georg V.]

Dann ist es halt ein Kopierfehler, das Gänsefüßchen gehört so oder so nicht zum Statement.

 

[Robert]

Danke für eure Antworten!
Ja, die Gänsefüßchen sind ein Kopierfehler. Die kommen noch aus dem Python-Skript. Sorry. Stromverbrauch ist ein Feld.

Die Datenbank läuft auf einem Raspberry 4B mit 8GB. Die Tabelle strom hat aktuell 1,3 Mio Datensätze und ist 72 MB groß. Strom_Verbrauch hat 170.000 Datensätze hat 8,5 MB. Bis vor 2 oder 3 Wochen lief dieser Query auf einer uralten mysql-Version auf einem Raspi der 1. Generation. Hier ist mir nicht aufgefallen, dass die Mail sehr später oder nicht kommen würde (eine Mail aus dem Python-Skript, welches diesen Query ausführt).

Das "select sum(Verbrauch)" kann ich schwer anders gestalten. Es soll in der Abfrage geprüft werden, ob die Summe der einzelnen Verbräuche (v.Verbrauch) je Messperiode mit dem Zählerstand (s.Wert) übereinstimmt.

Es ist auch reproduzierbar. Führe ich nur den inneren Query aus, braucht das System 2-4 Sekunden für die Antwort. Führe ich den gesamten Query aus ist er nach einer Stunde nicht fertig und ich breche ihn dann ab.

Und das erste Mal explain verwendet. Nur die Ausgabe sagt mir nicht wirklich viel. Hier muss ich sicher noch viel über Ausführungspläne usw. lernen. Das Ergebnis für den gesamten Query war:

+------+--------------------+-----------------+--------+---------------+---------+---------+----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-----------------+--------+---------------+---------+---------+----------------------------+--------+-------------+ | 1 | PRIMARY | v | ALL | PRIMARY | NULL | NULL | NULL | 168606 | | | 1 | PRIMARY | s | eq_ref | PRIMARY | PRIMARY | 27 | haus.v.Zeitpunkt_GMT,const | 1 | Using where | | 3 | DEPENDENT SUBQUERY | Strom_Verbrauch | ALL | PRIMARY | NULL | NULL | NULL | 168606 | Using where | +------+--------------------+-----------------+--------+---------------+---------+---------+----------------------------+--------+-------------+

Nur für den inneren Query gab es diese Ausgabe:
+------+--------------------+-----------------+--------+---------------+---------+---------+----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-----------------+--------+---------------+---------+---------+----------------------------+--------+-------------+ | 1 | PRIMARY | v | index | PRIMARY | PRIMARY | 27 | NULL | 168608 | | | 1 | PRIMARY | s | eq_ref | PRIMARY | PRIMARY | 27 | haus.v.Zeitpunkt_GMT,const | 1 | Using where | | 2 | DEPENDENT SUBQUERY | Strom_Verbrauch | ALL | PRIMARY | NULL | NULL | NULL | 168608 | Using where | +------+--------------------+-----------------+--------+---------------+---------+---------+----------------------------+--------+-------------+

Sehr ähnlich. Unter type ist mir in der ersten Zeile nur index<>ALL aufgefallen. Ohne Index-Unterstützung ist es natürlich viel schwieriger. Nur wie kann man auf den Ausführungsplan Einfluss nehmen?

VG Robert

 

[akretschmer]

Ohne Index-Unterstützung ist es natürlich viel schwieriger. Nur wie kann man auf den Ausführungsplan Einfluss nehmen?

siehe #1, umschreiben auf expliziete JOIN-Syntak *könnte* helfen. Das EXPLAIN von MySQL ist leider wie der Rest von MySQL: wertlos.

 

[Georg V.]

Die Ursache für das Problem ist im Unterschied der ersten Zeile von dem Explain zu erkennen, es wird nicht mehr der Index verwendet. Schnellste Lösung dürfte sein, dass Du das Ergebnis in eine Zwischen-Tabelle schreibst und dann diese Tabelle verarbeitest (nicht anstrebenswert). Hast Du nach dem Wechsel der MySQL Installation auch mal eine analyze table durchgeführt (Check and analyze the STATISTICS in the MySQL database Man kann in MySQL und MariaDB übrigens die Verwendung eines Index anfordern (zusätzliches USE INDEX(..) im SELECT statement (siehe USE INDEX). Ich vermute aber mal, dass die potentiell fehlende Tabellenstatistik die Ursache ist.

 

[dabadepdu]

Das "select sum(Verbrauch)" kann ich schwer anders gestalten

Man kann jedes Inline Statement in einen Join oder andere Konstruktionen umwandeln.

Das Order by hat spätestens im 2. Query keinen Sinn. Keine Ahnung, ob der Optimzer das "bemerkt".
Die Feldauswahl (*) dürfte relativ egal sein, außer es sind dutzende oder hunderte Spalten, die eigentlich nicht gebraucht werden. Verglichen mit der Spalte aus dem Inline Select jedenfalls eher bedeutungslos. (Ach was schreibe ich da, das Resultat von Select * kann man ja im vorigen Statement ablesen)

Schnellste Lösung dürfte sein, dass Du das Ergebnis in eine Zwischen-Tabelle schreibst und dann diese Tabelle verarbeitest (nicht anstrebenswert)

Das ist gleichzeitig gruselig und richtig, da "(nicht erstrebenswert)" ergänzt wurde.
Ich würde wie @Georg V. geraten hat, die Indexnutzung, Statistiken klären / forcieren und als "schnellste Lösung" lieber das Statement umstellen, indem es aufgelöst wird. Ob es hilft, wird man sehen, kommt drauf an, was der Optimizer draus macht, kostet nur ein paar Minuten.

@bashing: Ich denke, dezente Hinweise zu guten Lösungen/Alternativen, die dauerhaft Spielraum und Leistung durch Beseitigung komplexer Workarounds bringen, sind vertretbar. Schlimmer noch: wenn ich mir vorstelle, ich bekäme in einem Forum die fürchterlichsten Sachen erklärt, freundlich und so wie gefragt und würde am Sterbebett erfahren, dass es alles auch einfacher gewesen wäre, wenn ich nur andere Sachen gefragt hätte, dann kommt mir das schon ziemlich skurril vor.
Dabei darf es natürlich gerne sachlich zugehen. Wenn man gefühlt zum 1000sten Mal erklärt, was da alles schief geht und dass es bessere Alternativen gibt und ein Haufen Workarounds unnötig sind, dann kann ich aber auch nicht garantieren, nicht gelegentlich mal etwas zu menscheln.

 

[Robert]

War auch schon fast auf dem Weg, die Ergebnisse in einer Zwischentabelle abzulegen. Ist immerhin eine Lösung, wenn man keine bessere Lösung findet.

Die ANALYZE TABLE fand, dass alles o.k. ist. Ein UPDATE HISTOGRAM scheiterte aber an der Syntax, obwohl ich die Befehle aus diversen Quellen kopiert hatte und nur die Tabelle/Felder ersetzt hatte.

Deshalb habe ich mich an die Umstellung des Qeury gemacht und dabei fast aufgegeben, da mein Synapsen gestern wohl etwas eigenwillig waren und ich schon nachfragen wollte. Heute ging es besser und habe nun folgendes SQL:

select s.Zeitpunkt, s.Wert, v1.Zeitpunkt_GMT, sum(v2.Verbrauch) from haus.strom as s, haus.Strom_Verbrauch as v1, haus.Strom_Verbrauch as v2 where v2.Zeitpunkt_GMT <= v1.Zeitpunkt_GMT and v1.Zeitpunkt_GMT = s.Zeitpunkt_GMT and s.Zeitpunkt >= now() - interval 1 hour and s.OBIS = '1-0:1.8.0*255' group by v1.Zeitpunkt_GMT having sum(v2.Verbrauch) <> s.Wert

Dieser Query benötigt jetzt 4 Sekunden

Die Explain-Ausgabe sieht auch besser aus, da er jetzt wieder den Index verwendet.
+------+-------------+-------+--------+---------------+---------+---------+-----------------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-----------------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | v1 | index | PRIMARY,Prim | PRIMARY | 27 | NULL | 169250 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 27 | haus.v1.Zeitpunkt_GMT,const | 1 | Using where | | 1 | SIMPLE | v2 | ALL | PRIMARY,Prim | NULL | NULL | NULL | 169250 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+--------+---------------+---------+---------+-----------------------------+--------+-------------------------------------------------+

Die Umstellung des Query hat nun zum Erfolg geführt und ich habe viel dabei gelernt.
Danke euch allen!

 

[akretschmer]

Stimmt denn auch das Resultat? Die Abfrage ist syntaktisch falsch, alle aktuellen SQL-Datenbanken würden einen Fehler liefern, und sehr aktuelle Versionen von MySQL auch.

 

[Robert]

Wo meinst du sollte die Abfrage syntaktisch falsch sein? Das Ergebnis sieht jedenfalls ganz gut aus. Wenn ich das <> durch ein = ersetze bekomme ich genau die Datensätze der letzten Stunde mit dem summierten Verbrauch und den Zählerständen. Mit <> wir mit diesen Zahlen kein Datensatz zurückgeliefert.
+---------------------+-----------+---------------------+-------------------+ | Zeitpunkt | Wert | Zeitpunkt_GMT | sum(v2.Verbrauch) | +---------------------+-----------+---------------------+-------------------+ | 2022-09-16 10:47:41 | 6288368.3 | 2022-09-16 08:47:41 | 6288368.3 | | 2022-09-16 10:53:41 | 6288381.5 | 2022-09-16 08:53:41 | 6288381.5 | | 2022-09-16 10:59:40 | 6288466.2 | 2022-09-16 08:59:40 | 6288466.2 | | 2022-09-16 11:05:40 | 6288648.4 | 2022-09-16 09:05:40 | 6288648.4 | | 2022-09-16 11:11:47 | 6288815.0 | 2022-09-16 09:11:47 | 6288815.0 | | 2022-09-16 11:17:47 | 6288944.9 | 2022-09-16 09:17:47 | 6288944.9 | | 2022-09-16 11:23:52 | 6289075.7 | 2022-09-16 09:23:52 | 6289075.7 | | 2022-09-16 11:30:05 | 6289204.6 | 2022-09-16 09:30:05 | 6289204.6 | | 2022-09-16 11:36:10 | 6289333.0 | 2022-09-16 09:36:10 | 6289333.0 | +---------------------+-----------+---------------------+-------------------+