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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
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 冲突  

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

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

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

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

模拟
开启lock跟踪:

log_lock_waits = on   
deadlock_timeout = 1s  

创建测试表

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  

测试脚本

vi test1.sql  
\setrandom id 1 10000000  
update test set info=info where id=:id;  

.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  

开启长时间更新请求

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 10 -j 10 -T 10000  

.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)  

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

.3. 模拟一个DDL请求A

postgres=# alter table test add column c1 int;  

等待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  

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

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 500 -j 500 -T 10000  

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

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  

.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  

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  

将后建立的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  

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

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"  

要追踪大锁,
可以使用以下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;  

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
SQL 缓存 监控
14个Flink SQL性能优化实践分享
【7月更文挑战第12天】 1. **合理设置并行度**: 根据数据量和资源调整以提高处理速度. 2. **优化数据源**: 使用分区表并进行预处理减少输入量. 3. **数据缓存**: 采用 `BROADCAST` 或 `REPARTITION` 缓存常用数据. 4. **索引和分区**: 创建索引并按常用字段分区. 5. **避免不必要的计算**: 检查并移除多余的计算步骤. 6. **调整内存配置**: 分配足够内存避免性能下降. 7. **优化连接操作**: 选择适合大表和小表的连接方式. 8. **数据类型优化**: 选择合适类型以节省资源. ........
120 1
|
18天前
|
SQL
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
29 1
|
2月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
536 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
1月前
|
SQL 监控 Oracle
Oracle SQL性能优化全面指南
在数据库管理领域,Oracle SQL性能优化是确保数据库高效运行和数据查询速度的关键
|
1月前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据:技巧、方法与性能优化
id="">SQL查询功能详解 SQL(Structured Query Language,结构化查询语言)是一种专门用于与数据库进行沟通和操作的语言
|
2月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
3月前
|
SQL 存储 关系型数据库
PostgreSQL核心之SQL基础学习
PostgreSQL核心之SQL基础学习
43 3
|
3月前
|
SQL 安全 关系型数据库
PostgreSQL SQL注入漏洞(CVE-2018-10915)--处理
【8月更文挑战第8天】漏洞描述:PostgreSQL是一款自由的对象关系型数据库管理系统,支持多种SQL标准及特性。存在SQL注入漏洞,源于应用未有效验证外部输入的SQL语句,允许攻击者执行非法命令。受影响版本包括10.5及更早版本等。解决方法为升级PostgreSQL
256 2
|
3月前
|
SQL 存储 数据库
|
3月前
|
SQL 数据处理 数据库
SQL正则表达式应用:文本数据处理的强大工具——深入探讨数据验证、模式搜索、字符替换等核心功能及性能优化和兼容性问题
【8月更文挑战第31天】SQL正则表达式是数据库管理和应用开发中处理文本数据的强大工具,支持数据验证、模式搜索和字符替换等功能。本文通过问答形式介绍了其基本概念、使用方法及注意事项,帮助读者掌握这一重要技能,提升文本数据处理效率。尽管功能强大,但在不同数据库系统中可能存在兼容性问题,需谨慎使用以优化性能。
54 0

相关产品

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