Re: pgbouncer - massive overhead?

On 06/19/2012 09:00 AM, Strange, John W wrote:
> Given a baseline postgresql.conf config and a couple DL580 40 core/256GB memory I noticed a large over head for
pgbouncer,has  anyone seen this before? 
>
>
> $ pgbench -h `hostname -i` -j 32 -p 4320 -U asgprod -s 500 -c 32 -S -T 60 pgbench_500
> Scale option ignored, using pgbench_branches table count = 500
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 500
> query mode: simple
> number of clients: 32
> number of threads: 32
> duration: 60 s
> number of transactions actually processed: 1743073
> tps = 29049.886666 (including connections establishing)
> tps = 29050.308194 (excluding connections establishing)
>
> $ pgbench -h `hostname -i` -j 32 -p 4310 -U asgprod -s 500 -c 32 -S -T 60 pgbench_500
> Scale option ignored, using pgbench_branches table count = 500
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 500
> query mode: simple
> number of clients: 32
> number of threads: 32
> duration: 60 s
> number of transactions actually processed: 8692204
> tps = 144857.505107 (including connections establishing)
> tps = 144880.181341 (excluding connections establishing)
>
> processor       : 39
> vendor_id       : GenuineIntel
> cpu family      : 6
> model           : 47
> model name      :        Intel(R) Xeon(R) CPU E7- 4860  @ 2.27GHz
>
>
I'm very dubious that the stats are meaningful as run. Were the above
stats generated on consecutive runs on the same machine or was the test
database fully returned to baseline between runs and the machine
restarted to clear cache?

I doubt anyone here would trust the results of a 60-second pgbench run -
especially a select-only test on a server that will likely end up with
virtually everything ultimately in cache. Make sure each run is started
from the same state and run for 30-60 minutes.

Still, you *are* adding a layer between the client and the server.
Running the simplest of read-only queries against a fully-cached
database on a fast many-core machine is likely to emphasize any latency
introduced by pgbouncer. But it's also not a use-case for which
pgbouncer is intended. If you were to add -C so each query required a
new client connection a different picture would emerge. Same thing if
you had 2000 client connections of which only a handful were running
queries at any moment.

Cheers,
Steve