HTAP数据库 PostgreSQL 场景与性能测试之 2 - (OLTP) 多表JOIN-阿里云开发者社区

开发者社区> 德哥> 正文

HTAP数据库 PostgreSQL 场景与性能测试之 2 - (OLTP) 多表JOIN

简介:
+关注继续查看

标签

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来估算。

场景 - 多表JOIN (OLTP)

1、背景

数据属性分散设计,查询时,通过多表JOIN提取各个属性表的数据。

2、设计

10张表,每张表1000万数据量,一对一,join。

3、准备测试表

create table t1(  
  id int primary key,  
  info text default 'tessssssssssssssssssssssssssssssssssssst',   
  state int default 0,   
  crt_time timestamp default now(),   
  mod_time timestamp default now()  
);  
  
create table t2 (like t1 including all);  
create table t3 (like t1 including all);  
create table t4 (like t1 including all);  
create table t5 (like t1 including all);  
create table t6 (like t1 including all);  
create table t7 (like t1 including all);  
create table t8 (like t1 including all);  
create table t9 (like t1 including all);  
create table t10 (like t1 including all);  

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

5、准备测试数据

insert into t1 select generate_series(1,10000000);  
insert into t2 select * from t1;  
insert into t3 select * from t1;  
insert into t4 select * from t1;  
insert into t5 select * from t1;  
insert into t6 select * from t1;  
insert into t7 select * from t1;  
insert into t8 select * from t1;  
insert into t9 select * from t1;  
insert into t10 select * from t1;  
alter role all set join_collapse_limit=1;  

6、准备测试脚本

vi test.sql  
  
\set id random(1,10000000)  
select * from t1 join t2 using (id) join t3 using (id) join t4 using (id) join t5 using (id) join t6 using (id) join t7 using (id) join t8 using (id) join t9 using (id) join t10 using (id) where t1.id=:id;  

7、测试

CONNECTS=112  
TIMES=300  
export PGHOST=$PGDATA  
export PGPORT=1999  
export PGUSER=postgres  
export PGPASSWORD=postgres  
export PGDATABASE=postgres  
  
pgbench -M prepared -n -r -P 5 -f ./test.sql -c $CONNECTS -j $CONNECTS -T $TIMES  
alter role all reset join_collapse_limit;  

8、测试结果

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 112  
number of threads: 112  
duration: 300 s  
number of transactions actually processed: 31033358  
latency average = 1.083 ms  
latency stddev = 1.063 ms  
tps = 103408.162713 (including connections establishing)  
tps = 103433.470434 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.001  \set id random(1,10000000)  
         1.082  select * from t1 join t2 using (id) join t3 using (id) join t4 using (id) join t5 using (id) join t6 using (id) join t7 using (id) join t8 using (id) join t9 using (id) join t10 using (id) where t1.id=:id;  

TPS:103433

平均响应时间:1.083 毫秒

参考

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

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

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

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

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
《vSphere性能设计:性能密集场景下CPU、内存、存储及网络的最佳设计实践》一3.3.2 实验室小结
本节书摘来华章计算机《vSphere性能设计:性能密集场景下CPU、内存、存储及网络的最佳设计实践》一书中的第3章 ,第3.3.2节,[美] 克里斯托弗·库塞克(Christopher Kusek) 著 吕南德特·施皮斯(Rynardt Spies)姚海鹏 刘韵洁 译, 更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1105 0
《vSphere性能设计:性能密集场景下CPU、内存、存储及网络的最佳设计实践》一3.4 小结
本节书摘来华章计算机《vSphere性能设计:性能密集场景下CPU、内存、存储及网络的最佳设计实践》一书中的第3章 ,第3.4节,[美] 克里斯托弗·库塞克(Christopher Kusek) 著 吕南德特·施皮斯(Rynardt Spies)姚海鹏 刘韵洁 译, 更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1109 0
PostgreSQL 通过SQL接口关闭、重启数据库
PostgreSQL 通过SQL接口关闭、重启数据库
1153 0
分布式数据库如何实现 Join?
PolarDB-X 不仅语法兼容 MySQL,作为分布式数据库,也力求保持与单机数据库一致的使用体验。在分布式场景下,Join 的两张表可能都是分布式表,因此需要通过多次网络请求获取相应的数据。如何高效地实现这一点呢?
649 0
使用Spring AOP实现MySQL数据库读写分离案例分析
使用Spring AOP实现MySQL数据库读写分离案例分析 前言 分布式环境下数据库的读写分离策略是解决数据库读写性能瓶颈的一个关键解决方案,更是最大限度了提高了应用中读取 (Read)数据的速度和并发量。
1776 0
MySQL Innodb数据库性能实践
在实际工作中,经常有同事问道:MySQL Innodb表记录数多大是合适的? 一般的理解肯定是表越大性能越低,但具体低多少呢,是缓慢下降还是急剧下降,是1000万就下降还是1亿才下降呢? 针对这些问题,我做了一下基准测试,基准测试环境如下: ...
703 0
《vSphere性能设计:性能密集场景下CPU、内存、存储及网络的最佳设计实践》一3.3.3 供应实验室
本节书摘来华章计算机《vSphere性能设计:性能密集场景下CPU、内存、存储及网络的最佳设计实践》一书中的第3章 ,第3.3.3节,[美] 克里斯托弗·库塞克(Christopher Kusek) 著 吕南德特·施皮斯(Rynardt Spies)姚海鹏 刘韵洁 译, 更多章节内容可以访问云栖社区“华章计算机”公众号查看。
854 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载