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

[Lonni J Friedman]

On Tue, Jul 23, 2013 at 9:59 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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.
>
> If you try this:
>
> pgbench/psql -> pgpool_B -> CLUSTER_B
>
> How is the performance?

Perf is pretty good, nearly the same as:
pgbench/psql -> CLUSTER_A

Actual output:
########
Scale option ignored, using pgbench_branches table count = 10000
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: 7922742
tps = 2200.752746 (including connections establishing)
tps = 2200.760460 (excluding connections establishing)
########

It seems conclusive that pgpool is somehow not handling the FDW stuff
well.  Can you try setting something similar up on your end, and see
if you can reproduce the perf loss?  It doesn't require any special
data, just the normal default pgbench schema.


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