PostgreSQL数据库 OLTP高并发请求性能优化
2015-10-14 11:00:00| 作者：德哥：分类： |
2015年度PG大象会报名地址:
PostgreSQL中国社区: http://postgres.cn/index.php/home
PostgreSQL QQ交流群：100910388
PostgresChina微信公众号:
关注微信公众号“PostgresChina”获得更多门票优惠码
在多核系统中，一般TPS会随并发数的增加而提升，但是当并发数超过一定的数值（如CPU核数的2到3倍以后），性能开始下降，并发数越高，下降越严重。
例子：
更新500万记录表中的1条随机记录。开8000个并发。
create table test_8000 (id int primary key,cntintdefault0);
insertinto test_8000 selectgenerate_series(1,5000000);
vit.sql
\setrandom id 15000000
update test_8000 setcnt=cnt+1where id=:id;
update test_8000 setcnt=cnt+2where id=:id;
每次加载80个并发，循环100次，一共加载8000个并发。
vi test.sh
#!/bin/bash
for ((i=0;i<100;i++))
do
sleep1;
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-0817: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个空闲连接：
vitest.sql
selectpg_sleep(100000);
vi test.sh
#!/bin/bash
for ((i=0;i<100;i++))
do
sleep1;
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 postgrespostgres
progress:1.0 s,29429.2tps,lat0.336msstddev0.109
progress:2.0 s,28961.1tps,lat0.343msstddev0.114
progress:3.0 s,30433.8tps,lat0.326msstddev0.103
progress:4.0 s,29597.1tps,lat0.336msstddev0.114
progress:5.0 s,28714.1tps,lat0.346msstddev0.117
progress:6.0 s,28319.0tps,lat0.351msstddev0.121
progress:7.0 s,28540.0tps,lat0.348msstddev0.118
progress:8.0 s,29408.9tps,lat0.338msstddev0.111
progress:9.0 s,29178.1tps,lat0.340msstddev0.119
progress:10.0 s,29146.9tps,lat0.341msstddev0.118
progress:11.0 s,27498.5tps,lat0.361msstddev0.123
这种方法的性能约6万qps。
优化思路：
排队处理用户请求。类似pgbouncer或Oracle的shared server机制，真实处理请求的进程数有限。
使用PostgreSQL的advisory函数可以模拟这种排队机制：
createor replace functionupd(l int,v_idint) returns voidas $$
declare
begin
LOOP
ifpg_try_advisory_xact_lock(l)then --只有获得这个应用级锁才执行更新，否则就等待。
update test_8000 setcnt=cnt+1where id=v_id;
update test_8000 setcnt=cnt+2where id=v_id;
return;
else
perform pg_sleep(30*random()); -- 随机等待时间
endif;
END LOOP;
end;
$$ language plpgsql strict;
增加一个随机变量l，用来表示应用所的号码，也就是说模拟10个同时在更新的操作，其他的都在等待。
这个是没有经过优化的排队机制，因为不是独立的进程处理用户请求，依旧是backend process在处理用户请求，依旧有8000个进程。
vit.sql
\setrandom id 15000000
\setrandom l 110
selectupd(:l,:id);
vi test.sh
#!/bin/bash
for ((i=0;i<100;i++))
do
sleep1;
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 frompg_stat_all_tables where relname='test_8000'; now |?column?-------------------------------+-----------2015-10-0819:06:37.951332+08|221045069(1 row)
postgres=# select now(),n_tup_upd+n_tup_hot_upd frompg_stat_all_tables where relname='test_8000'; now |?column?------------------------------+-----------2015-10-0819:07:46.46325+08|222879057(1 row)
postgres=# select timestamptz '2015-10-0819: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, 5running,8866 sleeping, 1stopped, 0zombie
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。请参考。
把这种优化思路加入到PostgreSQL的内核中是比较靠谱的，最终实现的效果会和Oracle的shared server非常类似。
阿里云PG内核组的小鲜肉和老腊肉们，优化开始搞起吧。
在没有优化前，还是使用pgbouncer这种连接池吧。
[参考]
1.