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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:浅谈 fsync 参数  

2015-10-29 12:00:19|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
       这两天有朋友在做性能测试,咨询 PG 的 fsync 参数对性能的影响。这个参数比较特殊,通常情况都需要打开,今天有空研究下这个参数的影响。

一关于 fsync  参数
       如果 fsync 参数设置成 on ,PostgreSQL 服务通过调用系统fsync()或其它方式确保更新已经物理写到磁盘,这样就保证了数据库集群将在操作系统或者硬件崩溃的情况下恢复到一个一致的状态。虽然关闭这个参数有一定提升,通常情况下需要打开这个参数,除非您能经受掉电或硬件故障带来的数据丢失,否则不要关闭这参数,下面做两方面测试:1) 关闭 sync 参数观察对 SELECT/UPDATE 压力测试的影响;2)关闭参数,冷关闭主机测试数据库是否能正常启动。
  

二 测试场景 fsync = on  
--测试模型:一张 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;


--设置 fsync 参数

pg95@pg95:~/script/load_test> psql
psql (9.5alpha2)
Type "help" for help.

postgres=# show fsync;
 fsync 
-------
 on
(1 row)


--主键查询

[pg95@pg95 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 &

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: 2031338
latency average: 3.781 ms
tps = 16909.975656 (including connections establishing)
tps = 16930.166433 (excluding connections establishing)


--主键更新

[pg95@pg95 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 &

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: 713242
latency average: 10.768 ms
tps = 5939.386731 (including connections establishing)
tps = 5946.560775 (excluding connections establishing)



三 测试场景 fsync = off   
--设置 fsync 参数

pg95@pg95:~/script/load_test> psql
psql (9.5alpha2)
Type "help" for help.

postgres=# show fsync;
 fsync 
-------
 off
(1 row)


--为了测试准确重新刷以下脚本
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;

--主键查询

[pg95@pg95 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 &

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: 2006681
latency average: 3.827 ms
tps = 16707.303006 (including connections establishing)
tps = 16727.661063 (excluding connections establishing)


--主键更新

[pg95@pg95 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 &

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: 1505343
latency average: 5.102 ms
tps = 12530.405426 (including connections establishing)
tps = 12545.757516 (excluding connections establishing)



四 测试场景: fsync = off 的情况下模数据库异常

   接下来测试当 fsync=off 的情况下数据库异常宕掉的情况,做测试前做好数据库备份。

--跑 update 脚本,给数据库施加压力

pg95@pg95 :~/script/load_test> pgbench -c 64 -T 300 -n -N -M prepared -d francs  -U francs -f update_1.sql   > update_1.out 2>&1 &
[1] 52424


--异常关闭数据库
      强制关闭数据库主机,模拟掉电。

--再次启动主机,并启动数据库
  再次启动数据库,发现数据库无法启动,数据库日志如下:

--数据库日志

2015-10-28 16:51:59.942 CST,,,10141,,56308caf.279d,1,,2015-10-28 16:51:59 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2015-10-28 16:51:59.944 CST,,,10143,,56308caf.279f,1,,2015-10-28 16:51:59 CST,,0,LOG,00000,"database system was interrupted; last known up at 2015-10-29 00:37:41 CST",,,,,,,,,""
2015-10-28 16:51:59.944 CST,,,10143,,56308caf.279f,2,,2015-10-28 16:51:59 CST,,0,LOG,00000,"invalid primary checkpoint record",,,,,,,,,""
2015-10-28 16:51:59.944 CST,,,10143,,56308caf.279f,3,,2015-10-28 16:51:59 CST,,0,LOG,00000,"invalid secondary checkpoint record",,,,,,,,,""
2015-10-28 16:51:59.944 CST,,,10143,,56308caf.279f,4,,2015-10-28 16:51:59 CST,,0,PANIC,XX000,"could not locate a valid checkpoint record",,,,,,,,,""
2015-10-28 16:51:59.944 CST,,,10141,,56308caf.279d,2,,2015-10-28 16:51:59 CST,,0,LOG,00000,"startup process (PID 10143) was terminated by signal 6: Aborted",,,,,,,,,""
2015-10-28 16:51:59.944 CST,,,10141,,56308caf.279d,3,,2015-10-28 16:51:59 CST,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,,""

备注:数据库果然无法启动,这时你可能通过 pg_resetxlog 救活数据库,当然会丢失一部分事务;或者如果有备份,从备份中恢复数据库。

五 总结
       把上面的测试结果汇总,如下图,可见,关闭 fsync 对 SELECT 无影响, 而 UPDATE 性能有较大提升,这个场景提升了 111%;当然关闭 fsync 参数的代价是巨大的,当数据库主机遭受操作系统故障或硬件故障时,数据库很有可能无法启动,并丢失数据,建议生产库不要关闭这参数。
 fsync   SELECT 场景TPS UPDATE 场景TPS
 on  16930      5946
 off  16727      12545
   

六 参考


  评论这张
 
阅读(679)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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