One place for hosting & domains

      TimescaleDB

      Installieren und Verwenden von TimescaleDB unter Ubuntu 18.04


      Der Autor hat das Computer History Museum für eine Spende im Rahmen des Programms Write for DOnations ausgewählt.

      Einführung

      Viele Anwendungen, wie z. B. Überwachungs- und Datenerfassungssysteme, sammeln Daten zur weiteren Analyse. Mit diesen Analysen wird häufig untersucht, wie sich ein Datenelement oder ein System über die Zeit ändert. In diesen Instanzen werden die Daten als Zeitreihen dargestellt, wobei jeder Datenpunkt mit einem Zeitstempel versehen ist. Ein Beispiel würde wie folgt aussehen:

      2019-11-01 09:00:00    server.cpu.1    0.9
      2019-11-01 09:00:00    server.cpu.15   0.8
      2019-11-01 09:01:00    server.cpu.1    0.9
      2019-11-01 09:01:00    server.cpu.15   0.8
      ...
      

      Die Verwaltung von Zeitreihendaten ist mit dem Aufstieg des Internets der Dinge (IoT) und des industriellen Internets der Dinge zu einer grundlegenden Fähigkeit geworden. Es gibt immer mehr Geräte, die verschiedene Zeitreihendaten sammeln: Fitnesstracker, Smartwatches, Heim-Wetterstationen und verschiedene Sensoren, um nur einige zu nennen. Diese Geräte sammeln eine Menge Daten, die alle irgendwo gespeichert werden müssen.

      Zur Speicherung von Daten werden meist klassische relationale Datenbanken verwendet, die jedoch nicht immer perfekt geeignet sind, wenn es um die riesigen Datenvolumina von Zeitreihen geht. Wenn Sie eine große Menge von Zeitreihendaten verarbeiten müssen, können relationale Datenbanken zu langsam für diese Aufgabe sein. Aus diesem Grund wurden speziell optimierte Datenbanken, so genannte NoSQL-Datenbanken, entwickelt, um die Probleme mit relationalen Datenbanken zu vermeiden.

      TimescaleDB ist eine Open-Source-Datenbank, die für die Speicherung von Zeitreihendaten optimiert ist. Sie ist als eine Erweiterung von PostgreSQL implementiert und kombiniert die Benutzerfreundlichkeit rationaler Datenbanken mit der Geschwindigkeit von NoSQL-Datenbanken. So können Sie mit PostgreSQL sowohl Geschäftsdaten als auch Zeitreihendaten an einem Ort speichern.

      Durch Befolgung dieses Tutorials richten Sie TimescaleDB unter Ubuntu 18.04 ein und lernen, wie Sie damit arbeiten. Sie werden Zeitreihendatenbanken erstellen und einfache Abfragen vornehmen. Am Ende erfahren Sie, wie Sie unnötige Daten entfernen können.

      Voraussetzungen

      Um dieser Anleitung zu folgen, benötigen Sie:

      Schritt 1 — Installieren der TimescaleDB

      TimescaleDB ist in den Repositorys des Standardpakets von Ubuntu nicht verfügbar. Darum installieren Sie in diesem Schritt die Datenbank aus dem TimescaleDB PPA (Privatpaket-Repository).

      Fügen Sie zunächst das APT-Repository von Timescale hinzu:

      • sudo add-apt-repository ppa:timescale/timescaledb-ppa

      Bestätigen Sie diese Aktion durch Betätigung der Eingabetaste.

      Als Nächstes aktualisieren Sie Ihren APT-Cache, um Ihre Paketlisten zu aktualisieren:

      Sie können nun mit der Installation fortfahren. In diesem Tutorial kommt PostgreSQL Version 10 zum Einsatz. Falls Sie eine andere Version von PostgreSQL verwenden (z. B. 9.6 oder 11), ersetzen Sie den Wert im folgenden Befehl und führen Sie den Befehl aus:

      • sudo apt install timescaledb-postgresql-10

      TimescaleDB ist nun installiert und bereit für den Einsatz. Als Nächstes aktivieren Sie die Datenbank und passen einige der damit verbundenen Einstellungen in der PostgreSQL-Konfigurationsdatei an, um die Datenbank zu optimieren.

      Schritt 2 — Konfigurieren von TimescaleDB

      Das TimescaleDB-Modul funktioniert gut mit den Standard-Konfigurationseinstellungen für PostgreSQL, jedoch schlagen die Entwickler von TimescaleDB eine Konfiguration bestimmter Parameter vor, um die Leistung zu erhöhen und die Ressourcen von Prozessor, Arbeitsspeicher und Festplatte besser zu nutzen. Dies kann automatisch mit dem Tool timescaledb-tune oder durch die manuelle Bearbeitung der Datei postgresql.conf Ihres Servers erfolgen.

      In diesem Tutorial verwenden Sie das Tool timescaledb-tune, das die Datei postgresql.conf lesen und interaktiv Änderungen vorschlagen wird.

      Führen Sie den folgenden Befehl aus, um den Konfigurationsassistenten zu starten:

      Zuerst werden Sie aufgefordert, den Pfad zu der PostgreSQL-Konfigurationsdatei zu bestätigen:

      Output

      Using postgresql.conf at this path: /etc/postgresql/10/main/postgresql.conf Is this correct? [(y)es/(n)o]:

      Das Dienstprogramm erkennt den Pfad zu der Konfigurationsdatei automatisch, bestätigen Sie also durch Eingabe von y:

      Output

      ... Is this correct? [(y)es/(n)o]: y Writing backup to: /tmp/timescaledb_tune.backup201911181111

      Als Nächstes werden Sie dazu aufgefordert, die Variable shared_preload_libraries zu ändern, um beim Starten des PostgreSQL-Servers das TimescaleDB-Modul im Voraus zu laden:

      Output

      shared_preload_libraries needs to be updated Current: #shared_preload_libraries = '' Recommended: shared_preload_libraries = 'timescaledb' Is this okay? [(y)es/(n)o]:

      shared_preload_libraries akzeptiert eine durch Trennzeichen getrennte Liste der Module als Wert, die angibt, welche Module PostgreSQL vor dem Start des Datenbankservers laden soll. Durch Vornehmen dieser Änderung wird das Modul timescaledb der Liste hinzugefügt.

      Anmerkung: Wenn eine von shared_preload_libraries angegebene Bibliothek nicht gefunden werden kann, wird der Datenbankserver nicht gestartet. Denken Sie daran, wenn Sie Anwendungen debuggen, die shared_preload_libraries verwenden. Weitere Informationen dazu finden Sie in diesem PostgresqlCO.NF-Artikel zu shared_preload_libraries.

      Aktivieren Sie das TimescaleDB-Modul, indem Sie in dieser Eingabeaufforderung y eingeben und die Eingabetaste drücken:

      Output

      ... Is this okay? [(y)es/(n)o]: y success: shared_preload_libraries will be updated

      Basierend auf den Eigenschaften Ihres Servers und der PostgreSQL-Version wird Ihnen das Skript dann anbieten, Ihre Einstellungen zu optimieren. Drücken Sie y, um den Optimierungsvorgang zu starten:

      Output

      Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y Recommendations based on 7.79 GB of available memory and 4 CPUs for PostgreSQL 10 Memory settings recommendations Current: shared_buffers = 128MB #effective_cache_size = 4GB #maintenance_work_mem = 64MB #work_mem = 4MB Recommended: shared_buffers = 1994MB effective_cache_size = 5982MB maintenance_work_mem = 1021001kB work_mem = 5105kB Is this okay? [(y)es/(s)kip/(q)uit]:

      timescaledb-tune erkennt automatisch den verfügbaren Arbeitsspeicher des Servers und berechnet empfohlene Werte für eine Reihe von Einstellungen. shared_buffers zum Beispiel ermittelt die für das Zwischenspeichern von Daten zugewiesene Menge an Arbeitsspeicher. Standardmäßig ist dieser Wert relativ niedrig, um einer größeren Palette von Plattformen gerecht zu werden; darum schlägt timescaledb-tune vor, diesen Wert von 128 MB auf 1994 MB zu erhöhen, sodass Sie Ressourcen besser nutzen können, indem mehr Platz zum Speichern zwischengespeicherter Informationen (z. B. wiederholte Abfragen) zur Verfügung steht. Die Variable work_mem wurde ebenfalls erhöht, um kompliziertere Sortiervorgänge zuzulassen.

      Wenn Sie mehr über die Feinabstimmung der Arbeitsspeichereinstellungen für PostgreSQL erfahren möchten, konsultieren Sie den Artikel Optimierung Ihres PostgreSQL-Servers in der PostgreSQL-Wiki.

      Geben Sie y ein, um die Werte zu akzeptieren:

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: memory settings will be updated

      Wenn Ihr Server über mehrere CPUs verfügt, finden Sie an dieser Stelle Empfehlungen für die Parallelitätseinstellungen. Diese Einstellungen bestimmen darüber, wie mehrere CPUs simultane Abfragen parallel ausführen können, um Datenbanken zu durchsuchen und die angeforderten Daten schneller zurückzugeben.

      Diejenigen mit mehreren CPUs werden auf Empfehlungen wie diese treffen:

      Output

      Parallelism settings recommendations Current: missing: timescaledb.max_background_workers #max_worker_processes = 8 #max_parallel_workers_per_gather = 2 #max_parallel_workers = 8 Recommended: timescaledb.max_background_workers = 8 max_worker_processes = 13 max_parallel_workers_per_gather = 1 max_parallel_workers = 2 Is this okay? [(y)es/(s)kip/(q)uit]:

      Diese Einstellungen steuern die Anzahl der Worker, die Anfragen und Hintergrundaufgaben verarbeiten. Mehr über diese Einstellungen können Sie der TimescaleDB- und der PostgreSQL-Dokumentation entnehmen.

      Geben Sie y ein und betätigen Sie dann die Eingabetaste, um diese Einstellungen zu akzeptieren:

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: parallelism settings will be updated

      Als Nächstes finden Sie Empfehlungen für Write Ahead Log (WAL):

      Output

      WAL settings recommendations Current: #wal_buffers = -1 #min_wal_size = 80MB #max_wal_size = 1GB Recommended: wal_buffers = 16MB min_wal_size = 4GB max_wal_size = 8GB Is this okay? [(y)es/(s)kip/(q)uit]:

      WAL ist eine Protokollierungsmethode, mit der PostgreSQL Änderungen an Datendateien protokolliert, bevor die Änderungen an der Datenbank vorgenommen werden. Durch Priorisierung eines aktuellen Datensatzes mit Datenänderungen sorgt WAL dafür, dass Sie Ihre Datenbank im Falle eines Absturzes rekonstruieren können. So wird die Datenintegrität gewahrt. Die Standardeinstellungen können jedoch ineffiziente Input/Output (I/O)-Operationen verursachen, was die Schreibperformance verringert. Um dieses Problem zu beheben, geben Sie y ein und bestätigen Sie mit der Eingabetaste:

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: WAL settings will be updated

      Sie finden nun verschiedene Empfehlungen:

      Output

      Miscellaneous settings recommendations Current: #default_statistics_target = 100 #random_page_cost = 4.0 #checkpoint_completion_target = 0.5 #max_locks_per_transaction = 64 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #effective_io_concurrency = 1 Recommended: default_statistics_target = 500 random_page_cost = 1.1 checkpoint_completion_target = 0.9 max_locks_per_transaction = 64 autovacuum_max_workers = 10 autovacuum_naptime = 10 effective_io_concurrency = 200 Is this okay? [(y)es/(s)kip/(q)uit]:

      Alle diese verschiedenen Parameter zielen auf Leistungssteigerung ab. Beispielsweise können SSDs viele gleichzeitige Anfragen verarbeiten, sodass der beste Wert für die effective_io_concurrency im Hunderterbereich liegen könnte. Weitere Informationen über diese Optionen finden Sie in der PostgreSQL-Dokumentation.

      Drücken Sie y und dann die Eingabetaste, um fortzufahren.

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: miscellaneous settings will be updated Saving changes to: /etc/postgresql/10/main/postgresql.conf

      Als Ergebnis erhalten Sie eine gebrauchsfertige Konfigurationsdatei unter /etc/postgresql/10/main/postgresql.conf.

      Anmerkung: Wenn Sie die Installation automatisieren, könnten Sie den anfänglichen Befehl auch mit den Flags --quiet und --yes ausführen, wodurch automatisch alle Empfehlungen angewendet und Änderungen an der Konfigurationsdatei postgresql.conf vorgenommen werden:

      • sudo timescaledb-tune --quiet --yes

      Damit die Konfigurationsänderungen wirksam werden, müssen Sie den Dienst PostgreSQL neu starten:

      • sudo systemctl restart postgresql.service

      Nun wird die Datenbank mit optimalen Parametern ausgeführt und ist bereit dazu, mit den Zeitreihendaten zu arbeiten. In den nächsten Schritten werden Sie das Arbeiten mit diesen Daten testen; Sie erstellen neue Datenbanken und Hypertabellen und führen Operationen aus.

      Schritt 3 — Erstellen einer neuen Datenbank und Hypertabelle

      Mit Ihrer optimierten TimescaleDB-Einrichtung sind Sie bereit, mit Zeitreihendaten zu arbeiten. TimescaleDB ist als eine Erweiterung von PostgreSQL implementiert, sodass sich Operationen mit Zeitreihendaten nicht wesentlich von relationalen Datenoperationen unterscheiden. Gleichzeitig erlaubt die Datenbank, Daten aus Zeitreihen und relationalen Tabellen in Zukunft frei zu kombinieren.

      Zum Demonstrieren dieser Möglichkeit verwenden Sie PostgreSQL-Befehle, um eine Datenbank zu erstellen und dann die TimescaleDB-Erweiterung zu aktivieren. Dadurch erstellen Sie eine Hypertabelle, die eine Abstraktion vieler individueller Tabellen auf höherer Ebene ist. Hypertabellen sind die Hauptstrukturen, mit denen Sie in TimescaleDB arbeiten.

      Melden Sie sich bei Ihrer PostgreSQL-Datenbank an:

      Erstellen Sie nun eine neue Datenbank und stellen Sie eine Verbindung zu ihr her. In diesem Tutorial wird die Datenbank timeseries heißen:

      • CREATE DATABASE timeseries;
      • c timeseries

      Weitere Informationen über die Arbeit mit der PostgreSQL-Datenbank finden Sie in unserem Tutorial Erstellen, Entfernen und Verwalten von Tabellen in PostgreSQL auf einem Cloud-Server.

      Aktivieren Sie zum Schluss die TimescaleDB-Erweiterung:

      • CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

      Sie sehen den folgenden Output:

      Output

      WARNING: WELCOME TO _____ _ _ ____________ |_ _(_) | | | _ ___ | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ / | | | | _ ` _ / _ / __|/ __/ _` | |/ _ | | | ___ | | | | | | | | | __/__ (_| (_| | | __/ |/ /| |_/ / |_| |_|_| |_| |_|___||___/_____,_|_|___|___/ ____/ Running version 1.5.1 For more information on TimescaleDB, please visit the following links: 1. Getting started: https://docs.timescale.com/getting-started 2. API reference documentation: https://docs.timescale.com/api 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture Note: TimescaleDB collects anonymous reports to better understand and assist our users. For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry. CREATE EXTENSION

      Wie bereits erwähnt, sind die primären Interaktionspunkte mit Ihren Zeitreihendaten Hypertabellen, die aus vielen einzelnen Tabellen namens Chunks bestehen.

      Um eine Hypertabelle zu erstellen, beginnen Sie mit einer regulären SQL-Tabelle und konvertieren diese dann mit der Funktion create_hypertable in eine Hypertabelle.

      Erstellen Sie eine Tabelle, die Daten zur Verfolgung von Temperatur und Feuchtigkeit für verschiedene Geräte im Zeitablauf speichert:

      • CREATE TABLE conditions (
      • time TIMESTAMP WITH TIME ZONE NOT NULL,
      • device_id TEXT,
      • temperature NUMERIC,
      • humidity NUMERIC
      • );

      Dieser Befehl erstellt eine Tabelle namens conditions mit vier Spalten. Die erste Spalte speichert den Zeitstempel, der die Zeitzone enthält und nicht leer sein darf. Als Nächstes werden Sie die Zeitspalte verwenden, um Ihre Tabelle in eine nach Zeit partitionierte Hypertabelle umzuwandeln:

      • SELECT create_hypertable('conditions', 'time');

      Dieser Befehl ruft die Funktion create_hypertable() auf, die eine TimescaleDB-Hypertabelle aus einer PostgreSQL-Tabelle erstellt und diese ersetzt.

      Sie erhalten die folgende Ausgabe:

      Output

      create_hypertable ------------------------- (1,public,conditions,t) (1 row)

      In diesem Schritt haben Sie eine neue Hypertabelle zur Speicherung von Zeitreihendaten angelegt. Nun können Sie sie mit Daten füllen, indem Sie in die Hypertabelle schreiben und dann den Löschvorgang ausführen.

      Schritt 4 — Schreiben und Löschen von Daten

      In diesem Schritt fügen Sie Daten unter Verwendung von SQL-Standardbefehlen ein und importieren große Datensätze aus externen Quellen. Dies veranschaulicht Ihnen die relationalen Datenbankaspekte von TimescaleDB.

      Probieren Sie zunächst die grundlegenden Befehle aus. Sie können Daten in die Hypertabelle einfügen, indem Sie den SQL-Standardbefehl INSERT verwenden. Fügen Sie mit dem folgenden Befehl einige Beispieldaten für Temperatur und Feuchtigkeit für das theoretische Gerät weather-pro-000000 ein:

      • INSERT INTO conditions(time, device_id, temperature, humidity)
      • VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);

      Sie sehen die folgende Ausgabe:

      Output

      INSERT 0 1

      Außerdem können Sie mehrere Datenzeilen gleichzeitig einfügen. Versuchen Sie Folgendes:

      • INSERT INTO conditions
      • VALUES
      • (NOW(), 'weather-pro-000002', 71.0, 51.0),
      • (NOW(), 'weather-pro-000003', 70.5, 50.5),
      • (NOW(), 'weather-pro-000004', 70.0, 50.2);

      Sie erhalten Folgendes:

      Output

      INSERT 0 3

      Sie können auch festlegen, dass der Befehl INSERT einige oder alle eingefügten Daten mit der Anweisung RETURNING zurückgibt:

      • INSERT INTO conditions
      • VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;

      Sie sehen den folgenden Output:

      Output

      time | device_id | temperature | humidity -------------------------------+--------------------+-------------+---------- 2019-09-15 14:14:01.576651+00 | weather-pro-000002 | 70.1 | 50.1 (1 row)

      Wenn Sie Daten aus der Hypertabelle löschen möchten, verwenden Sie den SQL-Standardbefehl DELETE. Führen Sie Folgendes aus, um alle Daten zu löschen, die eine temperature (Temperatur) von mehr als 80 oder eine humidity (Luftfeuchtigkeit) von mehr als 50 haben:

      • DELETE FROM conditions WHERE temperature > 80;
      • DELETE FROM conditions WHERE humidity > 50;

      Verwenden Sie nach dem Löschvorgang den Befehl VACUUM, um Platz freizugeben, der noch von gelöschten Daten verwendet wird.

      Weitere Informationen über den Befehl VACUUM finden Sie in der PostgreSQL-Dokumentation.

      Diese Befehle eignen sich gut für die Dateneingabe in kleinem Maßstab. Da Zeitreihendaten aber oft riesige Datensätze von mehreren Geräten gleichzeitig generieren, ist es wichtig zu wissen, wie sich Hunderte oder Tausende von Zeilen auf einmal einfügen lassen. Wenn Sie Daten aus externen Quellen in strukturierter Form, z. B. im Format csv, erstellt haben, kann diese Aufgabe schnell erledigt werden.

      Um dies auszuprobieren, verwenden Sie einen Beispieldatensatz, der Temperatur- und Luftfeuchtigkeitsdaten von verschiedenen Orten darstellt. Dies ist ein offizieller TimescaleDB-Datensatz, der zum Testen der Datenbank dient. Weitere Informationen über Beispieldatensätze finden Sie in der TimescaleDB-Dokumentation.

      Sehen wir uns an, wie Sie Daten aus dem Beispieldatensatz weather_small importieren können. Beenden Sie zunächst PostgreSQL:

      Laden Sie dann den Datensatz herunter und extrahieren Sie ihn:

      • wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz
      • tar -xvzf weather_small.tar.gz

      Als Nächstes importieren Sie die Temperatur- und Luftfeuchtigkeitsdaten in Ihre Datenbank:

      • sudo -u postgres psql -d timeseries -c "COPY conditions FROM weather_small_conditions.csv CSV"

      Dadurch wird eine Verbindung mit der Datenbank timeseries hergestellt und der Befehl COPY ausgeführt, der die Daten aus der ausgewählten Datei in die Hypertabelle conditions kopiert. Der Vorgang dauert einige Sekunden.

      Nachdem die Daten in Ihre Tabelle eingespeist worden sind, erhalten Sie die folgende Ausgabe:

      Output

      COPY 1000000

      In diesem Schritt haben Sie Daten manuell und in Stapeln in die Hypertabelle eingefügt. Als Nächstes fahren Sie mit der Durchführung von Abfragen fort.

      Schritt 5 — Abfragen von Daten

      Nachdem Ihre Tabelle nun Daten enthält, können Sie verschiedene Abfragen durchführen, um sie zu analysieren.

      Melden Sie sich zunächst bei der Datenbank an:

      • sudo -u postgres psql -d timeseries

      Wie bereits erwähnt, können Sie für die Arbeit mit Hypertabellen SQL-Standardbefehle verwenden. Um beispielsweise die letzten 10 Einträge aus der Hypertabelle conditions anzuzeigen, führen Sie den folgenden Befehl aus:

      • SELECT * FROM conditions LIMIT 10;

      Sie sehen den folgenden Output:

      Output

      time | device_id | temperature | humidity ------------------------+--------------------+--------------------+---------- 2016-11-15 12:00:00+00 | weather-pro-000000 | 39.9 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000001 | 32.4 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 | 50.2 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000004 | 71.8 | 50.1 2016-11-15 12:00:00+00 | weather-pro-000005 | 71.8 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000006 | 37 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000007 | 72 | 50 2016-11-15 12:00:00+00 | weather-pro-000008 | 31.3 | 50 2016-11-15 12:00:00+00 | weather-pro-000009 | 84.4 | 87.8 (10 rows)

      Mit diesem Befehl können Sie sehen, welche Daten sich in der Datenbank befinden. Da die Datenbank eine Million Einträge enthält, haben Sie mit LIMIT 10 die Ausgabe auf 10 Einträge beschränkt.

      Um die letzten Einträge zu sehen, sortieren Sie das Datenarray in absteigender Reihenfolge nach der Zeit:

      • SELECT * FROM conditions ORDER BY time DESC LIMIT 20;

      Dadurch werden die 20 letzten Einträge ausgegeben.

      Sie können auch einen Filter hinzufügen. Um beispielsweise Einträge von dem Gerät weather-pro-000000 anzuzeigen, führen Sie Folgendes aus:

      • SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;

      In diesem Fall sehen Sie die 10 letzten Temperatur- und Feuchtigkeitsdatenpunkte, die vom Gerät weather-pro-000000 aufgezeichnet wurden.

      Zusätzlich zu den SQL-Standardbefehlen bietet TimescaleDB auch eine Reihe spezieller Funktionen, die für die Analyse von Zeitreihendaten nützlich sind. Um beispielsweise den Mittelwert der Temperaturwerte zu ermitteln, können Sie die folgende Abfrage mit der Funktion percentile_cont verwenden:

      • SELECT percentile_cont(0.5)
      • WITHIN GROUP (ORDER BY temperature)
      • FROM conditions
      • WHERE device_id = 'weather-pro-000000';

      Sie sehen den folgenden Output:

      Output

      percentile_cont ----------------- 40.5 (1 row)

      Auf diese Weise sehen Sie den Mittelwert der Temperatur für den gesamten Beobachtungszeitraum des Ortes, an dem sich der Sensor weather-pro-000000 befindet.

      Um die letzten Werte der einzelnen Sensoren anzuzeigen, können Sie die Funktion last verwenden:

      • select device_id, last(temperature, time)
      • FROM conditions
      • GROUP BY device_id;

      In der Ausgabe sehen Sie eine Liste aller Sensoren und der jeweiligen letzten Werte.

      Um die ersten Werte zu erhalten, verwenden Sie die Funktion first.

      Das folgende Beispiel ist komplexer. Es zeigt die stündlichen Durchschnitts-, Minimal- und Maximaltemperaturen für den ausgewählten Sensor in den letzten 24 Stunden an:

      • SELECT time_bucket('1 hour', time) "hour",
      • trunc(avg(temperature), 2) avg_temp,
      • trunc(min(temperature), 2) min_temp,
      • trunc(max(temperature), 2) max_temp
      • FROM conditions
      • WHERE device_id = 'weather-pro-000000'
      • GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;

      Hier haben Sie die Funktion time_bucket verwendet, die eine leistungsfähigere Version der PostgreSQL-Funktion date_trunc darstellt. Als Ergebnis sehen Sie, in welchen Tagesabschnitten die Temperatur steigt oder fällt:

      Output

      hour | avg_temp | min_temp | max_temp ------------------------+----------+----------+---------- 2016-11-16 21:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-16 20:00:00+00 | 41.92 | 41.69 | 42.00 2016-11-16 19:00:00+00 | 41.07 | 40.59 | 41.59 2016-11-16 18:00:00+00 | 40.11 | 39.79 | 40.59 2016-11-16 17:00:00+00 | 39.46 | 38.99 | 39.79 2016-11-16 16:00:00+00 | 38.54 | 38.19 | 38.99 2016-11-16 15:00:00+00 | 37.56 | 37.09 | 38.09 2016-11-16 14:00:00+00 | 36.62 | 36.39 | 37.09 2016-11-16 13:00:00+00 | 35.59 | 34.79 | 36.29 2016-11-16 12:00:00+00 | 34.59 | 34.19 | 34.79 2016-11-16 11:00:00+00 | 33.94 | 33.49 | 34.19 2016-11-16 10:00:00+00 | 33.27 | 32.79 | 33.39 2016-11-16 09:00:00+00 | 33.37 | 32.69 | 34.09 2016-11-16 08:00:00+00 | 34.94 | 34.19 | 35.49 2016-11-16 07:00:00+00 | 36.12 | 35.49 | 36.69 2016-11-16 06:00:00+00 | 37.02 | 36.69 | 37.49 2016-11-16 05:00:00+00 | 38.05 | 37.49 | 38.39 2016-11-16 04:00:00+00 | 38.71 | 38.39 | 39.19 2016-11-16 03:00:00+00 | 39.72 | 39.19 | 40.19 2016-11-16 02:00:00+00 | 40.67 | 40.29 | 40.99 2016-11-16 01:00:00+00 | 41.63 | 40.99 | 42.00 2016-11-16 00:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 23:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 22:00:00+00 | 42.00 | 42.00 | 42.00 (24 rows)

      Weitere nützliche Funktionen finden Sie in der TimescaleDB-Dokumentation.

      Nun wissen Sie, wie Sie mit Ihren Daten umgehen können. Als Nächstes werden Sie unnötige Daten löschen und Daten komprimieren.

      Schritt 6 — Konfigurieren der Datenkomprimierung und -löschung

      Wenn sich Daten ansammeln, nehmen sie immer mehr Platz auf Ihrer Festplatte ein. Um Platz zu sparen, bietet die neueste Version von TimescaleDB eine Komprimierungsfunktion für Daten. Diese Funktion erfordert keine Anpassung der Dateisystemeinstellungen und ermöglicht eine rasche Optimierung Ihrer Datenbank. Weitere Informationen über die Funktionsweise dieser Komprimierung finden Sie in diesem Komprimierungsartikel von TimescaleDB.

      Aktivieren Sie zunächst die Komprimierung Ihrer Hypertabelle:

      • ALTER TABLE conditions SET (
      • timescaledb.compress,
      • timescaledb.compress_segmentby = 'device_id'
      • );

      Sie erhalten die folgenden Ausgabedaten:

      Output

      NOTICE: adding index _compressed_hypertable_2_device_id__ts_meta_sequence_num_idx ON _timescaledb_internal._compressed_hypertable_2 USING BTREE(device_id, _ts_meta_sequence_num) ALTER TABLE

      Anmerkung: Sie können TimescaleDB auch so einrichten, dass Daten nach dem angegebenen Zeitraum komprimiert werden. Beispielsweise können Sie Folgendes ausführen:

      • SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');

      In diesem Beispiel werden die Daten nach einer Woche automatisch komprimiert.

      Sie können die Statistiken der komprimierten Daten mit folgendem Befehl anzeigen:

      • SELECT *
      • FROM timescaledb_information.compressed_chunk_stats;

      Sie sehen dann eine Liste von Chunks mit ihrem Status: Komprimierungsstatus und wie viel Speicherplatz unkomprimierte und komprimierte Daten in Bytes belegen.

      Wenn es nicht notwendig ist, Daten über einen längeren Zeitraum zu speichern, können Sie veraltete Daten löschen, um noch mehr Speicherplatz freizugeben. Dafür gibt es eine spezielle Funktion namens drop_chunks. Sie ermöglicht das Löschen von Chunks, die Daten enthalten, die älter als die angegebene Zeit sind:

      • SELECT drop_chunks(interval '24 hours', 'conditions');

      Diese Abfrage löscht alle Chunks aus der Hypertabelle conditions, die nur Daten enthalten, die älter als ein Tag sind.

      Sie erhalten die folgende Ausgabe:

      Output

      drop_chunks ---------------------------------------- _timescaledb_internal._hyper_1_2_chunk (1 row)

      Um alte Daten automatisch zu löschen, können Sie eine Aufgabe cron konfigurieren. In unserem Tutorial erfahren Sie mehr über die Verwendung von cron zur Automatisierung verschiedener Systemaufgaben.

      Verlassen Sie die Datenbank:

      Bearbeiten Sie als Nächstes crontab mit dem folgenden Befehl, der von der Shell aus ausgeführt werden sollte:

      Fügen Sie nun am Ende der Datei die folgende Zeile hinzu:

      crontab

      ...
      
      0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1
      

      Dieser Job löscht jeden Tag um 1:00 Uhr obsolete Daten, die älter als ein Tag sind.

      Zusammenfassung

      Sie haben TimescaleDB auf Ihrem Ubuntu 18.04-Server eingerichtet. Sie haben auch das Erstellen von Hypertabellen, das Einfügen von Daten in diese Tabellen, das Abfragen der Daten, das Komprimieren und das Löschen unnötiger Datensätze getestet. Mit diesen Beispielen können Sie bei der Speicherung von Zeitreihendaten die wichtigsten Vorteile von TimescaleDB gegenüber traditionellen relationalen Datenbankverwaltungssystemen nutzen, einschließlich:

      • Höhere Datenerfassungsraten
      • Schnellere Abfrageleistung
      • Zeitorientierte Funktionen

      Nachdem Sie wissen, wie sich Zeitreihendaten speichern lassen, können Sie die Daten nun zum Erstellen von Diagrammen verwenden. TimescaleDB ist mit Visualisierungstools kompatibel, die mit PostgreSQL funktionieren (wie Grafana). Sie können unser Tutorial Installieren und Schützen von Grafana unter Ubuntu 18.04 konsultieren, um mehr über dieses beliebte Visualisierungstool zu erfahren.



      Source link

      So installieren und verwenden Sie TimescaleDB unter CentOS 7


      Der Autor wählte das Computer History Museum, um eine Spende im Rahmen des Programms Write for DOnations zu erhalten.

      Einführung

      Viele Anwendungen, wie z. B. Überwachungs- und Datenerfassungssysteme, sammeln Daten zur weiteren Analyse. Mit diesen Analysen wird häufig die Art und Weise untersucht, wie sich ein Datenelement oder ein System im Verlauf der Zeit ändert. In diesen Instanzen werden die Daten als Zeitreihen dargestellt, wobei jeder Datenpunkt mit einem Zeitstempel versehen ist. Ein Beispiel würde wie folgt aussehen:

      2019-11-01 09:00:00    server.cpu.1    0.9
      2019-11-01 09:00:00    server.cpu.15   0.8
      2019-11-01 09:01:00    server.cpu.1    0.9
      2019-11-01 09:01:00    server.cpu.15   0.8
      ...
      

      Die Relevanz von Zeitreihendaten hat in letzter Zeit dank der neuen Bereitstellungen des Internet der Dinge (IoT) und des industriellen Internets der Dinge zugenommen. Es gibt immer mehr Geräte, die verschiedene Zeitreiheninformationen sammeln: Fitnesstracker, Smartwatches, Heim-Wetterstationen und verschiedene Sensoren, um nur einige zu nennen. Diese Geräte sammeln eine Menge Informationen und alle diese Daten müssen irgendwo gespeichert werden.

      Zur Speicherung von Daten werden meist klassische relationale Datenbanken verwendet, die jedoch nicht immer geeignet sind, wenn es um die riesigen Datenmengen von Zeitreihen geht. Wenn Sie eine große Menge von Zeitreihendaten verarbeiten müssen, können relationale Datenbanken zu langsam sein. Aus diesem Grund wurden speziell optimierte Datenbanken, so genannte NoSQL-Datenbanken erstellt, um die Probleme relationaler Datenbanken zu vermeiden.

      TimescaleDB ist eine Open-Source-Datenbank, die für die Speicherung von Zeitreihendaten optimiert ist. Sie wird als eine Erweiterung von PostgreSQL implementiert und kombiniert die Benutzerfreundlichkeit rationaler Datenbanken mit der Geschwindigkeit von NoSQL-Datenbanken. Dadurch können Sie PostgreSQL sowohl für die Speicherung von Geschäftsdaten als auch Zeitreihendaten an einem Ort verwenden.

      Wenn Sie diesem Tutorial folgen, werden Sie TimescaleDB unter CentOS 7 einrichten, konfigurieren und lernen, wie damit gearbeitet wird. Dann erstellen Sie Zeitreihendatenbanken und führen einfache Abfragen durch. Abschließend sehen Sie, wie unnötige Daten entfernt werden.

      Voraussetzungen

      Um dieser Anleitung zu folgen, benötigen Sie:

      Schritt 1 – Installieren der TimescaleDB

      TimescaleDB ist nicht in den Repositorys der CentOS-Standardpakete verfügbar. Daher installieren Sie es in diesem Schritt aus dem TimescaleDB-Repository eines Drittanbieters.

      Erstellen Sie zunächst eine neue Repository-Datei:

      • sudo vi /etc/yum.repos.d/timescaledb.repo

      Gehen Sie in den Einfügemodus, indem Sie i drücken, und fügen Sie folgende Konfiguration in die Datei ein:

      /etc/yum.repos.d/timescaledb.repo

      [timescale_timescaledb]
      name=timescale_timescaledb
      baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch
      repo_gpgcheck=1
      gpgcheck=0
      enabled=1
      gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
      sslverify=1
      sslcacert=/etc/pki/tls/certs/ca-bundle.crt
      metadata_expire=300
      

      Wenn Sie fertig sind, drücken Sie ESC, um den Einfügemodus zu verlassen, dann :wq und die EINGABETASTE, um die Datei zu speichern und zu verlassen. Um mehr über den Texteditor vi und seinen Nachfolger vim zu erfahren, sehen Sie sich unser Tutorial Installieren und Verwenden des Vim Texteditors auf einem Cloud-Server an.

      Sie können nun mit der Installation fortfahren. Dieses Tutorial verwendet PostgreSQL Version 11; falls Sie eine andere Version von PostgreSQL (z. B. 9.6 oder 11) verwenden, ersetzen Sie den Wert im folgenden Befehl und führen Sie ihn aus:

      • sudo yum install -y timescaledb-postgresql-11

      TimescaleDB ist nun installiert und bereit für die Verwendung. Als Nächstes aktivieren Sie es und passen einige der damit verbundenen Einstellungen in der PostgreSQL-Konfigurationsdatei an, um die Datenbank zu optimieren.

      Schritt 2 – Konfigurieren der TimescaleDB

      Das TimescaleDB-Modul funktioniert gut mit den Standard-Konfigurationseinstellungen, jedoch schlagen die Entwickler von TimescaleDB die Konfiguration einzelner Parameter vor, um die Leistung zu verbessern und die Ressourcen von Prozessor, Speicher und Festplatte besser zu nutzen. Dies kann automatisch mit dem Tool timescaledb-tune oder durch die manuelle Bearbeitung der Datei postgresql.conf Ihres Servers erfolgen.

      In diesem Tutorial werden Sie das Tool timescaledb-tune verwenden. Es liest die Datei postgresql.conf und schlägt interaktiv Änderungen vor.

      Führen Sie den folgenden Befehl aus, um den Konfigurationsassistenten zu starten:

      • sudo timescaledb-tune --pg-config=/usr/pgsql-11/bin/pg_config

      Zuerst werden Sie aufgefordert, den Pfad zu der PostgreSQL-Konfigurationsdatei zu bestätigen:

      Output

      Using postgresql.conf at this path: /var/lib/pgsql/11/data/postgresql.conf Is this correct? [(y)es/(n)o]:

      Das Dienstprogramm erkennt den Pfad zu der Konfigurationsdatei automatisch, bestätigen Sie dies also durch Eingabe von y:

      Output

      ... Is this correct? [(y)es/(n)o]: y Writing backup to: /tmp/timescaledb_tune.backup201912191633

      Als Nächstes aktivieren Sie das TimescaleDB-Modul, indem Sie an der nächsten Eingabeaufforderung y eingeben und die EINGABETASTE drücken:

      Output

      shared_preload_libraries needs to be updated Current: #shared_preload_libraries = '' Recommended: shared_preload_libraries = 'timescaledb' Is this okay? [(y)es/(n)o]: y success: shared_preload_libraries will be updated

      Basierend auf den Eigenschaften Ihres Servers und der PostgreSQL-Version wird Ihnen dann die Optimierung Ihrer Einstellungen angeboten. Drücken Sie y, um den Optimierungsvorgang zu starten:

      Output

      Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y Recommendations based on 7.64 GB of available memory and 4 CPUs for PostgreSQL 11 Memory settings recommendations Current: shared_buffers = 128MB #effective_cache_size = 4GB #maintenance_work_mem = 64MB #work_mem = 4MB Recommended: shared_buffers = 1955MB effective_cache_size = 5865MB maintenance_work_mem = 1001121kB work_mem = 5005kB Is this okay? [(y)es/(s)kip/(q)uit]:

      timescaledb-tune erkennt automatisch den verfügbaren Speicher des Servers und berechnet empfohlene Werte für die Einstellungen shared_buffers, effective_cache_size, maintenance_work_mem und work_mem. Wenn Sie mehr über die Ausführung erfahren möchten, sehen Sie sich die GitHub-Seite für timescaledb-tune an.

      Wenn diese Einstellungen gut aussehen, geben Sie y ein:

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: memory settings will be updated

      Wenn Ihr Server über mehrere CPUs verfügt, finden Sie an dieser Stelle Empfehlungen für die Parallelitätseinstellungen. Wenn Sie jedoch nur eine CPU haben, wird timescaledb-tune Sie direkt zu den WAL-Einstellungen leiten.

      Bei mehreren CPUs werden in etwa folgende Empfehlungen angezeigt:

      Output

      Parallelism settings recommendations Current: missing: timescaledb.max_background_workers #max_worker_processes = 8 #max_parallel_workers_per_gather = 2 #max_parallel_workers = 8 Recommended: timescaledb.max_background_workers = 8 max_worker_processes = 15 max_parallel_workers_per_gather = 2 max_parallel_workers = 4 Is this okay? [(y)es/(s)kip/(q)uit]:

      Diese Einstellungen regulieren die Anzahl der Workers, die Anfragen und Hintergrundaufgaben verarbeiten. Mehr über diese Einstellungen können Sie der TimescaleDB- und der PostgreSQL-Dokumentation entnehmen.

      Geben Sie y und dann die EINGABETASTE ein, um diese Einstellungen zu akzeptieren:

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: parallelism settings will be updated

      Als Nächstes finden Sie Empfehlungen für Write Ahead Log (WAL):

      Output

      WAL settings recommendations Current: #wal_buffers = -1 #min_wal_size = 80MB #max_wal_size = 1GB Recommended: wal_buffers = 16MB min_wal_size = 4GB max_wal_size = 8GB Is this okay? [(y)es/(s)kip/(q)uit]:

      WAL bewahrt die Datenintegrität, aber die Standardeinstellungen können eine ineffiziente E/A verursachen, die die Schreibleistung verlangsamt. Geben Sie y ein, um diese Einstellungen zu optimieren:

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: WAL settings will be updated

      Sie finden nun verschiedene Empfehlungen:

      Output

      Miscellaneous settings recommendations Current: #default_statistics_target = 100 #random_page_cost = 4.0 #checkpoint_completion_target = 0.5 #max_locks_per_transaction = 64 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #effective_io_concurrency = 1 Recommended: default_statistics_target = 500 random_page_cost = 1.1 checkpoint_completion_target = 0.9 max_locks_per_transaction = 64 autovacuum_max_workers = 10 autovacuum_naptime = 10 effective_io_concurrency = 200 Is this okay? [(y)es/(s)kip/(q)uit]:

      Alle diese verschiedenen Parameter zielen auf eine Leistungssteigerung ab. Beispielsweise können SSDs viele gleichzeitige Anfragen verarbeiten, sodass der beste Wert für die effective_io_concurrency im Hunderterbereich liegen könnte. Weitere Informationen über diese Optionen finden Sie in der PostgreSQL-Dokumentation.

      Drücken Sie y und dann die EINGABETASTE, um fortzufahren.

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: miscellaneous settings will be updated Saving changes to: /var/lib/pgsql/11/data/postgresql.conf

      Als Ergebnis erhalten Sie eine gebrauchsfertige Konfigurationsdatei unter /var/lib/pgsql/11/data/postgresql.conf.

      Anmerkung: Wenn Sie die Installation von Grund auf neu durchführen, könnten Sie den anfänglichen Befehl auch mit den Flags --quiet und --yes ausführen, wodurch automatisch alle Empfehlungen angewendet und Änderungen an der Konfigurationsdatei postgresql.conf vorgenommen werden:

      • sudo timescaledb-tune --pg-config=/usr/pgsql-11/bin/pg_config --quiet --yes

      Damit die Konfigurationsänderungen wirksam werden, müssen Sie den Dienst PostgreSQL neu starten:

      • sudo systemctl restart postgresql-11.service

      Nun wird die Datenbank mit optimalen Parametern ausgeführt und ist bereit dazu, mit den Zeitreihendaten zu arbeiten. In den nächsten Schritten werden Sie das Arbeiten mit diesen Daten testen; Sie erstellen neue Datenbanken und Hypertabellen und führen Operationen aus.

      Schritt 3 – Erstellen einer neuen Datenbank und Hypertabelle

      Mit Ihrer optimierten TimescaleDB-Einrichtung sind Sie bereit, mit Zeitreihendaten zu arbeiten. TimescaleDB ist als eine Erweiterung von PostgreSQL implementiert, sodass sich Operationen mit Zeitreihendaten nicht wesentlich von relationalen Datenoperationen unterscheiden. Gleichzeitig erlaubt die Datenbank, Daten aus Zeitreihen und relationalen Tabellen in Zukunft frei zu kombinieren.

      Zuerst erstellen Sie eine neue Datenbank und aktivieren die TimescaleDB-Erweiterung dafür. Melden Sie sich in Ihrer PostgreSQL-Datenbank an:

      Erstellen Sie nun eine neue Datenbank und stellen Sie eine Verbindung zu ihr her. In diesem Tutorial heißt die Datenbank timeseries:

      • CREATE DATABASE timeseries;
      • c timeseries

      Weitere Informationen über die Arbeit mit der PostgreSQL-Datenbank finden Sie in unserem Tutorial So erstellen, entfernen und verwalten Sie Tabellen in PostgreSQL auf einem Cloud-Server.

      Aktivieren Sie zum Schluss die TimescaleDB-Erweiterung:

      • CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

      Sie sehen die folgende Ausgabe:

      Output

      WARNING: WELCOME TO _____ _ _ ____________ |_ _(_) | | | _ ___ | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ / | | | | _ ` _ / _ / __|/ __/ _` | |/ _ | | | ___ | | | | | | | | | __/__ (_| (_| | | __/ |/ /| |_/ / |_| |_|_| |_| |_|___||___/_____,_|_|___|___/ ____/ Running version 1.5.1 For more information on TimescaleDB, please visit the following links: 1. Getting started: https://docs.timescale.com/getting-started 2. API reference documentation: https://docs.timescale.com/api 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture Note: TimescaleDB collects anonymous reports to better understand and assist our users. For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry. CREATE EXTENSION

      Der primäre Interaktionspunkt mit Ihren Zeitreihendaten sind Hypertabellen, eine Abstraktion vieler einzelner Tabellen, die die Daten enthalten, die als Chunks bezeichnet werden.

      Um eine Hypertabelle zu erstellen, beginnen Sie mit einer regulären SQL-Tabelle und konvertieren diese dann mit der Funktion create_hypertable in eine Hypertabelle.

      Erstellen Sie eine Tabelle, die Daten zur Verfolgung von Temperatur und Feuchtigkeit für verschiedene Geräten im Zeitablauf speichert:

      • CREATE TABLE conditions (
      • time TIMESTAMP WITH TIME ZONE NOT NULL,
      • device_id TEXT,
      • temperature NUMERIC,
      • humidity NUMERIC
      • );

      Dieser Befehl erstellt eine Tabelle namens conditions mit vier Spalten. Die erste Spalte speichert den Zeitstempel, der die Zeitzone enthält und nicht leer sein darf. Als Nächstes verwenden Sie die Zeitspalte, um Ihre Tabelle in eine nach Zeit partionierte Hypertabelle umzuwandeln:

      • SELECT create_hypertable('conditions', 'time');

      Dieser Befehl ruft die Funktion create_hypertable() auf, die eine TimescaleDB-Hypertabelle aus einer PostgreSQL-Tabelle erstellt und diese ersetzt.

      Sie erhalten die folgende Ausgabe:

      Output

      create_hypertable ------------------------- (1,public,conditions,t) (1 row)

      In diesem Schritt haben Sie eine neue Hypertabelle zur Speicherung von Zeitreihendaten angelegt. Nun können Sie sie mit Daten füllen, indem Sie in die Hypertabelle schreiben und dann den Löschvorgang ausführen.

      Schritt 4 – Schreiben und Löschen von Daten

      In diesem Schritt fügen Sie Daten unter Verwendung von SQL-Standardbefehlen ein und importieren große Datensätze aus externen Quellen. Dies veranschaulicht Ihnen die relationalen Datenbankaspekte von TimescaleDB.

      Probieren Sie zunächst die grundlegenden Befehle aus. Daten können mit dem SQL-Standardbefehl INSERT in die Hypertabelle eingefügt werden. Fügen Sie mit dem folgenden Befehl einige Beispieldaten für Temperatur und Feuchtigkeit für das theoretische Gerät weather-pro-000000 ein:

      • INSERT INTO conditions(time, device_id, temperature, humidity)
      • VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);

      Sie erhalten die folgende Ausgabe:

      Output

      INSERT 0 1

      Sie können auch mehrere Datenreihen gleichzeitig einfügen. Probieren Sie Folgendes aus:

      • INSERT INTO conditions
      • VALUES
      • (NOW(), 'weather-pro-000002', 71.0, 51.0),
      • (NOW(), 'weather-pro-000003', 70.5, 50.5),
      • (NOW(), 'weather-pro-000004', 70.0, 50.2);

      Sie erhalten Folgendes:

      Output

      INSERT 0 3

      Sie können auch festlegen, dass der Befehl INSERT einige oder alle eingefügten Daten mit der Anweisung RETURNING zurückgibt:

      • INSERT INTO conditions
      • VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;

      Sie sehen die folgende Ausgabe:

      Output

      time | device_id | temperature | humidity -------------------------------+--------------------+-------------+---------- 2019-09-15 14:14:01.576651+00 | weather-pro-000002 | 70.1 | 50.1 (1 row)

      Wenn Sie Daten aus der Hypertabelle löschen möchten, verwenden Sie den SQL-Standardbefehl DELETE. Führen Sie Folgendes aus, um alle Daten zu löschen, die eine Temperatur von mehr als 80 oder eine Feuchtigkeit von mehr als 50 haben:

      • DELETE FROM conditions WHERE temperature > 80;
      • DELETE FROM conditions WHERE humidity > 50;

      Nach dem Löschvorgang wird empfohlen, den Befehl VACUUM zu verwenden, der den von den gelöschten Daten noch genutzten Speicherplatz zurückgewinnt.

      Weitere Informationen über den Befehl VACUUM finden Sie in der PostgreSQL-Dokumentation.

      Diese Befehle eignen sich gut für die Dateneingabe in kleinem Maßstab, aber da Zeitreihendaten oft riesige Datensätze von mehreren Geräten gleichzeitig generieren, ist es auch wichtig zu wissen, wie gleichzeitig Hunderte oder Tausende von Zeilen eingefügt werden. Wenn Sie Daten aus externen Quellen in strukturierter Form, z. B. im Format csv, erstellt haben, kann diese Aufgabe schnell erledigt werden.

      Um dies auszuprobieren, verwenden Sie einen Beispieldatensatz, der Temperatur- und Feuchtigkeitsdaten von verschiedenen Orten darstellt. Er wurde von den Entwicklern der TimescaleDB erstellt, damit Sie ihre Datenbank testen können. Weitere Informationen über Beispieldatensätze finden Sie in der TimescaleDB-Dokumentation.

      Sehen wir uns an, wie Sie Daten aus dem Beispieldatensatz weather_small in Ihre Datenbank importieren können. Beenden Sie zunächst Postgresql:

      Laden Sie dann den Datensatz herunter und extrahieren Sie ihn:

      • cd /tmp
      • curl https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz -o weather_small.tar.gz
      • tar -xvzf weather_small.tar.gz

      Als Nächstes importieren Sie die Temperatur- und Feuchtigkeitsdaten in Ihre Datenbank:

      • sudo -u postgres psql -d timeseries -c "COPY conditions FROM weather_small_conditions.csv CSV"

      Damit wird eine Verbindung mit der Datenbank timeseries hergestellt und der Befehl COPY ausgeführt, der die Daten aus der gewählten Datei in die Hypertabelle conditions kopiert. Er wird einige Sekunden lang ausgeführt.

      Nachdem die Daten in Ihre Tabelle eingegeben wurden, erhalten Sie die folgende Ausgabe:

      Output

      COPY 1000000

      In diesem Schritt haben Sie Daten manuell und in Stapeln in die Hypertabelle eingefügt. Als Nächstes fahren Sie mit der Durchführung von Abfragen fort.

      Schritt 5 – Abfrage von Daten

      Nachdem Ihre Tabelle nun Daten enthält, können Sie verschiedene Abfragen durchführen, um sie zu analysieren.

      Um zu beginnen, melden Sie sich in der Datenbank an:

      • sudo -u postgres psql -d timeseries

      Wie bereits erwähnt, können Sie für die Arbeit mit Hypertabellen SQL-Standardbefehle verwenden. Um beispielsweise die letzten 10 Einträge aus der Hypertabelle conditions anzuzeigen, führen Sie den folgenden Befehl aus:

      • SELECT * FROM conditions LIMIT 10;

      Sie sehen die folgende Ausgabe:

      Output

      time | device_id | temperature | humidity ------------------------+--------------------+--------------------+---------- 2016-11-15 12:00:00+00 | weather-pro-000000 | 39.9 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000001 | 32.4 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 | 50.2 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000004 | 71.8 | 50.1 2016-11-15 12:00:00+00 | weather-pro-000005 | 71.8 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000006 | 37 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000007 | 72 | 50 2016-11-15 12:00:00+00 | weather-pro-000008 | 31.3 | 50 2016-11-15 12:00:00+00 | weather-pro-000009 | 84.4 | 87.8 (10 rows)

      Mit diesem Befehl können Sie sehen, welche Daten sich in der Datenbank befinden. Da die Datenbank eine Million Datensätze enthält, haben Sie mit LIMIT 10 die Ausgabe auf 10 Einträge beschränkt.

      Um die letzten Einträge zu sehen, sortieren Sie das Datenarray in absteigender Reihenfolge nach der Zeit:

      • SELECT * FROM conditions ORDER BY time DESC LIMIT 20;

      Dadurch werden die 20 neuesten Einträge ausgegeben.

      Sie können auch einen Filter hinzufügen. Um beispielsweise Einträge von dem Gerät weather-pro-000000 anzuzeigen, führen Sie Folgendes aus:

      • SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;

      In diesem Fall sehen Sie die 10 neuesten Temperatur- und Feuchtigkeitsdatenpunkte, die von dem Gerät weather-pro-000000 aufgezeichnet wurden.

      Zusätzlich zu den SQL-Standardbefehlen bietet die TimescaleDB auch eine Reihe spezieller Funktionen, die für die Analyse von Zeitreihendaten nützlich sind. Um beispielsweise den Mittelwert der Temperaturwerte zu finden, können Sie die folgende Abfrage mit der Funktion percentile_cont verwenden:

      • SELECT percentile_cont(0.5)
      • WITHIN GROUP (ORDER BY temperature)
      • FROM conditions
      • WHERE device_id = 'weather-pro-000000';

      Sie sehen die folgende Ausgabe:

      Output

      percentile_cont ----------------- 40.5 (1 row)

      Auf diese Weise sehen Sie den Medianwert der Temperatur für den gesamten Beobachtungszeitraum des Ortes, an dem sich der Sensor weather-pro-00000 befindet.

      Um die neuesten Werte von jedem der Sensoren anzuzeigen, können Sie die Funktion last verwenden:

      • select device_id, last(temperature, time)
      • FROM conditions
      • GROUP BY device_id;

      In der Ausgabe sehen Sie eine Liste aller Sensoren und der relevanten neuesten Werte.

      Um die ersten Werte zu erhalten, verwenden Sie die Funktion first.

      Das folgende Beispiel ist komplexer. Es zeigt die stündlichen Durchschnitts-, Minimal- und Maximaltemperaturen für den gewählten Sensor innerhalb der letzten 24 Stunden:

      • SELECT time_bucket('1 hour', time) "hour",
      • trunc(avg(temperature), 2) avg_temp,
      • trunc(min(temperature), 2) min_temp,
      • trunc(max(temperature), 2) max_temp
      • FROM conditions
      • WHERE device_id = 'weather-pro-000000'
      • GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;

      Hier haben Sie die Funktion time_bucket verwendet, die als eine leistungsfähigere Version der PostgreSQL-Funktion date_trunc fungiert. Als Ergebnis sehen Sie, in welchen Tagesabschnitten die Temperatur steigt oder fällt:

      Output

      hour | avg_temp | min_temp | max_temp ------------------------+----------+----------+---------- 2016-11-16 21:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-16 20:00:00+00 | 41.92 | 41.69 | 42.00 2016-11-16 19:00:00+00 | 41.07 | 40.59 | 41.59 2016-11-16 18:00:00+00 | 40.11 | 39.79 | 40.59 2016-11-16 17:00:00+00 | 39.46 | 38.99 | 39.79 2016-11-16 16:00:00+00 | 38.54 | 38.19 | 38.99 2016-11-16 15:00:00+00 | 37.56 | 37.09 | 38.09 2016-11-16 14:00:00+00 | 36.62 | 36.39 | 37.09 2016-11-16 13:00:00+00 | 35.59 | 34.79 | 36.29 2016-11-16 12:00:00+00 | 34.59 | 34.19 | 34.79 2016-11-16 11:00:00+00 | 33.94 | 33.49 | 34.19 2016-11-16 10:00:00+00 | 33.27 | 32.79 | 33.39 2016-11-16 09:00:00+00 | 33.37 | 32.69 | 34.09 2016-11-16 08:00:00+00 | 34.94 | 34.19 | 35.49 2016-11-16 07:00:00+00 | 36.12 | 35.49 | 36.69 2016-11-16 06:00:00+00 | 37.02 | 36.69 | 37.49 2016-11-16 05:00:00+00 | 38.05 | 37.49 | 38.39 2016-11-16 04:00:00+00 | 38.71 | 38.39 | 39.19 2016-11-16 03:00:00+00 | 39.72 | 39.19 | 40.19 2016-11-16 02:00:00+00 | 40.67 | 40.29 | 40.99 2016-11-16 01:00:00+00 | 41.63 | 40.99 | 42.00 2016-11-16 00:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 23:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 22:00:00+00 | 42.00 | 42.00 | 42.00 (24 rows)

      Weitere nützliche Funktionen finden Sie in der TimescaleDB-Dokumentation.

      Nun wissen Sie, wie Sie mit Ihren Daten umgehen können. Als Nächstes löschen Sie unnötige Daten und komprimieren Daten.

      Schritt 6 – Konfigurieren der Datenkomprimierung und -löschung

      Wenn sich Daten ansammeln, nehmen sie immer mehr Platz auf Ihrer Festplatte ein. Um Platz zu sparen, bietet die neueste Version der TimescaleDB eine Datenkomprimierungsfunktion. Diese Funktion erfordert keine Anpassung der Dateisystemeinstellungen und kann dazu verwendet werden, Ihre Datenbank schnell effizienter zu gestalten. Weitere Informationen über die Funktionsweise dieser Komprimierung finden Sie in diesem Artikel zur Komprimierung von TimescaleDB.

      Aktivieren Sie zunächst die Komprimierung Ihrer Hypertabelle:

      • ALTER TABLE conditions SET (
      • timescaledb.compress,
      • timescaledb.compress_segmentby = 'device_id'
      • );

      Sie erhalten die folgenden Ausgabedaten:

      Output

      NOTICE: adding index _compressed_hypertable_2_device_id__ts_meta_sequence_num_idx ON _timescaledb_internal._compressed_hypertable_2 USING BTREE(device_id, _ts_meta_sequence_num) ALTER TABLE

      Anmerkung: Sie können TimescaleDB auch einrichten, damit Daten über den angegebenen Zeitraum komprimiert werden. Beispielsweise können Sie Folgendes ausführen:

      • SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');

      In diesem Beispiel werden die Daten nach einer Woche automatisch komprimiert.

      Sie können die Statistiken der komprimierten Daten mit folgendem Befehl anzeigen:

      • SELECT *
      • FROM timescaledb_information.compressed_chunk_stats;

      Sie sehen dann eine Liste von Chunks mit ihrem Status: Komprimierungsstatus und wie viel Speicherplatz unkomprimierte und komprimierte Daten in Byte belegen.

      Wenn es nicht notwendig ist, Daten über einen längeren Zeitraum zu speichern, können Sie veraltete Daten löschen, um noch mehr Speicherplatz freizugeben. Dafür gibt es eine spezielle Funktion drop_chunks. Sie ermöglicht die Löschung von Chunks mit Daten, die älter als die angegebene Zeit sind:

      • SELECT drop_chunks(interval '24 hours', 'conditions');

      Diese Abfrage löscht alle Chunks aus der Hypertabelle conditions, die nur Daten enthalten, die älter als ein Tag sind.

      Sie erhalten die folgende Ausgabe:

      Output

      drop_chunks ---------------------------------------- _timescaledb_internal._hyper_1_2_chunk (1 row)

      Um alte Daten automatisch zu löschen, können Sie eine Cron-Aufgabe konfigurieren. In unserem Tutorial erfahren Sie mehr über die Verwendung von Cron zur Automatisierung verschiedener Systemaufgaben.

      Verlassen Sie die Datenbank:

      Bearbeiten Sie als Nächstes crontab mit dem folgenden Befehl, der von der Shell aus ausgeführt werden sollte:

      Fügen Sie nun die folgende Zeile dem Ende der Datei hinzu:

      crontab

      ...
      
      0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1
      

      Dieser Job löscht jeden Tag um 1:00 Uhr veraltete Daten, die älter als ein Tag sind.

      Zusammenfassung

      Sie haben nun TimescaleDB auf Ihrem CentOS-Server eingerichtet. Sie haben auch das Erstellen von Hypertabellen, das Einfügen von Daten in diese, das Abfragen der Daten, das Komprimieren und Löschen unnötiger Datensätze ausprobiert. Mit diesen Beispielen können Sie die wichtigsten Vorteile von TimescaleDB gegenüber traditionellen relationalen Datenbankverwaltungssystemen für die Speicherung von Zeitreihendaten nutzen, u. a. durch:

      • Höhere Datenaufnahmeraten
      • Schnellere Abfrageleistung
      • Zeitorientierte Funktionen



      Source link

      Comment installer et utiliser TimescaleDB sur Ubuntu 18.04


      L’auteur a choisi que le Computer History Museum recevrait une donation dans le cadre du programme Write for Donations.

      Introduction

      De nombreuses apps, telles que les systèmes de surveillance et les systèmes de collecte de données, permettent d’accumuler des données en vue d’une analyse plus approfondie. Ces analyses portent souvent sur la façon dont une donnée ou un système évolue dans le temps. Dans ces cas, les données sont représentées sous la forme d’une série chronologique, chaque point de données étant accompagné d’un horodatage. Voici un exemple :

      2019-11-01 09:00:00    server.cpu.1    0.9
      2019-11-01 09:00:00    server.cpu.15   0.8
      2019-11-01 09:01:00    server.cpu.1    0.9
      2019-11-01 09:01:00    server.cpu.15   0.8
      ...
      

      La gestion des données de séries chronologiques est devenue une compétence essentielle avec l’essor de l’Internet des Objets (IdO) et de l’internet industriel des objets. Il existe de plus en plus d’appareils qui recueillent diverses informations sur les séries chronologiques : traqueurs de forme physique, montres intelligentes, stations météo domestiques et divers capteurs, pour n’en citer que quelques-uns. Ces appareils collectent beaucoup d’informations, et toutes ces données doivent être stockées quelque part.

      Les bases de données relationnelles classiques sont le plus souvent utilisées pour stocker des données, mais elles ne conviennent pas toujours lorsqu’il s’agit des énormes volumes de données des séries chronologiques. Lorsque vous devez traiter une grande quantité de données de séries chronologiques, les bases de données relationnelles peuvent être trop lentes. C’est pourquoi des bases de données spécialement optimisées, appelées bases de données NoSQL, ont été créées pour éviter les problèmes des bases de données relationnelles.

      TimescaleDB est une base de données open source optimisée pour le stockage de données de séries chronologiques. Elle est implémentée comme extension de PostgreSQL et combine la facilité d’utilisation des bases de données relationnelles et la rapidité des bases de données NoSQL. Par conséquent, elle vous permet d’utiliser PostgreSQL à la fois pour stocker des données commerciales et des données de séries chronologiques en un seul endroit.

      En suivant ce tutoriel, vous allez installer TimescaleDB sur Ubuntu 18.04, le configurer et apprendre à travailler avec lui. Vous créerez des bases de données de séries chronologiques et effectuerez des requêtes simples. Enfin, vous verrez comment vous débarrasser des données inutiles.

      Conditions préalables

      Pour suivre ce tutoriel, vous aurez besoin de :

      Étape 1 – Installation de TimescaleDB

      Timesc3aleDB n’est pas disponible dans les référentiels par défaut d’Ubuntu, vous l’installerez donc dans cette étape depuis l’archive TimescaleDB Personal Packages (PPA).

      Tout d’abord, ajoutez le référentiel APT de Timescale :

      • sudo add-apt-repository ppa:timescale/timescaledb-ppa

      Confirmez cette action en appuyant sur la touche ENTER.

      Ensuite, rafraîchissez votre cache APT pour mettre à jour vos listes de paquets :

      Vous pouvez maintenant passer à l’installation. Ce tutoriel utilise PostgreSQL version 10 ; si vous utilisez une version différente de PostgreSQL (11 ou 9.6, par exemple), remplacez la valeur dans la commande suivante et exécutez-la :

      • sudo apt install timescaledb-postgresql-10

      TimescaleDB est maintenant installé et prêt à être utilisé. Ensuite, vous allez l’activer et ajuster certains des paramètres qui lui sont associés dans le fichier de configuration PostgreSQL afin d’optimiser la base de données.

      Étape 2 – Configuration de TimescaleDB

      Le module TimescaleDB fonctionne bien avec les paramètres de configuration par défaut de PostgreSQL, mais pour améliorer les performances et faire un meilleur usage des ressources du processeur, de la mémoire et du disque, les développeurs de TimescaleDB suggèrent de configurer certains paramètres individuels. Cette configuration peut être effectuée automatiquement avec l’outil timescaledb-tune ou en éditant manuellement le fichier postgresql.conf de votre serveur.

      Dans ce tutoriel, vous utiliserez l’outil timescaledb-tune, qui lira le fichier postgresql.conf et vous proposera de manière interactive d’effectuer des changements.

      Exécutez la commande suivante pour démarrer l’assistant de configuration :

      Tout d’abord, il vous sera demandé de confirmer le chemin d’accès au fichier de configuration PostgreSQL :

      Output

      Using postgresql.conf at this path: /etc/postgresql/10/main/postgresql.conf Is this correct? [(y)es/(n)o]:

      L’utilitaire détecte automatiquement le chemin d’accès au fichier de configuration, il faut donc le confirmer en entrant y :

      Output

      ... Is this correct? [(y)es/(n)o]: y Writing backup to: /tmp/timescaledb_tune.backup201911181111

      Ensuite, vous serez invité à changer la variable shared_preload_libraries pour précharger le module TimescaleDB lors du démarrage du serveur PostgreSQL :

      Output

      shared_preload_libraries needs to be updated Current: #shared_preload_libraries = '' Recommended: shared_preload_libraries = 'timescaledb' Is this okay? [(y)es/(n)o]:

      shared_preload_libraries accepte comme valeur une liste de modules séparés par des virgules, en désignant quels modules PostgreSQL doit charger avant de démarrer le serveur de base de données. Ce changement ajoutera le module timescaledb à cette liste.

      Remarque : si une bibliothèque spécifiée par shared_preload_libraries est introuvable, le serveur de la base de données ne se lancera pas. Gardez cela en tête lors du débogage des apps qui font usage de shared_preload_libraries. Pour plus d’informations a ce propos, consultez cet article PostgresqlCO.NF sur shared_preload_libraries.

      Activez le module TimescaleDB en tapant y dans cette invite et en appuyant sur ENTER :

      Output

      ... Is this okay? [(y)es/(n)o]: y success: shared_preload_libraries will be updated

      En fonction des caractéristiques de votre serveur et de la version de PostgreSQL, le script vous proposera alors de régler vos paramètres. Appuyez sur y pour lancer le processus de réglage :

      Output

      Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y Recommendations based on 7.79 GB of available memory and 4 CPUs for PostgreSQL 10 Memory settings recommendations Current: shared_buffers = 128MB #effective_cache_size = 4GB #maintenance_work_mem = 64MB #work_mem = 4MB Recommended: shared_buffers = 1994MB effective_cache_size = 5982MB maintenance_work_mem = 1021001kB work_mem = 5105kB Is this okay? [(y)es/(s)kip/(q)uit]:

      timescaledb-tune va automatiquement détecter la mémoire disponible des serveurs et calculer les valeurs recommandées pour un certain nombre de paramètres. shared_buffers, par exemple, détermine la quantité de mémoire allouée pour la mise en cache des données. Par défaut, ce paramètre est relativement faible pour tenir compte d’un plus grand nombre de plates-formes, c’est pourquoi timescaledb-tune a suggéré d’augmenter la valeur de 128 Mo à 1994 Mo, afin de mieux exploiter les ressources en faisant plus de place pour stocker les informations mises en cache, comme les requêtes répétées. La variable work_mem a également été augmentée pour permettre des tris plus complexes.

      Si vous souhaitez en savoir plus sur le processus de réglage des paramètres de mémoire pour PostgreSQL, consultez l’article Tuning Your PostgreSQL Server sur le wiki de PostgreSQL.

      Entrez y pour accepter les valeurs :

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: memory settings will be updated

      À ce stade, si votre serveur dispose de plusieurs processeurs, vous trouverez les recommandations pour les paramètres de parallélisme. Ces paramètres déterminent comment plusieurs processeurs peuvent effectuer des requêtes simultanées en parallèle afin d’analyser les bases de données et de renvoyer les données demandées plus rapidement.

      Ceux qui disposent de plusieurs processeurs rencontreront des recommandations de ce type :

      Output

      Parallelism settings recommendations Current: missing: timescaledb.max_background_workers #max_worker_processes = 8 #max_parallel_workers_per_gather = 2 #max_parallel_workers = 8 Recommended: timescaledb.max_background_workers = 8 max_worker_processes = 13 max_parallel_workers_per_gather = 1 max_parallel_workers = 2 Is this okay? [(y)es/(s)kip/(q)uit]:

      Ces paramètres régulent le nombre de travailleurs qui traitent les demandes et les tâches de fond. Vous pouvez en apprendre davantage sur ces paramètres grâce à la documentation TimescaleDB et PostgreSQL.

      Tapez y et ENTER pour accepter ces paramètres.

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: parallelism settings will be updated

      Ensuite, vous trouverez des recommandations pour Write Ahead Log (WAL) :

      Output

      WAL settings recommendations Current: #wal_buffers = -1 #min_wal_size = 80MB #max_wal_size = 1GB Recommended: wal_buffers = 16MB min_wal_size = 4GB max_wal_size = 8GB Is this okay? [(y)es/(s)kip/(q)uit]:

      WAL est une méthode de connexion dans laquelle PostgreSQL enregistre les modifications apportées aux fichiers de données avant que les modifications ne soient apportées à la base de données. En accordant la priorité à un enregistrement à jour des changements de données, WAL vous permet de reconstruire votre base de données en cas de crash. De cette manière, il préserve l’intégrité des données. Cependant, les paramètres par défaut peuvent entraîner des opérations d’entrée/sortie (E/S) inefficaces qui ralentissent les performances d’écriture. Pour remédier à cela, tapez et entrez y :

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: WAL settings will be updated

      Vous trouverez quelques recommandations diverses :

      Output

      Miscellaneous settings recommendations Current: #default_statistics_target = 100 #random_page_cost = 4.0 #checkpoint_completion_target = 0.5 #max_locks_per_transaction = 64 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #effective_io_concurrency = 1 Recommended: default_statistics_target = 500 random_page_cost = 1.1 checkpoint_completion_target = 0.9 max_locks_per_transaction = 64 autovacuum_max_workers = 10 autovacuum_naptime = 10 effective_io_concurrency = 200 Is this okay? [(y)es/(s)kip/(q)uit]:

      Tous ces paramètres visent à accroître les performances. Par exemple, les DSS peuvent traiter de nombreuses demandes simultanées, de sorte que la meilleure valeur pour la effective_io_concurrency pourrait se situer dans les centaines. Vous pouvez trouver plus d’informations sur ces options dans la documentation PostgreSQL.

      Appuyez sur y puis sur ENTER pour continuer.

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: miscellaneous settings will be updated Saving changes to: /etc/postgresql/10/main/postgresql.conf

      En conséquence, vous obtiendrez un fichier de configuration prêt à l’emploi à l’adresse /etc/postgresql/10/main/postgresql.conf​​​​​​.

      Remarque :Si vous automatisez l’installation, vous pouvez également lancer la commande initiale avec les drapeaux --quiet et --yes, qui appliqueront automatiquement toutes les recommandations et apporteront des modifications au fichier de configuration postgresql.conf :

      • sudo timescaledb-tune --quiet --yes

      Pour que les changements de configuration prennent effet, vous devez redémarrer le service PostgreSQL :

      • sudo systemctl restart postgresql.service

      La base de données fonctionne désormais avec des paramètres optimaux et est prête à travailler avec les données des séries chronologiques. Dans les étapes suivantes, vous allez essayer de travailler avec ces données : créer de nouvelles bases de données et de nouvelles hypertables et effectuer des opérations.

      Étape 3 – Création d’une nouvelle base de données et d’une hypertable

      Grâce à l’optimisation de la configuration de votre TimecaleDB, vous êtes prêt à travailler avec des données de séries chronologiques. TimescaleDB est implémenté comme une extension de PostgreSQL, de sorte que les opérations avec des données de séries chronologiques ne sont pas très différentes des opérations avec des données relationnelles. En même temps, la base de données vous permet de combiner librement les données des séries chronologiques et des tables relationnelles à l’avenir.

      Pour le démontrer, vous utiliserez les commandes PostgreSQL pour créer une base de données, puis vous activerez l’extension TimescaleDB pour créer une hypertable, qui est une abstraction de haut niveau de nombreuses tables individuelles. Les hypertables sont les structures principales avec lesquelles vous allez travailler dans TimescaleDB.

      Connectez-vous à votre base de données PostgreSQL :

      Créez maintenant une nouvelle base de données et connectez-vous à celle-ci. Ce tutoriel appellera la base de données timeseries :

      • CREATE DATABASE timeseries;
      • c timeseries

      Vous pouvez trouver des informations supplémentaires sur le fonctionnement d’une base de données PostgreSQL dans notre tutoriel Comment créer, supprimer et gérer des tables dans PostgreSQL sur un serveur Cloud.

      Enfin, activez l’extension TimescaleDB :

      • CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

      Vous verrez le résultat suivant :

      Output

      WARNING: WELCOME TO _____ _ _ ____________ |_ _(_) | | | _ ___ | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ / | | | | _ ` _ / _ / __|/ __/ _` | |/ _ | | | ___ | | | | | | | | | __/__ (_| (_| | | __/ |/ /| |_/ / |_| |_|_| |_| |_|___||___/_____,_|_|___|___/ ____/ Running version 1.5.1 For more information on TimescaleDB, please visit the following links: 1. Getting started: https://docs.timescale.com/getting-started 2. API reference documentation: https://docs.timescale.com/api 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture Note: TimescaleDB collects anonymous reports to better understand and assist our users. For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry. CREATE EXTENSION

      Comme mentionné précédemment, les principaux points d’interaction avec vos données de séries chronologiques sont les hypertables, qui consistent en de nombreuses tables individuelles contenant des données, appelées chunks

      Pour créer une hypertable, commencez par une table SQL normale et convertissez-la ensuite en hypertable via la fonction create_hypertable.

      Créez une table qui permettra de stocker des données pour suivre la température et l’humidité dans un ensemble d’appareils au fil du temps :

      • CREATE TABLE conditions (
      • time TIMESTAMP WITH TIME ZONE NOT NULL,
      • device_id TEXT,
      • temperature NUMERIC,
      • humidity NUMERIC
      • );

      Cette commande crée une table appelée conditions contenant quatre colonnes. La première colonne enregistrera l’horodatage, qui comprend le fuseau horaire et ne peut être vide. Ensuite, vous utiliserez la colonne temporelle pour transformer votre table en une hypertable qui est partitionnée par le temps :

      • SELECT create_hypertable('conditions', 'time');

      Cette commande appelle la fonction create_hypertable(), qui crée une hyper table TimescaleDB à partir d’une table PostgreSQL en remplaçant cette dernière.

      Vous recevrez le résultat suivant :

      Output

      create_hypertable ------------------------- (1,public,conditions,t) (1 row)

      Dans cette étape, vous avez créé une nouvelle hypertable pour stocker des données de séries chronologiques. Vous pouvez maintenant la remplir de données en écrivant dans l’hypertable, puis en la supprimant.

      Étape 4 – Rédaction et suppression des données

      Au cours de cette étape, vous insérerez des données à l’aide de commandes SQL standard et importerez de grands ensembles de données provenant de sources externes. Cela vous montrera les aspects de la base de données relationnelle de TimescaleDB.

      Tout d’abord, essayez les commandes de base. Vous pouvez insérer des données dans l’hypertable en utilisant la commande SQL standard INSERT. Insérez quelques échantillons de données sur la température et l’humidité pour l’appareil théorique, weather-pro-000000, en utilisant la commande suivante :

      • INSERT INTO conditions(time, device_id, temperature, humidity)
      • VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);

      Vous verrez la sortie suivante :

      Output

      INSERT 0 1

      Vous pouvez également insérer plusieurs lignes de données à la fois. Essayez ce qui suit :

      • INSERT INTO conditions
      • VALUES
      • (NOW(), 'weather-pro-000002', 71.0, 51.0),
      • (NOW(), 'weather-pro-000003', 70.5, 50.5),
      • (NOW(), 'weather-pro-000004', 70.0, 50.2);

      Vous recevrez ce qui suit :

      Output

      INSERT 0 3

      Vous pouvez également spécifier que la commande INSERT renverra une partie ou la totalité des données insérées en utilisant l’énoncé RETURNING :

      • INSERT INTO conditions
      • VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;

      Vous verrez le résultat suivant :

      Output

      time | device_id | temperature | humidity -------------------------------+--------------------+-------------+---------- 2019-09-15 14:14:01.576651+00 | weather-pro-000002 | 70.1 | 50.1 (1 row)

      Si vous souhaitez supprimer des données de l’hypertable, utilisez la commande SQL standard DELETE. Exécutez la procédure suivante pour supprimer toute donnée dont la température est supérieure à 80 ou l'humidité supérieure à 50.

      • DELETE FROM conditions WHERE temperature > 80;
      • DELETE FROM conditions WHERE humidity > 50;

      Après l’opération de suppression, utilisez la commande VACUUM pour récupérer l’espace encore utilisé par les données qui ont été supprimées.

      Vous pouvez trouver plus d’informations sur la commande VACUUM dans la documentation PostgreSQL.

      Ces commandes conviennent parfaitement à la saisie de données à petite échelle, mais comme les données de séries chronologiques génèrent souvent d’énormes ensembles de données provenant de plusieurs appareils simultanément, il est également essentiel de savoir comment insérer des centaines ou des milliers de lignes à la fois. Si vous avez préparé des données provenant de sources externes sous une forme structurée, par exemple au format cvs, cette tâche peut être accomplie rapidement.

      Pour tester la procédure, vous utiliserez un échantillon de données qui représente des données de température et d’humidité provenant de divers endroits. Il s’agit d’un ensemble de données officiel de TimescaleDB, créé pour tester leur base de données. Vous pouvez consulter davantage d’informations sur les échantillons de fichiers de données dans la documentation TimescaleDB.

      Voyons comment vous pouvez importer les données provenant de l’échantillon de données weather_small sample dans votre base de données. Tout d’abord, quittez Postgresql :

      Ensuite, téléchargez l’ensemble de données et extrayez-le :

      • wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz
      • tar -xvzf weather_small.tar.gz

      Puis, importez les données de température et d’humidité dans votre base de données :

      • sudo -u postgres psql -d timeseries -c "COPY conditions FROM weather_small_conditions.csv CSV"

      Cette opération vous permet de vous connecter à la base de données timeseries et d’exécuter la commande COPY qui copie les données du fichier choisi dans l’hypertable des conditions. Elle s’exécutera pendant quelques secondes.

      Lorsque les données ont été saisies dans votre tableau, vous obtenez le résultat suivant :

      Output

      COPY 1000000

      Au cours de cette étape, vous avez ajouté des données à l’hypertable manuellement et par lots. Ensuite, continuez à effectuer des requêtes.

      Étape 5 – Interrogation des données

      Maintenant que votre table contient des données, vous pouvez effectuer diverses requêtes pour l’analyser.

      Pour commencer, connectez-vous à la base de données :

      • sudo -u postgres psql -d timeseries

      Comme mentionné précédemment, pour travailler avec les hypertables, vous pouvez utiliser des commandes SQL standard. Par exemple, pour afficher les 10 dernières entrées de l’hypertable des conditions, exécutez la commande suivante.

      • SELECT * FROM conditions LIMIT 10;

      Vous verrez le résultat suivant :

      Output

      time | device_id | temperature | humidity ------------------------+--------------------+--------------------+---------- 2016-11-15 12:00:00+00 | weather-pro-000000 | 39.9 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000001 | 32.4 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 | 50.2 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000004 | 71.8 | 50.1 2016-11-15 12:00:00+00 | weather-pro-000005 | 71.8 | 49.9 2016-11-15 12:00:00+00 | weather-pro-000006 | 37 | 49.8 2016-11-15 12:00:00+00 | weather-pro-000007 | 72 | 50 2016-11-15 12:00:00+00 | weather-pro-000008 | 31.3 | 50 2016-11-15 12:00:00+00 | weather-pro-000009 | 84.4 | 87.8 (10 rows)

      Cette commande vous permet de voir quelles données se trouvent dans la base de données. Comme la base de données contient un million d’enregistrements, vous avez utilisé LIMIT 10 pour limiter la sortie à 10 entrées.

      Pour voir les entrées les plus récentes, triez la table de données par ordre décroissant dans le temps :

      • SELECT * FROM conditions ORDER BY time DESC LIMIT 20;

      Cela permettra d’obtenir les 20 entrées les plus récentes.

      Vous pouvez également ajouter un filtre. Par exemple, pour voir les entrées de l’appareil weather-pro-000000, exécutez la commande suivante :

      • SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;

      Dans ce cas, vous verrez les 10 points de données de température et d’humidité les plus récents enregistrés par l’appareil weather-pro-000000.

      En plus des commandes SQL standard, TimescaleDB fournit également un certain nombre de fonctions spéciales qui sont utiles pour l’analyse des données des séries chronologiques. Par exemple, pour trouver la médiane des valeurs de température, vous pouvez utiliser la requête suivante avec la fonction percentile_cont.

      • SELECT percentile_cont(0.5)
      • WITHIN GROUP (ORDER BY temperature)
      • FROM conditions
      • WHERE device_id = 'weather-pro-000000';

      Vous verrez le résultat suivant :

      Output

      percentile_cont ----------------- 40.5 (1 row)

      De cette façon, vous verrez la température médiane du lieu où se trouve le capteur weather-pro-00000 pour toute la période d’observation.

      Pour afficher les dernières valeurs de chacun des capteurs, vous pouvez utiliser la fonction last :

      • select device_id, last(temperature, time)
      • FROM conditions
      • GROUP BY device_id;

      Dans la sortie, vous verrez une liste de tous les capteurs et les dernières valeurs pertinentes.

      Pour obtenir les premières valeurs, utilisez la fonction first.

      L’exemple suivant est plus complexe. Il indiquera les températures horaires moyennes, minimales et maximales pour le capteur choisi au cours des dernières 24 heures :

      • SELECT time_bucket('1 hour', time) "hour",
      • trunc(avg(temperature), 2) avg_temp,
      • trunc(min(temperature), 2) min_temp,
      • trunc(max(temperature), 2) max_temp
      • FROM conditions
      • WHERE device_id = 'weather-pro-000000'
      • GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;

      Ici, vous avez utilisé la fonction time_bucket, qui agit comme une version plus puissante de la fonction date_trunc de PostgreSQL. Vous verrez ainsi à quelles périodes de la journée la température augmente ou diminue :

      Output

      hour | avg_temp | min_temp | max_temp ------------------------+----------+----------+---------- 2016-11-16 21:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-16 20:00:00+00 | 41.92 | 41.69 | 42.00 2016-11-16 19:00:00+00 | 41.07 | 40.59 | 41.59 2016-11-16 18:00:00+00 | 40.11 | 39.79 | 40.59 2016-11-16 17:00:00+00 | 39.46 | 38.99 | 39.79 2016-11-16 16:00:00+00 | 38.54 | 38.19 | 38.99 2016-11-16 15:00:00+00 | 37.56 | 37.09 | 38.09 2016-11-16 14:00:00+00 | 36.62 | 36.39 | 37.09 2016-11-16 13:00:00+00 | 35.59 | 34.79 | 36.29 2016-11-16 12:00:00+00 | 34.59 | 34.19 | 34.79 2016-11-16 11:00:00+00 | 33.94 | 33.49 | 34.19 2016-11-16 10:00:00+00 | 33.27 | 32.79 | 33.39 2016-11-16 09:00:00+00 | 33.37 | 32.69 | 34.09 2016-11-16 08:00:00+00 | 34.94 | 34.19 | 35.49 2016-11-16 07:00:00+00 | 36.12 | 35.49 | 36.69 2016-11-16 06:00:00+00 | 37.02 | 36.69 | 37.49 2016-11-16 05:00:00+00 | 38.05 | 37.49 | 38.39 2016-11-16 04:00:00+00 | 38.71 | 38.39 | 39.19 2016-11-16 03:00:00+00 | 39.72 | 39.19 | 40.19 2016-11-16 02:00:00+00 | 40.67 | 40.29 | 40.99 2016-11-16 01:00:00+00 | 41.63 | 40.99 | 42.00 2016-11-16 00:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 23:00:00+00 | 42.00 | 42.00 | 42.00 2016-11-15 22:00:00+00 | 42.00 | 42.00 | 42.00 (24 rows)

      Vous trouverez d’autres fonctions utiles dans la documentation TimescaleDB.

      Maintenant, vous savez comment traiter vos données. Ensuite, vous allez voir comment supprimer les données inutiles et comment comprimer les données.

      Étape 6 – Configuration de la compression et de la suppression des données

      À mesure que les données s’accumulent, elles prennent de plus en plus de place sur votre disque dur. Pour gagner de l’espace, la dernière version de TimescaleDB propose une fonction de compression des données. Cette fonction ne nécessite aucune modification des paramètres du système de fichiers et peut être utilisée pour rendre votre base de données plus efficace. Pour plus d’informations sur le fonctionnement de cette compression, consultez cet article de TimescaleDB sur la compression.

      Tout d’abord, activez la compression de votre hypertable :

      • ALTER TABLE conditions SET (
      • timescaledb.compress,
      • timescaledb.compress_segmentby = 'device_id'
      • );

      Vous recevrez la donnée suivante :

      Output

      NOTICE: adding index _compressed_hypertable_2_device_id__ts_meta_sequence_num_idx ON _timescaledb_internal._compressed_hypertable_2 USING BTREE(device_id, _ts_meta_sequence_num) ALTER TABLE

      Remarque : vous pouvez également configurer TimescaleDB pour compresser les données sur la période spécifiée. Par exemple, vous pouvez exécuter :

      • SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');

      Dans cet exemple, les données seront automatiquement compressées après une semaine.

      Vous pouvez voir les statistiques sur les données compressées avec la commande :

      • SELECT *
      • FROM timescaledb_information.compressed_chunk_stats;

      Vous verrez ensuite une liste de chunks avec leur état : état de la compression et combien d’espace est occupé par les données non compressées et compressées en octets.

      Si vous n’avez pas besoin de stocker des données pendant une longue période, vous pouvez supprimer les données obsolètes pour libérer encore plus d’espace. Il existe une fonction spéciale appelée drop_chunks pour ça. Elle vous permet de supprimer les chunks contenant des données antérieures à la date spécifiée :

      • SELECT drop_chunks(interval '24 hours', 'conditions');

      Cette requête supprimera tous les morceaux de l'hypertable des conditions qui n’incluent que les données datant de plus d’un jour.

      Vous recevrez le résultat suivant :

      Output

      drop_chunks ---------------------------------------- _timescaledb_internal._hyper_1_2_chunk (1 row)

      Pour supprimer automatiquement les anciennes données, vous pouvez configurer une tâche cron. Consultez notre tutoriel pour apprendre à utiliser cron pour automatiser diverses tâches de système.

      Quittez la base de données :

      Ensuite, modifiez votre crontab avec la commande suivante, qui doit être exécutée depuis le shell :

      Maintenant, ajoutez les lignes suivantes à la fin de votre fichier :

      crontab

      ...
      
      0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1
      

      Cette tâche consiste à supprimer les données obsolètes qui datent de plus d’un jour à 1 heure du matin tous les jours.

      Conclusion

      Vous avez maintenant configuré TimescaleDB sur votre serveur Ubuntu 18.04. Vous avez également essayé de créer des hypertables, d’y insérer des données, d’interroger les données, de compresser et de supprimer les enregistrements inutiles. Grâce à ces exemples, vous pourrez profiter des principaux avantages de TimescaleDB par rapport aux systèmes traditionnels de gestion de bases de données relationnelles pour le stockage de données chronologiques, notamment :

      • Des taux d’acquisition de données plus élevés
      • Une exécution des requêtes plus rapide
      • Des caractéristiques orientées chronologiquement

      Maintenant que vous savez comment stocker des données de séries chronologiques, vous pourriez utiliser les données pour créer des graphiques. TimescaleDB est compatible avec les outils de visualisation qui fonctionnent avec PostgreSQL, comme Grafana. Vous pouvez utiliser notre tutoriel Comment installer et sécuriser Grafana sur Ubuntu 18.04 pour en savoir plus sur cet outil de visualisation très répandu.



      Source link