HTAP数据库 PostgreSQL 场景与性能测试之 24 - (OLTP) 物联网 - 时序数据并发写入(含时序索引BRIN)

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试


背景

PostgreSQL是一个历史悠久的数据库,历史可以追溯到1973年,最早由2014计算机图灵奖得主,关系数据库的鼻祖Michael_Stonebraker 操刀设计,PostgreSQL具备与Oracle类似的功能、性能、架构以及稳定性。

pic

PostgreSQL社区的贡献者众多,来自全球各个行业,历经数年,PostgreSQL 每年发布一个大版本,以持久的生命力和稳定性著称。

2017年10月,PostgreSQL 推出10 版本,携带诸多惊天特性,目标是胜任OLAP和OLTP的HTAP混合场景的需求:

《最受开发者欢迎的HTAP数据库PostgreSQL 10特性》

1、多核并行增强

2、fdw 聚合下推

3、逻辑订阅

4、分区

5、金融级多副本

6、json、jsonb全文检索

7、还有插件化形式存在的特性,如 向量计算、JIT、SQL图计算、SQL流计算、分布式并行计算、时序处理、基因测序、化学分析、图像分析 等。

pic

在各种应用场景中都可以看到PostgreSQL的应用:

pic

PostgreSQL近年来的发展非常迅猛,从知名数据库评测网站dbranking的数据库评分趋势,可以看到PostgreSQL向上发展的趋势:

pic

从每年PostgreSQL中国召开的社区会议,也能看到同样的趋势,参与的公司越来越多,分享的公司越来越多,分享的主题越来越丰富,横跨了 传统企业、互联网、医疗、金融、国企、物流、电商、社交、车联网、共享XX、云、游戏、公共交通、航空、铁路、军工、培训、咨询服务等 行业。

接下来的一系列文章,将给大家介绍PostgreSQL的各种应用场景以及对应的性能指标。

环境

环境部署方法参考:

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户)》

阿里云 ECS:56核,224G,1.5TB*2 SSD云盘

操作系统:CentOS 7.4 x64

数据库版本:PostgreSQL 10

PS:ECS的CPU和IO性能相比物理机会打一定的折扣,可以按下降1倍性能来估算。跑物理主机可以按这里测试的性能乘以2来估算。

场景 - 物联网 - 时序数据并发写入(含时序索引BRIN) (OLTP)

1、背景

物联网数据,并发量大,写入吞吐大,但是时序属性,按时间区间查询、聚合、过滤、流式处理的需求最为旺盛。

PostgreSQL的时序索引(也可以称为块级索引),索引小,但是对于时序数据的过滤性特别好,并且几乎不影响写入效率。

2、设计

1、单表,含时序索引,单条并发写入。

2、多表,含时序索引,单条并发写入。

3、单表,含时序索引,批量并发写入。

4、多表,含时序索引,批量并发写入。

3、准备测试表

包含索引。

create table feed (id int, val float, crt_time timestamp default now());  
create index idx_feed on feed using BRIN (crt_time) tablespace tbs1;  
  
do language plpgsql $$  
declare  
begin  
  for i in 1..1024 loop  
    execute 'create table feed'||i||' (like feed including all)';  
  end loop;  
end;  
$$;  

4、准备测试函数(可选)

动态SQL,写入不同分表。

create or replace function ins_batch(int, int) returns void as $$  
declare  
begin  
  execute 'insert into feed'||$1||' select id , 0.1 from generate_series(1,'||$2||') t(id)';  
end;  
$$ language plpgsql strict;  
  
create or replace function ins(int) returns void as $$  
declare  
begin  
  execute 'insert into feed'||$1||' values (1, 0.1)';  
end;  
$$ language plpgsql strict;  

5、准备测试数据

6、准备测试脚本

1、单表,含时序索引,单条并发写入。

vi test.sql  
  
insert into feed (id, val) values (1,0.1);  

2、多表,含时序索引,单条并发写入。

vi test.sql  
  
\set suffix random(1,1024)  
select ins(:suffix)  

3、单表,含时序索引,批量并发写入。

vi test.sql  
  
insert into feed (id, val) select 1, 0.1 from generate_series(1,1000);  

4、多表,含时序索引,批量并发写入。

vi test.sql  
  
\set suffix random(1,1024)  
select ins_batch(:suffix, 1000)  

压测

CONNECTS=56  
TIMES=300  
export PGHOST=$PGDATA  
export PGPORT=1999  
export PGUSER=postgres  
export PGPASSWORD=postgres  
export PGDATABASE=postgres  
  
pgbench -M prepared -n -r -f ./test.sql -P 5 -c $CONNECTS -j $CONNECTS -T $TIMES  

7、测试

1、单表,含时序索引,单条并发写入。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 300 s  
number of transactions actually processed: 81975309  
latency average = 0.205 ms  
latency stddev = 0.351 ms  
tps = 273236.057797 (including connections establishing)  
tps = 273259.238238 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.205  insert into feed (id, val) values (1,0.1);  

2、多表,含时序索引,单条并发写入。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 300 s  
number of transactions actually processed: 52089776  
latency average = 0.322 ms  
latency stddev = 0.267 ms  
tps = 173584.822070 (including connections establishing)  
tps = 173612.564907 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set suffix random(1,1024)  
         0.321  select ins(:suffix)  

3、单表,含时序索引,批量并发写入。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 300 s  
number of transactions actually processed: 605700  
latency average = 27.735 ms  
latency stddev = 25.144 ms  
tps = 2018.830266 (including connections establishing)  
tps = 2019.002544 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
        27.735  insert into feed (id, val) select 1, 0.1 from generate_series(1,1000);  

4、多表,含时序索引,批量并发写入。

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 300 s
number of transactions actually processed: 941248
latency average = 17.847 ms
latency stddev = 27.876 ms
tps = 3137.373893 (including connections establishing)
tps = 3137.648888 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.002  \set suffix random(1,1024)
        17.846  select ins_batch(:suffix, 1000)

TPS

1、单表,含时序索引,单条并发写入。TPS: 273259 。

2、多表,含时序索引,单条并发写入。TPS: 173612 。

3、单表,含时序索引,批量并发写入。TPS: 2019 。相当于每秒写入 201.9万 条记录。

4、多表,含时序索引,批量并发写入。TPS: 3137 。相当于每秒写入 313.7万 条记录。

平均响应时间

1、单表,含时序索引,单条并发写入。0.205 毫秒。

2、多表,含时序索引,单条并发写入。0.322 毫秒。

3、单表,含时序索引,批量并发写入。27.735 毫秒。

4、多表,含时序索引,批量并发写入。17.847 毫秒。

参考

《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》

《数据库选型之 - 大象十八摸 - 致 架构师、开发者》

《PostgreSQL 使用 pgbench 测试 sysbench 相关case》

《数据库界的华山论剑 tpc.org》

https://www.postgresql.org/docs/10/static/pgbench.html

相关实践学习
钉钉群中如何接收IoT温控器数据告警通知
本实验主要介绍如何将温控器设备以MQTT协议接入IoT物联网平台,通过云产品流转到函数计算FC,调用钉钉群机器人API,实时推送温湿度消息到钉钉群。
阿里云AIoT物联网开发实战
本课程将由物联网专家带你熟悉阿里云AIoT物联网领域全套云产品,7天轻松搭建基于Arduino的端到端物联网场景应用。 开始学习前,请先开通下方两个云产品,让学习更流畅: IoT物联网平台:https://iot.console.aliyun.com/ LinkWAN物联网络管理平台:https://linkwan.console.aliyun.com/service-open
目录
相关文章
|
13天前
|
SQL 存储 运维
从建模到运维:联犀如何完美融入时序数据库 TDengine 实现物联网数据流畅管理
本篇文章是“2024,我想和 TDengine 谈谈”征文活动的三等奖作品。文章从一个具体的业务场景出发,分析了企业在面对海量时序数据时的挑战,并提出了利用 TDengine 高效处理和存储数据的方法,帮助企业解决在数据采集、存储、分析等方面的痛点。通过这篇文章,作者不仅展示了自己对数据处理技术的理解,还进一步阐释了时序数据库在行业中的潜力与应用价值,为读者提供了很多实际的操作思路和技术选型的参考。
24 1
|
5月前
|
物联网 数据管理 Apache
拥抱IoT浪潮,Apache IoTDB如何成为你的智能数据守护者?解锁物联网新纪元的数据管理秘籍!
【8月更文挑战第22天】随着物联网技术的发展,数据量激增对数据库提出新挑战。Apache IoTDB凭借其面向时间序列数据的设计,在IoT领域脱颖而出。相较于传统数据库,IoTDB采用树形数据模型高效管理实时数据,具备轻量级结构与高并发能力,并集成Hadoop/Spark支持复杂分析。在智能城市等场景下,IoTDB能处理如交通流量等数据,为决策提供支持。IoTDB还提供InfluxDB协议适配器简化迁移过程,并支持细致的权限管理确保数据安全。综上所述,IoTDB在IoT数据管理中展现出巨大潜力与竞争力。
131 1
|
2月前
|
JavaScript 安全 编译器
TypeScript 与 Jest 测试框架的结合使用,从 TypeScript 的测试需求出发,介绍了 Jest 的特点及其与 TypeScript 结合的优势,详细讲解了基本测试步骤、常见测试场景及异步操作测试方法
本文深入探讨了 TypeScript 与 Jest 测试框架的结合使用,从 TypeScript 的测试需求出发,介绍了 Jest 的特点及其与 TypeScript 结合的优势,详细讲解了基本测试步骤、常见测试场景及异步操作测试方法,并通过实际案例展示了其在项目中的应用效果,旨在提升代码质量和开发效率。
60 6
|
2月前
|
传感器 安全 算法
物联网发布者在数据传输过程中如何防止数据被篡改
在物联网数据传输中,为防止数据被篡改,可采用加密技术、数字签名、数据完整性校验等方法,确保数据的完整性和安全性。
|
2月前
|
存储 安全 算法
物联网发布者在发送数据时如何保证数据的安全性和完整性
数据加密、密钥管理和数据完整性验证是物联网安全的重要组成部分。对称加密(如AES)和非对称加密(如RSA)分别适用于大量数据和高安全需求的场景。密钥需安全存储并定期更新。数据完整性通过MAC(如HMAC-SHA256)和数字签名(如RSA签名)验证。通信协议如MQTT over TLS/SSL和CoAP over DTLS增强传输安全,确保数据在传输过程中的机密性和完整性。
|
2月前
|
网络协议 关系型数据库 应用服务中间件
【项目场景】请求数据时测试环境比生产环境多花了1秒是怎么回事?
这是一位粉丝(谢同学)给V哥的留言,描述了他在优化系统查询时遇到的问题:测试环境优化达标,但生产环境响应时间多出1秒。通过抓包分析,发现MySQL请求和响应之间存在500毫秒的延迟,怀疑是网络传输开销。V哥给出了以下优化建议:
|
4月前
|
设计模式 SQL 安全
PHP中的设计模式:单例模式的深入探索与实践在PHP的编程实践中,设计模式是解决常见软件设计问题的最佳实践。单例模式作为设计模式中的一种,确保一个类只有一个实例,并提供全局访问点,广泛应用于配置管理、日志记录和测试框架等场景。本文将深入探讨单例模式的原理、实现方式及其在PHP中的应用,帮助开发者更好地理解和运用这一设计模式。
在PHP开发中,单例模式通过确保类仅有一个实例并提供一个全局访问点,有效管理和访问共享资源。本文详细介绍了单例模式的概念、PHP实现方式及应用场景,并通过具体代码示例展示如何在PHP中实现单例模式以及如何在实际项目中正确使用它来优化代码结构和性能。
61 2
|
4月前
|
JavaScript 前端开发 数据库
数据库测试场景实践总结
本文介绍了数据库超时和应用锁表SSDB测试场景的验证方法,通过锁定数据表模拟写入失败情况,并利用SSDB进行重试。测试需开发人员配合验证功能。同时,提供了SSDB服务器登录、查询队列数量及重启服务等常用命令。适用于验证和解决数据库写入问题。
45 7
|
5月前
|
传感器 物联网 测试技术
未来科技浪潮中的领航者:区块链、物联网与虚拟现实的融合与创新探索自动化测试之美——以Selenium为例
【8月更文挑战第30天】本文深入探讨了当前最前沿的技术趋势——区块链、物联网和虚拟现实,并分析了它们各自的发展脉络及相互之间的融合可能性。我们将通过具体应用场景描绘这些技术如何塑造未来社会的面貌,同时提供代码示例以加深理解。文章旨在为读者揭示这些技术背后的巨大潜力,以及它们将如何影响我们的工作和生活方式。
|
5月前
|
物联网 测试技术 持续交付
软件测试的艺术与科学:探索自动化测试框架未来技术的融合与创新:探索区块链、物联网和虚拟现实的交汇点
【8月更文挑战第30天】在软件开发的海洋中,测试是确保航行安全不可或缺的灯塔。本文将带领读者揭开软件测试神秘的面纱,深入理解自动化测试框架的重要性和实现方法。通过实际案例,我们将一起探索如何构建高效、可靠的自动化测试系统,从而保障软件质量,提升开发效率。

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版