pgbouncer 实测

prefect

描述

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:
3. [databases]
4. * = port=5432
5. ; or kyle = host=10.0.2.19 port=2011 user=postgres password=password
6. [pgbouncer]
7. logfile = /data/postgresql/pgbouncer.log
8. pidfile = /data/postgresql/pgbouncer.pid
9. listen_addr = *
10. listen_port = 6432
11. unix_socket_dir = /var/run/postgresql
12. auth_type = trust
13. auth_file = /data/postgresql/8.4/main/global/pg_auth
14. admin_users = postgres
15. stats_users = postgres
16. pool_mode = transaction
17. server_reset_query = DISCARD ALL;
18. server_check_query = select 1
19. server_check_delay = 10
20. max_client_conn = 1000
21. default_pool_size = 20
22. log_connections = 1
23. log_disconnections = 1
24. 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自带的测试模板：

详细请看这里 ： http://www.postgresql.org/docs/9.0/interactive/pgbench.html  

 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. 

一个表明事务吞吐量的一个值

一个事务是一个仓储模型里面，处理下单的一组操作