沉浸式学习PostgreSQL|PolarDB 11: 物联网(IoT)、监控系统、应用日志、用户行为记录等场景 - 时序数据高吞吐存取分析

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 物联网场景, 通常有大量的传感器(例如水质监控、气象监测、新能源汽车上的大量传感器)不断探测最新数据并上报到数据库.监控系统, 通常也会有采集程序不断的读取被监控指标(例如CPU、网络数据包转发、磁盘的IOPS和BW占用情况、内存的使用率等等), 同时将监控数据上报到数据库.应用日志、用户行为日志, 也就有同样的特征, 不断产生并上报到数据库.以上数据具有时序特征, 对数据库的关键能力要求如下:数据高速写入高速按时间区间读取和分析, 目的是发现异常, 分析规律.尽量节省存储空间

作者

digoal

日期

2023-09-01

标签

PostgreSQL , PolarDB , 数据库 , 教学


背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

  • 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

ARM机器使用以下docker image:

业务场景1 介绍: 物联网(IoT)、监控系统、应用日志、用户行为记录等场景 - 时序数据高吞吐存取分析

物联网场景, 通常有大量的传感器(例如水质监控、气象监测、新能源汽车上的大量传感器)不断探测最新数据并上报到数据库.

监控系统, 通常也会有采集程序不断的读取被监控指标(例如CPU、网络数据包转发、磁盘的IOPS和BW占用情况、内存的使用率等等), 同时将监控数据上报到数据库.

应用日志、用户行为日志, 也就有同样的特征, 不断产生并上报到数据库.

以上数据具有时序特征, 对数据库的关键能力要求如下:

  • 数据高速写入
  • 高速按时间区间读取和分析, 目的是发现异常, 分析规律.
  • 尽量节省存储空间

实现和对照

传统方法 设计和实验

1、建表

drop table if exists tbl;  
create unlogged table tbl(  -- 为了方便测试使用unlogged table  
  sid int,   -- 传感器ID  
  v1 float,  -- v1维度的值  
  v2 float,  -- v2维度的值  
  v3 float,  -- v3维度的值  
  v4 float,  -- v4维度的值  
  v5 float,  -- v5维度的值  
  ts timestamp   -- 记录上报时间戳  
);

2、建索引, 传统方法使用btree索引

create index on tbl using btree (sid,ts);

3、编写写入性能压测脚本

假设有1万个传感器.

vi t1.sql  
\set sid random(1,10000)  
insert into tbl values (:sid, random()*100,random()*100,random()*100,random()*100,random()*100, now());

4、压测写入性能

pgbench -M prepared -n -r -P 1 -f ./t1.sql -c 10 -j 10 -T 120  
transaction type: ./t1.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 120 s  
number of transactions actually processed: 14539863  
latency average = 0.082 ms  
latency stddev = 0.300 ms  
initial connection time = 14.393 ms  
tps = 121175.788589 (without initial connection time)  
statement latencies in milliseconds:  
         0.000  \set sid random(1,10000)  
         0.082  insert into tbl values (:sid, random()*100,random()*100,random()*100,random()*100,random()*100, now());

5、编写读取性能压测脚本

假设一次读取约1万条数据进行聚合.

select min(ts), max(ts) from (  
  select * from tbl where sid=1 order by ts limit 10000  
) t;  
            min             |            max  
----------------------------+----------------------------  
 2023-09-01 06:46:33.643741 | 2023-09-01 06:48:33.542128  
(1 row)
vi t2.sql  
\set sid random(1,10000)  
select count(*), min(v1), max(v2), min(v3), max(v4), avg(v5) from tbl where sid=:sid and ts between '2023-09-01 06:46:33.643741' and '2023-09-01 06:48:33.542128';

6、压测读性能

pgbench -M prepared -n -r -P 1 -f ./t2.sql -c 10 -j 10 -T 120  
transaction type: ./t2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 120 s  
number of transactions actually processed: 77681  
latency average = 15.446 ms  
latency stddev = 6.741 ms  
initial connection time = 22.071 ms  
tps = 647.375584 (without initial connection time)  
statement latencies in milliseconds:  
         0.001  \set sid random(1,10000)  
        15.452  select count(*), min(v1), max(v2), min(v3), max(v4), avg(v5) from tbl where sid=:sid and ts between '2023-09-01 06:46:33.643741

7、存储空间

postgres=# \dt+  
                                   List of relations  
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description  
--------+------+-------+----------+-------------+---------------+---------+-------------  
 public | tbl  | table | postgres | unlogged    | heap          | 1172 MB |  
(1 row)  
postgres=# \di+  
                                            List of relations  
 Schema |      Name      | Type  |  Owner   | Table | Persistence | Access method |  Size  | Description  
--------+----------------+-------+----------+-------+-------------+---------------+--------+-------------  
 public | tbl_sid_ts_idx | index | postgres | tbl   | unlogged    | btree         | 620 MB |  
(1 row)  
postgres=# select count(*) from tbl;  
  count  
----------  
 14539863  
(1 row)

PolarDB|PG新方法1 设计和实验

1、建表.

drop table if exists tbl1;  
create unlogged table tbl1(  -- 为了方便测试使用unlogged table  
  sid int,   -- 传感器ID  
  v1 float,  -- v1维度的值  
  v2 float,  -- v2维度的值  
  v3 float,  -- v3维度的值  
  v4 float,  -- v4维度的值  
  v5 float,  -- v5维度的值  
  ts timestamp   -- 记录上报时间戳  
) ;

2、建立索引, 使用brin索引方法

create index on tbl1 using brin (sid,ts);

3、编写写入性能压测脚本

假设有1万个传感器.

vi test1.sql  
\set sid random(1,10000)  
insert into tbl1 values (:sid, random()*100,random()*100,random()*100,random()*100,random()*100, now());

4、压测写入性能

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 10 -j 10 -T 120  
transaction type: ./test1.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 120 s  
number of transactions actually processed: 21043922  
latency average = 0.057 ms  
latency stddev = 0.079 ms  
initial connection time = 20.634 ms  
tps = 175385.806032 (without initial connection time)  
statement latencies in milliseconds:  
         0.000  \set sid random(1,10000)  
         0.057  insert into tbl1 values (:sid, random()*100,random()*100,random()*100,random()*100,random()*100, now());

5、按sid聚集数据.

这个步骤是为了加速. 因为没有使用sid分区, 通一个数据块中的所有sid的数据会聚集在一起, 导致需要大量过滤sid不等于目标sid的数据.

思考: 如果每次分析的是所有sid在同一个时间区间的数据, 还需要做这一步吗?

除了这个步骤, 还有什么优化方法? 按sid建立分区表? 物理上隔离不同sid的存储区间.

create index on tbl1 using btree (sid,ts);  
cluster tbl1 using tbl1_sid_ts_idx1;  
drop index tbl1_sid_ts_idx1;

6、编写读取性能压测脚本

select min(ts), max(ts) from (  
  select * from tbl1 where sid=1 order by ts limit 10000  
) t;  
            min             |            max  
----------------------------+----------------------------  
 2023-09-01 07:39:43.109957 | 2023-09-01 07:41:43.079047  
(1 row)
\set sid 1  
\set t1 '''2023-09-01 07:39:43.109957'''  
\set t2 '''2023-09-01 07:41:43.079047'''
vacuum analyze tbl1;  
explain analyze select count(*), min(v1), max(v2), min(v3), max(v4), avg(v5) from tbl1 where sid=:sid and ts between :t1 and :t2;  
                                                                                    QUERY PLAN  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=13255.97..13255.98 rows=1 width=48) (actual time=1.474..1.475 rows=1 loops=1)  
   ->  Bitmap Heap Scan on tbl1  (cost=12.60..13222.16 rows=2254 width=40) (actual time=0.158..1.335 rows=2145 loops=1)  
         Recheck Cond: ((sid = 1) AND (ts >= '2023-09-01 07:39:43.109957'::timestamp without time zone) AND (ts <= '2023-09-01 07:41:43.079047'::timestamp without time zone))  
         Rows Removed by Index Recheck: 10271  
         Heap Blocks: lossy=128  
         ->  Bitmap Index Scan on tbl1_sid_ts_idx  (cost=0.00..12.03 rows=12415 width=0) (actual time=0.148..0.148 rows=1280 loops=1)  
               Index Cond: ((sid = 1) AND (ts >= '2023-09-01 07:39:43.109957'::timestamp without time zone) AND (ts <= '2023-09-01 07:41:43.079047'::timestamp without time zone))  
 Planning Time: 0.683 ms  
 Execution Time: 1.517 ms  
(9 rows)
vi test2.sql  
\set sid random(1,10000)  
select count(*), min(v1), max(v2), min(v3), max(v4), avg(v5) from tbl1 where sid=:sid and ts between '2023-09-01 07:39:43.109957' and '2023-09-01 07:41:43.079047';

7、压测读性能

pgbench -M prepared -n -r -P 1 -f ./test2.sql -c 10 -j 10 -T 120  
transaction type: ./test2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 120 s  
number of transactions actually processed: 311116  
latency average = 3.856 ms  
latency stddev = 1.796 ms  
initial connection time = 20.068 ms  
tps = 2592.960327 (without initial connection time)  
statement latencies in milliseconds:  
         0.000  \set sid random(1,10000)  
         3.856  select count(*), min(v1), max(v2), min(v3), max(v4), avg(v5) from tbl1 where sid=:sid and ts between '2023-09-01 07:39:43.10995

8、存储空间

postgres=# \dt+  
                                   List of relations  
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description  
--------+------+-------+----------+-------------+---------------+---------+-------------  
 public | tbl1 | table | postgres | unlogged    | heap          | 1695 MB |  
(1 row)  
postgres=# \di+  
                                            List of relations  
 Schema |      Name       | Type  |  Owner   | Table | Persistence | Access method |  Size  | Description  
--------+-----------------+-------+----------+-------+-------------+---------------+--------+-------------  
 public | tbl1_sid_ts_idx | index | postgres | tbl1  | unlogged    | brin          | 120 kB |  
(1 row)  
postgres=# select count(*) from tbl1;  
  count  
----------  
 21043922  
(1 row)

对照

使用PolarDB|PostgreSQL的brin时序索引, 空间占用相比传统的btree缩小1000倍以上, 并且查询和写入性能更好.

场景 技术 索引占用空间 写入性能 按时间段查询分析性能(每次分析约1万条)
物联网时序类业务 传统方法使用Btree索引 14539863 条记录 620 MB 121175 TPS 647 TPS
物联网时序类业务 PolarDB|PostgreSQL 方法使用BRIN索引 21043922 条记录 120 KB 175385 TPS 2592 TPS

知识点

1 分区表(分区类型、多级分区、表达式分区、非平衡分区...) : https://www.postgresql.org/docs/16/ddl-partitioning.html

2 BRIN 索引 : https://www.postgresql.org/docs/16/indexes-types.html#INDEXES-TYPES-BRIN

3 数组类型 : https://www.postgresql.org/docs/16/arrays.html

4 JSONB类型 : https://www.postgresql.org/docs/16/datatype-json.html

5 任务调度 :

思考

1 如果分析维度是同一个传感器的在一个时间区间的多条数据. 还有什么方法能提升性能(读写)和压缩比(节省空间)?

  • 对于同一个传感器ID, 采用聚集存储类型jsonb/数组?
  • 例如同一个每5分钟有1000条数据, 原来要存储1000条, 现在可以存储在一个 jsonb/数组 值里面, 节省空间. 并且聚集在少量的几个数据块中, 提升度性能.

2 优化器在分区裁剪部分有多大开销? 什么情况下会影响写入|读取性能?

drop table if exists tbl1;  
create unlogged table tbl1(  -- 为了方便测试使用unlogged table  
  sid int,   -- 传感器ID  
  v1 float,  -- v1维度的值  
  v2 float,  -- v2维度的值  
  v3 float,  -- v3维度的值  
  v4 float,  -- v4维度的值  
  v5 float,  -- v5维度的值  
  ts timestamp   -- 记录上报时间戳  
) partition by LIST (sid) ;  
create index on tbl1 using brin (ts);  
do language plpgsql $$  
declare  
begin  
  for i in 1..10000 loop  
    execute format($_$create unlogged table tbl1_%s PARTITION OF tbl1 FOR VALUES in (%s)$_$, i, i);  
  end loop;  
end;  
$$;  
create or replace function ins(int,float,float,float,float,float,timestamp) returns void as $$  
declare  
begin  
  execute format('execute p%s(%s,%s,%s,%s,%s,%s,%L)', $1, $1, $2, $3, $4, $5, $6, $7);  
  exception when others then  
    execute format('prepare p%s(int) as insert into tbl1_%s values($1, $2, $3, $4, $5, $6, $7) ', $1, $1);  
    execute format('execute p%s(%s,%s,%s,%s,%s,%s,%L)', $1, $1, $2, $3, $4, $5, $6, $7);  
end;  
$$ language plpgsql strict;  
vi test1.sql  
\set sid random(1,10000)  
select ins(:sid, random()*100,random()*100,random()*100,random()*100,random()*100, now()::timestamp);  
pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 10 -j 10 -T 120

3 为什么brin索引比btree索引占用的空间少这么多? 并且查询性能几乎一样. brin适合什么场景使用?

4 brin的索引结构如何? 有哪些微调参数, 这些微调参数的作用是什么?

5 常用的统计分析函数有哪些? 方差、最大最小平均值、中位数、N分位数? ...

参考

202301/20230105_01.md 《PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等》
202212/20221221_03.md 《PolarDB 开源版通过 brin 实现千分之一的存储空间, 高效率检索时序数据》
202206/20220610_05.md 《rule ELT 入库行存变列存 - 时序数据行存与点存实时转换》
202107/20210715_04.md 《PostgreSQL timescaledb 时序数据库 Hyperfunctions 时序数据分析函数》
202106/20210605_02.md 《重新发现PostgreSQL之美 - 13 brin 时序索引》
202105/20210518_02.md 《结合PostgreSQL, MADlib, Tensorflow 实现机器学习是时序分析. 使用本地数据, 不需要move data.》
202105/20210514_01.md 《PostgreSQL 时序数据库插件 timescaledb 2.2.1 通过custom plan provider接口实现index skip scan, 加速distinct, last_value, first_value等大表稀疏值快速搜索, 最快上万倍性能提升》
202105/20210509_01.md 《PostgreSQL 应用开发解决方案最佳实践系列课程 - 6. 时空、时态、时序、日志等轨迹系统》
202105/20210507_05.md 《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 2》
202104/20210429_03.md 《PostgreSQL - 时序、IoT类场景 - time_bucket 分析函数 - 内置 date_bin》
202104/20210429_02.md 《PostgreSQL - 时序、IoT类场景 - first_value , last_value , agg , cte , window , recursive》
202104/20210429_01.md 《PostgreSQL - 时序、IoT类场景 - 自定义histogram函数, 绘制数据分布柱状图 - cte window range width_bucket format plpgsql》
202104/20210428_03.md 《PostgreSQL 时序数据库设计最佳实践 - 关联 citus,columnar,partition,timescaledb,压缩,高速写,parallel append 多分区并行查询,分区》
202103/20210326_02.md 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 bloom filter - 随机,大量distinct value, 等值查询》
202103/20210326_01.md 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 multi-range min-max [s] - 分段范围索引》
202103/20210325_01.md 《PostgreSQL 14 preview - date_bin 任意起点, 任意bucket(interval) split bucket align 统计 - 时序场景(iot, 金融等), 关联timescaledb》
202103/20210324_01.md 《PostgreSQL 14 preview - brin 索引内存优化》
202004/20200412_03.md 《一款兼容PostgreSQL协议的时序数据库 - QuestDB》
201912/20191218_01.md 《PostgreSQL 时序数据库timescaledb支持compress调度》
201910/20191027_04.md 《PostgreSQL timescaledb插件 pg_prometheus - PostgreSQL作为prometheus的时序数据库必备插件》
201905/20190509_01.md 《pipelinedb 团队加入Confluent,TimescaleDB时序插件支持准实时聚合(类流式计算)》
201804/20180420_01.md 《[未完待续] PostgreSQL + timescaleDB + Prometheus 物联网时序数据监控组合》
201803/20180323_05.md 《PostgreSQL 11 preview - BRIN索引接口功能扩展(BLOOM FILTER、min max分段)》
201801/20180129_01.md 《PostgreSQL 时序数据库插件 timescaleDB 部署实践(含例子 纽约TAXI数据透视分析) - PostGIS + timescaleDB => PG时空数据库》
201712/20171225_01.md 《PostgreSQL 时序数据案例 - 时间流逝, 自动压缩(时间粒度老化), 同比\环比》
201711/20171107_25.md 《HTAP数据库 PostgreSQL 场景与性能测试之 24 - (OLTP) 物联网 - 时序数据并发写入(含时序索引BRIN)》
201711/20171107_16.md 《HTAP数据库 PostgreSQL 场景与性能测试之 15 - (OLTP) 物联网 - 查询一个时序区间的数据》
201708/20170824_01.md 《PostgreSQL BRIN索引的pages_per_range选项优化与内核代码优化思考》
201708/20170823_02.md 《时序业务,求最新值(从7秒到7毫秒的优化之旅) - 阿里云RDS PostgreSQL最佳实践》
201708/20170823_01.md 《万亿级电商广告 - brin黑科技带你(最低成本)玩转毫秒级圈人(视觉挖掘姊妹篇) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳实践》
201708/20170820_01.md 《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践》
201707/20170705_01.md 《PostgreSQL 海量时序数据(任意滑动窗口实时统计分析) - 传感器、人群、物体等对象跟踪》
201707/20170702_01.md 《PostgreSQL FDW 伪列实现 时序数据存储自动分区 - FUNCTION pushdown》
201706/20170617_01.md 《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
201706/20170611_02.md 《PostgreSQL 并行写入堆表,如何保证时序线性存储 - BRIN索引优化》
201705/20170518_01.md 《(流式、lambda、触发器)实时处理大比拼 - 物联网(IoT)\金融,时序处理最佳实践》
201704/20170417_01.md 《PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践》
201704/20170409_05.md 《时序数据库有哪些特点? TimescaleDB时序数据库介绍》
201704/20170405_01.md 《PostgreSQL 10.0 preview 功能增强 - BRIN 索引更新smooth化》
201702/20170219_01.md 《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》
201611/20161128_01.md 《时序数据合并场景加速分析和实现 - 复合索引,窗口分组查询加速,变态递归加速》
201604/20160414_01.md 《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》
201505/20150526_01.md 《PostgreSQL 9.5 new feature - lets BRIN be used with R-Tree-like indexing strategies For "inclusion" opclasses》
201504/20150419_01.md 《PostgreSQL 9.5 new feature - BRIN (block range index) index》
相关实践学习
函数计算X RDS PostgreSQL,基于LLM大语言模型构建AI知识库
基于ChatGLM6B 大语言模型构建AI知识库问答应用。答疑群17125058181
目录
相关文章
|
3月前
|
存储 SQL 安全
应用案例|开源 PolarDB-X 在互联网安全场景的应用实践
中盾集团采用PolarDB-X云原生分布式数据库开源版本,有效解决了大数据量处理、复杂查询以及历史数据维护等难题,实现了业务的高效扩展与优化。
|
21天前
|
安全 物联网 网络安全
智能设备的安全隐患:物联网(IoT)安全指南
智能设备的安全隐患:物联网(IoT)安全指南
61 12
|
19天前
|
传感器 监控 安全
物联网(IoT):定义、影响与未来
物联网(IoT):定义、影响与未来
46 3
|
28天前
|
存储 JSON 运维
智能物联网平台:Azure IoT Hub在设备管理中的实践
【10月更文挑战第26天】随着物联网技术的发展,Azure IoT Hub成为企业管理和连接数百万台设备的强大平台。本文介绍Azure IoT Hub的设备管理功能,包括设备注册、设备孪生、直接方法和监控诊断,并通过示例代码展示其应用。
40 4
|
27天前
|
SQL 监控 物联网
ClickHouse在物联网(IoT)中的应用:实时监控与分析
【10月更文挑战第27天】随着物联网(IoT)技术的快速发展,越来越多的设备被连接到互联网上,产生了海量的数据。这些数据不仅包含了设备的状态信息,还包括用户的使用习惯、环境参数等。如何高效地处理和分析这些数据,成为了一个重要的挑战。作为一位数据工程师,我在一个物联网项目中深入使用了ClickHouse,以下是我的经验和思考。
61 0
|
2月前
|
人工智能 安全 物联网
|
3月前
|
存储 物联网 关系型数据库
PolarDB在物联网(IoT)数据存储中的应用探索
【9月更文挑战第6天】随着物联网技术的发展,海量设备数据对实时存储和处理提出了更高要求。传统数据库在扩展性、性能及实时性方面面临挑战。阿里云推出的PolarDB具备高性能、高可靠及高扩展性特点,能有效应对这些挑战。它采用分布式存储架构,支持多副本写入优化、并行查询等技术,确保数据实时写入与查询;多副本存储架构和数据持久化存储机制保证了数据安全;支持动态调整数据库规模,适应设备和数据增长。通过API或SDK接入IoT设备,实现数据实时写入、分布式存储与高效查询,展现出在IoT数据存储领域的巨大潜力。
70 1
|
3月前
|
传感器 监控 安全
物联网通信的基石:LoRa、Sigfox与NB-IoT详解
物联网通信的基石:LoRa、Sigfox与NB-IoT详解
341 0
|
3月前
|
关系型数据库 MySQL 分布式数据库
【开发者评测】PolarDB MySQL场景评测获奖名单公布
PolarDB MySQL场景评测获奖名单公布!!
|
4月前
|
消息中间件 存储 传感器
RabbitMQ 在物联网 (IoT) 项目中的应用案例
【8月更文第28天】随着物联网技术的发展,越来越多的设备被连接到互联网上以收集和传输数据。这些设备可以是传感器、执行器或其他类型的硬件。为了有效地管理这些设备并处理它们产生的大量数据,需要一个可靠的消息传递系统。RabbitMQ 是一个流行的开源消息中间件,它提供了一种灵活的方式来处理和转发消息,非常适合用于物联网环境。
174 1

热门文章

最新文章

相关产品

  • 云原生数据库 PolarDB