PostgreSQL数据库 OLTP高并发请求性能优化

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

• Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
• Postgres-XL的项目发起人Mason Sharp
• pgpool的作者石井达夫(Tatsuo Ishii)
• PG-Strom的作者海外浩平(Kaigai Kohei)
• Greenplum研发总监姚延栋
• 周正中(德哥), PostgreSQL中国用户会创始人之一
• 汪洋，平安科技数据库技术部经理
• ……

 

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/

PostgreSQL中国社区： http://postgres.cn/

PostgreSQL专业1群： 3336901（已满）

PostgreSQL专业2群： 100910388

PostgreSQL专业3群： 150657323

在多核系统中，一般TPS会随并发数的增加而提升，但是当并发数超过一定的数值（如CPU核数的2到3倍以后），性能开始下降，并发数越高，下降越严重。

例子：

更新500万记录表中的1条随机记录。开8000个并发。

create table test_8000 (id int primary key, cnt int default 0);
insert into test_8000 select generate_series(1,5000000);

vi t.sql
\setrandom id 1 5000000
update test_8000 set cnt=cnt+1 where id=:id;
update test_8000 set cnt=cnt+2 where id=:id;

每次加载80个并发，循环100次，一共加载8000个并发。

vi test.sh
#!/bin/bash
for ((i=0;i<100;i++))
do

sleep 1;
pgbench -M simple -n -r -f ./t.sql -c 80 -j 80 -T 100000 -U postgres &

done

开始

. ./test.sh

当连接数达到8000后，观察TPS，我们可以使用PG的统计信息表来计算QPS。

postgres=# select count(*) from pg_stat_activity;
 count 
-------
  8002
(1 row)

postgres=# select timestamptz '2015-10-08 17:01:24.203089+08' - timestamptz '2015-10-08 17:01:16.574076+08';
    ?column?     
-----------------
 00:00:07.629013
(1 row)

postgres=# select 43819090-43749480;
 ?column? 
----------
    69610
(1 row)

postgres=# select 69610/07.629013;
       ?column?        
-----------------------
 9124.3782124896103860
(1 row)

8000个并发的时候，更新TPS约9124。大部分时间可能浪费在CPU调度上了。

另一种场景，

如果有8000个并发是空闲连接，只有10个在执行更新，性能是这样的：

先制造8000个空闲连接：

vi test.sql
select pg_sleep(100000);

vi test.sh
#!/bin/bash
for ((i=0;i<100;i++))
do

sleep 1;
pgbench -M simple -n -r -f ./test.sql -c 80 -j 80 -T 100000 -U postgres &

done

. ./test.sh

postgres=# select count(*) from pg_stat_activity;
 count 
-------
  8002
(1 row)

然后开启10个连接执行更新操作。

pgbench -M prepared -n -r -f ./t.sql -P 1 -c 10 -j 10 -T 1000 -U postgres postgres
progress: 1.0 s, 29429.2 tps, lat 0.336 ms stddev 0.109
progress: 2.0 s, 28961.1 tps, lat 0.343 ms stddev 0.114
progress: 3.0 s, 30433.8 tps, lat 0.326 ms stddev 0.103
progress: 4.0 s, 29597.1 tps, lat 0.336 ms stddev 0.114
progress: 5.0 s, 28714.1 tps, lat 0.346 ms stddev 0.117
progress: 6.0 s, 28319.0 tps, lat 0.351 ms stddev 0.121
progress: 7.0 s, 28540.0 tps, lat 0.348 ms stddev 0.118
progress: 8.0 s, 29408.9 tps, lat 0.338 ms stddev 0.111
progress: 9.0 s, 29178.1 tps, lat 0.340 ms stddev 0.119
progress: 10.0 s, 29146.9 tps, lat 0.341 ms stddev 0.118
progress: 11.0 s, 27498.5 tps, lat 0.361 ms stddev 0.123

这种方法的性能约6万 qps。

优化思路：

排队处理用户请求。类似pgbouncer或Oracle的shared server机制，真实处理请求的进程数有限。

使用PostgreSQL的advisory函数可以模拟这种排队机制：

create or replace function upd(l int,v_id int) returns void as $$
declare
begin
  LOOP
    if pg_try_advisory_xact_lock(l) then  -- 只有获得这个应用级锁才执行更新，否则就等待。
      update test_8000 set cnt=cnt+1 where id=v_id;
      update test_8000 set cnt=cnt+2 where id=v_id;
      return;
    else
      perform pg_sleep(30*random());  --  随机等待时间
    end if;
  END LOOP;
end;
$$ language plpgsql strict;

增加一个随机变量l，用来表示应用所的号码，也就是说模拟10个同时在更新的操作，其他的都在等待。

这个是没有经过优化的排队机制，因为不是独立的进程处理用户请求，依旧是backend process在处理用户请求，依旧有8000个进程。

vi t.sql
\setrandom id 1 5000000
\setrandom l 1 10
select upd(:l, :id);

vi test.sh
#!/bin/bash
for ((i=0;i<100;i++))
do

sleep 1;
pgbench -M simple -n -r -f ./t.sql -c 80 -j 80 -T 100000 -U postgres &

done

. ./test.sh

测试结果比较理想，已经提升了1倍性能。

postgres=# select now(),n_tup_upd+n_tup_hot_upd from pg_stat_all_tables where relname='test_8000';
              now              | ?column?  
-------------------------------+-----------
 2015-10-08 19:06:37.951332+08 | 221045069
(1 row)

postgres=# select now(),n_tup_upd+n_tup_hot_upd from pg_stat_all_tables where relname='test_8000';
             now              | ?column?  
------------------------------+-----------
 2015-10-08 19:07:46.46325+08 | 222879057
(1 row)

postgres=# select timestamptz '2015-10-08 19:07:46.46325+08' - timestamptz '2015-10-08 19:06:37.951332+08';
    ?column?     
-----------------
 00:01:08.511918
(1 row)

postgres=# select 222879057-221045069;
 ?column? 
----------
  1833988
(1 row)

postgres=# select 1833988/68.5;
      ?column?      
--------------------
 26773.547445255474
(1 row)

模拟结果，相比不排队，有1倍以上的性能提升。  

TOP

top - 19:09:37 up 119 days,  3:59,  2 users,  load average: 0.96, 0.98, 1.01
Tasks: 8872 total,   5 running, 8866 sleeping,   1 stopped,   0 zombie
Cpu(s):  5.3%us,  0.8%sy,  0.0%ni, 93.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  132124976k total, 118066688k used, 14058288k free,   316752k buffers
Swap:  2097144k total,      148k used,  2096996k free, 63702028k cached

advisory lock是PG提供的一种轻量级的面向用户的锁（当然比LWLOCK是要重的），我之前在秒杀场景的优化中也有叙述，可以达到每秒处理19万次的单条记录更新请求的性能，并且保持1毫秒以内的RT。请参考。

http://blog.163.com/digoal@126/blog/static/16387704020158149538415/

把这种优化思路加入到PostgreSQL的内核中是比较靠谱的，最终实现的效果会和Oracle的shared server非常类似。

阿里云PG内核组的小鲜肉和老腊肉们，优化开始搞起吧。

在没有优化前，还是使用pgbouncer这种连接池吧。

[参考]

1. http://blog.163.com/digoal@126/blog/static/16387704020158149538415/