Skip to content

21.whpg_fdw.md

Latest commit

 

History

History
171 lines (150 loc) · 7.18 KB

21.whpg_fdw.md

File metadata and controls

171 lines (150 loc) · 7.18 KB

WarehousePG_FDW의 병렬 처리 기능

EDB의 가이드대로 Coordinator-to-Coordinator 방식이 아닌, Segment-to-Segment 방식의 병렬 데이터 처리가 정상 작동

구분 | 내용
--------------------------------------------------------------------
주요 설정 | SERVER 정의 시 mpp_execute 'all segments' 옵션 적용
동작 방식 | 로컬의 각 세그먼트가 원격 세그먼트에 직접 접속하여 데이터를 분산 패치함으로써 쿼리 성능을 최적화함
검증 내용 | 쿼리 실행 시 각 세그먼트 노드에서 개별 워커 프로세스가 활성화되는 것을 모니터링을 통해 최종 검증

원격(Remote) WHPG 서버 환경 구성 및 데이터 생성

CREATE DATABASE remote_db;
CREATE ROLE remote_user WITH LOGIN PASSWORD 'remote_pwd';
 
CREATE TABLE public.employees (
emp_id INT,
emp_name TEXT,
dept_name TEXT,
salary INT,
join_date DATE
);
 
GRANT ALL ON TABLE public.employees TO remote_user;
 
insert into public.employees (emp_id, emp_name, dept_name, salary, join_date)
select
i,
'Emp_' || i,
'Dept_' || (i % 10),
(random() * 50000 + 50000)::int,
CURRENT_DATE - (random() * 3650)::int
FROM generate_series(1,10000000) AS i;

로컬(Local) WHPG 서버 FDW 연동 구성

CREATE EXTENSION IF NOT EXISTS whpg_fdw;
 
CREATE SERVER remote_whpg_server
FOREIGN DATA WRAPPER whpg_fdw
OPTIONS (
host '192.168.100.71',
port '5555',
dbname 'remote_db',
mpp_execute 'all segments',
num_segments '4',
fetch_size '1000'
);
 
CREATE USER MAPPING FOR current_user
SERVER remote_whpg_server
OPTIONS (user 'remote_user', password 'remote_pwd');
 
CREATE SCHEMA IF NOT EXISTS fdw_test;
 
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_whpg_server
INTO fdw_test;

로컬에서 조회

whpg=# SELECT dept_name, COUNT(*), AVG(salary)
FROM fdw_test.employees
GROUP BY dept_name
ORDER BY dept_name;
dept_name  |  count  |        avg
-------------+---------+--------------------
Dept_0      | 1000000 | 74993.684884000000
Dept_1      | 1000000 | 75019.167268000000
Dept_2      | 1000000 | 75007.995534000000
Dept_3      | 1000000 | 75000.166069000000
Dept_4      | 1000000 | 75003.777779000000
Dept_5      | 1000000 | 75009.874009000000
Dept_6      | 1000000 | 74998.354354000000
Dept_7      | 1000000 | 74982.520049000000
Dept_8      | 1000000 | 75001.509692000000
Dept_9      | 1000000 | 74997.542790000000
Engineering |       3 | 85000.000000000000
Marketing   |       1 | 60000.000000000000
Sales       |       1 | 75000.000000000000
(13 rows)

로컬 모니터링(다른 세션)

whpg=# SELECT
gp_execution_segment() AS segment_id,
pid,
usename,
state,
query_start,
query
FROM gp_dist_random('pg_stat_activity')
WHERE query LIKE '%fdw_test%'
AND state != 'idle' and pid not in (3184801,3184800,3178874,3178873)
ORDER BY segment_id;
 
\watch 0.5
Tue Apr 7 15:55:25 2026 (every 0.5s)
 
segment_id | pid | usename | state | query_start | query
------------+-----+---------+-------+-------------+-------
(0 rows)
 
Tue Apr 7 15:55:26 2026 (every 0.5s)
 
segment_id | pid | usename | state | query_start | query
------------+---------+---------+--------+-------------------------------+-----------------------------------------
0 | 3185921 | gpadmin | active | 2026-04-07 15:55:25.96558+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
0 | 3183761 | gpadmin | active | 2026-04-07 15:55:25.965584+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
1 | 3185922 | gpadmin | active | 2026-04-07 15:55:25.965792+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
1 | 3183760 | gpadmin | active | 2026-04-07 15:55:25.965878+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
2 | 3180008 | gpadmin | active | 2026-04-07 15:55:25.965526+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
2 | 3177839 | gpadmin | active | 2026-04-07 15:55:25.965412+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
3 | 3180007 | gpadmin | active | 2026-04-07 15:55:25.965388+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
3 | 3177838 | gpadmin | active | 2026-04-07 15:55:25.965413+09 | SELECT dept_name, COUNT(*), AVG(salary)+
| | | | | FROM fdw_test.employees +
| | | | | GROUP BY dept_name +
| | | | | ORDER BY dept_name;
(8 rows)

원격 모니터링(세그먼트 프로세스 레벨 체크)

while true ; do date; ps -fu gpadmin | grep 'postgres' | grep 'con' ; sleep 0.1 ; echo "" ; done
 
Wed Apr  8 09:45:46 KST 2026
gpadmin   145778    9638  0 09:20 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(59294) con5526 seg0 idle
gpadmin   145779    9670  0 09:20 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(47546) con5526 seg1 idle
gpadmin   148491    9638  0 09:41 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(61316) con5632 seg0 idle
gpadmin   148492    9670  0 09:41 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(21252) con5632 seg1 idle
 
Wed Apr  8 09:45:46 KST 2026
gpadmin   145778    9638  0 09:20 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(59294) con5526 seg0 idle
gpadmin   145779    9670  0 09:20 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(47546) con5526 seg1 idle
gpadmin   148491    9638  0 09:41 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(61316) con5632 seg0 idle in transaction
gpadmin   148492    9670  0 09:41 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(21252) con5632 seg1 idle in transaction
gpadmin   149763    9638  0 09:45 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(54614) con5632 seg0 idle
gpadmin   149764    9670  0 09:45 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(33858) con5632 seg1 startup
 
Wed Apr  8 09:45:47 KST 2026
gpadmin   145778    9638  0 09:20 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(59294) con5526 seg0 idle
gpadmin   145779    9670  0 09:20 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(47546) con5526 seg1 idle
gpadmin   148491    9638  0 09:41 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(61316) con5632 seg0 idle in transaction
gpadmin   148492    9670  0 09:41 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(21252) con5632 seg1 idle in transaction
gpadmin   149763    9638  0 09:45 ?        00:00:00 postgres:  6000, remote_user remote_db 192.168.100.71(54614) con5632 seg0 cmd34 MPPEXEC SELECT
gpadmin   149764    9670  0 09:45 ?        00:00:00 postgres:  6001, remote_user remote_db 192.168.100.71(33858) con5632 seg1 cmd34 MPPEXEC SELECT