[Pgpool-general] pgpool II performance

[Tatsuo Ishii]

I think you are comapring an apple with an orange.

In reality typical PostgreSQL clients repeat the pattern "connect to
PostgreSQL, do something, disconnect" (for example web
applications). You could add -C option to pgbench to simulate the
behavior. Probably you could see how pgpool enhance the performance by
using connection pooling.

If your application keeps the connection to PostgreSQL, yes pgpool add
just some overhead. We have worked hard to minimize it, but it seems
very difficult to lower than as it is.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Thank you for your reply.   I have done some further tests:
> 
> Taking into account possible network problems I ran pgbench on the pgpool
> server hitting the db server directly to gauge any network latency issues:
> 
> postgres at db-2:~$ pgbench -c 20 -t 1000 -h XX.XXX.XX.XXX -p 5432 testdb1
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 20
> number of transactions per client: 1000
> number of transactions actually processed: 20000/20000
> tps = 974.866385 (including connections establishing)
> tps = 977.606505 (excluding connections establishing)
> 
> turning off replication and only using pgpool "connection pooling" I still
> get a pretty big decrease in performance.
> postgres at db-2:~$ pgbench -c 20 -t 1000 -h localhost -p 9999 testdb
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 20
> number of transactions per client: 1000
> number of transactions actually processed: 20000/20000
> tps = 718.794739 (including connections establishing)
> tps = 742.188834 (excluding connections establishing)
> 
> 
> I still think I might have something configured incorrectly.   Is pgbench
> not a good tool to benchmark the connection pooling with?
> 
> 
> Thank you for your time.
> 
> --J
> On Thu, Mar 6, 2008 at 10:46 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
> > > I'm running Postgres 8.1.11.   Here are the results of a pgbench test
> > > pgbench -c 20 -t 1000 testdb
> > > starting vacuum...end.
> > > transaction type: TPC-B (sort of)
> > > scaling factor: 1
> > > number of clients: 20
> > > number of transactions per client: 1000
> > > number of transactions actually processed: 20000/20000
> > > tps = 731.350579 (including connections establishing)
> > > tps = 732.860160 (excluding connections establishing)
> > >
> > > I set up another environment with 3 servers 1 pgpool server and 2
> > masters
> > > for master-master replication.  Everything on the pgpool functions great
> > but
> > > the performance is terrible.   Here are the results of the same pgbench
> > test
> > > run on the pgpool cluster.
> > > pgbench -p 9999 -c 20 -t 1000 testdb
> > > starting vacuum...end.
> > > transaction type: TPC-B (sort of)
> > > scaling factor: 1
> > > number of clients: 20
> > > number of transactions per client: 1000
> > > number of transactions actually processed: 20000/20000
> > > tps = 443.242941 (including connections establishing)
> > > tps = 443.912381 (excluding connections establishing)
> > > tps = 401.669441 (excluding connections establishing)
> > >
> > >
> > > All four servers have exactly the same hardware configuration.
> > >
> > > Is this performance loss normal?
> >
> > Yes.
> >
> > I think existing shared-nothing-synchronous-replication softwares
> > including PGCluster will show more or less same performance.
> >
> > I should note that while other such replication softwares show the
> > performance degration almost propotional to the numbers of PostgreSQL
> > servers, pgpool's performance is not worse than 1/2 of PostgreSQL.
> >
> > Also please note that READ query performance will increase according
> > to the numbers of PostgreSQL servers. So you have a chance to get
> > performance boost if most of your quries are READ. Of course this may
> > vary to the characteristics of load though.
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> >