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

[Lonni J Friedman]

On Tue, Jul 23, 2013 at 5:42 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> Not sure how is like your configuration. Did you actually test like this?
>
> pgbench/psql -> CLUSTER_A -> PG_FDW -> pgpool_B -> CLUSTER_B

Yes, that's the config that exhibited the 40% performance loss.

> --
> 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