作者选择了 计算机历史博物馆作为 写给捐赠计划的一部分接受捐赠。
介绍
许多应用程序,如监控系统和数据收集系统,用于进一步分析收集数据. 这些分析通常会看数据片段或系统随着时间的推移而发生变化的方式. 在这些情况下,数据被表示为一个时间序列,每个数据点伴随着一个时间标签。
12019-11-01 09:00:00 server.cpu.1 0.9
22019-11-01 09:00:00 server.cpu.15 0.8
32019-11-01 09:01:00 server.cpu.1 0.9
42019-11-01 09:01:00 server.cpu.15 0.8
5...
随着物联网(IoT)和工业物联网的兴起,管理时间序列数据已成为一个必不可少的技能,越来越多的设备收集各种时间序列信息:健身跟踪器,智能手表,家庭天气站和各种传感器,仅举几个。
经典的关系数据库通常用于存储数据,但在处理大量时间序列数据时,它们并不总是适合。
TimescaleDB是一个开放源代码的数据库,用于存储时间序列数据,它作为PostgreSQL(https://andsky.com/tech/tutorials/introduction-to-queries-postgresql)的扩展实现,并结合了关系数据库的易用性和NoSQL数据库的速度,因此,它允许您在一个地方使用PostgreSQL来存储业务数据和时间序列数据。
通过遵循本教程,您将在Ubuntu 18.04上设置 TimescaleDB,配置它,并学习如何使用它. 您将创建时间序列数据库并进行简单的查询。
前提条件
要遵循本教程,您将需要:
- 一个 Ubuntu 18.04 服务器通过遵循我们的 Ubuntu 18.04 初始服务器设置指南设置,包括具有 sudo 特权和防火墙的非根用户。
- PostgreSQL 安装在您的服务器上。
步骤 1 – 安装 TimescaleDB
TimescaleDB在Ubuntu的默认包存储库中不可用,所以在这个步骤中,您将从TimescaleDB个人包存档(PPA)中安装它。
首先,添加 Timescale 的 APT 存储库:
1sudo add-apt-repository ppa:timescale/timescaledb-ppa
通过点击ENTER键来确认此操作。
接下来,更新 APT 缓存以更新您的包列表:
1sudo apt update
此教程使用 PostgreSQL 版本 10;如果您正在使用不同的 PostgreSQL 版本(例如 11 或 9.6),请更换下列命令中的值并运行它:
1sudo apt install timescaledb-postgresql-10
TimescaleDB 现在已安装并准备使用,接下来,您将打开它并在 PostgreSQL 配置文件中调整与其相关的设置,以优化数据库。
步骤 2 – 配置 TimescaleDB
TimescaleDB 模块在默认 PostgreSQL 配置设置上运行得很好,但为了提高性能并更好地利用处理器、内存和磁盘资源, TimescaleDB 的开发人员建议配置一些个别参数。
在本教程中,您将使用 timescaledb-tune 工具,它会读取 postgresql.conf 文件,并互动地建议进行更改。
运行以下命令来启动配置向导:
1sudo timescaledb-tune
首先,您将被要求确认通往 PostgreSQL 配置文件的路径:
1[secondary_label Output]
2Using postgresql.conf at this path:
3/etc/postgresql/10/main/postgresql.conf
4
5Is this correct? [(y)es/(n)o]:
该实用程序会自动检测到通往配置文件的路径,因此通过输入y来确认:
1[secondary_label Output]
2...
3Is this correct? [(y)es/(n)o]: y
4Writing backup to:
5/tmp/timescaledb_tune.backup201911181111
接下来,您将被要求更改shared_preload_libraries变量以在启动 PostgreSQL 服务器时预加载 TimescaleDB 模块:
1[secondary_label Output]
2shared_preload_libraries needs to be updated
3Current:
4#shared_preload_libraries = ''
5Recommended:
6shared_preload_libraries = 'timescaledb'
7Is this okay? [(y)es/(n)o]:
shared_preload_libraries 接受一个单元格分开的模块列表作为一个值,指定在启动数据库服务器之前应该加载哪些 PostgreSQL 模块。
<$>[注] 注: 如果未找到由「shared_preload_libraries」指定的库,则数据库服务器将无法启动。在调试使用「shared_preload_libraries」的应用程序时,请记住这一点。
启用 TimescaleDB 模块,在这个提示下键入y并按ENTER:
1[secondary_label Output]
2...
3Is this okay? [(y)es/(n)o]: y
4success: shared_preload_libraries will be updated
根据您的服务器和PostgreSQL版本的特性,脚本将提供调节您的设置。
1[secondary_label Output]
2Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
3Recommendations based on 7.79 GB of available memory and 4 CPUs for PostgreSQL 10
4
5Memory settings recommendations
6Current:
7shared_buffers = 128MB
8#effective_cache_size = 4GB
9#maintenance_work_mem = 64MB
10#work_mem = 4MB
11Recommended:
12shared_buffers = 1994MB
13effective_cache_size = 5982MB
14maintenance_work_mem = 1021001kB
15work_mem = 5105kB
16Is this okay? [(y)es/(s)kip/(q)uit]:
「timescaledb-tune」会自动检测服务器的可用内存,并为多个设置计算建议的值。「shared_buffers」例如,决定了用于缓存数据的内存量。默认情况下,此设置相对较低,以应对更广泛的平台,因此「timescaledb-tune」建议将值从「128MB」增加到「1994MB」,更好地利用资源,为缓存信息提供更多的空间,例如重复查询。
如果您想了解有关 PostgreSQL 内存设置调节过程的更多信息,请参阅 PostgreSQL 维基上的 Tuning Your PostgreSQL Server 文章。
输入y以接受值:
1[secondary_label Output]
2...
3Is this okay? [(y)es/(s)kip/(q)uit]: y
4success: memory settings will be updated
在此时,如果您的服务器有多个CPU,您将找到对平行设置的建议. 这些设置决定了多台CPU如何同时执行查询,以扫描数据库并更快地返回所请求的数据。
具有多个CPU的人会遇到这样的建议:
1[secondary_label Output]
2Parallelism settings recommendations
3Current:
4missing: timescaledb.max_background_workers
5#max_worker_processes = 8
6#max_parallel_workers_per_gather = 2
7#max_parallel_workers = 8
8Recommended:
9timescaledb.max_background_workers = 8
10max_worker_processes = 13
11max_parallel_workers_per_gather = 1
12max_parallel_workers = 2
13Is this okay? [(y)es/(s)kip/(q)uit]:
这些设置控制了处理请求和背景任务的 workers 数量,您可以从 TimescaleDB和 PostgreSQL文档中了解有关这些设置的更多信息。
输入y,然后输入ENTER,以接受这些设置:
1[secondary_label Output]
2...
3Is this okay? [(y)es/(s)kip/(q)uit]: y
4success: parallelism settings will be updated
接下来,您将找到 Write Ahead Log (WAL)的建议:
1[secondary_label Output]
2WAL settings recommendations
3Current:
4#wal_buffers = -1
5#min_wal_size = 80MB
6#max_wal_size = 1GB
7Recommended:
8wal_buffers = 16MB
9min_wal_size = 4GB
10max_wal_size = 8GB
11Is this okay? [(y)es/(s)kip/(q)uit]:
WAL 是 PostgreSQL 在对数据库进行更改之前记录数据文件的更改的日志存储方法. 通过优先考虑数据更改的最新的记录,WAL 确保您在发生故障时能够重建您的数据库。 这样,它保留了数据完整性。 但是,默认设置可能会导致低效的输入/输出(I/O)操作,从而减缓写作性能。 要修复此问题,请键入并输入y:
1[secondary_label Output]
2...
3Is this okay? [(y)es/(s)kip/(q)uit]: y
4success: WAL settings will be updated
现在你会发现一些模糊的建议:
1[secondary_label Output]
2Miscellaneous settings recommendations
3Current:
4#default_statistics_target = 100
5#random_page_cost = 4.0
6#checkpoint_completion_target = 0.5
7#max_locks_per_transaction = 64
8#autovacuum_max_workers = 3
9#autovacuum_naptime = 1min
10#effective_io_concurrency = 1
11Recommended:
12default_statistics_target = 500
13random_page_cost = 1.1
14checkpoint_completion_target = 0.9
15max_locks_per_transaction = 64
16autovacuum_max_workers = 10
17autovacuum_naptime = 10
18effective_io_concurrency = 200
19Is this okay? [(y)es/(s)kip/(q)uit]:
例如,SSD 可以处理许多同时请求,因此effective_io_concurrency的最佳值可能是数百个。
按y然后按ENTER继续。
1[secondary_label Output]
2...
3Is this okay? [(y)es/(s)kip/(q)uit]: y
4success: miscellaneous settings will be updated
5Saving changes to: /etc/postgresql/10/main/postgresql.conf
因此,您将收到一个已完成的配置文件在 /etc/postgresql/10/main/postgresql.conf。
<$>[注]
注: 如果您正在自动安装,您还可以运行初始命令,使用--quiet和--yes旗帜,这将自动应用所有建议,并对postgresql.conf配置文件进行更改:
1sudo timescaledb-tune --quiet --yes
美元
要使配置更改生效,您必须重新启动 PostgreSQL 服务:
1sudo systemctl restart postgresql.service
现在数据库运行了最佳参数,并已准备好使用时间序列数据,在接下来的步骤中,您将尝试使用这些数据:创建新的数据库和超表并执行操作。
步骤 3 – 创建一个新的数据库和超级表
随着 TimescaleDB 设置的优化,您已经准备好使用时间序列数据。 TimescaleDB 是 PostgreSQL 的扩展,因此与时间序列数据的操作与关系数据操作不太不同。
要证明这一点,您将使用 PostgreSQL 命令创建数据库,然后启用 TimescaleDB 扩展来创建一个 hypertable,这是许多单个表的更高级别抽象。
登入您的 PostgreSQL 数据库:
1sudo -u postgres psql
现在创建一个新的数据库,并连接到它,本教程将命名数据库为timeseries:
1CREATE DATABASE timeseries;
2\c timeseries
您可以在 [我们的如何在云服务器上创建,删除和管理 PostgreSQL 表] 中找到有关使用 PostgreSQL 数据库的其他信息(https://andsky.com/tech/tutorials/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server)。
最后,启用 TimescaleDB 扩展:
1CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
您将看到以下结果:
1[secondary_label Output]
2WARNING:
3WELCOME TO
4 _____ _ _ ____________
5|_ _(_) | | | _ \ ___ \
6 | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
7 | | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
8 | | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
9 |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
10 Running version 1.5.1
11For more information on TimescaleDB, please visit the following links:
12
13 1. Getting started: https://docs.timescale.com/getting-started
14 2. API reference documentation: https://docs.timescale.com/api
15 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
16
17Note: TimescaleDB collects anonymous reports to better understand and assist our users.
18For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
19
20CREATE EXTENSION
如前所述,与时间序列数据的主要交互点是超级表,这些表由包含数据的许多个别表组成,称为 chunks 。
若要创建超级表,请从常规的 SQL 表开始,然后通过函数 create_hypertable 将其转换为超级表。
创建一个表格,该表格将存储数据来跟踪一系列设备的温度和湿度:
1CREATE TABLE conditions (
2 time TIMESTAMP WITH TIME ZONE NOT NULL,
3 device_id TEXT,
4 temperature NUMERIC,
5 humidity NUMERIC
6);
此命令会创建一个名为条件的表,包含四个列。第一列将存储包含时间区的时刻印记,并且不能空。
1SELECT create_hypertable('conditions', 'time');
此命令调用了 create_hypertable()函数,从 PostgreSQL 表中创建一个 TimescaleDB 超级表,取代后者。
您将获得以下输出:
1[secondary_label Output]
2 create_hypertable
3-------------------------
4 (1,public,conditions,t)
5(1 row)
在此步骤中,您创建了一个新的超级表来存储时间序列数据,现在您可以通过写入超级表来填充数据,然后运行删除数据的过程。
步骤 4 – 写入和删除数据
在此步骤中,您将使用标准的 SQL 命令输入数据,并从外部来源导入大型数据集,这将向您展示 TimescaleDB 的关系数据库方面。
首先,尝试基本命令. 您可以使用标准的INSERT SQL 命令将数据插入超级表中。 使用以下命令为理论设备weather-pro-000000插入一些示例的温度和湿度数据:
1INSERT INTO conditions(time, device_id, temperature, humidity)
2 VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);
您将看到以下输出:
1[secondary_label Output]
2INSERT 0 1
您还可以同时插入多个数据行. 尝试以下方法:
1INSERT INTO conditions
2 VALUES
3 (NOW(), 'weather-pro-000002', 71.0, 51.0),
4 (NOW(), 'weather-pro-000003', 70.5, 50.5),
5 (NOW(), 'weather-pro-000004', 70.0, 50.2);
你将收到以下内容:
1[secondary_label Output]
2INSERT 0 3
您还可以指定INSERT命令将使用RETURNING命令返回部分或全部输入的数据:
1INSERT INTO conditions
2 VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;
您将看到以下结果:
1[secondary_label Output]
2 time | device_id | temperature | humidity
3-------------------------------+--------------------+-------------+----------
4 2019-09-15 14:14:01.576651+00 | weather-pro-000002 | 70.1 | 50.1
5(1 row)
如果您想从超级表中删除数据,请使用标准的DELETE SQL 命令. 执行以下操作来删除任何数据的温度高于80或湿度高于50:
1DELETE FROM conditions WHERE temperature > 80;
2DELETE FROM conditions WHERE humidity > 50;
删除操作后,使用VACUUM命令恢复已删除的数据仍然使用的空间。
1VACUUM conditions;
您可以在 PostgreSQL 文档中找到有关 VACUUM 命令的更多信息。
这些命令适用于小规模的数据输入,但由于时间序列数据通常会同时从多个设备生成巨大的数据集,因此必须知道如何同时插入数百行或数千行。
要测试这一点,您将使用一个示例数据集,它代表各种地点的温度和湿度数据。 这是一个官方的 TimescaleDB 数据集,用于测试他们的数据库。 您可以在 TimescaleDB 文档中查看有关示例数据集的更多信息。
让我们看看你如何从 weather_small 样本数据集中导入数据库。
1\q
然后下载数据集并提取它:
1wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz
2tar -xvzf weather_small.tar.gz
接下来,将温度和湿度数据导入您的数据库:
1sudo -u postgres psql -d timeseries -c "\COPY conditions FROM weather_small_conditions.csv CSV"
这将连接到timeseries数据库并执行\COPY命令,将数据从所选文件复制到条件超级表中。
当数据已输入到您的表中时,您将收到以下输出:
1[secondary_label Output]
2COPY 1000000
在此步骤中,您将数据添加到超级表手动和批次,然后继续执行查询。
步骤五:查询数据
现在表中包含数据,您可以执行各种查询来分析它。
要开始,请登录到数据库:
1sudo -u postgres psql -d timeseries
如前所述,您可以使用标准 SQL 命令来使用超级表格,例如,要显示条件超级表中的最后 10 个条目,请运行以下命令:
1SELECT * FROM conditions LIMIT 10;
您将看到以下结果:
1[secondary_label Output]
2 time | device_id | temperature | humidity
3------------------------+--------------------+--------------------+----------
4 2016-11-15 12:00:00+00 | weather-pro-000000 | 39.9 | 49.9
5 2016-11-15 12:00:00+00 | weather-pro-000001 | 32.4 | 49.8
6 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 | 50.2
7 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 | 49.8
8 2016-11-15 12:00:00+00 | weather-pro-000004 | 71.8 | 50.1
9 2016-11-15 12:00:00+00 | weather-pro-000005 | 71.8 | 49.9
10 2016-11-15 12:00:00+00 | weather-pro-000006 | 37 | 49.8
11 2016-11-15 12:00:00+00 | weather-pro-000007 | 72 | 50
12 2016-11-15 12:00:00+00 | weather-pro-000008 | 31.3 | 50
13 2016-11-15 12:00:00+00 | weather-pro-000009 | 84.4 | 87.8
14(10 rows)
由于数据库包含100万个记录,您使用LIMIT 10来限制输出到10个条目。
若要查看最新的条目,请以下行顺序按时间对数据阵列进行排序:
1SELECT * FROM conditions ORDER BY time DESC LIMIT 20;
这将输出前20个最新的条目。
例如,若要查看来自 weather-pro-000000 设备的条目,请执行以下操作:
1SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;
在这种情况下,您将看到最新的 10 个温度和湿度数据点,这些数据点是由 weather-pro-000000 设备记录的。
除了标准的 SQL 命令外,TimescaleDB 还提供了一些用于时间序列数据分析的特殊功能,例如,要查找温度值的中位数,您可以使用以下查询使用percentile_cont函数:
1SELECT percentile_cont(0.5)
2 WITHIN GROUP (ORDER BY temperature)
3 FROM conditions
4 WHERE device_id = 'weather-pro-000000';
您将看到以下结果:
1[secondary_label Output]
2 percentile_cont
3-----------------
4 40.5
5(1 row)
这样,你会看到整个观测期间的平均温度,在那里位于pro-00000气象传感器。
要显示每个传感器的最新值,您可以使用最后函数:
1select device_id, last(temperature, time)
2 FROM conditions
3 GROUP BY device_id;
在输出中,您将看到所有传感器和相关的最新值的列表。
要获取第一个值,使用第一个函数。
以下示例更为复杂,显示了过去 24 小时内所选传感器的小时平均、最低和最高温度:
1SELECT time_bucket('1 hour', time) "hour",
2trunc(avg(temperature), 2) avg_temp,
3trunc(min(temperature), 2) min_temp,
4trunc(max(temperature), 2) max_temp
5FROM conditions
6WHERE device_id = 'weather-pro-000000'
7GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;
在这里,您使用了‘time_bucket’函数,它作为PostgreSQL date_trunc函数的更强大的版本。
1[secondary_label Output]
2 hour | avg_temp | min_temp | max_temp
3------------------------+----------+----------+----------
4 2016-11-16 21:00:00+00 | 42.00 | 42.00 | 42.00
5 2016-11-16 20:00:00+00 | 41.92 | 41.69 | 42.00
6 2016-11-16 19:00:00+00 | 41.07 | 40.59 | 41.59
7 2016-11-16 18:00:00+00 | 40.11 | 39.79 | 40.59
8 2016-11-16 17:00:00+00 | 39.46 | 38.99 | 39.79
9 2016-11-16 16:00:00+00 | 38.54 | 38.19 | 38.99
10 2016-11-16 15:00:00+00 | 37.56 | 37.09 | 38.09
11 2016-11-16 14:00:00+00 | 36.62 | 36.39 | 37.09
12 2016-11-16 13:00:00+00 | 35.59 | 34.79 | 36.29
13 2016-11-16 12:00:00+00 | 34.59 | 34.19 | 34.79
14 2016-11-16 11:00:00+00 | 33.94 | 33.49 | 34.19
15 2016-11-16 10:00:00+00 | 33.27 | 32.79 | 33.39
16 2016-11-16 09:00:00+00 | 33.37 | 32.69 | 34.09
17 2016-11-16 08:00:00+00 | 34.94 | 34.19 | 35.49
18 2016-11-16 07:00:00+00 | 36.12 | 35.49 | 36.69
19 2016-11-16 06:00:00+00 | 37.02 | 36.69 | 37.49
20 2016-11-16 05:00:00+00 | 38.05 | 37.49 | 38.39
21 2016-11-16 04:00:00+00 | 38.71 | 38.39 | 39.19
22 2016-11-16 03:00:00+00 | 39.72 | 39.19 | 40.19
23 2016-11-16 02:00:00+00 | 40.67 | 40.29 | 40.99
24 2016-11-16 01:00:00+00 | 41.63 | 40.99 | 42.00
25 2016-11-16 00:00:00+00 | 42.00 | 42.00 | 42.00
26 2016-11-15 23:00:00+00 | 42.00 | 42.00 | 42.00
27 2016-11-15 22:00:00+00 | 42.00 | 42.00 | 42.00
28(24 rows)
您可以在 TimescaleDB 文档中找到更多有用的函数。
现在你知道如何处理你的数据,接下来,你将通过如何删除不必要的数据和如何压缩数据。
步骤 6 – 配置数据压缩和删除
随着数据的积累,它将在硬盘上占用更多的空间。为了节省空间,最新版本的 TimescaleDB 提供了数据压缩功能. 此功能不需要调整任何文件系统设置,可以用来快速使数据库更有效率。 有关这种压缩的更多信息,请参阅 TimescaleDB 的这个压缩文章。
首先,启用您的超级表压缩:
1ALTER TABLE conditions SET (
2 timescaledb.compress,
3 timescaledb.compress_segmentby = 'device_id'
4);
您将收到以下数据:
1[secondary_label Output]
2NOTICE: 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)
3ALTER TABLE
<$>[注] 注: 您也可以设置 TimescaleDB 以在指定时间段内压缩数据。
1SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');
在此示例中,数据将在一周后自动压缩。
您可以使用命令查看压缩数据上的统计数据:
1SELECT *
2FROM timescaledb_information.compressed_chunk_stats;
然后,您将看到一份列表中的碎片及其状态:压缩状态以及未压缩和压缩数据在字节中占用了多少空间。
如果您不需要长时间存储数据,您可以删除过时的数据,以释放更多的空间. 为此,有一个特殊的 drop_chunks功能。
1SELECT drop_chunks(interval '24 hours', 'conditions');
此查询将从仅包含一个天前数据的超级表条件中删除所有片段。
您将获得以下输出:
1[secondary_label Output]
2 drop_chunks
3----------------------------------------
4 _timescaledb_internal._hyper_1_2_chunk
5(1 row)
要自动删除旧数据,您可以配置一个cron任务. 参阅我们的教程,了解更多关于 如何使用cron来自动化各种系统任务的信息。
退出数据库:
1\q
接下来,用以下命令编辑你的crontab,该命令应该从壳中运行:
1crontab -e
现在将下列行添加到文件的末尾:
1[label crontab]
2...
3
40 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1
此工作将删除每天上午 1 点以前的过时数据。
结论
您现在已经在 Ubuntu 18.04 服务器上设置了 TimescaleDB。您还尝试了创建超级表格,将数据插入其中,查询数据,压缩和删除不必要的记录。
- 更高的数据输入率
- 更快的查询性能
- 面向时间的功能
现在你知道如何存储时间序列数据,你可以使用这些数据来创建图表。 TimescaleDB 与与 PostgreSQL 兼容的可视化工具兼容,例如 Grafana。