PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理. 如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等.本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.

作者

digoal

日期

2023-08-05

标签

PostgreSQL , PolarDB , 队列 , 锁 , hash mod , advisory lock , cte , update limit , delete limit , vacuum , index , IO浪费 , CPU浪费


背景

在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理.

这个过程的核心流程: 高速写入队列、从队列按先后顺序提取并高速处理、从队列清除已处理订单记录.

如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等.

  • 文末的《打车与宇宙大爆炸的关系》一文有相似问题和优化方法, 思路类似.

本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.

想体验一下的同学, 也可以通过云起实验启动环境来进行体验, 这个实验室是永久免费的.

DEMO

1、测试环境

MacBook Pro (15-inch, 2018)  
2.2 GHz 六核Intel Core i7  
32 GB 2400 MHz DDR4  
PostgreSQL 15.1

因为是macos, 可能需要设置一下ulimit.

ulimit -n 1000000

2、上游写入订单处理队列表

create table t_order_q (  
  id serial8 primary key,   -- 自增主键  
  order_id uuid unique,     -- 上游传递过来的订单号  
  cts timestamp not null    -- 上游传递过来的订单创建时间   
);   
-- create index on t_order_q (cts); -- 如果按订单时间先后取出处理, 则需要创建时间字段索引.  也可以按自增主键顺序处理, 则不需要时间索引.

3、取出并处理后的订单状态表

create table t_order_u (  
  id serial8 primary key,   -- 自增主键  
  order_id uuid unique,     -- 上游传递过来的订单号  
  cts timestamp not null,    -- 上游传递过来的订单创建时间   
  uts timestamp not null,   -- 订单处理时间  
  status int not null       -- 订单处理状态标记   
);

4、写入100万条订单队列

insert into t_order_q (order_id, cts) select gen_random_uuid(), clock_timestamp() from generate_series(1,1000000);

5、写pgbench压测脚本, 从队列取出, 并且使用ad lock对队列ID加事务锁, 判断是否正在处理, 事务结束自动释放ad lock. ad lock也经常被用于秒杀场景泄压.

vi t.sql  
with tmp as   
  (delete from t_order_q where ctid = (select ctid from t_order_q where pg_try_advisory_xact_lock(id) order by id limit 1) returning order_id, cts)  
insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;
begin;
select id as v_id from t_order_q where pg_try_advisory_xact_lock(id) order by id limit 1 \gset
with tmp as (delete from t_order_q where id = :v_id returning order_id, cts) 
  insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;
或(sleep 模拟应用拿到需要处理的订单后的应用端操作增加的耗时.)
begin; 
select id as v_id from t_order_q where pg_try_advisory_xact_lock(id) order by id limit 1 \gset
\sleep 10ms
with tmp as (delete from t_order_q where id = :v_id returning order_id, cts) 
  insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;

6、压测256个并发消耗队列, 平均每个连接处理3906个事务.

select 1000000/256.0;  
3906.2500000000000

7、压测结果

pgbench -M extended -f ./t.sql -n -r -P 1 -c 256 -j 2 -t 3906
transaction type: ./t.sql  
scaling factor: 1  
query mode: extended  
number of clients: 256  
number of threads: 2  
maximum number of tries: 1  
number of transactions per client: 3906  
number of transactions actually processed: 999936/999936  
number of failed transactions: 0 (0.000%)  
latency average = 8.111 ms  
latency stddev = 5.376 ms  
initial connection time = 429.698 ms  
tps = 25379.081141 (without initial connection time)  
statement latencies in milliseconds and failures:  
         8.114           0  with tmp as

未优化前的性能如何?

1、写pgbench压测脚本, 从队列取出, 并且使用ad lock对队列ID加事务锁, 判断是否正在处理, 事务结束自动释放ad lock. ad lock也经常被用于秒杀场景泄压.

vi t1.sql  
begin;  
select id as vid from t_order_q order by id for update limit 1 \gset  
with tmp as   
  (delete from t_order_q where id = :vid returning order_id, cts)  
insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;

2、压测结果

pgbench -M extended -f ./t1.sql -n -r -P 1 -c 256 -j 2 -t 3906
TPS 约 1200.

增加了skip locked后, TPS也只能到2500左右. 降低并发后使用skip locked性能可提升到8K tps左右.

begin;  
select id as vid from t_order_q order by id for update skip locked limit 1 \gset  
with tmp as   
  (delete from t_order_q where id = :vid returning order_id, cts)  
insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;

还有什么可以提升性能的点?

1、减少浪费的IO和cpu计算:

  • 在并发的情况下, order by id limit 1需要扫描若干行, 而不是1行, 因为可能有些ID已经被ad lock touch了, 浪费的pg_try_advisory_xact_lock() cpu ops计算次数约等于 n + n-1 + n-2 + ... + n-n, 浪费的IO约等于N.

优化方法:

  • 固定N个链接, 按ID hash mod 取不同的数据分片, 从而减少浪费的IO和cpu计算.
  • 或者将队列表拆分成几个分区表, 入库的时候 按id hash mode, 每个分区分配给不同的进程取数, 从而减少冲突和浪费的扫描提高并发.

2、提高index vacuum的频率, 减少因没有index version导致的垃圾数据判断带来的cpu和回表的IO浪费. 提升autovacuum_work_mem, 容纳下所有dead tuple ctid避免多次扫描index.

优化方法:

  • 配置参数autovacuum_naptime、autovacuum_work_mem(或者老版本 maintenance_work_mem)即可.

3、使用并行vacuum, 配置max_parallel_maintenance_workers.

4、配置vacuum使用prefetch blocks, 减少io delay带来的vacuum 比较久的问题. (适合 单次IO delay较高, 但是吞吐没有瓶颈的云盘)

5、一次取出多条, 批量处理.

6、使用IOPS较高, 单次IO delay较低的本地nvme SSD.

更多请参考末尾文章.

参考

《DB吐槽大会,第69期 - PG 不支持update | delete limit语法》

《在PostgreSQL中实现update | delete limit - CTID扫描实践 (高效阅后即焚)》

《PostgreSQL skip locked与CTE妙用 - 解决并发批量更新锁冲突带来的锁等待,提高处理吞吐》

《PostgreSQL SELECT 的高级用法(CTE, LATERAL, ORDINALITY, WINDOW, SKIP LOCKED, DISTINCT, GROUPING SETS, ...) - 珍藏级》

《PostgreSQL 秒杀4种方法 - 增加 批量流式加减库存 方法》

《HTAP数据库 PostgreSQL 场景与性能测试之 30 - (OLTP) 秒杀 - 高并发单点更新》

《聊一聊双十一背后的技术 - 不一样的秒杀技术, 裸秒》

《PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem》

《[直播]为什么打车和宇宙大爆炸有关?》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
7天前
|
JavaScript 关系型数据库 API
Nest.js 实战 (二):如何使用 Prisma 和连接 PostgreSQL 数据库
这篇文章介绍了什么是Prisma以及如何在Node.js和TypeScript后端应用中使用它。Prisma是一个开源的下一代ORM,包含PrismaClient、PrismaMigrate、PrismaStudio等部分。文章详细叙述了安装PrismaCLI和依赖包、初始化Prisma、连接数据库、定义Prisma模型、创建Prisma模块的过程,并对比了Prisma和Sequelize在Nest.js中的使用体验,认为Prisma更加便捷高效,没有繁琐的配置。
Nest.js 实战 (二):如何使用 Prisma 和连接 PostgreSQL 数据库
|
3天前
|
存储 关系型数据库 数据库
关系型数据库设计范式:深入理解与实践
【7月更文挑战第20天】关系型数据库设计范式是数据库设计中的重要指导原则,它通过一系列规范来减少数据冗余、提高数据一致性和优化查询性能。在实际应用中,我们应该根据具体需求和数据特点,灵活选择和应用不同的范式级别,以构建高效、可靠和可扩展的数据库系统。同时,也需要注意范式设计带来的挑战和限制,根据实际情况进行权衡和调整。
|
5天前
|
SQL Oracle 关系型数据库
|
6天前
|
SQL 监控 关系型数据库
实时计算 Flink版操作报错合集之在设置监控PostgreSQL数据库时,将wal_level设置为logical,出现一些表更新和删除操作报错,怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
7天前
|
关系型数据库 MySQL 数据库
实时计算 Flink版操作报错合集之在处理PostgreSQL数据库遇到报错。该如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
4天前
|
关系型数据库 分布式数据库 数据库
基于PolarDB的图分析:保险数据分析实践
本文以公开的保险数据集为例,示例了基于云原生数据库PolarDB上,在保险理赔场景下,执行图查询来发现异常理赔记录和欺诈团伙:例如,查询与欺诈保单有相同理赔病人的其他保单,或者找出欺诈保单的投保人社交关系,以便进行欺诈预警。PolarDB在关系型数据库的基础上,提供了图分析能力,为企业的统一数据管理和分析,提供了强有力的支撑。
|
17天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
15天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
990 7
Mysql 数据库主从复制
|
15天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
5天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。

相关产品

  • 云原生数据库 PolarDB