暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

digoal 2016-06-17
183
TAG 21

作者

digoal

日期

2016-06-17

标签

PostgreSQL , 绑定变量 , 执行计划 , 倾斜


背景

早上看到盖国强老师在朋友圈里分享了一篇关于软解析带来的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/digoal/pgsql9.6/bin/postgres
18074.00 2.4% AllocSetAlloc /home/digoal/pgsql9.6/bin/postgres
15403.00 2.1% LWLockAcquire /home/digoal/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/digoal/pgsql9.6/bin/postgres
17798.00 2.3% AllocSetAlloc /home/digoal/pgsql9.6/bin/postgres
15030.00 2.0% LWLockAcquire /home/digoal/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,随便绑。

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

文章转载自digoal,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论