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

背景

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

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

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

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

x86_64机器使用以下docker image:

• 《amd64 image》

ARM机器使用以下docker image:

• 《arm64 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 分区表(分区类型、多级分区、表达式分区、非平衡分区...) : 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 任务调度 :

• 《PostgreSQL Oracle 兼容性之 - DBMS_JOBS - Daily Maintenance - Timing Tasks(pgagent)》
• 《PostgreSQL JOB 插件 - pg_task》

思考

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

• 对于同一个传感器ID, 采用聚集存储类型jsonb/数组?

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

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

• 使用timescaledb插件有什么功能、性能提升? 《PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等》

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分位数? ...

• https://www.postgresql.org/docs/16/functions-aggregate.html

参考

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》