mikrocontroller.net

Forum: PC-Programmierung Datenbank für Messreihe und Darstellung in Grafana


Announcement: there is an English version of this forum on EmbDev.net. Posts you create there will be displayed on Mikrocontroller.net and EmbDev.net.
Autor: Tobi (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Hallo zusammen,

ich versuche derzeit sekündlich in einer MariaDB abgelegte Messwerte mit 
Hilfe von Grafana darzustellen. Die Performance gestaltet sicher 
allerdings trotz performanter Hardware inkl. SSD als Speichermedium eher 
problematisch. Die Daten der Sensoren liegen in einer Tabelle mit 
InnoDB-Engine und dem folgenden Schema:

id, int, 10, primary, auto incr.
zeit, datetime, index
unix_timestamp, int, 11, index
temp, float
pressure, float
humidity, float
windspeed, float
rain, float

Derzeit enthält die Tabelle etwa 60 Millionen Datensätze. Eine Änderung 
des Layouts ist nicht vorgesehen. Für die Spalte mit dem Zeitstempel, 
über den die Daten später selektiert werden, wurde bereits einen Index 
angelegt, um die Antwortzeit zu verkürzen.
Die Messwerte werden für die Darstellung in Grafana zu 
Durchschnittswerten zusammengefasst. Je nach Größe des zur Ansicht 
gewählten Zeitraums, liegt die Antwortzeit bis alle Darstellungen 
aktualisiert sind allerdings über einer Minute. Der Test der in Grafana 
generierten SQL Abfragen mit vorangestelltem "explain" zeigt, dass der 
Index bei der Abfrage verwendet wird. Daher stellt sich mir die Frage, 
an welcher Stelle noch Optimierungspotenzial liegt. Eine Änderung des 
Datentyps von Decimal auf Float brachte nur eine sehr gerine 
Verbesserung. Auch die Nutzung des unix_timestamps brachte wenig 
Performance. Trotz der nicht geplanten Änderung der zu speichernden 
Daten: Wäre ein Schema der Form Zeitpunkt, Sensor, Messwert sinnvoller 
und wie würde sich das auf die Performance auswirken? In der Form würden 
ja entsprechend mehrere Zeilen mit gleichem Zeitstempel eingefügt, dafür 
sind allerdings weniger Spalten notwendig.
In der Konfigurationsdatei habe ich den Wert für innodb_buffer_pool_size 
bereits erhöht.
Gibt es aus eurer Sicht noch Ansatzpunkte, welche auf die Performance 
einen maßgeblichen Einfluss haben? Nach Möglichkeit sollen die Daten in 
der MariaDB verbleiben und der Umstieg auf eine andere Datenbank 
vermieden werden.

Vielen Dank eure Zeit!

Autor: Spongebob (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Tobi schrieb:
> Gibt es aus eurer Sicht noch Ansatzpunkte, welche auf die Performance
> einen maßgeblichen Einfluss haben? Nach Möglichkeit sollen die Daten in
> der MariaDB verbleiben und der Umstieg auf eine andere Datenbank
> vermieden werden.


Das ist der falsche Ansatz. Dafür ist ein RDBMS denkbar ungeeignet und 
Du wirst keine wesentliche Beschleunigung bekommen.
Spricht etwas dagegen, die Daten parallel in InfluxDB oder Prometheus zu 
speichern?

Autor: Johannes S. (jojos)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
InfluxDB habe ich auch eine Zeit lang benutzt, würde ich auch empfehlen. 
Das spielt auch sehr gut mit Grafana zusammen. Es ist eben auf 
zeitbasierte Messreihen optimiert.

Autor: Thomas W. (thomas_v2)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Du könntest Hintergrund automatisch die verdichteten Werte bilden und in 
einer separaten Tabelle abspeichern, z.B. Minuten- Stunden- und 
Tageswerte (Mittelwert, zusätzlich Min/Max-Werte). Und in den Diagrammen 
dann je nach gewählter Zeitdauer die sinnvollen Intervallwerte 
heranziehen.

Autor: Tobi (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Vielen Dank für euren Input!
Die Daten in separaten Tabellen in aggregierter Form vorzuhalten wäre 
natürlich noch eine Möglichkeit. Tendenziell würde ich mangels Erfahrung 
mit anderen Datenbanken und der bestehenden Anbindung gerne bei der 
MariaDB bleiben. Würdet ihr für diesen Fall die Tabelle so lassen, wie 
sie derzeit ist oder das Schema zu einer Art Schlüssel-Wert-Tabelle 
umbauen.
Gibt es hier sonst tatsächlich keine Möglichkeit mehr alleine mit den 
Rohdaten die Performance zu verbessern? Gibt es ggf. in der 
Konfiguration noch weitere relevante Stellschrauben?
Werde mir die InfluxDB dennoch mal anschauen..

Autor: Irgend W. (Firma: egal) (irgendwer)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Tobi schrieb:
> Der Test der in Grafana
> generierten SQL Abfragen mit vorangestelltem "explain" zeigt, dass der
> Index bei der Abfrage verwendet wird.

Hast du den sql auch mal abgesendet und geschaut wie lange es dauert bis 
alle Daten übertragen wurden. Nicht das die Abfrage recht schnell geht, 
nur dein Programm zum Auswerten ewig benötigt um die Daten 
aufzubereiten. Das würde ich an deiner Stelle erstmal eingrenzen um 
genau zu wissen auf welcher Seite das Problem sitzt.
Bei einem explain muss man manchmal auch sehr genau hinschauen. Der sagt 
zwar using index, aber durchsucht dennoch z.B. 1 Mio. von 60 Mio. 
Einträgen.

Was ich auch schon hatte ist das die die Reihenfolge der where-Clauseln 
die DB so durcheinander gebracht haben das sie die Abfrage nicht emhr 
richtig optimieren konnte und damit ewig benötigt hatte. Die Reihenfolge 
verändert und gewundert wie schnell die auf einmal waren. Wenn mehrere 
Index vorhanden sind wird auch gerne mal der erst beste genommen bei dem 
garnicht alle where mit abgedeckt sind obwohl es einen gibt der genau 
passen würde. Schonmal zu schauen in wie weit die Reihenfolge der where 
mit dem index überein passt schadet jedenfalls nicht. usw.

Ansonsten ist Ferndiagnose bei sowas immer schwierig.
Viele der vorhanden Stellschrauben beziehen sich eher auf immer 
wiederkehrende Abfragen und wie viele, wie lange und wie groß 
zwischengespeichert wird und ähnliches.

Autor: Jan H. (j_hansen)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Wie lange dauert es denn, wenn du selbst ein SQL-Statement mit 
entsprechender Selektion und Aggregation absetzt?

Geht das flott, dann kannst du ev. einen View anlegen und diesen nutzen.

Autor: Tobi (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Die Abfrage für die Darstellung eines einzelnen Messwerts inkl. Bildung 
der Durchschnittswerte dauert für einen längeren Zeitraum schnell 
zwischen 15 und 30 Sekunden.

SELECT
  unix_timestamp DIV 3600 * 3600 AS "time",
  avg(temp) AS "temp",
FROM auszug
WHERE
  unix_timestamp  >= 1552575959 AND unix_timestamp <= 1568469959
GROUP BY 1
ORDER BY unix_timestamp DIV 3600 * 3600;

Diese beispielhafte Abfrage wird in Grafana verwendet, um die Daten für 
die Darstellung in Stunden-Werte zusammenzufassen und benötigt auch bei 
direkter Abfrage der Datenbank die genannte Laufzeit.

Autor: pumuggl (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
> benötigt auch bei direkter Abfrage der Datenbank die genannte Laufzeit.

> Das ist der falsche Ansatz.

Das geht schon beim Tabellenlayout los.

> Für die Spalte mit dem Zeitstempel,
> über den die Daten später selektiert werden

Dann nimmt man das als primary key und keine generierte Pseudo-ID.

Autor: Gerd E. (robberknight)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Tobi schrieb:
> Diese beispielhafte Abfrage wird in Grafana verwendet, um die Daten für
> die Darstellung in Stunden-Werte zusammenzufassen und benötigt auch bei
> direkter Abfrage der Datenbank die genannte Laufzeit.

Dann nimm die doch mal als Basis und versuche sie zu vereinfachen. Wird 
das merklich schneller wenn Du z.B. das Order by oder das group by 
weglässt und den Rest ggf. anpasst?

Ich vermute mal eher nein und der Knackpunkt dürfte einfach das 
"unix_timestamp  >= 1552575959 AND unix_timestamp <= 1568469959" bei der 
großen Menge an Datensätzen in der Tabelle sein.

Du könntest ja nochmal versuchen nur einen einzigen, konkreten 
Timestamp-Wert zu suchen. Geht das schneller? Ich vermute auch nicht 
wesentlich.

Die eigentliche Lösung wäre dann eine Datenbank, die für diese 
Zeitreihen-Geschichten optimiert ist, wie z.B. das Influxdb.

Wenn es unbedingt MariaDB bleiben soll, dann probier mal ob Du nicht mit 
Partitionierung anhand der Timestamp-Werte etwas rausholen kannst.

Antwort schreiben

Die Angabe einer E-Mail-Adresse ist freiwillig. Wenn Sie automatisch per E-Mail über Antworten auf Ihren Beitrag informiert werden möchten, melden Sie sich bitte an.

Wichtige Regeln - erst lesen, dann posten!

  • Groß- und Kleinschreibung verwenden
  • Längeren Sourcecode nicht im Text einfügen, sondern als Dateianhang

Formatierung (mehr Informationen...)

  • [c]C-Code[/c]
  • [avrasm]AVR-Assembler-Code[/avrasm]
  • [code]Code in anderen Sprachen, ASCII-Zeichnungen[/code]
  • [math]Formel in LaTeX-Syntax[/math]
  • [[Titel]] - Link zu Artikel
  • Verweis auf anderen Beitrag einfügen: Rechtsklick auf Beitragstitel,
    "Adresse kopieren", und in den Text einfügen




Bild automatisch verkleinern, falls nötig
Bitte das JPG-Format nur für Fotos und Scans verwenden!
Zeichnungen und Screenshots im PNG- oder
GIF-Format hochladen. Siehe Bildformate.

Mit dem Abschicken bestätigst du, die Nutzungsbedingungen anzuerkennen.