在同一张表上进行多个分片查询出现udp...
 
通知
清除全部

在同一张表上进行多个分片查询出现udp错误


wangcarven
帖子: 3
Topic starter
初出茅庐
已加入: 3月 前

数据库版本为:greenplum-db-6.11.2

有一张表,按日分区,每日数据量十万,在此表上进行多个子查询后将子查询结果逐个连接起来,在达到8个及以上slice的时候,必然在segment上出现类似 "WARNING","01000","interconnect may encountered a network error, please check your network","Failed to send packet (seq 1) to 127.0.0.1:4720 (pid 9487 cid -1) after 100 retries. ",0,,"ic_udpifc.c",5107,的错误。但因为是连接127.0.0.1所以不可能是网络层问题。此时检查postgres进程可以看到类似这样的,结合到查询计划中提示有broadcast的情况,盲猜是部分slice进程提前关闭了端口导致broadcast失败。

gpadmin  238853  15867  0 17:49 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38304) con1796087 seg0 idle in transaction
gpadmin  238854  15865  0 17:49 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(25848) con1796087 seg1 idle in transaction
gpadmin  238855  15868  0 17:49 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(12888) con1796087 seg2 idle in transaction
gpadmin  238856  15866  0 17:49 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48002) con1796087 seg6 idle in transaction
gpadmin  239113  15867  0 17:50 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38632) con1796087 seg0 cmd6 slice8 MPPEXEC SELECT
gpadmin  239114  15865  0 17:50 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(26176) con1796087 seg1 cmd6 slice8 MPPEXEC SELECT
gpadmin  239115  15868  0 17:50 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(13216) con1796087 seg2 cmd6 slice8 MPPEXEC SELECT
gpadmin  239116  15866  0 17:50 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48330) con1796087 seg6 cmd6 slice8 MPPEXEC SELECT
gpadmin  239121  15867  0 17:50 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38656) con1796087 seg0 cmd6 slice1 MPPEXEC SELECT
gpadmin  239122  15865  0 17:50 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(26200) con1796087 seg1 cmd6 slice1 MPPEXEC SELECT
gpadmin  239123  15868  0 17:50 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(13240) con1796087 seg2 cmd6 slice1 MPPEXEC SELECT
gpadmin  239124  15866  0 17:50 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48354) con1796087 seg6 cmd6 slice1 MPPEXEC SELECT
gpadmin  239129  15867  0 17:50 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38680) con1796087 seg0 cmd6 slice2 MPPEXEC SELECT
gpadmin  239130  15865  0 17:50 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(26224) con1796087 seg1 cmd6 slice2 MPPEXEC SELECT
gpadmin  239131  15868  0 17:50 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(13264) con1796087 seg2 cmd6 slice2 MPPEXEC SELECT
gpadmin  239132  15866  0 17:50 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48378) con1796087 seg6 cmd6 slice2 MPPEXEC SELECT
gpadmin  239137  15865  0 17:50 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(26248) con1796087 seg1 cmd6 slice3 MPPEXEC SELECT
gpadmin  239138  15867  0 17:50 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38704) con1796087 seg0 cmd6 slice3 MPPEXEC SELECT
gpadmin  239139  15868  0 17:50 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(13288) con1796087 seg2 cmd6 slice3 MPPEXEC SELECT
gpadmin  239140  15866  0 17:50 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48402) con1796087 seg6 cmd6 slice3 MPPEXEC SELECT
gpadmin  239145  15865  0 17:50 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(26272) con1796087 seg1 cmd6 slice4 MPPEXEC SELECT
gpadmin  239146  15867  0 17:50 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38728) con1796087 seg0 cmd6 slice4 MPPEXEC SELECT
gpadmin  239147  15868  0 17:50 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(13312) con1796087 seg2 cmd6 slice4 MPPEXEC SELECT
gpadmin  239148  15866  0 17:50 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48426) con1796087 seg6 cmd6 slice4 MPPEXEC SELECT
gpadmin  239153  15865  0 17:50 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(26296) con1796087 seg1 cmd6 slice5 MPPEXEC SELECT
gpadmin  239154  15867  0 17:50 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38752) con1796087 seg0 cmd6 slice5 MPPEXEC SELECT
gpadmin  239155  15868  0 17:50 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(13336) con1796087 seg2 cmd6 slice5 MPPEXEC SELECT
gpadmin  239156  15866  0 17:50 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48450) con1796087 seg6 cmd6 slice5 MPPEXEC SELECT
gpadmin  239161  15865  0 17:50 ?        0000 postgres:  6000, cygp db_ana 172.17.200.100(26320) con1796087 seg1 idle
gpadmin  239162  15867  0 17:50 ?        0000 postgres:  6003, cygp db_ana 172.17.200.100(38776) con1796087 seg0 idle
gpadmin  239163  15868  0 17:50 ?        0000 postgres:  6001, cygp db_ana 172.17.200.100(13360) con1796087 seg2 idle
gpadmin  239164  15866  0 17:50 ?        0000 postgres:  6002, cygp db_ana 172.17.200.100(48474) con1796087 seg6 idle

 

生成的查询计划如下

Aggregate  (cost=0.00..3879.02 rows=1 width=64)
  ->  Gather Motion 12:1  (slice11; segments: 12)  (cost=0.00..3879.02 rows=1 width=64)
        ->  Aggregate  (cost=0.00..3879.02 rows=1 width=64)
              ->  Hash Left Join  (cost=0.00..3879.02 rows=1 width=64)
                    Hash Cond: (player_enter_7.player_id = player_enter_8.player_id)
                    Join Filter: ((max(player_enter_8.log_time)) > (max(player_enter_7.log_time)))
                    ->  Hash Left Join  (cost=0.00..3448.02 rows=1 width=72)
                          Hash Cond: (player_enter_6.player_id = player_enter_7.player_id)
                          Join Filter: ((max(player_enter_7.log_time)) > (max(player_enter_6.log_time)))
                          ->  Hash Left Join  (cost=0.00..3017.02 rows=1 width=64)
                                Hash Cond: (player_enter_5.player_id = player_enter_6.player_id)
                                Join Filter: ((max(player_enter_6.log_time)) > (max(player_enter_5.log_time)))
                                ->  Hash Left Join  (cost=0.00..2586.01 rows=1 width=56)
                                      Hash Cond: (player_enter_4.player_id = player_enter_5.player_id)
                                      Join Filter: ((max(player_enter_5.log_time)) > (max(player_enter_4.log_time)))
                                      ->  Redistribute Motion 1:12  (slice6)  (cost=0.00..2155.01 rows=5 width=48)
                                            ->  Hash Left Join  (cost=0.00..2155.01 rows=1 width=48)
                                                  Hash Cond: (player_enter_3.player_id = player_enter_4.player_id)
                                                  Join Filter: ((max(player_enter_4.log_time)) > (max(player_enter_3.log_time)))
                                                  ->  Hash Left Join  (cost=0.00..1724.01 rows=1 width=40)
                                                        Hash Cond: (player_enter_2.player_id = player_enter_3.player_id)
                                                        Join Filter: ((max(player_enter_3.log_time)) > (max(player_enter_2.log_time)))
                                                        ->  Hash Left Join  (cost=0.00..1293.00 rows=1 width=32)
                                                              Hash Cond: (player_enter_1.player_id = player_enter_2.player_id)
                                                              Join Filter: ((max(player_enter_2.log_time)) > (max(player_enter_1.log_time)))
                                                              ->  Hash Left Join  (cost=0.00..862.00 rows=1 width=24)
                                                                    Hash Cond: (player_enter.player_id = player_enter_1.player_id)
                                                                    Join Filter: ((max(player_enter_1.log_time)) > (min(player_enter.log_time)))
                                                                    ->  Gather Motion 12:1  (slice1; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                                                          ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                                                                ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                                                      Group Key: player_enter.player_id
                                                                                      ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                                                            Sort Key: player_enter.player_id
                                                                                            ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                                                                  ->  Partition Selector for player_enter (dynamic scan id: 1)  (cost=10.00..100.00 rows=9 width=4)
                                                                                                        Partitions selected: 17 (out of 601)
                                                                                                  ->  Dynamic Seq Scan on player_enter (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=28)
                                                                                                        Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2021-01-02'::date) AND (wash_date <= '2021-01-30'::date))
                                                                    ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                                                                          ->  Gather Motion 12:1  (slice2; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                                                                ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                                                                      ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                                                            Group Key: player_enter_1.player_id
                                                                                            ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                                                                  Sort Key: player_enter_1.player_id
                                                                                                  ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                                                                        ->  Partition Selector for player_enter (dynamic scan id: 2)  (cost=10.00..100.00 rows=9 width=4)
                                                                                                              Partitions selected: 27 (out of 601)
                                                                                                        ->  Dynamic Seq Scan on player_enter player_enter_1 (dynamic scan id: 2)  (cost=0.00..431.00 rows=1 width=28)
                                                                                                              Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2021-02-02'::date) AND (wash_date <= '2021-02-28'::date))
                                                              ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                                                                    ->  Gather Motion 12:1  (slice3; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                                                          ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                                                                ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                                                      Group Key: player_enter_2.player_id
                                                                                      ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                                                            Sort Key: player_enter_2.player_id
                                                                                            ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                                                                  ->  Partition Selector for player_enter (dynamic scan id: 3)  (cost=10.00..100.00 rows=9 width=4)
                                                                                                        Partitions selected: 29 (out of 601)
                                                                                                  ->  Dynamic Seq Scan on player_enter player_enter_2 (dynamic scan id: 3)  (cost=0.00..431.00 rows=1 width=28)
                                                                                                        Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2021-03-02'::date) AND (wash_date <= '2021-03-30'::date))
                                                        ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                                                              ->  Gather Motion 12:1  (slice4; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                                                    ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                                                          ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                                                Group Key: player_enter_3.player_id
                                                                                ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                                                      Sort Key: player_enter_3.player_id
                                                                                      ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                                                            ->  Partition Selector for player_enter (dynamic scan id: 4)  (cost=10.00..100.00 rows=9 width=4)
                                                                                                  Partitions selected: 29 (out of 601)
                                                                                            ->  Dynamic Seq Scan on player_enter player_enter_3 (dynamic scan id: 4)  (cost=0.00..431.00 rows=1 width=28)
                                                                                                  Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2021-04-02'::date) AND (wash_date <= '2021-04-30'::date))
                                                  ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                                                        ->  Gather Motion 12:1  (slice5; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                                              ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                                                    ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                                          Group Key: player_enter_4.player_id
                                                                          ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                                                Sort Key: player_enter_4.player_id
                                                                                ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                                                      ->  Partition Selector for player_enter (dynamic scan id: 5)  (cost=10.00..100.00 rows=9 width=4)
                                                                                            Partitions selected: 29 (out of 601)
                                                                                      ->  Dynamic Seq Scan on player_enter player_enter_4 (dynamic scan id: 5)  (cost=0.00..431.00 rows=1 width=28)
                                                                                            Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2021-05-02'::date) AND (wash_date <= '2021-05-30'::date))
                                      ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                                            ->  Broadcast Motion 12:12  (slice7; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                                  ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                                        ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                              Group Key: player_enter_5.player_id
                                                              ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                                    Sort Key: player_enter_5.player_id
                                                                    ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                                          ->  Partition Selector for player_enter (dynamic scan id: 6)  (cost=10.00..100.00 rows=9 width=4)
                                                                                Partitions selected: 29 (out of 601)
                                                                          ->  Dynamic Seq Scan on player_enter player_enter_5 (dynamic scan id: 6)  (cost=0.00..431.00 rows=1 width=28)
                                                                                Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2021-06-02'::date) AND (wash_date <= '2021-06-30'::date))
                                ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                                      ->  Broadcast Motion 12:12  (slice8; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                            ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                                  ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                        Group Key: player_enter_6.player_id
                                                        ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                              Sort Key: player_enter_6.player_id
                                                              ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                                    ->  Partition Selector for player_enter (dynamic scan id: 7)  (cost=10.00..100.00 rows=9 width=4)
                                                                          Partitions selected: 29 (out of 601)
                                                                    ->  Dynamic Seq Scan on player_enter player_enter_6 (dynamic scan id: 7)  (cost=0.00..431.00 rows=1 width=28)
                                                                          Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2021-07-02'::date) AND (wash_date <= '2021-07-30'::date))
                          ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                                ->  Broadcast Motion 12:12  (slice9; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                      ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                            ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                                  Group Key: player_enter_7.player_id
                                                  ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                        Sort Key: player_enter_7.player_id
                                                        ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                              ->  Partition Selector for player_enter (dynamic scan id: 8)  (cost=10.00..100.00 rows=9 width=4)
                                                                    Partitions selected: 11 (out of 601)
                                                              ->  Dynamic Seq Scan on player_enter player_enter_7 (dynamic scan id: 8)  (cost=0.00..431.00 rows=1 width=28)
                                                                    Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2020-01-02'::date) AND (wash_date <= '2020-01-30'::date))
                    ->  Hash  (cost=431.00..431.00 rows=1 width=16)
                          ->  Broadcast Motion 12:12  (slice10; segments: 12)  (cost=0.00..431.00 rows=1 width=16)
                                ->  Result  (cost=0.00..431.00 rows=1 width=16)
                                      ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=16)
                                            Group Key: player_enter_8.player_id
                                            ->  Sort  (cost=0.00..431.00 rows=1 width=16)
                                                  Sort Key: player_enter_8.player_id
                                                  ->  Sequence  (cost=0.00..431.00 rows=1 width=28)
                                                        ->  Partition Selector for player_enter (dynamic scan id: 9)  (cost=10.00..100.00 rows=9 width=4)
                                                              Partitions selected: 9 (out of 601)
                                                        ->  Dynamic Seq Scan on player_enter player_enter_8 (dynamic scan id: 9)  (cost=0.00..431.00 rows=1 width=28)
                                                              Filter: (((channel)::text = ANY ('{a,b}'::text[])) AND (wash_date >= '2020-02-02'::date) AND (wash_date <= '2020-02-28'::date))
Optimizer: Pivotal Optimizer (GPORCA)

查询SQL如下:

 

SELECT
COUNT(_step_1.oid) ,
COUNT(_step_2.oid) ,
COUNT(_step_3.oid) ,
COUNT(_step_4.oid) ,
COUNT(_step_5.oid) ,
COUNT(_step_6.oid) ,
COUNT(_step_7.oid) ,
COUNT(_step_8.oid)
FROM
(
SELECT
tem.oid,
MIN(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2021-01-02'
AND '2021-01-30'
) AS tem
GROUP BY
tem.oid
) AS _step_1
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2021-02-02'
AND '2021-02-28'
) AS tem
GROUP BY
tem.oid
) AS _step_2 ON _step_2.oid = _step_1.oid
AND _step_2.log_time > _step_1.log_time
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2021-03-02'
AND '2021-03-30'
) AS tem
GROUP BY
tem.oid
) AS _step_3 ON _step_3.oid = _step_2.oid
AND _step_3.log_time > _step_2.log_time
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2021-04-02'
AND '2021-04-30'
) AS tem
GROUP BY
tem.oid
) AS _step_4 ON _step_4.oid = _step_3.oid
AND _step_4.log_time > _step_3.log_time
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2021-05-02'
AND '2021-05-30'
) AS tem
GROUP BY
tem.oid
) AS _step_5 ON _step_5.oid = _step_4.oid
AND _step_5.log_time > _step_4.log_time
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2021-06-02'
AND '2021-06-30'
) AS tem
GROUP BY
tem.oid
) AS _step_6 ON _step_6.oid = _step_5.oid
AND _step_6.log_time > _step_5.log_time
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2021-07-02'
AND '2021-07-30'
) AS tem
GROUP BY
tem.oid
) AS _step_7 ON _step_7.oid = _step_6.oid
AND _step_7.log_time > _step_6.log_time
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2020-01-02'
AND '2020-01-30'
) AS tem
GROUP BY
tem.oid
) AS _step_8 ON _step_8.oid = _step_7.oid
AND _step_8.log_time > _step_7.log_time
LEFT JOIN (
SELECT
tem.oid,
MAX(tem.log_time) as log_time
FROM
(
SELECT
player_enter.player_id AS oid,
player_enter.log_time AS log_time
FROM
player_enter
WHERE
player_enter.channel in ('a','b')
AND player_enter.wash_date BETWEEN '2020-02-02'
AND '2020-02-28'
) AS tem
GROUP BY
tem.oid
) AS _step_9 ON _step_9.oid = _step_8.oid
AND _step_9.log_time > _step_8.log_time
Close Bitnami banner
Bitnami