免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2175 | 回复: 0

pgbouncer 实测 [复制链接]

论坛徽章:
0
发表于 2011-12-22 08:53 |显示全部楼层
描述

pgbouncer 是一个 PostgreSQL 的连接池。任何目标应用都可以把 pgbouncer 当作一个 PostgreSQL 服务器来连接,然后 pgbouncer 会处理与服务器连接,或者是重用已存在的连接。

pgbouncer 的目标是降低因为新建到 PostgreSQL 的连接而导致的性能损失。


您可以从这里得到最新版。

安装需要 libevent  libevent-devel-


安装只需要README即可完成。

./configure --prefix=/data/pgbouncer \

 --with-libevent=libevent-prefix


中文手册,请登录http://www.pgsqldb.org 搜索 pgbouncer 来寻找


配置文件仅供参考:
  1. Here is my pgbouncer.ini file:
  2. [databases]
  3. * = port=5432
  4. ; or kyle = host=10.0.2.19 port=2011 user=postgres password=password
  5. [pgbouncer]
  6. logfile = /data/postgresql/pgbouncer.log
  7. pidfile = /data/postgresql/pgbouncer.pid
  8. listen_addr = *
  9. listen_port = 6432
  10. unix_socket_dir = /var/run/postgresql
  11. auth_type = trust
  12. auth_file = /data/postgresql/8.4/main/global/pg_auth
  13. admin_users = postgres
  14. stats_users = postgres
  15. pool_mode = transaction
  16. server_reset_query = DISCARD ALL;
  17. server_check_query = select 1
  18. server_check_delay = 10
  19. max_client_conn = 1000
  20. default_pool_size = 20
  21. log_connections = 1
  22. log_disconnections = 1
  23. log_pooler_errors = 1

安装中的备忘,算是FAQ吧

1.认证文件的配置,
auth_type = md5
auth_file = userlist.txt

而md5的字符串 和 pgpool-II里面的MD5有所差异,最终的字段建议从DB端获取。
kyle=# SELECT rolname,rolpassword from pg_authid where rolname = 'postgres';
 rolname  |             rolpassword             
----------+-------------------------------------
 postgres | md532e12f215ba27cb750c9e093ce4b5127
(1 row)

所以 userlist.txt的格式为:
"postgres" "md532e12f215ba27cb750c9e093ce4b5127"

测试效果:
使用PG自带的测试模板:
 The most important options are -c (number of clients), -t (number of transactions), -T (time limit), and -f (specify a custom script file). 

The default transaction script issues seven commands per transaction:

  1. BEGIN;

  2. UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

  3. SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

  4. UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

  5. UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

  6. INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

  7. END;

If you specify -N, steps 4 and 5 aren't included in the transaction. If you specify -S, only the SELECT is issued.


初始化数据
pgbench -i -F 100 -s 500 kyle 
初始化的时候scale使用了500,也就是创建了5000W条account记录.

第一次测试 
postgres@node2:~/data/pg_log> pgbench -c 10 -C -T 60 -h 10.0.0.9 -p 6432 kyle
Password: 
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 1
duration: 60 s
number of transactions actually processed: 4383
tps = 72.899549 (including connections establishing)
tps = 77.316202 (excluding connections establishing)
postgres@node2:~/data/pg_log> pgbench -c 10 -C -T 60 -h 10.0.2.19 -p 2011 kyle   
Password: 
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 1
duration: 60 s
number of transactions actually processed: 1942
tps = 32.297861 (including connections establishing)
tps = 38.212388 (excluding connections establishing)

Now both tests repeated several times, just bare results for brevity:

Lets make it more readable, and calculate performance gain (pgbench vs direct):

(pgbouncer)

tps=70.718001(includingconnectionsestablishing)

tps=74.873934(excludingconnectionsestablishing)

(direct)

tps=40.851624(includingconnectionsestablishing)

tps=51.423081(excludingconnectionsestablishing)

(pgbouncer)

tps=44.308056(includingconnectionsestablishing)

tps=45.914929(excludingconnectionsestablishing)

(direct)

tps=39.320531(includingconnectionsestablishing)

tps=48.693776(excludingconnectionsestablishing)

(pgbouncer)

tps=47.776184(includingconnectionsestablishing)

tps=49.646028(excludingconnectionsestablishing)

(direct)

tps=36.120564(includingconnectionsestablishing)

tps=43.882230(excludingconnectionsestablishing)

(pgbouncer)

tps=52.843930(includingconnectionsestablishing)

tps=55.079497(excludingconnectionsestablishing)

(direct)

tps=35.466112(includingconnectionsestablishing)

tps=42.926671(excludingconnectionsestablishing)

(pgbouncer)

tps=57.992424(includingconnectionsestablishing)

tps=60.753912(excludingconnectionsestablishing)

(direct)

tps=28.972150(includingconnectionsestablishing)

tps=33.738600(excludingconnectionsestablishing)

(pgbouncer)

tps=49.628159(includingconnectionsestablishing)

tps=51.657994(excludingconnectionsestablishing)

(direct)

tps=33.368558(includingconnectionsestablishing)

tps=39.708264(excludingconnectionsestablishing)

(pgbouncer)

tps=46.731625(includingconnectionsestablishing)

tps=48.526225(excludingconnectionsestablishing)

(direct)

tps=37.645878(includingconnectionsestablishing)

tps=46.178010(excludingconnectionsestablishing)

(pgbouncer)

tps=54.543213(includingconnectionsestablishing)

tps=56.986342(excludingconnectionsestablishing)

(direct)

tps=30.300758(includingconnectionsestablishing)

tps=35.530590(excludingconnectionsestablishing)

(pgbouncer)

tps=56.551342(includingconnectionsestablishing)

tps=59.184591(excludingconnectionsestablishing)

(direct)

tps=34.553342(includingconnectionsestablishing)

tps=41.406925(excludingconnectionsestablishing)


direct vs pgbouncer pgbench results - non persistent connections


We can see that - including time consumed by connections handling - pgbouncer gives about 60% speedup compared to raw postgres.

37.52% is also a very interesting result - this difference probably comes from postgres session initialization, but maybe pgbouncer handles connections/disconnections faster than postgresql itself.


tps -- transactions per second 
By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. 
一个表明事务吞吐量的一个值
一个事务是一个仓储模型里面,处理下单的一组操作

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP