TimescaleDB Optimieren

An einer Maschine fallen ständig Daten an. Die meisten sind zeitpunktbezogene Werte von Sensoren. Diese lassen sich prinzipiell natürlich in relationalen DBMS speichern, allerdings sind gängige DBMS nicht optimiert um mit diesem speziellen Workload umzugehen. Die Performance von Insert- und Select- Operationen insbesondere auf großen Zeitreihentabellen ist stark begrenzt. Siehe dazu TimescaleDB-Blog. Folgend sind einige der Herausforderungen aufgeführt, die ich bei der Implementierung eines Langzeitarchiv auf Basis dieser Technologie bewältigt habe.

Was sind Zeitreihendaten

Wenn man sich mehrere Sensoren vorstellt, die im Sekundentakt ihre Werte an ein übergeordnetes System melden, dann erhält man Tripletts aus (Sensor-ID, Zeitpunkt, Wert). Diese kann man auf verschiedene Weise als Relation darstellen. Es gibt jedoch einige Eigenschaften, die, egal wie man die Daten darstellt und egal welchen Use-Case man betrachtet, erhalten bleiben:

• Die Daten werden nur erzeugt, aber nicht verändert
• Die Daten liegen immer in der Vergangenheit
• Zu einem Zeitpunkt kann auf einem Sensor nur ein einziger Wert gemeldet worden sein
• Daten werden nicht genau zu dem Zeitpunkt der Messung auch in die Datenbank geschrieben, sondern eventuell viel später
• Daten, die in die Datenbank eingefügt werden, beziehen sich MEISTENS auf den Zeitbereich der nahen Vergangenheit
• Sensoren können in einem Zeitbereich ausfallen und so Datenlücken hinterlassen
• Das aufzeichnende System kann in einem Zeitbereich ausfallen. Das sollte aber nach Möglichkeit keine Datenlücken erzeugen, wenn man die Daten bspw. am Sensor puffert

Darstellung als Relation

Es gibt verschiedene Modelle, um solche Daten zu speichern (Siehe TimescaleDB-Blog Data Model). In diesem Beispiel zeige ich das Narrow-Table-Modell, weil es erlaubt, dynamisch Sensoren zum System hinzuzufügen und zu entfernen. Die Relation sieht dann so aus:

Man sieht hier deutlich, dass die Daten nicht in der Reihenfolge des Zeitpunkts in der Datenbank gespeichert werden. Ferner sind Datenpunkte bezogen auf den erzeugenden Sensor zeitlich durchmischt. Aber: Innerhalb der Daten eines Sensors bleibt die zeitliche Ordnung der Datensätze erhalten.

Wie werden die Daten gespeichert

Um zu verstehen, welche Effekte bei Insert- und Query-Performance der Datenbank auftreten können, muss man sich ansehen, wie die Daten auf der Festplatte durch TimescaleDB abgelegt werden: Die Tabelle der Zeitreihe wird als sogenannte Hypertable gespeichert. Der Unterschied zu normalen Tabellen ist, dass eine Hypertable las mehrere sogenannte Chunks gespeichert wird. Diese sind effektiv Tabellen im Schema "_timescaledb_internal", die dem gleichen Schema der eigentlichen Tabelle entsprechen, nur mit einem zusätzlichen Constraint: die Chunks sind nach Zeitbereichen unterteilt. Beispiel: bei einem Chunk-Time-Intervall von 10 Minuten würden obige Beispieldaten drei Chunk-Tabellen erzeugen. Eine von 01.01.1970 07:00:00 bis 07:09:59.999, eine von 07:10:00 bis 07:19:59.999 und eine ab 07:20. Diese Chunks sind wiederum normale PostgreSQL-Tabellen, die durch TimescaleDB als eine virutelle Hypertable der Anwendung präsentiert werden. Dabei wird der volle Funktionsumfang von SQL auf der Hypertable unterstützt und intern an die Chunks durchgereicht. Effektiv wird so also eine horizontale Partitionierung der Tabelle erreicht, nur dass durch TimescaleDB die Abfragen über diese partitionierte Tabelle nochmals besser optimiert werden können, insbesondere bei großen Datenmengen.

Vorteile dieser Zeitlichen Partitionierung

Bei einer Abfrage von Daten über einen begrenzten Zeitbereich muss so nicht mehr die gesamte Tabelle durchsucht werden, sondern nur die Chunks, die Daten zu diesem Zeitbereich enthalten. Außerdem kann das Caching der Daten deutlich sinnvoller erfolgen. Normalerweise werden Queries nämlich die nahe Vergangenheit bis Gegenwart betreffen, sodass die relativ aktuellen Chunks durch TimescaleDB bevorzugt im Hauptspeicher gecacht werden.

Wie werden große Datenmengen reduziert

Um auch große Zeitbereiche (wie Monate oder Jahre) an Daten schließlich auslesen zu können, müssen die Daten verdichtet werden. Um nämlich beispielsweise ein Sensorwerte-Verlaufsdiagramm anzeigen zu können, müssen nunmal die Daten des betrachteten Zeitbereich geladen werden. Wenn nun aber im Sekundentakt Werte von Sensoren aufgezeichnet werden, dann laufen über einen Betrachtungszeitraum von einem Jahr zu viele Datensätze auf, als dass diese in einem Diagramm effektiv dargestellt werden können. Daher können durch sogenannte Echtzeit-Aggregate (implementiert als Materialisierte Sichten auf die Hypertable) die Daten über kleinere Zeitbereiche verdichtet werden. Beispielsweise lassen sich Sensor-Min/Max/Durchschnitts-Werte über jeweils Minuten, Stunden, Tage usw. bilden. Lädt man dann die Daten für ein Jahresdiagramm, so kann die Anwendung auf die verdichteten Tagesdaten zugreifen und muss so nur noch 365 Datensätze laden, statt 31,5 Millionen (356x24x60x60). TimescaleDB berechnet diese Echtzeit-Aggregate im Hintergrund bereits vor, sodass dann bei Abfrage der Aggregate die Ergebnisdaten bereits vorliegen.

Anforderungen an die Datenbasis

Um das Narrow-Table-Modell erfolgreich einzusetzen, gibt es diverse Anforderungen an die zu speichernden Daten, die erfüllt sein müssen:

• Es werden dynamisch neue Sensoren hinzugefügt. Wenn neue Sensoren hinzugefügt werden, müsste man sonst z.B. beim Wide-Table-Modell das Tabellenschema ändern
• Die Sensor-Zeitstempel sind unabhängig voneinander: Wenn ein Sensor einen Wert meldet, dann müssen nicht auch zwingend alle anderen Sensoren Werte melden
• Alle Sensoren zeichnen in einem ähnlichen Takt Daten auf: Nicht einer im Sekundentakt und der nächste im Tagestakt. Das würde die Datendichte in Bezug auf die Sensor-ID heterogenisieren, was zu schlechterer Performance führt

Der Anwendungsfall

Es sei eine Anwendung gesucht, die beliebige Sensordaten in einem Langzeitarchiv auf Basis von TimescaleDB abzuleg und diese dann in Zeitverlaufsdiagrammen visualisieren oder Maschinenkennzahlen daraus zu berechnen kann.

1. Herausforderung: Beginn des Diagramms

Um ein Diagramm von Zeitreihendaten anzuzeigen, müssen die Daten von mehreren Sensoren (die als einzelne Linien im Diagramm dargestellt werden) über einen definierten Zeitbereich (Von/Bis-Zeitstempel = linker/rechter Rand des Diagramms) abgefragt werden. Dabei wird es aber nur im Ausnahmefall vorkommen, dass der erste aufgezeichnete Datensatz eines Sensors innerhalb dieses dargestellten Zeitbereichs auch wirklich ganz zu Beginn des Zeitbereichs aufgezeichnet wurde. Beispielsweise wenn ein Diagramm über obige Daten aufgerufen wird von 07:05 bis 07:25, so würde der erste Datensatz von Sensor 1 (07:15, 3.14) erst in der Mitte des Diagramms auftauchen. Bis zum zweiten Datensatz (07:20, 2.71) würde nun eine Linie gezeichnet werden und von diesem aus bis zur rechten Seite des Diagramms weiter. Je nachdem, ob der Verlauf des Sensorwertes aufgrund der Natur der Daten interpoliert werden kann oder nicht, kann diese Diagrammlinie entweder eine Treppenstufenkurve oder eine interpolierte Kurve sein. Jedenfalls kann keine Linie vom linken Rand des Diagramms bis zum ersten Datensatz gezeichnet werden, weil es aufgrund der Datensätze im Diagramm unbekannt ist, wo auf der linken Seite diese Linie beginnen sollte. Dazu benötigt man den letzten Datensatz dieses Sensor gerade ganz kurz vor dem Beginn des Diagramms. In diesem Beispiel also (1, 07:00, 42). Um diesen Datensatz zu bekommen, muss man die Datenbank nach diesem fragen. Diese geht nun daher und durchsucht die Daten beginnend mit dem Diagrammbeginn 07:05 in Richtung Vergangenheit. Dabei findet die Datenbank recht schnell den Datensatz und dieser kann die linke Diagrammseite vervollständigen. Anders sieht das aus für Sensor 2. Hier existiert kein Datensatz vor dem Beginn des Diagramms. Frage ich nun die Datenbank nach diesem Wert, so würde diese auch von 07:05 an in die Vergangenheit lossuchen, aber keinen Wert in naher Vergangenheit finden. Also sucht sie immer weiter bis zum ersten Datensatz, der jemals in der Tabelle vorhanden war. Das ist problematisch, denn wenn nun ein neuer Sensor (z.B. Sensor 2) hinzugefügt wird, aber schon 10 Jahre Daten vorher aufgezeichnet wurden, so würde die Datenbank einen riesigen Datenbestand durchsuchen müssen, nur um dann doch kein Ergebnis zu bekommen. Eine solche Abfrage würde natürlich extrem lange dauern und das Diagramm würde also eine inakzeptable Zeit brauchen, um vollständig dargestellt zu werden. Daher habe ich für jeden Sensor eine Art "Sendeintervall" definiert. Man kann also davon ausgehen, dass ein Sensor Daten immer mindestens in einem kleineren Intervall als diesem liefert. Wenn also kein Datensatz für einen Zeitbereich größer als Sendeintervall existiert, so kann man davon ausgehen, dass der Sensor für diesen Zeitbereich ausgefallen war (defekt oder vom Netzwerk getrennt o.Ä.). Mit dieser Annahme kann die Datenbank bei der Suche in die Vergangenheit schneller aufgeben und das Diagramm hat dann an dieser Stelle eben eine Lücke (was ja auch korrekt ist, denn es sollte keine Linie in einem Zeitbereich gezeichnet werden, für den keine bestätigten Daten vorliegen). Bei einem maximalen Datenalter von 30 Minuten für Sensor 2 würde die Datenbank also nur den Zeitbereich 07:05 bis 06:35 nach einem möglichen ersten Datensatz für diese Linie im Diagramm durchsuchen und dafür nur eine messbare, definierte Worst-Case-Zeit brauchen. Somit kann ich eine konstante Performance und System-Antwortzeit erreichen, um die qualitativen Requirements zu erfüllen.

2. Herausforderung: Live-Werte vs. historische Werte

Wenn das aufzeichnende System ausfällt (geplant oder ungeplant), so wäre es schlecht, für den Zeitraum der Downtime die Datenaufzeichnung von allen Sensoren zu verlieren. Daher sind Sensor-nahe Datenpuffer nötig, die sich auch nachträglich noch mit der Hauptanwendung/TimescaleDB synchronisieren können. Die Daten aus diesen Puffern bezeichne ich im weiteren als "historisch". Die Live-Werte hingegen können direkt (ohne Umweg über den Puffer) an das aufzeichnende System gemeldet werden. Dabei sind Live-Werte als inhärent lückenhaft anzusehen, da, egal wie gut man den Transportweg absichert, immer Störungen in der Datenübermittlung dazu führen können, dass einzelne Live-Werte in der Hauptanwendung nicht empfangen werden. Da nun aber auch (sowohl aus technischen wie auch aus Performance-Gründen) nicht an alle Arten von Datenpuffern Bestätigungen für den Empfang von Live-Daten empfangen können, müssen diese ständig zyklisch nach den historischen Daten abgefragt werden, um eventuell entstandene Datenlücken aufzufüllen. Dabei kann man entweder

• die Datenlücken erkennen und nur für diese die fehlenden Daten abfragen
• alle Daten aus dem vergangenen Zeitbereich abfragen und die zwischenzeitlich empfangenen Live-Daten vorher löschen, um keine Duplikate in der Datenbank zu haben
• alle Daten aus dem vergangenen Zeitbereich abfragen und filtern und nur die fehlenden Daten eintragen.

Weg 3 ist dabei wahrscheinlich der einfachste, weil damit die Synchronisierungslogik einfach und robust bleibt und mit dem Primärschlüssel auf (Sensor-ID, Zeitstempel) schon einen Filter in der Datenbank vorliegt, der doppelte Datensätze automatisch ausschließt, wenn sie mit INSERT ... ON CONFLICT DO NOTHING eingetragen werden.

3. Herausforderung: Richtige Indizees

Zuerst wollte ich in der Datenbank möglichst auch doppelte Werte bezüglich eines Sensors und einer Zeit erlauben. Dafür hatte ich den Primärschlüssel auf eine zusätzliche Spalte "Datensatz-ID" gelegt und kein Unique-Constraint auf (Sensor-ID, Zeit). Der Gedanke dabei war, auch Sensoraufzeichnungen erlauben zu können, die zu Spitzenlastzeiten schneller erfolgen als die Datentypen-Auflösung des Zeitstempels erlaubt. Beispielsweise könnte ein Sensor zum Zeitpunkt 07:15:01.05001 und zum Zeitpunkt 07:15:01.05002 einen Wert melden. Die Datenbank hätte diese Zeitpunkte beide als "07:15:01.050" gespeichert und so eine Kollision ausgelöst. Um dann die Tabelle schneller durchsuchen zu können, habe ich einen Index über (Sensor-ID, Zeit) angelegt. Das Problem bei einem solchen Index ist, dass bei einer so riesigen Tabelle der Index auch recht groß wird. In dem Fall wurde der Index sogar viele GiB groß und hat nicht mehr in den Heap von PostgreSQL gepasst. Dadurch musste die Datenbank für JEDE Query auf der Tabelle enorm viel in/aus dem Hauptspeicher ein/aus-pagen und die Abfrage-Zeiten haben sich um Faktor 10000 bis 100k erhöht. Nachdem ich also diese "Datensatz-ID" entfernt hatte und aus dem Index einen Primärschlüssel gemacht haben, konnte ich die Abfrage-Performance wieder korrigieren. Was man dadurch verliert ist lediglich die Fähigkeit ultra-hohe Datendichten zu speichern. Dieser Fall kann aber sowieso nie in der Realität auftreten, weil es keine so hochauflösenden Sensoren im industriellen Umfeld gibt. Sollten die Anwndung doch einmal einem solchen Anwendungsfall begegnen, so kann man natürlich immer noch einfach den Datentyp der Zeitwerte entsprechend verfeinern. Nun hat die TimescaleDB natürlich keinen Index mehr, um schnell die Werte für eine Abfrage finden zu können. Deshalb muss die Datenbank nun innerhalb der Chunks der Hypertable auf einen sequentiellen Scan zurückgreifen. Das ist aber kein Problem, da die Chunks alle nur so klein sind, dass diese problemlos im Hauptspeicher gecacht werden können und damit auch effizient durchsucht werden können. Egal wie lange also nun die Datenaufzeichnung läuft - durch das Chunking bleibt die Query-Performance (Abfrage-Zeit-Komplexität) konstant und nimmt nur linear mit der Gesamt-Datenrate (gespeicherte Datensätze von allen Sensoren pro Sekunde) zu. Will man nun mit enorm hohen Datenraten arbeiten, so würde man die Hypertable neben der horizontalen Partitionierung nach der Zeit ebenso nocheinmal horizontal nach einer Gruppe von Sensor-IDs partitionieren und dann auf mehrere Nodes in einem PostgreSQL-Cluster verteilen. So bleibt die Abfrage-Zeit-Komplexität auch in noch größeren Workloads konstant.

4. Herausforderung: Ermittlung der richtigen Verdichtungsstufe

Will man sich große Zeiträume (Monate, Jahre, Jahrzente) in einem Diagramm ansehen, so können nicht die Rohdaten von diesen Zeiträumen geladen werden. Das würde jedes Visualisierungssystem überlasten. Daher müssen diese Daten aggregiert werden (zu deutsch "verdichtet"). Dabei kann man beispielsweise Durchschnitte, Mediane, Ober/Unter-Grenzwerte usw. für bestimmte kleine Time-Buckets (Zeitabschnitte) bilden. Dabei ist es entscheidend, zu ermitteln, wie breit die Time-Buckets sein sollen. Wählt man einen Time-Bucket von 7 Tage, so erhält man etwa 52 Datensätze pro Jahr. Das wäre für eine Jahresverlaufskurve von Umgebungstemperaturen aber beispielsweise viel zu grob. Bei einem Time-Bucket von 4 Stunden hingegen erhält man etwa 365 d/a x 24 h/d / 4h = 2190 Datensätze. Mit einem Full-HD-Monitor wäre das etwas mehr als 1 Datensatz pro Pixel in der Visualisierung. Nun gibt es im industriellen Umfeld gewisse Gruppen von Sensoren, die etwa die gleiche Auflösung physikalisch sinnvoll machen. Daher lassen sich recht gut gewisse Verdichtungsstufen wählen, die in der großen Mehrheit der Sensorwerte Sinn ergeben. Wir haben dabei typische Betrachtungszeiträume genommen (Schichten, Tage, Wochen, Monate, Quartale, Jahre) und aufgrund der üblichen Visualisierungsauflösung ermittelt, wie groß die Verdichtungsstufen sein müssen, um eine gestochen scharfe Kurve zu erhalten, während wir die dafür benötigte Datenmenge so weit verringert haben, wie es eben geht. Um dabei beliebige Zeiträume im Diagramm darstellen zu können, muss das System also auch auf mehreren Verdichtungsstufen arbeiten. Aufgrund der Auflösung des Anzeigegeräts und des betrachteten Zeitraums lässt sich nun in jedem Fall automatisch die Verdichtungsstufe berechnen, von der man die Daten idealerweise aus der Datenbank abfragen sollte. Wenn man also in das Diagramm nun hereinzoomt, so wechselt dynamisch die Verdichtungsstufe und der Benutzer des Systems bekommt bei der Bedienung nicht mit, dass das System Daten aus verschiedenen Aggregationen lädt. Somit entsteht eine flüssige Bedienoberfläche, bei der man dynamisch von der Jahreskurve bis auf die Rohdaten reinzoomen kann. Wichtig ist ferner, dem Bediener aber noch mitzuteilen, wann verdichtete und wann Rohdaten angezeigt werden, denn durch die Verdichtung gehen natürlich manche Ausreißer-Datensätze oder andere feinere Strukturen in den Daten verloren.

Resumée

Das System ist mittlerweile in mehreren Anlagen in Betrieb und zeichnet fleißig Daten auf. Dabei werden Durchsätze von hunderten Datensätzen pro Sekunde und Archive von vielen GiB Größe verwaltet. Die Antwortzeit des Systems wird dadurch nicht signifikant beeinflusst und ist mit unter einer Sekunde für das Laden eines Diagramms innerhalb der qualitativen Requirements. Ich freue mich auf noch größere Workloads, denn bisher habe ich noch nicht gesehen, dass das System irgendwo an Leistungsgrenzen stoßen würden.