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

[Lonni J Friedman]

On Wed, Jul 24, 2013 at 6:27 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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

ok, I did as you suggested, however it didn't really provide any
useful clues.  The EXPLAIN cost ranges were identical regardless of
whether pgpool_B was in the configuration.  However, the pgbench tps
numbers remained dramatically different (by about 40%).  Anyway,
here's the EXPLAIN output for the most common queries:

nightly=# EXPLAIN ANALYZE SELECT aid, bid, abalance, filler, ctid FROM
public.pgbench_accounts WHERE ((aid = 708983046)) FOR UPDATE ;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 LockRows  (cost=100.00..111.38 rows=1 width=734) (actual
time=2.729..2.731 rows=1 loops=1)
   ->  Foreign Scan on pgbench_accounts  (cost=100.00..111.37 rows=1
width=734) (actual time=2.728..2.730 rows=1 loops=1)
 Total runtime: 4.297 ms
(3 rows)

nightly=# EXPLAIN ANALYZE SELECT NULL FROM public.pgbench_branches ;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on pgbench_branches  (cost=100.00..212.39 rows=3413
width=0) (actual time=3.148..174.946 rows=10000 loops=1)
 Total runtime: 177.309 ms
(2 rows)

nightly=# EXPLAIN ANALYZE SELECT abalance FROM public.pgbench_accounts
WHERE ((aid = 346327473)) ;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Foreign Scan on pgbench_accounts  (cost=100.00..146.86 rows=15
width=4) (actual time=2.365..2.366 rows=1 loops=1)
 Total runtime: 3.587 ms
(2 rows)

nightly=# EXPLAIN ANALYZE UPDATE public.pgbench_branches SET bbalance
= '85975' WHERE ctid = '(123,130)';
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Update on pgbench_branches  (cost=100.00..112.65 rows=1 width=366)
(actual time=3.043..3.043 rows=0 loops=1)
   ->  Foreign Scan on pgbench_branches  (cost=100.00..112.65 rows=1
width=366) (actual time=3.037..3.037 rows=0 loops=1)
 Total runtime: 4.321 ms
(3 rows

nightly=# EXPLAIN ANALYZE INSERT INTO public.pgbench_history(tid, bid,
aid, delta, mtime, filler) VALUES
('10526','8889','708983046','1806','2013-07-25 13:30:35.078487',NULL);
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Insert on pgbench_history  (cost=0.00..0.01 rows=1 width=0) (actual
time=2.576..2.576 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
time=0.002..0.002 rows=1 loops=1)
 Total runtime: 4.042 ms
(3 rows)