为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题
早上看到盖国强老师在朋友圈里分享了一篇关于软解析带来的Pin S等待的问题。
有感而发,跟大家聊一聊为什么PostgreSQL不存在这个问题。
在Oracle中多个会话高并发的执行同一条SQL,如果使用了绑定变量的话,会产生pin s的等待事件。
原因如下(取自互联网http://www.dbafree.net/?p=778)
每个child cursor(你可以认为是一条SQL的plan tree)下面都有一个mutexes这样的简单内存结构,当有session要执行该SQL而需要pin cursor操作的时候,session只需要以shared模式set这个内存位+1,表示session获得该mutex的shared mode lock.可以有很多session同时具有这个mutex的shared mode lock;
但在同一时间,只能有一个session在操作这个mutext +1或者-1。+1 -1的操作是排它性的原子操作。
如果因为session并行太多,而导致某个session在等待其他session的mutext +1/-1操作,则该session要等待cursor: pin S等待事件。
当看到系统有很多session等待cursor: pin S事件的时候,要么是CPU不够快,要么是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。如果是硬件的问题,则可以升级硬件。
如果是SQL执行频率太高。最简单的做法是,将一条SQL拆分成多条SQL。增加SQL的版本数来降低并发。如一个SQL:
select name from acct where acctno=:1
可以改为如下4个SQL,则并发的争用可以下降4倍。
select /*A*/ name from acct where acctno=:1 select /*B*/ name from acct where acctno=:1 select /*C*/ name from acct where acctno=:1 select /*D*/ name from acct where acctno=:1
另外,我们还会经常碰到另外一个等待事件“cursor: pin S wait on X”,这个等待事件主要是由硬解析引起的,解释如下:
“cursor: pin S wait on X” wait event is mostly related to mutex and hard parse. - When a process hard parses the SQL statement, it should acquire exclusive library cache pin for the corresponding LCO. - This means that the process acquires the mutex in exclusive mode. - Another process which also executes the same query needs to acquire the mutex but it’s being blocked by preceding process. The wait event is “cursor: pin S wait on X”.
cursor: pin S,
cursor: pin X,
cursor: pin S wait on X
这三个等待事件,实际上就是替代了cursor的library cache pin,
pin S代表执行(share pin),
pin X代表解析(exclusive pin),
pin S wait on X代表执行正在等待解析操作。
这里需要强调一下,它们只是替换了访问cursor的library cache pin,而对于访问procedure这种实体对象,依然是传统的library cache pin。
参考:
https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=5051110464464000 id=1310764.1 _afrWindowMode=0 _adf.ctrl-state=fu77hl3v2_4
http://www.hellodb.net/2010/07/oracle-library-cache.html 这篇文章不错,每次看都能有所收获。
很显然,产生这个锁的客观原因是Oracle的plan tree结构是共享的,并且加锁是串行的,所以高并发的情况下就出问题了。
如果你的业务形态确实如此,就只能改客户端程序来避免类似的问题。
下面给大家分析一下为什么PostgreSQL不存在这个问题
原因也很简单,PostgreSQL的plan cache是会话级别的,会话之间不共享plan cache.
因此不存在Oracle pin S的问题。
例子:
postgres=# create table t(id int primary key); CREATE TABLE postgres=# insert into t select generate_series(1,100); INSERT 0 100
.1. 使用绑定变量(pgbench -M prepared), 并发执行同一SQL
vi t.sql \setrandom id 1 100 select * from t where id=:id; pgbench -M prepared -n -r -f ./t.sql -c 64 -j 64 -T 120 tps = 1110129.983665 (including connections establishing) tps = 1110693.523542 (excluding connections establishing) 23283.00 3.1% GetSnapshotData /home/dege.zzz/pgsql9.6/bin/postgres 18074.00 2.4% AllocSetAlloc /home/dege.zzz/pgsql9.6/bin/postgres 15403.00 2.1% LWLockAcquire /home/dege.zzz/pgsql9.6/bin/postgres Cpu(s): 72.2%us, 18.9%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 8.8%si, 0.0%st
.2. 使用绑定变量(pgbench -M prepared), 并发执行不同SQL
for ((i=1;i i++)); do sed "s/select/select\ \/*\ $i\ *\//" t.sql ./t$i.sql ; done select /* 1 */ * from t where id=:id; ... ... select /* 64 */ * from t where id=:id; for ((i=1;i i++)); do pgbench -M prepared -n -r -f ./t$i.sql -c 1 -j 1 -T 120 | grep "^tps" done tps = 1089230.887 (including connections establishing) tps = 1090257.658 (excluding connections establishing) 23272.00 3.0% GetSnapshotData /home/dege.zzz/pgsql9.6/bin/postgres 17798.00 2.3% AllocSetAlloc /home/dege.zzz/pgsql9.6/bin/postgres 15030.00 2.0% LWLockAcquire /home/dege.zzz/pgsql9.6/bin/postgres Cpu(s): 70.5%us, 18.0%sy, 0.0%ni, 2.9%id, 0.0%wa, 0.0%hi, 8.6%si, 0.0%st
可以看到他们的profile, 性能指标, CPU的分配,几乎都没有差异。
如果你原来是Oracle的用户,开发人员再也不用为pin S的问题妥协,放心大胆的用同一条SQL,随便绑。
【.NET 6】使用EF Core 访问Oracle+Mysql+PostgreSQL并进行简单增改操作与性能比较 唠嗑一下。都在说去O或者开源,但是对于数据库选型来说,很多人却存在着误区。例如,去O,狭义上讲,是去Oracle数据库。但是从广义上来说,是去Oracle公司产品或者具有漂亮国垄断地位和需要商业授权的数据库产品。
关于PostgreSQL数据库兼容Oracle数据库闪回查询的实现方案 注:关于在PostgreSQL上面实现Oracle数据库的闪回功能(闪回查询 闪回表 闪回删除…)的这个想法已经有很长时间了,但是鉴于本人的能力 精力和身体条件 迟迟没有完成。期间也有很多的小伙伴跟我一起研究过这个功能,但是最终都因为各种各样的问题 没有做下去。Oracle数据库闪回功能跨越版本较大,功能也比较强大 在PostgreSQL数据库上实现,需要对数据库内核有很深入的理解 两大数据库不同的底层原理也终将影响各自的实现策略,PostgreSQL标记删除就地插入的特点和基于事务快照行可见性的特性是我们可以开发PostgreSQL闪回查询的大前提。本文主要介绍 实现闪回查询的 一种实现方案
相关文章
- Oracle 查询优化:利用变量加速查找(oracle查询变量)
- Oracle 变量实现自增的技巧(oracle变量自增)
- 利用Oracle函数为变量赋值(oracle函数赋值)
- 掌握Oracle系统变量,操作无忧(oracle系统变量)
- 规范的 Oracle 变量命名方法(oracle变量命名)
- Oracle绑定变量提升数据库性能(oracle绑定变量)
- Oracle 触发器:类型总览(oracle触发器类型)
- 变量替换:Oracle中使用转义符号(oracle转义符号)
- 接解锁Oracle数据库之路:连接技术指南(oracle数据库链)
- Oracle查询默认排序解析(oracle查询默认排序)
- Oracle宣布新变量: %极具可能性(oracle声明变量)
- 如何正确分配Oracle表权限(oracle分配表权限)
- 了解Oracle临时变量的使用方法及作用(oracle临时变量)
- Oracle简单易用:教你如何生成报表(oracle 生成报表)
- 报告 Oracle引领未来AWR报告颠覆绩效监控(oracle出awr)
- Oracle中占位符变量的应用(c oracle 占位符)
- 给08006业务护航的Oracle(08006 oracle)
- Oracle主键简化删减一列(oracle主键去掉一列)
- 认识Oracle中的输入变量(oracle中输入变量)
- Oracle中高效利用变量设置值(oracle中设置变量值)
- 的使用掌握Oracle中视图表的不断创新(oracle 中视图表)
- Oracle中添加列的新方式(oracle 之后添加列)
- Oracle中揭示变量价值之精髓转码(oracle中变量值转码)
- 变量Oracle中VNum变量的灵活处理(oracle中v_num)
- Oracle东北地区开启全新办事处之旅(oracle东北区办事处)
- Oracle携手蓝鸥,开启新一代IT精英之路(oracle与蓝鸥)
- 操作在Oracle中管理表格数据的技巧(oracle中表数据)