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

Lonni J Friedman netllama at gmail.com
Wed Jul 31 23:59:53 JST 2013


Hi Tatsuo,
Have you had a chance to replicate this problem yet?

thanks

On Fri, Jul 26, 2013 at 9:35 AM, Lonni J Friedman <netllama at gmail.com> wrote:
> On Thu, Jul 25, 2013 at 5:11 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> 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.
>
> I re-ran the commands with VERBOSE, but that still had no impact, as
> the queries without pgpool had the same cost as with pgpool.  Here's
> the output:
> ######
> nightly=# EXPLAIN ANALYZE VERBOSE 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=0.801..0.803 rows=1 loops=1)
>    Output: aid, bid, abalance, filler, ctid, pgbench_accounts.*
>    ->  Foreign Scan on public.pgbench_accounts  (cost=100.00..111.37
> rows=1 width=734) (actual time=0.800..0.801 rows=1 loops=1)
>          Output: aid, bid, abalance, filler, ctid, pgbench_accounts.*
>          Remote SQL: SELECT aid, bid, abalance, filler, ctid FROM
> public.pgbench_accounts WHERE ((aid = 708983046)) FOR UPDATE
>  Total runtime: 1.448 ms
> (6 rows)
>
> nightly=# EXPLAIN ANALYZE VERBOSE SELECT NULL FROM public.pgbench_branches ;
>                                                             QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------
>  Foreign Scan on public.pgbench_branches  (cost=100.00..212.39
> rows=3413 width=0) (actual time=1.537..267.226 rows=10000 loops=1)
>    Output: NULL::unknown
>    Remote SQL: SELECT NULL FROM public.pgbench_branches
>  Total runtime: 268.820 ms
> (4 rows)
>
> nightly=# EXPLAIN ANALYZE VERBOSE SELECT abalance FROM
> public.pgbench_accounts WHERE ((aid = 346327473)) ;
>                                                         QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------
>  Foreign Scan on public.pgbench_accounts  (cost=100.00..146.86 rows=15
> width=4) (actual time=0.931..0.933 rows=1 loops=1)
>    Output: abalance
>    Remote SQL: SELECT abalance FROM public.pgbench_accounts WHERE
> ((aid = 346327473))
>  Total runtime: 1.634 ms
> (4 rows)
>
> nightly=# EXPLAIN ANALYZE VERBOSE UPDATE public.pgbench_branches SET
> bbalance = '85975' WHERE ctid = '(123,130)';
>                                                            QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------
>  Update on public.pgbench_branches  (cost=100.00..112.65 rows=1
> width=366) (actual time=0.968..0.968 rows=0 loops=1)
>    Remote SQL: UPDATE public.pgbench_branches SET bbalance = $2 WHERE ctid = $1
>    ->  Foreign Scan on public.pgbench_branches  (cost=100.00..112.65
> rows=1 width=366) (actual time=0.956..0.956 rows=0 loops=1)
>          Output: bid, 85975, filler, ctid
>          Remote SQL: SELECT bid, filler, ctid FROM
> public.pgbench_branches WHERE ((ctid = '(123,130)'::tid)) FOR UPDATE
>  Total runtime: 1.660 ms
> (6 rows)
>
> nightly=# EXPLAIN ANALYZE VERBOSE 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 public.pgbench_history  (cost=0.00..0.01 rows=1 width=0)
> (actual time=1.164..1.164 rows=0 loops=1)
>    Remote SQL: INSERT INTO public.pgbench_history(tid, bid, aid,
> delta, mtime, filler) VALUES ($1, $2, $3, $4, $5, $6)
>    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
> time=0.001..0.002 rows=1 loops=1)
>          Output: 10526, 8889, 708983046, 1806, '2013-07-25
> 13:30:35.078487'::timestamp without time zone, NULL::character(22)
>  Total runtime: 1.976 ms
> (5 rows)
> ######
>
>
>
>
>
>>> 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