Re: [Postgres-xc-general] Looks like co-ordinator is my perf bottleneck....somebody help...

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Can you please check if there is increase in disk i/o as the number of rows
processed increases. I do not see any problem with the planner. But because
of huge result from datanode and not enough RAM, coordinator might be
choosing to store it on the disk.

On Mon, Feb 3, 2014 at 12:02 AM, Nirmal Sharma <sha...@gm...>wrote:

> Yes you are absolutely right.
> If I run the same query directly on nodes then it runs very fast. It is
> running slow when I run from coordinator. How am I going to resolve this
> tuple handling on coordinator?
> Please advise.
>
> Sent from my iPad
>
> On Feb 2, 2014, at 9:32 AM, Mason Sharp <ms...@tr...> wrote:
>
>
>
>
> On Sun, Feb 2, 2014 at 12:49 AM, Nirmal Sharma <sha...@gm...>wrote:
>
>> This is the explain plan for the query with limit 10000.
>>
>>
>> Limit  (cost=0.00..2.50 rows=1000 width=908) (actual
>> time=1586.926..1836.081 rows=10000 loops=1)
>>    Output: (COALESCE((fgpc.date_id)::bigint, fgcd.date_id)),
>> fgpc.m_ad_grp_pub_key, fgpc.m_kw_pub_key, kws.expr_names, kws.expr_values,
>> kws.m_ad_grp_semid, (sum(fgpc.m_imps)), (sum(fgpc.m_clicks)),
>> (sum(fgpc.m_cost)), (sum(fgpc.m_conv_1pc)), (sum(fgpc.m_conv_mpc)), (avg(f
>> gpc.m_cnv_rate_1pc)), (avg(fgpc.m_cnv_rate_mpc)), (avg(fgpc.m_avg_cpc)),
>> (avg(fgpc.m_max_cpc)), (avg(fgpc.m_firstpage_cpc)),
>> (avg(fgpc.m_topofpage_cpc)), (avg(fgpc.m_avg_cpm)), (avg(fgpc.m_max_cpm)),
>> (avg(fgpc.m_max_cpa_pct)), (avg(fgpc.m_avg_pos)), (avg(fgpc.m_lowest_pos
>> )), (avg(fgpc.m_highest_pos)), (avg(fgpc.m_quality_score)),
>> (avg(fgpc.m_view_thru_conv)), (sum(fgcd.m_revenue)),
>> (sum(fgcd.m_conversions)), (sum(COALESCE(kws.m_new_kw_bid, kws.m_kw_bid))),
>> (max(COALESCE(kws.m_new_kw_bid, kws.m_kw_bid))),
>> (min(COALESCE(kws.m_new_kw_bid, kw
>> s.m_kw_bid)))
>>    ->  Data Node Scan on "__REMOTE_LIMIT_QUERY__"  (cost=0.00..2.50
>> rows=1000 width=908) (actual time=1586.924..1834.118 rows=10000 loops=1)
>>          Output: (COALESCE((fgpc.date_id)::bigint, fgcd.date_id)),
>> fgpc.m_ad_grp_pub_key, fgpc.m_kw_pub_key, kws.expr_names, kws.expr_values,
>> kws.m_ad_grp_semid, (sum(fgpc.m_imps)), (sum(fgpc.m_clicks)),
>> (sum(fgpc.m_cost)), (sum(fgpc.m_conv_1pc)), (sum(fgpc.m_conv_mpc)),
>> (avg(fgpc.m_cnv_rate_1pc)), (avg(fgpc.m_cnv_rate_mpc)),
>> (avg(fgpc.m_avg_cpc)), (avg(fgpc.m_max_cpc)), (avg(fgpc.m_firstpage_cpc)),
>> (avg(fgpc.m_topofpage_cpc)), (avg(fgpc.m_avg_cpm)), (avg(fgpc.m_max_cpm)),
>> (avg(fgpc.m_max_cpa_pct)), (avg(fgpc.m_avg_pos)), (avg(fgpc.m_lowe
>> st_pos)), (avg(fgpc.m_highest_pos)), (avg(fgpc.m_quality_score)),
>> (avg(fgpc.m_view_thru_conv)), (sum(fgcd.m_revenue)),
>> (sum(fgcd.m_conversions)), (sum(COALESCE(kws.m_new_kw_bid, kws.m_kw_bid))),
>> (max(COALESCE(kws.m_new_kw_bid, kws.m_kw_bid))),
>> (min(COALESCE(kws.m_new_kw_b
>>  id, kws.m_kw_bid)))
>>          Node/s: d11, d12, d13, d14, d15, d16
>>          Remote query: SELECT COALESCE((l.a_1)::bigint, l.a_23), l.a_2,
>> l.a_3, r.a_1, r.a_2, r.a_3, sum(l.a_4), sum(l.a_5), sum(l.a_6), sum(l.a_7),
>> sum(l.a_8), pg_catalog.numeric_avg(avg(l.a_9)),
>> pg_catalog.numeric_avg(avg(l.a_10)), pg_catalog.numeric_avg(avg(l.a_11)), pg
>> _catalog.numeric_avg(avg(l.a_12)), pg_catalog.numeric_avg(avg(l.a_13)),
>> pg_catalog.numeric_avg(avg(l.a_14)), pg_catalog.numeric_avg(avg(l.a_15)),
>> pg_catalog.numeric_avg(avg(l.a_16)), pg_catalog.numeric_avg(avg(l.a_17)),
>> pg_catalog.numeric_avg(avg(l.a_18)), pg_catalog.nume
>> ric_avg(avg(l.a_19)), pg_catalog.numeric_avg(avg(l.a_20)),
>> pg_catalog.numeric_avg(avg(l.a_21)), pg_catalog.numeric_avg(avg(l.a_22)),
>> sum(l.a_24), sum(l.a_25), sum(COALESCE(r.a_4, r.a_5)), max(COALESCE(r.a_4,
>> r.a_5)), min(COALESCE(r.a_4, r.a_5)) FROM ((SELECT l.a_1, l.a_2,
>>  l.a_3, l.a_4, l.a_5, l.a_6, l.a_7, l.a_8, l.a_9, l.a_10, l.a_11, l.a_12,
>> l.a_13, l.a_14, l.a_15, l.a_16, l.a_17, l.a_18, l.a_19, l.a_20, l.a_21,
>> l.a_22, r.a_1, r.a_2, r.a_3 FROM ((SELECT fgpc.date_id,
>> fgpc.m_ad_grp_pub_key, fgpc.m_kw_pub_key, fgpc.m_imps, fgpc.m_clicks,
>> fgpc.m_cost, fgpc.m_conv_1pc, fgpc.m_conv_mpc, fgpc.m_cnv_rate_1pc,
>> fgpc.m_cnv_rate_mpc, fgpc.m_avg_cpc, fgpc.m_max_cpc, fgpc.m_firstpage_cpc,
>> fgpc.m_topofpage_cpc, fgpc.m_avg_cpm, fgpc.m_max_cpm, fgpc.m_max_cpa_pct,
>> fgpc.m_avg_pos, fgpc.m_lowest_pos, fgpc.m_highest_pos,
>>  fgpc.m_quality_score, fgpc.m_view_thru_conv FROM ONLY
>> bidw.fact_msn_kw_perf_daily fgpc WHERE true) l(a_1, a_2, a_3, a_4, a_5,
>> a_6, a_7, a_8, a_9, a_10, a_11, a_12, a_13, a_14, a_15, a_16, a_17, a_18,
>> a_19, a_20, a_21, a_22) LEFT JOIN (SELECT fgcd.date_id, fgcd.m_revenue,
>> fgcd.m_conversions, fgcd.m_ad_grp_pub_key, fgcd.m_kw_pub_key FROM ONLY
>> bidw.fact_msn_kw_conversion_daily fgcd WHERE true) r(a_1, a_2, a_3, a_4,
>> a_5) ON (((l.a_1 = l.a_1) AND (l.a_2 = r.a_4) AND (l.a_3 = r.a_5)))) WHERE
>> ((COALESCE((l.a_1)::bigint, r.a_1) >= 20131201) AND (
>> COALESCE((l.a_1)::bigint, r.a_1) <= 20140119))) l(a_1, a_2, a_3, a_4,
>> a_5, a_6, a_7, a_8, a_9, a_10, a_11, a_12, a_13, a_14, a_15, a_16, a_17,
>> a_18, a_19, a_20, a_21, a_22, a_23, a_24, a_25) JOIN (SELECT
>> kws.expr_names, kws.expr_values, kws.m_ad_grp_semid, kws.m_new_kw_bi
>> d, kws.m_kw_bid, kws.m_kw_pub_key, kws.m_ad_grp_pub_key FROM ONLY
>> biods.msn_keyword_sup kws WHERE true) r(a_1, a_2, a_3, a_4, a_5, a_6, a_7)
>> ON (true)) WHERE ((l.a_2 = r.a_7) AND (l.a_3 = r.a_6)) GROUP BY 1, 2, 3, 4,
>> 5, 6 LIMIT 10000::bigint
>>  Total runtime: 2194.762 ms
>> (7 rows)
>>
>>
> If you run the generated query on the nodes directly (through EXECUTE
> DIRECT) is the time similarly slow? If so, then it points to the query
> rewrite that is the problem. If it is fast, then it may mean an issue in
> tuple handling on the coordinator.
>
>
> --
> Mason Sharp
>
> TransLattice - http://www.translattice.com
> Distributed and Clustered Database Solutions
>
>
>
>
> ------------------------------------------------------------------------------
> WatchGuard Dimension instantly turns raw network data into actionable
> security intelligence. It gives you real-time visual feedback on key
> security issues and trends.  Skip the complicated setup - simply import
> a virtual appliance and go from zero to informed in seconds.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company