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

Tatsuo Ishii ishii at postgresql.org
Thu Jul 25 10:27:56 JST 2013


> On Wed, Jul 24, 2013 at 5:19 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> 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
>>
>> Interesting.
>>
>>> 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.
>>
>> I'll give it a try when I have spare time. At this point my wild guess
>> is PG_FDW does not generate good enough query plan. Can you show
>> EXPLAIN VERBOSE result?
> 
> How do I run pgbench with EXPLAIN?

In this configuration:
pgbench/psql -> CLUSTER_A -> PG_FDW -> pgpool_B -> CLUSTER_B

1) enable query log at CLUSTER_A

2) run pgbench

3) you will get some SELECTs, INSERTs, UPDATEs query. Choose one of
   them from each type of query as samples.

4) EXPLAIN ANALYZE query_sample
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


More information about the pgpool-general mailing list