开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题

简介: 早上看到盖国强老师在朋友圈里分享了一篇关于软解析带来的Pin S等待的问题。有感而发,跟大家聊一聊为什么PostgreSQL不存在这个问题。 Oracle 在Oracle中多个会话高并发的执行同一条SQL,如果使用了绑定变量的话,会产生pin s的等待事件。原因如下(取自互联网http://
+关注继续查看

早上看到盖国强老师在朋友圈里分享了一篇关于软解析带来的Pin S等待的问题。
有感而发,跟大家聊一聊为什么PostgreSQL不存在这个问题。

Oracle

在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不存在这个问题
原因也很简单,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<=64;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;

RUN
for ((i=1;i<=64;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,随便绑。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
RDS MySQL 5.7三节点企业版重磅发布 企业级业务云上数据库首选
随着云计算技术的逐渐普及,使用云服务的客户行业、场景的边界也在不断地被拓宽,不断提出新的需求。
343 0
RDS MySQL 5.7三节点企业版重磅发布 企业级业务云上数据库首选
10月23日15:00 多名专家联袂讲解 https://yq.aliyun.com/live/1536 随着云计算技术的逐渐普及,使用云服务的客户行业、场景的边界也在不断地被拓宽,不断提出新的需求。
3385 0
RDS数据库与自建库的gtid主从同步
一、在centos7上部署MySQL数据库1、从MYSQL官网下载安装文件wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.
1928 0
阿里云云数据库RDS如何监控、备份及克隆实例?
监控报警及备份恢复是DBA日常工作里面用的最多的功能。这里我们来对这个功能进行一个简单的介绍。 点击监控与报警。监控类型包括资源监控及引擎监控。资源监控,包括CPU和内存利用率、磁盘空间、IOPS链接数和网络流量;引擎监控,主要针对数据库引擎内部的深度监控,由mysql里面提供TPS、QPS、命中率、读写量、缓存请求次数、日志读写以及更多的深入监控信息。
1977 0
RDS for MySQL8.0物理备份恢复到本地自建数据库
此文章是centos7下的恢复流程。 1、安装MySQL8.0(采用yum方式安装):wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm yum localinstall mysql80-community-release-el7-1.noarch.rpm yum -y install yum-utils 默认安装的就是8.0版本yum install mysql-community-server 安装好了不要启动数据库。
3123 0
【阿里云新品发布·周刊】第11期:云数据库 MySQL 8.0 重磅发布,更适合企业使用场景的RDS数据库
2019年5月29日15时,阿里云云数据库 MySQL 8.0 重磅发布,2倍以上性能提升,SQL窗口函数、JSON扩展语法等企业级新功能震撼上市!主要从技术层面介绍MySQL 8.0的优势和与过去版本对比。
3304 0
小微企业阿里云最佳实践系列(二):RDS 数据库与DMS 数据库管理(数据管理)
在上一篇博文中主要介绍了 ECS 服务器与 RDS 数据库,在本篇重点为大家讲解我们的数据如何进行管理、在日常的管理过程中存在哪些风险、遇到突然事件如何通过日志分析问题以及解决问题。
2886 0
小微企业阿里云最佳实践系列(一):ECS 服务器与 RDS 数据库
本博文主要使用传统服务器架构与云服务架构进行横向对比,解决企业在搭建软件系统中所遇到等问题和痛点,以及为小微企业降低成本的同时尽可能提高软件系统的高可通、低延迟、高规范、低人力投入。
2982 0
自建Percona5.7.23同步阿里云RDS(MySQL5.6)TokuDB数据库
阿里云RDS支持TokuDB引擎,具有高压缩,高写入性能,读性能和InnoDB差不多。本文详解Percona MySQL5.7.23(包含TokuDB引擎)二进制的搭建,以及使用RDS备份文件恢复数据和搭建GTID同步到自建Percona MySQL5.7.23的过程。
2333 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
Oracle 至PostgreSQL案例分享
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载
ORACLE 10g 数据库体系结构图
立即下载