注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:synchronous_commit 参数性能测试  

2015-08-28 16:36:28|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

          synchronous_commit  参数是 PostgreSQL WAL 日志文件类的重要参数,当设置成 on 时,表示事务提交需等待 WAL 刷到磁盘后才返回成功信息,这是最大限度数据保护模式;当设置成 off 时,事务提交不需等待 WAL 刷到磁盘就返回成功信息。而这个理解来看,设置成 on 会带来性能开销,那么这个开销大概有多少呢? 一直想做这个测试,今天有空,测试下。
  
--环境信息
硬件:华为服务器 RH5885 V3
PostgreSQL版本:9.5alpha2
操作系统: SUSE Linux

--测试模型:一张 1000 万的表

drop table if exists test_sync;
create table test_sync(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());
insert into test_sync(id,name) select n,n||'_test' from generate_series(1,10000000) n;
alter table test_sync add primary key(id);
vacuum analyze test_sync;



一 测试场景 synchronous_commit = off   
--主键查询: select_1.sql

[pg93@db2 load_test]$ cat select_1.sql
\setrandom v_id 1 10000000

select name from test_sync where id=:v_id;


--pgbench

pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f select_1.sql   > select_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f select_1.sql   > select_2.out 2>&1 &

pg95@db1:~/script/load_test> tail -f select_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1891643
latency average: 4.060 ms
tps = 15741.393851 (including connections establishing)
tps = 15761.220964 (excluding connections establishing)

pg95@db1:~/script/load_test> tail -f select_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1867651
latency average: 4.112 ms
tps = 15546.474928 (including connections establishing)
tps = 15565.536436 (excluding connections establishing)

备注:合计 TPS 为: 15741+15546=31287, 开了两个 pgbench 进程,机器性能依然没压到极限。                                      

--主键更新: updaet_1.sql

[pg93@db2 load_test]$ cat update_1.sql
\setrandom v_id 1 10000000

update test_sync set name='off' where id=:v_id;


--pgbench

pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f update_1.sql   > update_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f update_1.sql   > update_2.out 2>&1 &

pg95@db1:~/script/load_test> tail -f update_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 533015
latency average: 14.409 ms
tps = 4436.334568 (including connections establishing)
tps = 4441.801624 (excluding connections establishing)

pg95@db1:~/script/load_test> tail -f update_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 538226
latency average: 14.269 ms
tps = 4480.139660 (including connections establishing)
tps = 4485.694991 (excluding connections establishing)

备注:合计 TPS 为:  4436+4480=8916


二 测试场景 synchronous_commit = on
--为了测试准确,重新刷一次脚本

drop table if exists test_sync;
create table test_sync(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());
insert into test_sync(id,name) select n,n||'_test' from generate_series(1,10000000) n;
alter table test_sync add primary key(id);
vacuum analyze test_sync;


--主键查询: select_1.sql

[pg93@db2 load_test]$ cat select_1.sql
\setrandom v_id 1 10000000

select name from test_sync where id=:v_id;


--pgbench

pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f select_1.sql   > select_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f select_1.sql   > select_2.out 2>&1 &


pg95@db1:~/script/load_test> tail -f select_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1907432
latency average: 4.026 ms
tps = 15872.562421 (including connections establishing)
tps = 15892.146509 (excluding connections establishing)

pg95@db1:~/script/load_test> tail -f select_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 1913928
latency average: 4.013 ms
tps = 15926.439118 (including connections establishing)
tps = 15946.082816 (excluding connections establishing)

备注:合计 TPS 为:  15872+15926=31798

--主键更新: updaet_1.sql

[pg93@db2 load_test]$ cat update_1.sql
\setrandom v_id 1 10000000

update test_sync set name='off' where id=:v_id;


--pgbench

pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f update_1.sql   > update_1.out 2>&1 &
pgbench -c 64 -T 120 -n -N -M prepared -d francs  -U francs -f update_1.sql   > update_2.out 2>&1 &


pg95@db1:~/script/load_test> tail -f update_1.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 337821
latency average: 22.734 ms
tps = 2803.250510 (including connections establishing)
tps = 2806.689839 (excluding connections establishing)

pg95@db1:~/script/load_test> tail -f update_2.out
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 120 s
number of transactions actually processed: 340240
latency average: 22.572 ms
tps = 2822.794480 (including connections establishing)
tps = 2826.240282 (excluding connections establishing)
备注:合计 TPS 为:  2822+2826=5648


三 测试总结
测试场景
 主键查询 主键更新
 synchronous_commit = off
 31287
 8916
 synchronous_commit = on
 31798     5648
备注:从结果看出,SELECT 几乎不受 synchronous_commit 参数影响,而 UPDATE 在 synchronous_commit= on 的场景下要比 synchronous_commit = off 场景下的 TPS 低 36.7% 左右。

四 参考
  评论这张
 
阅读(487)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016