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:

• Einen CentOS-7-Server, der gemäß unseres Leitfadens Ersteinrichtung des Servers mit CentOS 7 eingerichtet wurde, einschließlich eines Benutzers ohne Root-Berechtigung und mit sudo-Berechtigungen und eine mit firewalld eingerichtete Firewall. Für die Einrichtung von firewalld folgen Sie dem Abschnitt „Konfigurieren einer grundlegenden Firewall“ des Tutorials Zusätzlich empfohlene Schritte für neue CentOS-7-Server.
• Auf Ihrem Server installiertes PostgreSQL. Folgen Sie unserem Leitfaden So installieren und verwenden Sie PostgreSQL unter CentOS 7, um es zu installieren und zu konfigurieren.

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:

[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.

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:

sudo -u postgres psql

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.

VACUUM conditions;

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:

\q

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

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

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:

\q

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

crontab -e

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

...

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