One place for hosting & domains

      TimescaleDB

      How To Install and Use TimescaleDB on Ubuntu 20.04


      Not using Ubuntu 20.04?


      Choose a different version or distribution.

      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      Many applications, such as monitoring systems and data collection systems, accumulate data for further analysis. These analyses often look at the way a piece of data or a system changes over time. In these instances, data is represented as a time series, with every data point accompanied by a timestamp. An example would look like this:

      2020-06-01 09:00:00    server.cpu.1    0.9
      2020-06-01 09:00:00    server.cpu.15   0.8
      2020-06-01 09:01:00    server.cpu.1    0.9
      2020-06-01 09:01:00    server.cpu.15   0.8
      ...
      

      The relevance of time series data has recently grown thanks to the new deployments of the Internet of Things (IoT) and Industrial Internet of Things. There are more and more devices that collect various time-series information: fitness trackers, smart watches, home weather stations, and various sensors, to name a few. These devices collect a lot of information, and all this data must be stored somewhere.

      Classic relational databases are most often used to store data, but they don’t always fit when it comes to the huge data volumes of time series. When you need to process a large amount of time series data, relational databases can be too slow. Because of this, specially optimized databases, called NoSQL databases, have been created to avoid the problems of relational databases.

      TimescaleDB is an open-source database optimized for storing time-series data. It is implemented as an extension of PostgreSQL and combines the ease-of-use of relational databases and the speed of NoSQL databases. As a result, it allows you to use PostgreSQL for both storing business data and time-series data in one place.

      By following this tutorial, you’ll set up TimescaleDB on Ubuntu 20.04, configure it, and learn how to work with it. You’ll run through creating time-series databases and making simple queries. Finally, you’ll see how to get rid of unnecessary data.

      Prerequisites

      To follow this tutorial, you will need:

      Step 1 — Installing TimescaleDB

      TimescaleDB is not available in Ubuntu’s default package repositories, so in this step you will install it from the TimescaleDB Personal Packages Archive (PPA).

      First, add Timescale’s APT repository:

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

      Confirm this action by hitting the ENTER key.

      Next, refresh your APT cache to update your package lists:

      You can now proceed with the installation. This tutorial uses PostgreSQL version 12; if you are using a different version of PostgreSQL (11 or 10, for example), replace the value in the following command and run it:

      • sudo apt install timescaledb-postgresql-12

      Note: Support for PostgreSQL versions 9.6.3+ and 10.9+ is deprecated and will be removed in a future releases.

      TimescaleDB is now installed and ready to be used. Next, you will turn it on and adjust some of the settings associated with it in the PostgreSQL configuration file to optimize the database.

      Step 2 — Configuring TimescaleDB

      The TimescaleDB module works fine with the default PostgreSQL configuration settings, but to improve performance and make better use of processor, memory, and disk resources, developers of TimescaleDB suggest configuring some individual parameters. This can be done automatically with the timescaledb-tune tool or by manually editing your server’s postgresql.conf file.

      In this tutorial, you will use the timescaledb-tune tool. It reads the postgresql.conf file and interactively suggests making changes.

      Run the following command to start the configuration wizard:

      First, you will be asked to confirm the path to the PostgreSQL configuration file:

      Output

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

      The utility automatically detects the path to the configuration file, so confirm this by entering y:

      Output

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

      Next, you will be prompted to change the shared_preload_libraries variable to preload the TimescaleDB module upon starting the PostgreSQL server:

      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 accepts a comma separated list of modules as a value, designating which modules PostgreSQL should load before starting the database server. Making this change will add the timescaledb module to that list.

      Enable the TimescaleDB module by typing y at this prompt and pressing ENTER:

      Output

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

      Based on the characteristics of your server and the PostgreSQL version, you will then be offered to tune your settings. Press y to start the tuning process:

      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 12 Memory settings recommendations Current: shared_buffers = 128MB #effective_cache_size = 4GB #maintenance_work_mem = 64MB #work_mem = 4MB Recommended: shared_buffers = 1990MB effective_cache_size = 5971MB maintenance_work_mem = 1019114kB work_mem = 5095kB Is this okay? [(y)es/(s)kip/(q)uit]:

      timescaledb-tune will automatically detect the servers’s available memory and calculate recommended values for the shared_buffers, effective_cache_size, maintenance_work_mem, and work_mem settings. shared_buffers, for example, determines the amount of memory allocated for caching data. By default this setting is relatively low to account for a wider range of platforms, so timescaledb-tune has suggested increasing the value, taking better advantage of resources by making more room to store cached information like repeated queries. The work_mem variable has been increased as well to allow for more complicated sorts.

      If you’d like to learn more about how this tuner works, check out the GitHub page for timescaledb-tune.

      Enter y to accept the values:

      Output

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

      At this point, if your server has multiple CPUs, you will find the recommendations for parallelism settings. However if you have one CPU, timescaledb-tune will send you directly to the WAL settings.

      Those with multiple CPUs will encounter recommendations like this:

      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]:

      These settings regulate the number of workers, which process requests and background tasks. You can learn more about these settings from the TimescaleDB and PostgreSQL documentation.

      Type y then ENTER to accept these settings:

      Output

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

      Next, you will find recommendations for Write Ahead Log (WAL) settings:

      Output

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

      WAL preserves data integrity, but the default settings can cause inefficient I/O that slows down write performance. Type and enter y to optimize these settings:

      Output

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

      You’ll now find some miscellaneous recommendations:

      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]:

      All of these different parameters are aimed at increasing performance. For example, SSDs can process many concurrent requests, so the best value for the effective_io_concurrency might be in the hundreds. You can find more info about these options in the PostgreSQL documentation.

      Press y then ENTER to continue:

      Output

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

      As a result, you will get a ready-made configuration file at /etc/postgresql/12/main/postgresql.conf.

      Note: If you are doing the installation from scratch, you could also run the initial command with the --quiet and --yes flags, which will automatically apply all the recommendations and will make changes to the postgresql.conf configuration file:

      • sudo timescaledb-tune --quiet --yes

      In order for the configuration changes to take effect, you must restart the PostgreSQL service:

      • sudo systemctl restart postgresql.service

      Now the database is running with optimal parameters and is ready to work with the time-series data. In the next steps, you’ll try out working with this data by creating new databases and hypertables and performing operations.

      Step 3 — Creating a New Database and Hypertable

      With your TimescaleDB setup optimized, you are ready to work with time-series data. TimescaleDB is implemented as an extension of PostgreSQL, so operations with time-series data are not much different from relational data operations. At the same time, the database allows you to freely combine data from time series and relational tables in the future.

      First, you will create a new database and turn on the TimescaleDB extension for it. Log in to your PostgreSQL database:

      Now create a new database and connect to it. This tutorial will name the database timeseries:

      • CREATE DATABASE timeseries;
      • c timeseries

      You can find additional information about working with the PostgreSQL database in our How To Create, Remove & Manage Tables in PostgreSQL on a Cloud Server tutorial.

      Finally, enable the TimescaleDB extension:

      • CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

      You will see the following output:

      Output

      WARNING: WELCOME TO _____ _ _ ____________ |_ _(_) | | | _ ___ | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ / | | | | _ ` _ / _ / __|/ __/ _` | |/ _ | | | ___ | | | | | | | | | __/__ (_| (_| | | __/ |/ /| |_/ / |_| |_|_| |_| |_|___||___/_____,_|_|___|___/ ____/ Running version 1.7.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

      The primary point of interaction with your time-series data are hypertables, an abstraction of many individual tables holding the data, called chunks.

      To create a hypertable, start with a regular SQL table and then convert it into a hypertable via the function create_hypertable.

      For this tutorial, make a table that will store data for tracking temperature and humidity across a collection of devices over time:

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

      This command will create a table called conditions with four columns. The first column will store the timestamp, which includes the time zone and cannot be empty. Next, you will use the time column to transform your table into a hypertable that is partitioned by time:

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

      This command calls the create_hypertable() function, which creates a TimescaleDB hypertable from a PostgreSQL table, replacing the latter.

      You will receive the following output:

      Output

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

      In this step, you created a new hypertable to store time-series data. Now you can populate it with data by writing to the hypertable, then run through the process of deleting it.

      Step 4 — Writing and Deleting Data

      In this step, you will insert data using standard SQL commands and import large sets of data from external sources. This will show you the relational database aspects of TimescaleDB.

      First, try out the basic commands. Data can be inserted into the hypertable using the standard INSERT SQL command. Insert some sample temperature and humidity data for the theoretical device weather-pro-000000 using the following command:

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

      You will receive the following output:

      Output

      INSERT 0 1

      You can also insert multiple rows of data at once. Try the following:

      • 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);

      You will receive the following:

      Output

      INSERT 0 3

      You can specify that the INSERT command will return some or all of the inserted data using the RETURNING statement:

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

      You will see the following output:

      Output

      time | device_id | temperature | humidity -------------------------------+--------------------+-------------+---------- 2020-05-30 05:31:27.842009+00 | weather-pro-000002 | 70.1 | 50.1 (1 row)

      If you want to delete data from the hypertable, use the standard DELETE SQL command. Run the following to delete whatever data has a temperature higher than 80 or a humidity higher than 50:

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

      After the delete operation, it is recommended to use the VACUUM command, which will reclaim space still used by data that had been deleted:

      You can find more info about the VACUUM command in the PostgreSQL documentation.

      These commands are fine for small-scale data entry, but since time-series data often generates huge datasets from multiple devices simultaneously, it’s essential to also know how to insert hundreds or thousands of rows at a time. If you have prepared data from external sources in a structured form, for example in csv format, this task can be accomplished quickly.

      To test this out, you will use a sample dataset that represents temperature and humidity data from a variety of locations. It was created by TimescaleDB developers to allow you to test out their database. You can check out more info about sample datasets in the TimescaleDB documentation.

      Next, you will import data from the weather_small sample dataset into your database. First, quit Postgresql:

      Then download the dataset and extract it:

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

      Next, import the temperature and humidity data into your database:

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

      This connects to the timeseries database and executes the COPY command that copies the data from the chosen file into the conditions hypertable. It will run for a few seconds.

      When the data has been entered into your table, you will receive the following output:

      Output

      COPY 1000000

      In this step, you added data to the hypertable manually and in batches. Next, continue on to performing queries.

      Step 5 — Querying Data

      Now that your table contains data, you can perform various queries to analyze it.

      To get started, log in to the database:

      • sudo -u postgres psql -d timeseries

      As mentioned before, to work with hypertables you can use standard SQL commands. For example, to show the last 10 entries from the conditions hypertable, run the following command:

      • SELECT * FROM conditions LIMIT 10;

      You will see the following 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)

      This command lets you see what data is in the database. Since the database contains a million records, you used LIMIT 10 to limit the output to 10 entries.

      To see the most recent entries, sort the data array by time in descending order:

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

      This will output the top 20 most recent entries.

      You can also add a filter. For example, to see entries from the weather-pro-000000 device, run the following:

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

      In this case, you will see the 10 most recent temperature and humidity datapoints recorded by the weather-pro-000000 device.

      In addition to standard SQL commands, TimescaleDB also provides a number of special functions that are useful for time series data analysis. For example, to find the median of the temperature values, you can use the following query with the percentile_cont function:

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

      You will see the following output:

      Output

      percentile_cont ------------------- 40.49999999999998 (1 row)

      In this way, you’ll see the median temperature for the entire observation period where the weather-pro-00000 sensor is located.

      To show the latest values from each of the sensors, you can use the last function:

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

      In the output you will see a list of all the sensors and relevant latest values.

      To get the first values use the first function.

      The following example is more complex. It will show the hourly average, minimum, and maximum temperatures for the chosen sensor within the last 24 hours:

      • 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;

      Here you used the time_bucket function, which acts as a more powerful version of the PostgreSQL date_trunc function. As a result, you will see which periods of the day the temperature rises or decreases:

      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)

      You can find more useful functions in the TimescaleDB documentation.

      Now you know how to handle your data. Next, you will go through how to delete unnecessary data and how to compress data.

      Step 6 — Configuring Data Compression and Deletion

      As data accumulates, it will take up more and more space on your hard drive. To save space, the latest version of TimescaleDB provides a data compression feature. This feature doesn’t require tweaking any file system settings, and can be used to quickly make your database more efficient. For more information on how this compression works, take a look at this Compression article from TimescaleDB.

      First, enable the compression of your hypertable:

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

      You will receive the following output:

      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

      Note: You can also set up TimescaleDB to compress data over the specified time period. For example, you could run:

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

      In this example, the data will be automatically compressed after a week.

      You can see the statistics on the compressed data with the command:

      • SELECT * FROM timescaledb_information.compressed_chunk_stats;

      You will then see a list of chunks with their statuses: compression status and how much space is taken up by uncompressed and compressed data in bytes.

      If you don’t have the need to store data for a long period of time, you can delete out-of-date data to free up even more space. There is a special drop_chunks function for this. It allows you to delete chunks with data older than the specified time:

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

      This query will drop all chunks from the hypertable conditions that only include data older than a day ago.

      You will receive the following output:

      Output

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

      To automatically delete old data, you can configure a cron task. See our tutorial to learn more about how to use cron to automate various system tasks.

      Exit from the database:

      Next, edit your crontab with the following command, which should be run from the shell:

      Now add the following line to the end of the file:

      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
      

      This job will delete obsolete data that is older than one day at 1:00 AM every day.

      Conclusion

      You’ve now set up TimescaleDB on your Ubuntu 20.04 server. You also tried out creating a hypertable, inserting data into it, querying the data, compressing the data, and deleting unnecessary records. With these examples, you’ll be able to take advantage of TimescaleDB’s key benefits over traditional relational database management systems for storing time-series data, including:

      • Higher data ingest rates
      • Quicker query performance
      • Time-oriented features

      Now that you know how to store time-series data, you could use the data to create graphs. TimescaleDB is compatible with visualization tools that work with PostgreSQL, like Grafana. You can use our How To Install and Secure Grafana on Ubuntu 20.04 tutorial to learn more about this popular visualization tool. If you’d like to learn more about databases, take a look at our Databases topic page.



      Source link

      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