HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

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

场景 - 不含索引多表单点写入 (OLTP+OLAP)

1、背景

不含索引,多表(128个表),每次写入一条记录。这是非常典型的测试TP或AP场景,数据实时灌入场景的能力。

2、设计

多表(128个表),不含索引,单事务单条写入(一次写入1条)。高并发。

3、准备测试表

create table t_sensor(    
  id int8,    
  c1 int8 default 0,    
  c2 int8 default 0,    
  c3 int8 default 0,    
  c4 float8 default 0,    
  c5 text default 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',    
  ts timestamp default clock_timestamp()    
) with (autovacuum_enabled=off, toast.autovacuum_enabled=off);    
    
-- create index idx_t_sensor_ts on t_sensor using btree (ts);    
do language plpgsql $$  
declare  
begin  
  for i in 1..128 loop  
    execute format('create table t_sensor%s (like t_sensor including all) inherits (t_sensor) with (autovacuum_enabled=off, toast.autovacuum_enabled=off)', i);  
  end loop;  
end;  
$$;  

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

create or replace function ins_sensor(int, int) returns void as $$  
declare  
begin  
  execute format('insert into t_sensor%s (id) values (%s)', $1, $2);  
  -- 为了拼接表名,使用了动态SQL,硬解析耗时。  
  -- 导致测试结果有出入,至少不会比单表无索引写入性能差。  
  -- 批量写入的话,硬解析的问题可以被掩盖。  
end;  
$$ language plpgsql strict;  

5、准备测试数据

6、准备测试脚本

vi test.sql    
    
\set sid random(1,128)  
\set id random(1,100000000)  
select ins_sensor(:sid, :id);  

压测

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、测试

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: 54481238  
latency average = 0.308 ms  
latency stddev = 0.496 ms  
tps = 181596.052130 (including connections establishing)  
tps = 181613.896897 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set sid random(1,128)  
         0.000  \set id random(1,100000000)  
         0.306  select ins_sensor(:sid, :id);  

TPS: 181613 ( 动态SQL,导致测试结果与实际能力有出入 )

多表(128个表),不含索引,单事务单条写入(一次写入1条)。高并发。

  -- 为了拼接表名,使用了动态SQL,硬解析耗时。  
  
  -- 导致测试结果有出入,至少不会比单表无索引写入性能差。至少不低于 26万行/s.    
  
  -- 批量写入的话,硬解析的问题可以被掩盖。  

主要瓶颈:xlog lock.

平均响应时间: 0.308 毫秒

多表(128个表),不含索引,单事务单条写入(一次写入1条)。高并发。

主要瓶颈:xlog lock.

参考

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

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

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

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

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

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
20天前
|
数据库 索引
深入探索数据库索引技术:回表与索引下推解析
【10月更文挑战第15天】在数据库查询优化的领域中,回表和索引下推是两个核心概念,它们对于提高查询性能至关重要。本文将详细解释这两个术语,并探讨它们在数据库操作中的作用和影响。
43 3
|
20天前
|
数据库 索引
深入理解数据库索引技术:回表与索引下推详解
【10月更文挑战第23天】 在数据库查询性能优化中,索引的使用是提升查询效率的关键。然而,并非所有的索引都能直接加速查询。本文将深入探讨两个重要的数据库索引技术:回表和索引下推,解释它们的概念、工作原理以及对性能的影响。
42 3
|
6天前
|
数据库 索引
数据库索引
数据库索引 1、索引:建立在表一列或多列的辅助对象,目的是加快访问表的数据。 2、索引的优点: (1)、创建唯一性索引,可以确保数据的唯一性; (2)、大大加快数据检索速度; (3)、加速表与表之间的连接; (4)、在查询过程中,使用优化隐藏器,提高系统性能。 3、索引的缺点: (1)、创建和维护索引需要耗费时间,随数据量增加而增加; (2)、索引占用物理空间; (3)、对表的数据进行增删改时,索引需要动态维护,降低了数据的维护速度。
18 2
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
61 3
Mysql(4)—数据库索引
|
17天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
84 1
|
11天前
|
存储 关系型数据库 数据库
Postgres数据库BRIN索引介绍
BRIN索引是PostgreSQL提供的一种高效、轻量级的索引类型,特别适用于大规模、顺序数据的范围查询。通过存储数据块的摘要信息,BRIN索引在降低存储和维护成本的同时,提供了良好的查询性能。然而,其适用场景有限,不适合随机数据分布或频繁更新的场景。在选择索引类型时,需根据数据特性和查询需求进行权衡。希望本文对你理解和使用PostgreSQL的BRIN索引有所帮助。
19 0
|
18天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
47 0
|
1月前
|
关系型数据库 MySQL 数据库
深入浅出MySQL索引优化:提升数据库性能的关键
在这个数据驱动的时代,数据库性能的优劣直接关系到应用的响应速度和用户体验。MySQL作为广泛使用的数据库之一,其索引优化是提升查询性能的关键。本文将带你一探MySQL索引的内部机制,分析索引的类型及其适用场景,并通过实际案例演示如何诊断和优化索引,以实现数据库性能的飞跃。
|
1月前
|
存储 SQL 数据库
深入理解数据库索引:提升查询性能的关键
数据库索引是优化查询性能的重要工具。本文将带你深入探索索引的内部结构和工作原理,揭示如何通过合理使用索引来加速数据库查询,同时避免常见的索引陷阱。
|
2月前
|
关系型数据库 MySQL 测试技术
《性能测试》读书笔记_数据库优化
《性能测试》读书笔记_数据库优化
31 7

相关产品

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