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

Tatsuo Ishii ishii at postgresql.org
Fri Jul 26 09:11:00 JST 2013


> 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

Sorry, you need to execute EXPLAIN ANALYZE VEBOSE to get the remote
execution plan. Also the PostgreSQL manual suggests that ANALYZE on
the foreign table helps to update the remote table statistics.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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


More information about the pgpool-general mailing list