分类: Mysql/postgreSQL
2011-08-29 10:06:48
pgbouncer 是一个 PostgreSQL 的连接池。任何目标应用都可以把 pgbouncer 当作一个 PostgreSQL 服务器来连接,然后 pgbouncer 会处理与服务器连接,或者是重用已存在的连接。
pgbouncer 的目标是降低因为新建到 PostgreSQL 的连接而导致的性能损失。
您可以从得到最新版。
安装需要 libevent libevent-devel-安装只需要README即可完成。
./configure --prefix=/data/pgbouncer \
--with-libevent=libevent-prefix
The default transaction script issues seven commands per transaction:
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
If you specify -N, steps 4 and 5 aren't included in the transaction. If you specify -S, only the SELECT is issued.
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)
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.