Re: Performance Bottleneck

> Jeff wrote:
> >
> > On Aug 8, 2004, at 1:29 AM, Martin Foster wrote:
> >
> >> I am currently making use of Apache::DBI which overrides the
> >> DBI::disconnect call and keeps a pool of active connections for use
> >> when need be. Since it offloads the pooling to the webserver, it
> >> seems more advantageous then pgpool which while being able to run on a
> >> external system is not adding another layer of complexity.
> >>
> >
> > Apache::DBI is not the same sort of a pool as pgpool. DB connections
> > are not shared among all your apache children (A common misconception).
> > So if you have 300 apache kids you can have have 300 db connections.
> > With pgpool connections are shared among all of them so even though you
> > have 300 kids you only have say 32 db connections.
> >
>
> Seems that you are right, never noticed that from the documentation
> before. I always assumed it had something to do with the long
> lasting/persistent scripts that would remain in transactions for
> extended periods of time.
>
> Here is an odd question. While the server run 7.4.x, the client
> connects with 7.3.x. Would this in itself make a difference in
> performance as the protocols are different? At least based from
> pgpool's documentation.

In this case the server fall back from V3 protocol (employed in 7.4 or
later) to V2 protocol (employed in from 6.4 to 7.3.x). As far as
pgpool concerning, performance difference is significant. Of course
that depends on the implementation though.

FYI here is the outline of the testing using pgbench.

H/W: Pentium4 2.4GHz x2/memory 1GB/HDD IDE 80GB (all PCs are same spec)
S/W: RedHat Linux 9/PostgreSQL 7.3.6/7.4.3

postgresql.conf:
tcpip_socket = true
max_connections = 512
shared_buffers = 2048

host A: pgbench, host B: pgpool, host C: PostgreSQL 7.3.6 or 7.4.3
pgbench parameters: -S -c 10 -t 1000

result:
TPS ratio(7.4.3) ratio(7.3.6)
----------------------------------------------------------------------------------------------------
without pgpool 4357.625059 100% 100%
with pgpool(connection pool mode) 4330.290294 99.4% 94.1%
with pgpool(replication mode) 4297.614996 98.6% 87.6%
with pgpoo(replication with strictmode) 4270.223136 98.0% 81.5%
--
Tatsuo Ishii