PostgreSQL rc,rr,ssi 隔离级别与性能对比 - 高隔离级别损耗性测试-阿里云开发者社区

开发者社区> 数据库> 正文

PostgreSQL rc,rr,ssi 隔离级别与性能对比 - 高隔离级别损耗性测试

简介: 标签 PostgreSQL , rc , rr , ssi , 隔离级别 , 性能 , 乐观锁 背景 https://www.postgresql.org/docs/11/mvcc.html PG支持到了最高级别的隔离级别SSI(serializable snapshot isolate)。 几种隔离级别的目标如下 1、读未提交,可以看到未提交的数据,脏读。 2、读已提交,只能看

标签

PostgreSQL , rc , rr , ssi , 隔离级别 , 性能 , 乐观锁


背景

https://www.postgresql.org/docs/11/mvcc.html

PG支持到了最高级别的隔离级别SSI(serializable snapshot isolate)。

几种隔离级别的目标如下

1、读未提交,可以看到未提交的数据,脏读。

2、读已提交,只能看到其他会话已提交的数据。

3、可重复度,只能看到事务开启前已提交的数据。更新(修改)事务开启后被其他会话修改过并提交的数据时,报错。

postgres=# create table abc(id int primary key, info text);  
CREATE TABLE  
postgres=# insert into abc values (1,'test');  
INSERT 0 1  

a

postgres=# begin transaction isolation level repeatable read ;  
BEGIN  
postgres=# select * from abc;  
 id | info   
----+------  
  1 | test  
(1 row)  

b

postgres=# update abc set info='hello digoal';  
UPDATE 1  
commit;  

a

postgres=# \set VERBOSITY verbose
postgres=# update abc set info ='new';  
ERROR:  40001: could not serialize access due to concurrent update
LOCATION:  ExecUpdate, nodeModifyTable.c:1257

4、严格串行,模拟串行执行,事务结束时,判断是否有会话之间的相互依赖,如果依赖的数据被已提交的事务修改,则报错,确保模拟严格串行的场景。

create table mytab (class int, value int);  
insert into mytab values (1,10),(1,20),(2,100),(2,200);  
  
 class | value  
-------+-------  
     1 |    10  
     1 |    20  
     2 |   100  
     2 |   200  

a

postgres=# begin transaction isolation level serializable ;  
BEGIN  
  
SELECT SUM(value) FROM mytab WHERE class = 1;  

b

postgres=# begin transaction isolation level serializable ;  
BEGIN  
postgres=# SELECT SUM(value) FROM mytab WHERE class = 2;  
 sum   
-----  
 300  
(1 row)  

a

insert into mytab values (2,30);  

b

insert into mytab values (1,30);  

a

postgres=# commit ;  
COMMIT  

b

postgres=# \set VERBOSITY verbose
postgres=# commit;
ERROR:  40001: could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
LOCATION:  PreCommit_CheckForSerializationFailure, predicate.c:4685

《PostgreSQL 10.0 preview 功能增强 - 串行隔离级别 预加锁阈值可控》

《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》

隔离级别越高,冲突可能性就越大,不同数据库的实现不一样,PG使用了巧妙的乐观锁实现。处理吞吐高。

对比rc, rr, ssi隔离级别的性能

为了方便测试,使用函数封装TPCB,测试rr和ssi

create or replace function tpcb (int,int,int,int) returns void as $$  
declare  
begin  
  UPDATE pgbench_accounts SET abalance = abalance + $4 WHERE aid = $1;  
  perform abalance FROM pgbench_accounts WHERE aid = $1;  
  UPDATE pgbench_tellers SET tbalance = tbalance + $4 WHERE tid = $3;  
  UPDATE pgbench_branches SET bbalance = bbalance + $4 WHERE bid = $2;  
  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($3, $2, $1, $4, CURRENT_TIMESTAMP);  
  exception when SQLSTATE '40001' then   -- skip 冲突 
    return;  
end;  
$$ language plpgsql strict;  

测试脚本

vi tpcb.sql  
\set aid random(1, 100000 * :scale)  
\set bid random(1, 1 * :scale)  
\set tid random(1, 10 * :scale)  
\set delta random(-5000, 5000)  
select tpcb(:aid, :bid, :tid, :delta);  

初始化数据,5亿条

pgbench -i -s 5000  

rc模式

alter role all set default_transaction_isolation ='read committed';  
pgbench -M prepared -v -r -P 1 -c 64 -j 64 -T 120  
  
transaction type: <builtin: TPC-B (sort of)>  
scaling factor: 5000  
query mode: prepared  
number of clients: 64  
number of threads: 64  
duration: 120 s  
number of transactions actually processed: 11343739  
latency average = 0.677 ms  
latency stddev = 0.747 ms  
tps = 94519.862919 (including connections establishing)  
tps = 94528.706372 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set aid random(1, 100000 * :scale)  
         0.001  \set bid random(1, 1 * :scale)  
         0.001  \set tid random(1, 10 * :scale)  
         0.001  \set delta random(-5000, 5000)  
         0.046  BEGIN;  
         0.137  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;  
         0.073  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
         0.082  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;  
         0.079  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;  
         0.067  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);  
         0.188  END;  
postgres=# select count(*) from pgbench_history ;  
  count     
----------  
 11343739  
(1 row)  

rr模式

alter role all set default_transaction_isolation ='repeatable read';  
pgbench -M prepared -v -r -P 1 -f ./tpcb.sql -c 64 -j 64 -T 120  
  
transaction type: ./tpcb.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 64  
number of threads: 64  
duration: 120 s  
number of transactions actually processed: 10555148  
latency average = 0.728 ms  
latency stddev = 0.732 ms  
tps = 87949.593197 (including connections establishing)  
tps = 87957.185597 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.001  \set aid random(1, 100000 * :scale)  
         0.000  \set bid random(1, 1 * :scale)  
         0.000  \set tid random(1, 10 * :scale)  
         0.000  \set delta random(-5000, 5000)  
         0.725  select tpcb(:aid, :bid, :tid, :delta);  

等待事件

select wait_event_type,wait_event,count(*) from pg_stat_activity  group by 1,2 order by 1,2;  
  
 wait_event_type |      wait_event      | count   
-----------------+----------------------+-------  
 Activity        | AutoVacuumMain       |     1  
 Activity        | BgWriterMain         |     1  
 Activity        | CheckpointerMain     |     1  
 Activity        | LogicalLauncherMain  |     1  
 Client          | ClientRead           |     4  
 IPC             | ProcArrayGroupUpdate |     1  
 LWLock          | buffer_content       |     5  
 Lock            | transactionid        |     6  
 Lock            | tuple                |    34  
                 |                      |    17  
(10 rows)  

正常事务:

postgres=# select count(*) from pgbench_history ;  
 count    
--------  
 638001  
(1 row)  

在冲突率这么高的情况下,可以保持87957的TPS,非常厉害。

ssi模式

alter role all set default_transaction_isolation ='serializable';  
pgbench -M prepared -v -r -P 1 -f ./tpcb.sql -c 64 -j 64 -T 120  
  
transaction type: ./tpcb.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 64  
number of threads: 64  
duration: 120 s  
number of transactions actually processed: 5166549  
latency average = 1.486 ms  
latency stddev = 1.098 ms  
tps = 43050.086666 (including connections establishing)  
tps = 43058.594768 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set aid random(1, 100000 * :scale)  
         0.000  \set bid random(1, 1 * :scale)  
         0.000  \set tid random(1, 10 * :scale)  
         0.000  \set delta random(-5000, 5000)  
         1.484  select tpcb(:aid, :bid, :tid, :delta);  

锁等待

select wait_event_type,wait_event,count(*) from pg_stat_activity  group by 1,2 order by 1,2;  
  
  
 wait_event_type |          wait_event          | count   
-----------------+------------------------------+-------  
 Activity        | AutoVacuumMain               |     1  
 Activity        | BgWriterHibernate            |     1  
 Activity        | CheckpointerMain             |     1  
 Activity        | LogicalLauncherMain          |     1  
 Activity        | WalWriterMain                |     1  
 Client          | ClientRead                   |     1  
 LWLock          | SerializableFinishedListLock |    58  
 LWLock          | SerializableXactHashLock     |     2  
 Lock            | transactionid                |     1  
 Lock            | tuple                        |     1  
                 |                              |     3  
(11 rows)  

正常事务:

postgres=# select count(*) from pgbench_history ;  
 count    
--------  
 411820  
(1 row)  

在冲突率这么高的情况下,可以保持43058的TPS,非常厉害。

性能对比

数据量隔离级别TPS(吞吐能力)QPS正常事务冲突比例
5亿rc94528472640113437390%
5亿rr8795743978563800193.95%
5亿ssi4305821529041182092.03%

小结

在冲突率高达90%以上的情况下,PG的RR模式可以保持87957的TPS,SSI模式可以保持43058的TPS,非常厉害,使用乐观锁带来的好处多多。

测试环境:32核虚拟机。(文章主要表明的是PG在高隔离级别下面的锁管理能力强悍,相对于RC这种低隔离级别,损耗做到了非常小。)

参考

https://www.postgresql.org/docs/11/mvcc.html

https://www.postgresql.org/docs/11/errcodes-appendix.html

《PostgreSQL 10.0 preview 功能增强 - 串行隔离级别 预加锁阈值可控》

《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》

https://www.jianshu.com/p/c348f68fecde

https://zhuanlan.zhihu.com/p/37087894

src/backend/storage/lmgr/README-SSI

https://www.postgresql.org/docs/11/runtime-config-locks.html

postgres=# show max_pred_locks_per_page ;
 max_pred_locks_per_page 
-------------------------
 2
(1 row)

postgres=# show max_pred_locks_per_relation ;
 max_pred_locks_per_relation 
-----------------------------
 -2
(1 row)

postgres=# show max_pred_locks_per_transaction ;
 max_pred_locks_per_transaction 
--------------------------------
 64
(1 row)
References
----------

[1] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Search for serial execution to find the relevant section.

[2] A. Fekete et al. Making Snapshot Isolation Serializable. In ACM
Transactions on Database Systems 30:2, Jun. 2005.
http://dx.doi.org/10.1145/1071610.1071615

[3] Joseph M. Hellerstein, Michael Stonebraker and James Hamilton. 2007.
Architecture of a Database System. Foundations and Trends(R) in
Databases Vol. 1, No. 2 (2007) 141-259.
http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf
  Of particular interest:
    * 6.1 A Note on ACID
    * 6.2 A Brief Review of Serializability
    * 6.3 Locking and Latching
    * 6.3.1 Transaction Isolation Levels
    * 6.5.3 Next-Key Locking: Physical Surrogates for Logical Properties

 

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

版权声明:如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developerteam@list.alibaba-inc.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章