[pgpool-general: 1927] Re: 40% performance loss when using pgpool with postgres foreign data wrapper

[Tatsuo Ishii]

Not sure how is like your configuration. Did you actually test like this?

pgbench/psql -> CLUSTER_A -> PG_FDW -> pgpool_B -> CLUSTER_B
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> I'm currently testing out postgres-9.3 beta with the new postgres
> foreign data wraper (FDW) functionality (
> http://www.postgresql.org/docs/devel/static/postgres-fdw.html ).
> Ideally, I'd like to have two separate 3 node (1 master, 2 slave) 9.3
> clusters, with pgpool sitting in front of them.  Then I'd setup a
> foreign server between the two clusters, but specify that it connect
> to the pgpool instance, rather than directly to the cluster.
> 
> This is a rough diagram of how the communication would work while
> using psql (or pgbench) as a client:
> 
> pgbench/psql -> pgpool_A -> CLUSTER_A -> PG_FDW -> pgpool_B -> CLUSTER_B
> 
> When I've set this up in a test environment (without pgpool_A at all),
> and run pgbench against it, the performance when connecting PG_FDW to
> pgpool_B is 40% worse than if I eliminate pgpool_B entirely, and
> connect PG_FDW directly to CLUSTER_B.
> 
> The pgbench command that I'm running is:
> pgbench -n -U lfriedman -s 10000 -T 3600 -j 10 -c 10 nightly
> 
> Here's the output when pgpool_B is in use:
> #######
> transaction type: TPC-B (sort of)
> scaling factor: 10000
> query mode: simple
> number of clients: 10
> number of threads: 10
> duration: 3600 s
> number of transactions actually processed: 261747
> tps = 72.707223 (including connections establishing)
> tps = 72.707423 (excluding connections establishing)
> #######
> 
> Here's the output if I remove pgpool_B from the setup:
> #######
> transaction type: TPC-B (sort of)
> scaling factor: 10000
> query mode: simple
> number of clients: 10
> number of threads: 10
> duration: 3600 s
> number of transactions actually processed: 421192
> tps = 116.997552 (including connections establishing)
> tps = 116.998788 (excluding connections establishing)
> ########
> 
> 
> Other details:
> I'm using pgpool-3.2.4
> OS is RHEL6-x86_64 on all servers
> All servers are identical hardware (128GB RAM, 2x Xeon CPU E5-2670,
> SSD storage for $PGDATA)
> 
> I've attached the pgpool.conf that I'm using.
> 
> At this point, I'm primarily wondering if a 40% perf hit is expected
> behavior, or if there's some sort of misconfiguration or bug
> somewhere?
> 
> thanks