[pgpool-general: 1776] Re: pgpool vs. pgbouncer

[Jeff Frost]

I think pgbouncer will really shine when it is running locally on the application server with a separate PostgreSQL server.  

Also, I'm not sure 10 clients with 1 thread is large enough contention to push the performance of either pgpool or pgbouncer.

On May 26, 2013, at 6:36 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Hi,
> 
> I often heard that pgbouncer is faster than pgpool-II as a connecton
> pooler. So I decided to test it while returing from PGCon. I
> downloaded pgbouncer 1.5.4 source code and install onto my laptop(CORE
> i5 dual 2.5GHz, mem 8GB, SSD drive). I have pgpool-II 3.2.4 and two
> PostgreSQL 9.2.4 instances, configured streaming replication primary
> and standby, installed on the box as well.  Pgpool-II is running in
> streaming replication mode and load balance is turned off.
> 
> First, I ran pgbench against stock PostgreSQL.
> $ pgbench -c 10 -p 11000 -S -T 30 test
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> query mode: simple
> number of clients: 10
> number of threads: 1
> duration: 30 s
> number of transactions actually processed: 436977
> tps = 14564.187737 (including connections establishing)
> tps = 14578.915570 (excluding connections establishing)
> 
> Next, via pgpool-II.
> $ pgbench -c 10 -p 11002 -S -T 30 test
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> query mode: simple
> number of clients: 10
> number of threads: 1
> duration: 30 s
> number of transactions actually processed: 321184
> tps = 10705.071033 (including connections establishing)
> tps = 10711.529445 (excluding connections establishing)
> 
> Third, pgbouncer(session mode).
> $ pgbench -c 10 -p 6432 -S -T 30 test
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> query mode: simple
> number of clients: 10
> number of threads: 1
> duration: 30 s
> number of transactions actually processed: 380464
> tps = 12680.854680 (including connections establishing)
> tps = 12681.339904 (excluding connections establishing)
> 
> Ok, pgbouncer is faster than pgpool-II. The difference between
> pgpool-II and pgbouncer was around 18.5%.
> 
> Next, I tried pgbouncer's "transaction mode". The benefit with this
> mode is, huge number of connections to pgbouncer can be accepted while
> number of connections to PostgreSQL stays low. This is one of features
> lacking in pgpool-II and I was very interested in this. Here is the
> result of pgbbouncer running in transaction mode.
> 
> $ pgbench -c 10 -p 6432 -S -T 30 test
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> query mode: simple
> number of clients: 10
> number of threads: 1
> duration: 30 s
> number of transactions actually processed: 241844
> tps = 8061.328281 (including connections establishing)
> tps = 8061.749903 (excluding connections establishing)
> 
> Hum. This time pgbouncer is 25% slower than pgpool-II.
> 
> The last test is cascading pgbouncer and
> pgpool-II. i.e. client->pgbouncer->pgpool-II->PostgreSQL.  The merit
> of the configuration is, pgbouncer running in transaction mode accepts
> huge number of connections from client(for example, several
> thousands), plus pgpool-II does failover and load blanacing
> etc. Question is How is the performance?
> 
> $ pgbench -c 10 -p 6432 -S -T 30 test
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> query mode: simple
> number of clients: 10
> number of threads: 1
> duration: 30 s
> number of transactions actually processed: 161209
> tps = 5372.965295 (including connections establishing)
> tps = 5373.335471 (excluding connections establishing)
> 
> Comparing with pgbouncer running in transaction mode directly connects
> to PostgreSQL, it is 33% slower. I think this number is not terribly
> bad and maybe usefull those who needs huge number of connections and
> failover, load balance at the same time.
> 
> Someday, I would like to implement "transaction mode" in pgpool-II.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general

---
Jeff Frost <jeff at pgexperts.com>
CTO, PostgreSQL Experts, Inc.
Phone: 1-888-PG-EXPRT x506
FAX: 415-762-5122
http://www.pgexperts.com/