데이터가 저장되고 있는 일반 테이블을 파티션 테이블로 전환하는 작업이 필요하였습니다.
요구사항
데이터 정합성이 보장되어야 함
적은 다운타임으로 작업을 진행
서비스에 개발 요청을 최소화하여 진행하고자 함
특이사항
작업 필요 테이블은 입력만 있는 케이스가 대부분
데이터 입력이 INSERT 구문이 아닌 COPY FROM 구문을 사용하여 BULK 입력이 처리됨
방안 1 배치로 데이터 이관
1. 신규 파티셔닝 테이블 생성
2. 1차 데이터 이관 : 기존 데이터를 배치를 사용하여 신규 파티셔닝 테이블로 이관
3. (데이터 변경이 있는 경우) : 기존 테이블에 트리거를 생성하고 별도 테이블에 이력을 저장
4. 2차 데이터 이관 : 1차 이후 증분 데이터의 경우 배치를 사용하여 SEQ 기준 적재
5. 점검 작업 : 2차 데이터 이관 이후 ~ 점검 이전 잔여 데이터 이관 및 테이블 네임 스위칭
테스트
파티션 테이블 생성
데이터건 별 입력 시간 측정
1000건단위 약 30ms
10000건 단위 약 180ms
50000건 단위 약 750ms
100000건 단위 약 1500ms
300000건 단위 약 4800ms
--> 10,000건 단위로 이관 진행
프로시저, 쉘스크립트 생성
CREATE OR REPLACE PROCEDURE move_data(
IN start_id BIGINT,
IN end_id BIGINT,
IN batch_size INT,
IN session_number INT
)
LANGUAGE plpgsql
AS $$
DECLARE
last_max_id BIGINT := start_id;
max_id BIGINT := end_id;
BEGIN
-- 반복문을 사용하여 배치 단위로 처리
WHILE last_max_id < max_id LOOP
-- 트랜잭션 시작
-- 데이터 삽입
INSERT INTO 신규테이블 (컬럼)
SELECT *
FROM 과거테이블
WHERE PK > last_max_id
AND PK <= last_max_id + batch_size
ORDER BY PK;
-- 트랜잭션 커밋
COMMIT;
-- 마지막으로 처리한 asset_log_sn 값을 업데이트
SELECT max(PK) INTO last_max_id
FROM 신규테이블
WHERE PK > last_max_id AND PK <= last_max_id + batch_size;
-- 변수 값 출력 (세션 번호 포함)
RAISE NOTICE '세션 %: Last processed asset_log_sn: %', session_number, last_max_id;
-- 잠시 대기 (필요시)
PERFORM pg_sleep(0.01); -- 0.01초 대기
END LOOP;
END;
$$;
쉘 스크립트 생성
#!/bin/bash
# 데이터베이스 접속 정보
DB_HOST=""
DB_USER=""
DB_PASS=""
DB_NAME=""
OUTPUT_FILE="./output.log" # 출력 파일 경로
# 세션 수 (10개의 세션)
NUM_SESSIONS=10
BATCH_SIZE=100000
START_ID=1
END_ID=180000000
# 프로시저 실행 후 출력 리디렉션
for session in $(seq 1 $NUM_SESSIONS)
do
SESSION_START_ID=$((START_ID + (session - 1) * END_ID))
SESSION_END_ID=$((SESSION_START_ID + END_ID - 1))
# psql을 사용하여 프로시저 호출하고 그 출력을 파일에 리디렉션
PGPASSWORD=$DB_PASS psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "CALL move_data($SESSION_START_ID, $SESSION_END_ID, $BATCH_SIZE, $session);" >> "$OUTPUT_FILE" 2>&1 &
# 로그에 세션 번호와 함께 작업 시작 로그 남기기
echo "세션 $session 실행 시작: 범위 $SESSION_START_ID ~ $SESSION_END_ID" >> "$OUTPUT_FILE"
# 세션 완료 후 로그 남기기
echo "세션 $session 실행 완료: 범위 $SESSION_START_ID ~ $SESSION_END_ID" >> "$OUTPUT_FILE"
done
# 모든 세션이 완료될 때까지 대기
wait
echo "모든 세션이 완료되었습니다." >> "$OUTPUT_FILE"
14:40 작업 시작
4XLARGE 기준으로 진행
약 22~24시간 소요, CPU 최대 32% 사용
네트워크 사용량 최대 720MB 까지 사용
방안 2
API 2중 쓰기
신규 파티셔닝 테이블 생성
기존 데이터를 배치를 사용하여 신규 파티셔닝 테이블로 이관
API에서 신규 테이블에도 데이터를 적재하도록 함
점검 이후 데이터 입력 멈춘 것 확인 후 스위칭
(작업간 seq 값 중복을 피하기 위한 방어 필요)
방안 3 view 사용
파티셔닝 테이블 생성
기존 테이블 rename
기존 테이블 명칭으로 view 생성
입력은 신규 테이블로 UPDATE, DELETE는 양쪽 모두 SELECT는 VIEW만
기존 데이터는 옮기면서 삭제하는 방식으로 원자성 보장 (트리거 또는 RULE 기능을 사용)
다 완료된 후 데이터 입력 멈춘 것 확인 후 VIEW명칭과 파티션 테이블 명칭 스위치
(작업간 seq 값 중복을 피하기 위한 방어 필요)
-> VIEW 사용으로 인한 쿼리 성능 저하가 발생할 수 있음
방안 4 과거 데이터를 하나의 파티션으로 생성
작업 스크립트
-- 작업 스크립트
1. PK 변경을 위한 유니크 인덱스 생성
SET max_parallel_maintenance_workers = 4;
SET LOCAL maintenance_work_mem = '50GB';
create unique index 신규PK명칭 on 과거테이블 (PK,created_at (파티션키));
2. 파티션 테이블에 추가하기 위한 제약조건 추가
ALTER TABLE 과거테이블 ADD CONSTRAINT 과거테이블_202510_created_not_null CHECK (created_at is not null) NOT VALID;
ALTER TABLE 과거테이블 ADD CONSTRAINT 과거테이블_202510_created_at CHECK (created_at >= '2025-01-01' AND created_at <'2025-11-01') NOT VALID;
3-1. NOT NULL VALID 추가
alter table 과거테이블 validate constraint nxpt_asset_log_202510_created_not_null;
--작업간 LOCK 확인
locktype | relname | pid | mode | granted | waitstart
---------------+-----------------------------------+------+--------------------------+---------+-----------
relation | 과거테이블 | 1093 | ShareUpdateExclusiveLock | t |
transactionid | | 1093 | ExclusiveLock | t |
virtualxid | | 1093 | ExclusiveLock | t |
3-2. 컬럼 NOT NULL 설정
ALTER TABLE 과거테이블 ALTER COLUMN created_at SET NOT NULL; (바로 바뀜)
alter table 과거테이블 drop constraint 과거테이블_202510_created_not_null;
--4. CHECK VALID 추가
alter table 과거테이블 과거테이블_202510_created_at;
--5. 데이터 유입이 없을 때 파티션 테이블로 전환
--5-1 과거 테이블 rename 및 PK 변경
ALTER TABLE 과거테이블 RENAME TO 과거테이블_202510;
alter table 과거테이블_202510 drop constraint 과거테이블_PK;
ALTER TABLE 과거테이블_202510 ADD CONSTRAINT 신규PK명칭 PRIMARY KEY USING INDEX 신규PK명칭;
--5-2 파티션 테이블 생성
CREATE TABLE public.기존테이블명 (
PK컬럼 bigint DEFAULT nextval('기존테이블의 시퀀스명'::regclass) NOT NULL,
A varchar(66) NULL,
B varchar(66) NULL,
C jsonb NOT NULL,
created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
D varchar(255) NULL,
CONSTRAINT PK명칭 PRIMARY KEY (PK컬럼, created_at)
)PARTITION BY RANGE (created_at);
--시퀀스 Owner 변경
ALTER SEQUENCE public.기존테이블의시퀀스명 OWNED BY 기존테이블명.PK컬럼 ;
--Owner 변경 확인
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
a.attname AS column_name,
s.relname AS sequence_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid
JOIN pg_class c ON d.refobjid = c.oid
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = d.refobjsubid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE s.relkind = 'S'
-------------------------------------------
schema_name | table_name | column_name | sequence_name
-------------+----------------+--------------+---------------------------------
public | 기존테이블명 | PK컬럼 | 기존테이블의시퀀스명칭
--5-3 파티션 추가
CREATE TABLE 기존테이블명_202511 (LIKE 기존테이블명 including all, check (created_at >= '2025-11-01' AND created_at <'2025-12-01'));
CREATE TABLE 기존테이블명_202512 (LIKE 기존테이블명 including all, check (created_at >= '2025-12-01' AND created_at <'2026-01-01'));
CREATE TABLE 기존테이블명_202601 (LIKE 기존테이블명 including all, check (created_at >= '2026-01-01' AND created_at <'2026-02-01'));
CREATE TABLE 기존테이블명_202602 (LIKE 기존테이블명 including all, check (created_at >= '2026-02-01' AND created_at <'2026-03-01'));
ALTER TABLE 기존테이블명 ATTACH PARTITION 기존테이블명_202511 FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
ALTER TABLE 기존테이블명 ATTACH PARTITION 기존테이블명_202512 FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
ALTER TABLE 기존테이블명 ATTACH PARTITION 기존테이블명_202601 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
ALTER TABLE 기존테이블명 ATTACH PARTITION 기존테이블명_202602 FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
ALTER TABLE 기존테이블명_202511 DROP CONSTRAINT 기존테이블명_202511_created_at_check;
ALTER TABLE 기존테이블명_202512 DROP CONSTRAINT 기존테이블명_202512_created_at_check;
ALTER TABLE 기존테이블명_202601 DROP CONSTRAINT 기존테이블명_202601_created_at_check;
ALTER TABLE 기존테이블명_202602 DROP CONSTRAINT 기존테이블명_202602_created_at_check;
--5-4 기존 테이블 파티션으로 추가
ALTER TABLE 기존테이블명 ATTACH PARTITION 기존테이블명_202510 FOR VALUES FROM ('2025-01-01') TO ('2025-11-01');
alter table 기존테이블명_202510 drop constraint 기존테이블명_202510_created_at;
--상태 확인
SELECT
nm.nspname AS schema_name,
parent.relname AS parent_table,
child.relname AS partition_table,
pg_get_expr(child.relpartbound, child.oid) AS partition_condition
FROM pg_partitioned_table p
JOIN pg_class parent ON p.partrelid = parent.oid
JOIN pg_namespace nm ON parent.relnamespace = nm.oid
LEFT JOIN pg_inherits i ON parent.oid = i.inhparent
LEFT JOIN pg_class child ON i.inhrelid = child.oid
LEFT JOIN pg_partitioned_table cp ON child.oid = cp.partrelid -- 자식 테이블이 다시 부모일 경우
ORDER BY schema_name, parent_table, partition_table;
schema_name | parent_table | partition_table | partition_condition
-------------+----------------+-----------------------+--------------------------------------------------------------------------
public | 기존테이블명 | 기존테이블명_202511 | FOR VALUES FROM ('2025-11-01 00:00:00+00') TO ('2025-12-01 00:00:00+00')
public | 기존테이블명 | 기존테이블명_202512 | FOR VALUES FROM ('2025-12-01 00:00:00+00') TO ('2026-01-01 00:00:00+00')
public | 기존테이블명 | 기존테이블명_202510 | FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2025-11-01 00:00:00+00')
(3 rows)
작업간 서비스에 영향을 줄 수 있는 요인
1. VALIDATE 작업 중 READ IOPS 가 대량 사용하며StorageNetworkThroughput이 높게 증가하는 것 확인.
이를 확인하기 위해 부하 테스트 진행
-- 8XLARGE
progress: 790.0 s, 25.1 tps, lat 39.868 ms stddev 0.455
progress: 800.0 s, 25.1 tps, lat 39.905 ms stddev 0.422
progress: 810.0 s, 25.0 tps, lat 39.950 ms stddev 0.444
progress: 820.0 s, 25.1 tps, lat 39.858 ms stddev 0.413
progress: 830.0 s, 25.0 tps, lat 39.912 ms stddev 0.427
progress: 840.0 s, 25.0 tps, lat 40.046 ms stddev 0.492
progress: 850.0 s, 25.0 tps, lat 39.933 ms stddev 0.432
progress: 860.0 s, 25.0 tps, lat 40.014 ms stddev 0.418
progress: 870.0 s, 25.0 tps, lat 40.031 ms stddev 0.502
progress: 880.0 s, 25.1 tps, lat 39.881 ms stddev 0.439
progress: 890.0 s, 25.0 tps, lat 39.915 ms stddev 0.502
progress: 900.0 s, 25.1 tps, lat 39.950 ms stddev 0.434
progress: 910.0 s, 24.9 tps, lat 40.103 ms stddev 0.400
progress: 920.0 s, 25.0 tps, lat 40.064 ms stddev 0.410
progress: 930.0 s, 24.9 tps, lat 40.081 ms stddev 0.912
---------------------------------------------------------VALIDATE NOT NULL 작업시작
progress: 940.0 s, 24.8 tps, lat 40.333 ms stddev 0.448
progress: 5120.0 s, 23.8 tps, lat 42.062 ms stddev 0.588
progress: 5130.0 s, 23.7 tps, lat 42.120 ms stddev 0.391
progress: 5140.0 s, 23.9 tps, lat 41.981 ms stddev 0.350
progress: 5150.0 s, 23.7 tps, lat 42.109 ms stddev 1.751
progress: 5160.0 s, 23.8 tps, lat 42.051 ms stddev 0.449
progress: 5170.0 s, 23.8 tps, lat 42.084 ms stddev 2.970
progress: 5180.0 s, 23.8 tps, lat 41.928 ms stddev 0.409
progress: 5190.0 s, 23.8 tps, lat 42.017 ms stddev 0.407
progress: 5200.0 s, 23.8 tps, lat 42.085 ms stddev 0.448
progress: 5210.0 s, 23.7 tps, lat 42.139 ms stddev 0.435
progress: 5220.0 s, 23.9 tps, lat 41.901 ms stddev 0.380
progress: 5230.0 s, 23.8 tps, lat 41.865 ms stddev 0.371
progress: 5240.0 s, 23.9 tps, lat 41.869 ms stddev 0.410
progress: 5250.0 s, 23.8 tps, lat 42.020 ms stddev 0.435
progress: 5260.0 s, 23.8 tps, lat 42.015 ms stddev 0.385
progress: 5270.0 s, 23.8 tps, lat 42.024 ms stddev 0.442
progress: 5280.0 s, 23.7 tps, lat 42.273 ms stddev 2.736
---------------------------------------------------------VALIDATE CHECK 작업시작
progress: 20940.0 s, 25.1 tps, lat 39.776 ms stddev 0.499
progress: 20950.0 s, 25.2 tps, lat 39.787 ms stddev 0.519
progress: 21230.0 s, 24.9 tps, lat 40.187 ms stddev 0.414
progress: 21240.0 s, 24.9 tps, lat 40.219 ms stddev 0.432
progress: 21250.0 s, 24.8 tps, lat 40.205 ms stddev 0.420
progress: 21260.0 s, 24.8 tps, lat 40.296 ms stddev 0.452
progress: 21270.0 s, 24.9 tps, lat 40.273 ms stddev 0.443
progress: 21280.0 s, 24.8 tps, lat 40.264 ms stddev 0.438
progress: 21290.0 s, 24.8 tps, lat 40.401 ms stddev 0.473
progress: 21300.0 s, 24.8 tps, lat 40.228 ms stddev 0.413
progress: 21310.0 s, 24.9 tps, lat 40.143 ms stddev 0.368
progress: 21320.0 s, 24.9 tps, lat 40.266 ms stddev 0.442
progress: 21330.0 s, 24.8 tps, lat 40.182 ms stddev 0.426
progress: 21340.0 s, 24.9 tps, lat 40.247 ms stddev 0.439
progress: 21350.0 s, 24.8 tps, lat 40.225 ms stddev 0.417
progress: 21360.0 s, 24.9 tps, lat 40.222 ms stddev 0.462
progress: 21370.0 s, 24.9 tps, lat 40.219 ms stddev 0.507
progress: 21380.0 s, 24.8 tps, lat 40.244 ms stddev 0.430
progress: 21390.0 s, 24.8 tps, lat 40.327 ms stddev 0.500
progress: 21400.0 s, 24.9 tps, lat 40.190 ms stddev 0.491
progress: 21410.0 s, 24.9 tps, lat 40.213 ms stddev 0.438
---------------------------------------------------------인덱스 생성
---------------------------------------------------------인덱스 생성을 위한 테이블 스캔 (tps 큰 변화 없음)
progress: 31180.0 s, 25.1 tps, lat 39.796 ms stddev 0.352
progress: 31190.0 s, 25.2 tps, lat 39.714 ms stddev 0.345
progress: 31200.0 s, 25.1 tps, lat 39.852 ms stddev 0.407
progress: 31210.0 s, 25.2 tps, lat 39.746 ms stddev 0.367
progress: 31220.0 s, 25.1 tps, lat 39.714 ms stddev 0.387
progress: 31230.0 s, 25.1 tps, lat 39.924 ms stddev 0.390
progress: 31240.0 s, 25.0 tps, lat 39.907 ms stddev 0.399
progress: 31250.0 s, 25.1 tps, lat 39.857 ms stddev 0.392
progress: 31260.0 s, 25.2 tps, lat 39.762 ms stddev 0.367
progress: 31270.0 s, 25.1 tps, lat 39.735 ms stddev 0.447
progress: 31280.0 s, 25.1 tps, lat 39.826 ms stddev 0.401
progress: 31290.0 s, 25.1 tps, lat 39.846 ms stddev 0.365
progress: 31300.0 s, 25.1 tps, lat 39.883 ms stddev 0.416
---------------------------------------------------------인덱스 쓰기 (tps가 많이 감소함)
progress: 35400.0 s, 10.9 tps, lat 91.724 ms stddev 34.446
progress: 35410.0 s, 10.9 tps, lat 91.228 ms stddev 27.440
progress: 35420.0 s, 10.3 tps, lat 97.409 ms stddev 38.709
progress: 35430.0 s, 11.0 tps, lat 90.261 ms stddev 31.768
progress: 35440.0 s, 10.3 tps, lat 96.982 ms stddev 37.754
progress: 35450.0 s, 10.8 tps, lat 93.156 ms stddev 34.499
progress: 35460.0 s, 10.4 tps, lat 95.709 ms stddev 39.890
progress: 35470.0 s, 10.4 tps, lat 96.176 ms stddev 40.841
progress: 35480.0 s, 10.9 tps, lat 90.952 ms stddev 31.791
progress: 35490.0 s, 10.2 tps, lat 98.994 ms stddev 44.529
progress: 35500.0 s, 11.0 tps, lat 91.489 ms stddev 35.286
progress: 35510.0 s, 10.9 tps, lat 91.655 ms stddev 36.899
progress: 35520.0 s, 10.6 tps, lat 94.014 ms stddev 41.932
progress: 35530.0 s, 10.4 tps, lat 95.538 ms stddev 41.995
progress: 35540.0 s, 9.9 tps, lat 101.709 ms stddev 51.222
2. 인덱스 생성시 작업 시간과 TPS 감소를 막기 위한 테스트
A. 인덱스 병렬 생성 테스트
--부하 입력 코드
pgbench DB명칭 -h 호스트 -p 5432 -U 계정명 -c 1 -j 1 -t 1000000 --progress 10 -r --no-vacuum --file=/dev/stdin <<'\q'
INSERT INTO 테이블 (컬럼)
SELECT 컬럼
FROM 데이터1000건입력한테이블 LIMIT 50;
\q
-- 인덱스 생성간 영향도 확인
-- 8XLARGE 싱글 스레드
progress: 790.0 s, 25.1 tps, lat 39.868 ms stddev 0.455
progress: 800.0 s, 25.1 tps, lat 39.905 ms stddev 0.422
progress: 810.0 s, 25.0 tps, lat 39.950 ms stddev 0.444
progress: 820.0 s, 25.1 tps, lat 39.858 ms stddev 0.413
progress: 830.0 s, 25.0 tps, lat 39.912 ms stddev 0.427
progress: 840.0 s, 25.0 tps, lat 40.046 ms stddev 0.492
---------------------------------------------------------인덱스 생성 (총 소요시간 약 300분)
---------------------------------------------------------인덱스 생성을 위한 테이블 스캔 (tps 큰 변화 없음 소요시간 : 130분)
progress: 31180.0 s, 25.1 tps, lat 39.796 ms stddev 0.352
progress: 31190.0 s, 25.2 tps, lat 39.714 ms stddev 0.345
progress: 31200.0 s, 25.1 tps, lat 39.852 ms stddev 0.407
progress: 31210.0 s, 25.2 tps, lat 39.746 ms stddev 0.367
progress: 31220.0 s, 25.1 tps, lat 39.714 ms stddev 0.387
progress: 31230.0 s, 25.1 tps, lat 39.924 ms stddev 0.390
progress: 31240.0 s, 25.0 tps, lat 39.907 ms stddev 0.399
progress: 31250.0 s, 25.1 tps, lat 39.857 ms stddev 0.392
---------------------------------------------------------인덱스 쓰기 (tps가 많이 감소함 소요시간 : 40분)
progress: 35400.0 s, 10.9 tps, lat 91.724 ms stddev 34.446
progress: 35410.0 s, 10.9 tps, lat 91.228 ms stddev 27.440
progress: 35420.0 s, 10.3 tps, lat 97.409 ms stddev 38.709
progress: 35430.0 s, 11.0 tps, lat 90.261 ms stddev 31.768
progress: 35440.0 s, 10.3 tps, lat 96.982 ms stddev 37.754
progress: 35450.0 s, 10.8 tps, lat 93.156 ms stddev 34.499
progress: 35460.0 s, 10.4 tps, lat 95.709 ms stddev 39.890
progress: 35470.0 s, 10.4 tps, lat 96.176 ms stddev 40.841
progress: 35480.0 s, 10.9 tps, lat 90.952 ms stddev 31.791
---------------------------------------------------------인덱스 생성을 위한 테이블 스캔 (tps 큰 변화 없음 소요시간 : 130분)
--8XLARGE 4개 스레드 처리
max_parallel_maintenance_workers = 4, maintenance_work_mem = 41943040 (40GB)
rogress: 220.0 s, 25.2 tps, lat 39.713 ms stddev 0.607
progress: 230.0 s, 25.0 tps, lat 39.849 ms stddev 0.909
progress: 240.0 s, 25.2 tps, lat 39.769 ms stddev 0.576
progress: 250.0 s, 25.2 tps, lat 39.723 ms stddev 0.490
progress: 260.0 s, 24.9 tps, lat 40.169 ms stddev 0.759
progress: 270.0 s, 24.5 tps, lat 40.798 ms stddev 0.498
progress: 280.0 s, 24.5 tps, lat 40.843 ms stddev 0.662
progress: 290.0 s, 24.4 tps, lat 40.839 ms stddev 0.608
progress: 300.0 s, 24.6 tps, lat 40.790 ms stddev 0.485
---------------------------------------------------------인덱스 생성
---------------------------------------------------------인덱스 생성을 위한 테이블 병렬 스캔, 정렬 (싱글 스레드보다 많이 TPS 감소, 소요시간 : 75분(조회45분 정렬 30분)
progress: 310.0 s, 24.5 tps, lat 40.816 ms stddev 0.680
progress: 320.0 s, 24.1 tps, lat 41.450 ms stddev 0.661
progress: 330.0 s, 24.1 tps, lat 41.481 ms stddev 0.496
progress: 340.0 s, 24.1 tps, lat 41.524 ms stddev 0.722
progress: 350.0 s, 18.1 tps, lat 55.216 ms stddev 160.335
progress: 360.0 s, 24.1 tps, lat 41.492 ms stddev 0.720
progress: 370.0 s, 24.2 tps, lat 41.287 ms stddev 0.554
---------------------------------------------------------인덱스 쓰기 (tps가 많이 감소함 소요시간 : 40분)
progress: 5180.0 s, 10.9 tps, lat 94.088 ms stddev 124.232
progress: 5190.0 s, 22.1 tps, lat 44.788 ms stddev 20.150
progress: 5200.0 s, 19.7 tps, lat 51.255 ms stddev 60.416
progress: 5210.0 s, 3.2 tps, lat 302.327 ms stddev 231.987
progress: 5220.0 s, 2.0 tps, lat 510.629 ms stddev 207.276
progress: 5230.0 s, 17.9 tps, lat 56.622 ms stddev 73.542
progress: 5240.0 s, 15.0 tps, lat 65.087 ms stddev 75.433
progress: 5250.0 s, 20.9 tps, lat 48.871 ms stddev 49.761
progress: 5260.0 s, 2.9 tps, lat 345.304 ms stddev 166.024
progress: 5270.0 s, 1.8 tps, lat 541.911 ms stddev 136.768
progress: 5280.0 s, 21.3 tps, lat 47.997 ms stddev 42.224
progress: 5290.0 s, 14.7 tps, lat 68.062 ms stddev 114.977
progress: 5300.0 s, 17.7 tps, lat 54.813 ms stddev 68.273
progress: 5310.0 s, 1.6 tps, lat 641.034 ms stddev 190.310
progress: 5320.0 s, 10.4 tps, lat 96.625 ms stddev 159.711
progress: 5330.0 s, 11.6 tps, lat 85.768 ms stddev 127.837
progress: 5340.0 s, 13.8 tps, lat 72.795 ms stddev 120.161
progress: 5350.0 s, 16.9 tps, lat 59.142 ms stddev 86.161
progress: 5360.0 s, 1.9 tps, lat 517.983 ms stddev 182.526
---------------------------------------------------------인덱스 생성을 위한 테이블 스캔 (tps 큰 변화 없음 싱글 스레드로 작동함 소요시간 : 약 130분)
progress: 11600.0 s, 24.2 tps, lat 41.210 ms stddev 0.440
progress: 11610.0 s, 24.3 tps, lat 41.242 ms stddev 0.472
progress: 11620.0 s, 24.2 tps, lat 41.311 ms stddev 0.423
progress: 11630.0 s, 24.6 tps, lat 40.657 ms stddev 0.491
progress: 11640.0 s, 24.7 tps, lat 40.535 ms stddev 0.539
progress: 11650.0 s, 24.7 tps, lat 40.482 ms stddev 0.498
progress: 11660.0 s, 24.7 tps, lat 40.397 ms stddev 0.620
--> 백그라운드 인덱스 생성 특성으로 두번의 스캔이 발생하는데 뒤에 발생하는 데이터 정합성 확인을 위한 스캔에서 병렬처리가되지 않아서 드라마틱한 차이는 발생하지 않음
--> 점검간 작업이기 때문에 테이블 LOCK이 발생하는 인덱스 생성으로 진행이 가능하며 그럴 경우 싱글 스레드로 스캔하는 부분이 발생하지 않음
부하를 넣는 상태에서 Validate 작업간 지표 변화
| 스토리지 네트워크 (MB) | TPS (1 호출당 50건 이력) | LATENCY (MS) | |
| 4XLARGE 작업전 | 22 | 25.8 | 39.4~39.7 |
| 4XLARGE 작업중 | 600~670 | 23.8~25.8 | 39.7~41.7 |
| 8XLARGE 작업전 | 22 | 25 | 39~40 |
| 8XLARGE Vadlidate 작업중 | 600-670 | 23.7~25.3 | 39~40 |
| 12XLARGE 부하X | 535~710 | ||
| 12XLARGE Validate 작업전 | 미실시 | - | - |
| 12XLARGE Validate 작업중 | 미실시 | - | - |
부하를 넣는 상태에서 인덱스 생성 작업간 지표 변화
| 작업 단계 | 스토리지 네트워크 | TPS (1 호출당 50건 이력) | 소요 시간 | |
| 8Xlarge 온라인 생성 싱글 스레드 | 테이블 스캔 | ~600 MB | 24.1 ~25.8 | 약 130분 |
| 8Xlarge 온라인 생성 싱글 스레드 | btree 저장 | ~800 MB | 10 ~ 12 | 약 45분 |
| 8Xlarge 온라인 생성 싱글 스레드 | 검증 | ~500 MB | 24.1 ~25.8 | 약 130분 |
| 총 5시간 | ||||
| 8Xlarge 온라인 생성 병렬 스레드 | 테이블 스캔 | ~1.4 GB | 24.1 ~25.8 | 약 45분 |
| 8Xlarge 온라인 생성 병렬 스레드 | 튜플 정렬 | - | 24.1 ~25.8 | 약 35분 |
| 8Xlarge 온라인 생성 병렬 스레드 | btree 저장 | ~800 MB | 10 ~ 12 | 약 45분 |
| 8Xlarge 온라인 생성 병렬 스레드 | 검증 | ~500 MB | 24.1 ~25.8 | 약 130분 |
| 총 4시간 15분 | ||||
| 8Xlarge 병렬 스레드 스레드 | 테이블 스캔 | ~1.4 GB | - | 약 45분 |
| 8Xlarge 병렬 스레드 스레드 | 튜플 정렬 | - | - | 약 30분 |
| 8Xlarge 병렬 스레드 스레드 | btree 저장 | ~700MB | - | 약 50분 |
| 총 2시간 10분 |
성능 지표
8XLARGE 부하 + VALIDATE
8XLARGE 인덱스 생성 + 부하
8XLARGE (병렬 CONCURRENTLY 인덱스 생성 (WORKERS = 4))
8XLARGE (병렬 인덱스 생성 (WORKERS = 4)
(병렬 프로세스 2,4,8,12로 진행하여도 동일 결과나옴 (네트워크 병목의 영향과 정렬, 저장 단계는 싱글 프로세스로 처리되는 영향으로 보임)
작업간 이슈 사항
1. 로컬 스토리지 사용
인덱스 생성시 로컬 스토리지 사용으로 인한 스펙 조정이 필요하였음.
약 100억건, BIGINT + TIMESTAMP 복합 인덱스 생성에서 약 270GB 사용함
8+8 바이트 * 100억 = 149GB 정도 사용할 것으로 생각하였으나 추가적인 메타데이터 등의 저장으로 인하여 저장 공간이 더 필요했음
(유사한 인덱스 사이즈 크기 확인)
데이터 건수 4,780,297,634 사용 컬럼 = BOOLEAN + TIMESTAMP
(1+8) byte * 4,780,297,634 = 40GB로 측정되나 실제 사용 크기는 182GB
(인덱스 내부 저장 정보로 인한 사이즈 증가로 튜플 당 약 28~32바이트 추가 사용되는 것으로 보임)
2. 네트워크 대역폭 초과로 인한 지연
모든 작업에서 지연이 확인되었는데 이는 네트워크 처리량 초과로 인하여 발생하였다.
다만 지표상에 네트워크 처리량, 스토리지 네트워크 처리량을 합하였을 때 인스턴스의 제한보다 적게 나왔는데
이는 오로라의 특성상 EC2에서의 네트워크 사용량을 확인할 수 없었기 때문이었다.
Validate 작업으로 인하여 스토리지에서 인스턴스로는 많은 양의 네트워크 처리량이 발생하였고
오로라의 지표에서는 인스턴스에서 수용한 만큼의 지표만 확인이 되어 처리량이 초과한 것을 케이스 오픈 외에는 확인이 되지 않았다.
즉 스토리지 -> EC2 : 네트워크 대역폭 초과의 사용량 발생
AURORA : 단일 프로세스로 처리되는 명령어이기 때문에 특정 값 정도의 처리량이 유지되어 CloudWatch등의 지표에서는 제한 이하의 수준으로 확인 됨
'공부 > DATABASE' 카테고리의 다른 글
| [Aurora PostgreSQL] Toast 테이블과 VACUUM (0) | 2025.10.08 |
|---|---|
| [ElastiCache] 실시간 알람을 위한 PUB/SUB (0) | 2025.10.08 |
| [AWS] DocumentDB Garbage Collection (0) | 2025.07.05 |
| [PostgreSQL] VACUUM 설정 적용 케이스 (5) | 2025.07.05 |
| [PostgreSQL] 로그 설정 (0) | 2025.06.06 |