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

PostgreSQL 中文网

 
 
 

日志

 
 

Pgpool 流复制模式压力测试  

2014-10-04 11:37:58|  分类: PG高可用性 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

       上篇 blog:  PostgreSQL 流复制 + Pgpool-II 实现高可用 HA   介绍了使用 pgpool 结合 PostgreSQL 自身的流复制功能搭建 HA 环境,之前听说 pgpool 会影响性能,那么今天利用上篇 blog 刚搭好的 HA 环境,简单做下测试,环境信息请参考上篇 blog。

一 测试数据准备

建用户
CREATE ROLE pgpool_db1 LOGIN  ENCRYPTED PASSWORD 'pgpool_db1' nosuperuser noinherit nocreatedb nocreaterole ;

创建表空间  ( 两节点操作 )
mkdir -p /database/pg93/pg_tbs/tbs_pgpool_db1

create tablespace tbs_pgpool_db1 owner postgres LOCATION '/database/pg93/pg_tbs/tbs_pgpool_db1';

创建数据库
CREATE DATABASE pgpool_db1 WITH  OWNER = pgpool_db1   TEMPLATE = template1      ENCODING = 'UTF8'     TABLESPACE = tbs_pgpool_db1;
      
创建模式
postgres=# \c pgpool_db1 pgpool_db1
You are now connected to database "pgpool_db1" as user "pgpool_db1".

pgpool_db1=> create schema pgpool_db1;
CREATE SCHEMA

创建测试表,并插入 500 万测试数据。
pgpool_db1=> create table test_1(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());
CREATE TABLE

pgpool_db1=> insert into test_1(id,name) select n,n||'_test' from generate_series(1,5000000) n;
INSERT 0 5000000

pgpool_db1=> alter table test_1 add primary key(id);
ALTER TABLE


二 pgpool 配置
--查询 pgpool_db1  md5 密码

postgres=# select rolname,rolpassword from pg_authid where rolname='pgpool_db1';
  rolname   |             rolpassword             
------------+-------------------------------------
 pgpool_db1 | md594f532461c9e3e3a591e77a373da0493

(1 row)

--pool_passwd 中加入密码信息

[pg93@db2 load_test]$  echo "pgpool_db1:md594f532461c9e3e3a591e77a373da0493" >> /opt/pgpool/etc/pool_passwd


--reload pgpool

[pg93@db2 load_test]$ pgpool reload



三 性能测试
--测试 SQL

[pg93@db2 load_test]$ cat get_name.sql
\setrandom v_id 1 5000000

select name from test_1 where id=:v_id;


--直连测试

pgbench -c 2 -T 20 -n -N -M prepared -d pgpool_db1  -U pgpool_db1 -f get_name.sql   > get_name.out 2>&1 &

连接数 2
pghost:  pgport: 1921 nclients: 2 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 20 s
number of transactions actually processed: 61021
tps = 3050.684070 (including connections establishing)
tps = 3051.633320 (excluding connections establishing)

连接数 4
pghost:  pgport: 1921 nclients: 4 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
duration: 20 s
number of transactions actually processed: 64743
tps = 3236.352239 (including connections establishing)
tps = 3239.085678 (excluding connections establishing)

连接数 8
pghost:  pgport: 1921 nclients: 8 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 20 s
number of transactions actually processed: 47789
tps = 2387.004156 (including connections establishing)
tps = 2390.072375 (excluding connections establishing)

连接数 16
pghost:  pgport: 1921 nclients: 16 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 1
duration: 20 s
number of transactions actually processed: 44099
tps = 2201.601035 (including connections establishing)
tps = 2206.286789 (excluding connections establishing)

连接数 32
pghost:  pgport: 1921 nclients: 32 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 1
duration: 20 s
number of transactions actually processed: 42541
tps = 2125.029203 (including connections establishing)
tps = 2135.919727 (excluding connections establishing)


--连接 pgpool 测试

pgbench -h 127.0.0.1 -p 9999 -c 2 -T 20 -n -N -M prepared -d pgpool_db1 -U pgpool_db1 -f get_name.sql   > get_name.out 2>&1 &

连接数 2
pghost: 127.0.0.1 pgport: 9999 nclients: 2 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 20 s
number of transactions actually processed: 13570
tps = 678.426052 (including connections establishing)
tps = 678.522323 (excluding connections establishing)

连接数 4
pghost: 127.0.0.1 pgport: 9999 nclients: 4 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
duration: 20 s
number of transactions actually processed: 20307
tps = 1015.210206 (including connections establishing)
tps = 1016.593395 (excluding connections establishing)

连接数 8
pghost: 127.0.0.1 pgport: 9999 nclients: 8 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 20 s
number of transactions actually processed: 35957
tps = 1796.691134 (including connections establishing)
tps = 1799.634067 (excluding connections establishing)

连接数 16
pghost: 127.0.0.1 pgport: 9999 nclients: 16 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 1
duration: 20 s
number of transactions actually processed: 33079
tps = 1653.351817 (including connections establishing)
tps = 1661.947374 (excluding connections establishing)

连接数 32
pghost: 127.0.0.1 pgport: 9999 nclients: 32 duration: 20 dbName: pgpool_db1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 1
duration: 20 s
number of transactions actually processed: 32259
tps = 1611.528149 (including connections establishing)
tps = 1615.327601 (excluding connections establishing)


四 测试统计
--将上面测试结果统计如下:
测试项                    连接数
2          4 8 16 32
直连主节点(tps) 3051 3239 2390 2206 2125
连接 pgpool(tps) 678 1016 1799 1661 1615
性能损失(%) 77.80% 68% 24.70% 24.70% 24%


--拉张图更直观些
2014年10月04日 - francs - PostgreSQL DBA
备注:从图中看到使用 pgpool 性能比直连方式降低很多,特别是在并发连接数少的情况差距越明显,达到 70% 左右,随着并发连接数数增加,性能差距缩小到 20%- 30% 左右。
 

五 参考

  评论这张
 
阅读(2052)| 评论(4)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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