作者
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 任务调度 :
- 《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分位数? ...
