PostgreSQL 性能优化之 - 大锁与long sql/xact的蝴蝶效应

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介:

在使用数据库时,某些特定的因素结合在一起,就可能引发蝴蝶效应。
导致数据库性能的急剧下降。
本文要讲的是和以下几个因素有关的:

因素1
PG的锁排队机制,即使没有获得锁,只要在锁队列中就会造成锁竞争。

session A lock1 get  
session B lock2 wait lock1 release  
session C lock3 可能和lock1, lock2 冲突  
session D lock4 可能和lock1,2,3 冲突  
AI 代码解读

因素2
SQL响应变慢后,服务端程序通常会增加到数据库的连接来处理拥塞的请求

因素3
数据库的性能会随着连接数增加到一个值(通常是核数的3倍)后,性能开始下降

因素4
对象锁在事务结束时释放
例如在事务中查询表时,表的共享锁需要等到事务结束时释放。

以上4个因素加在一起,就可能引发一次应用级别的故障。

模拟
开启lock跟踪:

log_lock_waits = on   
deadlock_timeout = 1s  
AI 代码解读

创建测试表

postgres=# create table test(id int primary key, info text, crt_time timestamp);  
CREATE TABLE  
postgres=# insert into test select generate_series(1,10000000),md5(random()::text),clock_timestamp();  
INSERT 0 10000000  
AI 代码解读

测试脚本

vi test1.sql  
\setrandom id 1 10000000  
update test set info=info where id=:id;  
AI 代码解读

.1. pgbench1 模拟数据更新A。使用10个链接(假设正常只需要10个)
正常的性能

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 10 -j 10 -T 10  
progress: 2.0 s, 65994.3 tps, lat 0.149 ms stddev 0.038  
progress: 3.0 s, 67706.5 tps, lat 0.145 ms stddev 0.051  
progress: 4.0 s, 72865.0 tps, lat 0.135 ms stddev 0.048  
progress: 5.0 s, 77664.2 tps, lat 0.126 ms stddev 0.032  
progress: 6.0 s, 77138.9 tps, lat 0.127 ms stddev 0.037  
progress: 7.0 s, 75941.3 tps, lat 0.129 ms stddev 0.061  
progress: 8.0 s, 77328.8 tps, lat 0.127 ms stddev 0.036  
AI 代码解读

开启长时间更新请求

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 10 -j 10 -T 10000  
AI 代码解读

.2. 模拟一个查询长事务,查询表A
也可能是后台的whole vacuum prevent wrapper

postgres=# begin;  
BEGIN  
postgres=# select * from test limit 1;  
 id |               info               |          crt_time            
----+----------------------------------+----------------------------  
  1 | e86e219d51c39d16f78d77cf697395ca | 2016-03-16 16:07:49.814487  
(1 row)  
AI 代码解读

暂不结束事务, 持有test表的shared lock.

.3. 模拟一个DDL请求A

postgres=# alter table test add column c1 int;  
AI 代码解读

等待test shared lock锁释放

马上会堵塞正常的业务请求,tps降到0

progress: 53.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 54.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 55.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 56.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 57.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 58.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 59.0 s, 0.0 tps, lat -nan ms stddev -nan  
AI 代码解读

.4. 这个时候,业务并不知道数据库堵塞了,会增加更多的连接来处理用户的请求。甚至可能把连接塞满。
pgbench2 模拟拥塞更新A,新建500链接

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 500 -j 500 -T 10000  
AI 代码解读

新增的连接会全部处于这样的状态:

digoal  25434  22068  0 16:21 ?        00:00:00 postgres: postgres postgres [local] PARSE waiting  
digoal  25437  22068  0 16:21 ?        00:00:00 postgres: postgres postgres [local] PARSE waiting  
AI 代码解读

.5. 结束长事务或结束DDL请求后,锁释放。
锁释放,大量并发的连接开始处理拥塞的请求
此时性能下降了一半
pgbench2

progress: 10.3 s, 270.5 tps, lat 1396.862 ms stddev 3498.526  
progress: 11.0 s, 34443.5 tps, lat 64.132 ms stddev 709.718  
progress: 12.0 s, 34986.1 tps, lat 14.229 ms stddev 18.469  
progress: 13.0 s, 36645.0 tps, lat 13.661 ms stddev 17.686  
progress: 14.0 s, 34570.1 tps, lat 14.463 ms stddev 18.716  
progress: 15.0 s, 36435.8 tps, lat 13.752 ms stddev 17.621  
progress: 16.0 s, 35513.3 tps, lat 14.052 ms stddev 18.087  
progress: 17.0 s, 35560.0 tps, lat 14.013 ms stddev 18.159  
AI 代码解读

pgbench1

progress: 59.0 s, 688.7 tps, lat 340.857 ms stddev 2734.371  
progress: 60.0 s, 733.0 tps, lat 13.659 ms stddev 18.501  
progress: 61.0 s, 816.0 tps, lat 12.237 ms stddev 16.941  
progress: 62.0 s, 811.0 tps, lat 12.328 ms stddev 16.715  
progress: 63.0 s, 809.9 tps, lat 12.370 ms stddev 17.370  
progress: 64.0 s, 750.1 tps, lat 13.338 ms stddev 17.745  
AI 代码解读

将后建立的500个连接释放后,恢复正常的性能

progress: 66.0 s, 1937.8 tps, lat 5.044 ms stddev 12.975  
progress: 67.0 s, 64995.8 tps, lat 0.157 ms stddev 0.757  
progress: 68.0 s, 73996.3 tps, lat 0.133 ms stddev 0.042  
progress: 69.0 s, 78099.4 tps, lat 0.125 ms stddev 0.038  
AI 代码解读

日志
可以追踪到锁等待的源头,但是不能追踪到大锁。

2016-03-16 16:25:57.531 CST,"postgres","postgres",48877,"[local]",56e91894.beed,3,"ALTER TABLE waiting",2016-03-16 16:25:56 CST,13/28,580426398,LOG,00000,"process 48877 still waiting for AccessExclusiveLock on relation 61245 of database 13241 after 1000.048 ms","Process holding the lock: 48557. Wait queue: 48877, 46333, 46331, 46338, 46334, 46339, 46335, 46340, 46337, 46328, 46336.",,,,,"alter table test add column c1 int;",,"ProcSleep, proc.c:1323","psql"  
2016-03-16 16:25:57.531 CST,"postgres","postgres",46333,"[local]",56e91871.b4fd,3,"BIND waiting",2016-03-16 16:25:21 CST,4/263058,0,LOG,00000,"process 46333 still waiting for RowExclusiveLock on relation 61245 of database 13241 after 1000.036 ms","Process holding the lock: 48557. Wait queue: 48877, 46333, 46331, 46338, 46334, 46339, 46335, 46340, 46337, 46328, 46336.",,,,,"update test set info=info where id=$1;",,"ProcSleep, proc.c:1323","pgbench"  
  
2016-03-16 16:26:10.191 CST,"postgres","postgres",49812,"[local]",56e918a1.c294,3,"PARSE waiting",2016-03-16 16:26:09 CST,14/29,0,LOG,00000,"process 49812 still waiting for RowExclusiveLock on relation 61245 of database 13241 after 1000.207 ms","Process holding the lock: 48557. Wait queue: 48877, 46333, 此处省略500+ PIDs, 50816, 50817.",,,,,"update test set info=info where id=$1;",8,"ProcSleep, proc.c:1323","pgbench"  
  
2016-03-16 16:26:19.367 CST,"postgres","postgres",48877,"[local]",56e91894.beed,4,"ALTER TABLE waiting",2016-03-16 16:25:56 CST,13/28,580426398,LOG,00000,"process 48877 acquired AccessExclusiveLock on relation 61245 of database 13241 after 22836.312 ms",,,,,,"alter table test add column c1 int;",,"ProcSleep, proc.c:1327","psql"  
2016-03-16 16:26:19.368 CST,"postgres","postgres",48877,"[local]",56e91894.beed,5,"ALTER TABLE",2016-03-16 16:25:56 CST,13/28,580426398,ERROR,42701,"column ""c1"" of relation ""test"" already exists",,,,,,"alter table test add column c1 int;",,"check_for_column_name_collision, tablecmds.c:5069","psql"  
  
2016-03-16 16:26:19.379 CST,"postgres","postgres",49814,"[local]",56e918a1.c296,4,"PARSE waiting",2016-03-16 16:26:09 CST,15/2,0,LOG,00000,"process 49814 acquired RowExclusiveLock on relation 61245 of database 13241 after 10177.162 ms",,,,,,"update test set info=info where id=$1;",8,"ProcSleep, proc.c:1327","pgbench"  
AI 代码解读

要追踪大锁,
可以使用以下SQL

with t_wait as                       
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,  
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,  
transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname   
  from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),  
t_run as   
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,  
a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,  
a,transactionid,b.query,b.xact_start,b.query_start,  
b.usename,b.datname from pg_locks a,pg_stat_activity b where   
a.pid=b.pid and a.granted)   
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,  
r.relation::regclass,r.pid r_pid,  
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,  
r.query_start r_query_start,  
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,  
w.pid w_pid,w.page w_page,  
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,  
now()-w.query_start w_locktime,w.query w_query    
from t_wait w,t_run r where  
  r.locktype is not distinct from w.locktype and  
  r.database is not distinct from w.database and  
  r.relation is not distinct from w.relation and  
  r.page is not distinct from w.page and  
  r.tuple is not distinct from w.tuple and  
  r.classid is not distinct from w.classid and  
  r.objid is not distinct from w.objid and  
  r.objsubid is not distinct from w.objsubid and  
  r.transactionid is not distinct from w.transactionid and  
  r.pid <> w.pid  
  order by   
  ((  case w.mode  
    when 'INVALID' then 0  
    when 'AccessShareLock' then 1  
    when 'RowShareLock' then 2  
    when 'RowExclusiveLock' then 3  
    when 'ShareUpdateExclusiveLock' then 4  
    when 'ShareLock' then 5  
    when 'ShareRowExclusiveLock' then 6  
    when 'ExclusiveLock' then 7  
    when 'AccessExclusiveLock' then 8  
    else 0  
  end  ) +   
  (  case r.mode  
    when 'INVALID' then 0  
    when 'AccessShareLock' then 1  
    when 'RowShareLock' then 2  
    when 'RowExclusiveLock' then 3  
    when 'ShareUpdateExclusiveLock' then 4  
    when 'ShareLock' then 5  
    when 'ShareRowExclusiveLock' then 6  
    when 'ExclusiveLock' then 7  
    when 'AccessExclusiveLock' then 8  
    else 0  
  end  )) desc,r.xact_start;  
AI 代码解读

优化措施或处理措施
.1. 养成大锁处理习惯, 配置锁等待超时
.2. 应用程序或中间件应该有自动释放空闲连接的功能
.3. auto_explain也不会记录锁等待的时间,所以不利于分析原因。只有从日志中才能分析。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
打赏
0
0
0
1
20703
分享
相关文章
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
CTE vs 子查询:深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE(公共表表达式)与子查询的选择对SQL性能的影响。通过分析两者底层机制,揭示CTE的物化特性及子查询的优化融合优势,并结合多场景案例对比执行效率。最终给出决策指南,帮助开发者根据数据量、引用次数和复杂度选择最优方案,同时提供高级优化技巧和版本演进建议,助力SQL性能调优。
168 1
拯救海量数据:PostgreSQL分区表性能优化实战手册(附压测对比)
本文深入解析PostgreSQL分区表的核心原理与优化策略,涵盖性能痛点、实战案例及压测对比。首先阐述分区表作为继承表+路由规则的逻辑封装,分析分区裁剪失效、全局索引膨胀和VACUUM堆积三大性能杀手,并通过电商订单表崩溃事件说明旧分区维护的重要性。接着提出四维设计法优化分区策略,包括时间范围分区黄金法则与自动化维护体系。同时对比局部索引与全局索引性能,展示后者在特定场景下的优势。进一步探讨并行查询优化、冷热数据分层存储及故障复盘,解决分区锁竞争问题。
229 2
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
141 2
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
202 9
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享,主要内容包括以下四个部分: 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
734 0
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
189 11

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 推荐镜像

    更多
    AI助理
    登录插画

    登录以查看您的控制台资源

    管理云资源
    状态一览
    快捷访问

    你好,我是AI助理

    可以解答问题、推荐解决方案等