PostgreSQL 성능 튜닝: work_mem의 힘
- Mary-Kate Olsen원래의
- 2024-12-05 03:36:10735검색
요약 보고
몇 년 전 저는 제가 근무하던 회사의 중요한 시스템에서 성능 문제를 해결하는 임무를 받았습니다. 힘든 도전이었고 잠 못 이루는 밤과 더 많은 탈모였습니다. 백엔드는 PostgreSQL을 사용했고 많은 노력과 파고 끝에 해결책은 한 줄처럼 간단하다는 것이 밝혀졌습니다.
ALTER USER foo SET work_mem='32MB';
솔직히 말하면 성능 문제가 바로 해결될 수도 있고 그렇지 않을 수도 있습니다. 쿼리 패턴과 시스템 작업량에 따라 크게 달라집니다. 그러나 백엔드 개발자라면 이 게시물이 특히 PostgreSQL과 관련된 문제를 해결하기 위한 또 다른 도구를 추가하길 바랍니다.
이 게시물에서는 성능 저하를 시뮬레이션하는 시나리오를 만들고 EXPLAIN, 로드 테스트용 k6, 심지어 PostgreSQL의 소스 코드에 대한 심층 분석과 같은 문제를 조사하기 위한 몇 가지 도구를 살펴보겠습니다. 관련 문제를 해결하는 데 도움이 되는 몇 가지 기사도 공유하겠습니다.
- ➡️ 완전한 구현을 갖춘 github 저장소
사례 연구
축구 선수의 경기력을 분석하는 간단한 시스템을 만들어 보겠습니다. 현재 유일한 비즈니스 규칙은 다음 질문에 답하는 것입니다.
- 가장 많은 득점에 관여한 상위 N명의 선수는 누구인가요?
다음 SQL은 데이터 모델을 생성하고 채웁니다.
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
nationality TEXT,
age INT,
position TEXT
);
CREATE TABLE matches (
match_id SERIAL PRIMARY KEY,
match_date DATE,
home_team TEXT,
away_team TEXT
);
CREATE TABLE player_stats (
player_stat_id SERIAL PRIMARY KEY,
player_id INT REFERENCES players(player_id),
match_id INT REFERENCES matches(match_id),
goals INT,
assists INT,
minutes_played INT
);
-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
('Country' || (1 + random()*100)::int), -- 100 different nationalities
(18 + random()*20)::int, -- Ages between 18 and 38
(ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);
데이터베이스를 초기화하고 채우는 스크립트는 github 저장소에서 사용할 수 있습니다.
예, 시스템 성능을 향상시키기 위해 데이터베이스를 설계할 수 있지만 여기서 주요 목표는 최적화되지 않은 시나리오를 탐색하는 것입니다. 저를 믿으세요. 잘못된 초기 설계 선택이나 예상치 못한 성장으로 인해 성능 개선을 위해 상당한 노력이 필요한 이와 같은 시스템을 접하게 될 것입니다.
문제 디버깅
work_mem 구성과 관련된 문제를 시뮬레이션하기 위해 다음 질문에 대답하는 쿼리를 만들어 보겠습니다. 목표에 가장 많이 기여한 상위 2000명의 플레이어는 누구입니까?
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
그렇습니다. 그런데 이 쿼리에서 병목 현상을 어떻게 식별할 수 있나요? 다른 DBMS와 마찬가지로 PostgreSQL은 EXPLAIN 명령을 지원합니다. 이는 쿼리 플래너가 실행하는 각 단계(최적화 여부)를 이해하는 데 도움이 됩니다.
다음과 같은 세부정보를 분석할 수 있습니다.
- 어떤 종류의 스캔이 사용되었나요? Index 스캔, Index Only 스캔, Seq 스캔 등
- 어떤 인덱스가, 어떤 조건에서 사용됐나요?
- 정렬이 포함된 경우 어떤 유형의 알고리즘이 사용되었나요? 전적으로 메모리에 의존합니까, 아니면 디스크 사용량이 필요합니까?
- 공유 버퍼의 사용.
- 실행 시간 추정.
여기에서 PostgreSQL 플래너/최적화 프로그램에 대해 자세히 알아볼 수 있습니다.
- 공식 문서
- pganalyze - Postgres 쿼리 계획의 기본
- cybertec - postgresql 해석 방법 설명
말은 싸다
토크는 비용이 저렴하므로 실제 사례를 살펴보겠습니다. 먼저 소스 코드에 정의된 대로 work_mem을 가능한 가장 작은 값인 64kB로 줄입니다.
ALTER USER foo SET work_mem='32MB';
다음으로 EXPLAIN 명령의 출력을 분석해 보겠습니다.
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
nationality TEXT,
age INT,
position TEXT
);
CREATE TABLE matches (
match_id SERIAL PRIMARY KEY,
match_date DATE,
home_team TEXT,
away_team TEXT
);
CREATE TABLE player_stats (
player_stat_id SERIAL PRIMARY KEY,
player_id INT REFERENCES players(player_id),
match_id INT REFERENCES matches(match_id),
goals INT,
assists INT,
minutes_played INT
);
-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
('Country' || (1 + random()*100)::int), -- 100 different nationalities
(18 + random()*20)::int, -- Ages between 18 and 38
(ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);
실행 시간은 82.718ms이고, 사용한 정렬 알고리즘은 외부 병합을 사용한 것을 알 수 있습니다. 이 알고리즘은 데이터가 64kB work_mem 제한을 초과했기 때문에 메모리 대신 디스크에 의존합니다.
참고로 tuplesort.c 모듈은 이 줄에서 상태를 SORTEDONTAPE로 설정하여 정렬 알고리즘이 디스크를 사용할 때 플래그를 지정합니다. 디스크 상호작용은 logtape.c 모듈에 의해 처리됩니다.
나처럼 시각적인 사람이라면 https://explain.dalibo.com/과 같이 EXPLAIN 출력을 이해하는 데 도움이 되는 도구가 있습니다. 다음은 정렬 방법: 외부 병합 및 사용된 정렬 공간: 2.2MB:
과 같은 세부 정보를 포함하여 정렬 단계가 있는 노드를 보여주는 예입니다.
'통계' 섹션은 각 쿼리 노드에 대한 실행 시간 세부 정보를 제공하므로 더욱 복잡한 쿼리를 분석하는 데 특히 유용합니다. 이 예에서는 Sort 노드 중 하나에서 의심스러울 정도로 높은 실행 시간(거의 42ms)을 강조합니다.
- 여기에서 이 쿼리 계획을 시각화하고 분석할 수 있습니다: https://explain.dalibo.com/plan/2gd0a8c8fab6a532#stats
EXPLAIN 출력에서 볼 수 있듯이 성능 문제의 주요 원인 중 하나는 디스크를 사용하는 Sort 노드입니다. 이 문제의 부작용은 특히 워크로드가 높은 시스템에서 쓰기 I/O 측정항목의 급증입니다(이를 모니터링하고 있기를 바랍니다. 그렇지 않은 경우 필요할 때 행운을 빕니다!). 그리고 그렇습니다. Sort 알고리즘이 임시 파일에 데이터를 쓰기 때문에 읽기 전용 쿼리라도 쓰기 급증이 발생할 수 있습니다.
해결책
work_mem=4MB(PostgreSQL의 기본값)로 동일한 쿼리를 실행하면 실행 시간이 50% 이상 단축됩니다.
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
- 시각적 분석을 보려면 다음 링크를 확인하세요: https://explain.dalibo.com/plan/b094ec2f1cfg44f6#
이 EXPLAIN 출력에서 Sort 노드 중 하나는 이제 인메모리 알고리즘인 heapsort를 사용합니다. 맥락상 플래너는 퀵 정렬보다 실행 비용이 더 저렴한 경우에만 힙 정렬을 선택합니다. PostgreSQL 소스 코드에서 의사결정 프로세스에 대해 더 자세히 알아볼 수 있습니다.
또한 이전에 거의 40ms의 실행 시간을 차지했던 두 번째 Sort 노드가 실행 계획에서 완전히 사라집니다. 이러한 변경은 해시 작업이 메모리에 적합하여 약 480kB를 소비하므로 이제 플래너가 MergeJoin 대신 HashJoin을 선택하기 때문에 발생합니다.
조인 알고리즘에 대한 자세한 내용은 다음 문서를 확인하세요.
- HashJoin 알고리즘
- MergeJoin 알고리즘
API에 미치는 영향
기본 work_mem이 시스템 작업 부하를 처리하기에 항상 충분하지는 않습니다. 다음을 사용하여 사용자 수준에서 이 값을 조정할 수 있습니다.
ALTER USER foo SET work_mem='32MB';
참고: 연결 풀이나 연결 풀러를 사용하는 경우 새 구성을 적용하려면 이전 세션을 재활용하는 것이 중요합니다.
데이터베이스 트랜잭션 수준에서 이 구성을 제어할 수도 있습니다. k6의 로드 테스트를 사용하여 work_mem 변경의 영향을 이해하고 측정하기 위해 간단한 API를 실행해 보겠습니다.
-
k6-test.js
CREATE TABLE players ( player_id SERIAL PRIMARY KEY, nationality TEXT, age INT, position TEXT ); CREATE TABLE matches ( match_id SERIAL PRIMARY KEY, match_date DATE, home_team TEXT, away_team TEXT ); CREATE TABLE player_stats ( player_stat_id SERIAL PRIMARY KEY, player_id INT REFERENCES players(player_id), match_id INT REFERENCES matches(match_id), goals INT, assists INT, minutes_played INT ); -- Populate players with a range of nationalities, ages, and positions INSERT INTO players (nationality, age, position) SELECT ('Country' || (1 + random()*100)::int), -- 100 different nationalities (18 + random()*20)::int, -- Ages between 18 and 38 (ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int] FROM generate_series(1, 10000);
API는 Go에서 구현되었으며 서로 다른 work_mem 구성으로 쿼리를 실행하는 두 개의 엔드포인트를 노출합니다.
-
main.go
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
아래는 부하 테스트를 실행하는 데 필요한 모든 종속성이 포함된 docker-compose 파일입니다.
-
docker-compose.yaml
/* * workMem is forced to be at least 64KB, the current minimum valid value * for the work_mem GUC. This is a defense against parallel sort callers * that divide out memory among many workers in a way that leaves each * with very little memory. */ state->allowedMem = Max(workMem, 64) * (int64) 1024;
ENDPOINT 환경 변수를 설정하여 테스트할 시나리오를 정의할 수 있습니다: /low-work-mem 또는 /optimized-work-mem. docker compose up --abort-on-container-exit를 사용하여 테스트를 실행합니다. 이 예에서는 Docker 버전 20.10.22를 사용했습니다.
-
테스트 끝점: /low-work-mem - work_mem=64kB
BEGIN; -- 1. Initialize a transaction. SET LOCAL work_mem = '64kB'; -- 2. Change work_mem at transaction level, another running transactions at the same session will have the default value(4MB). SHOW work_mem; -- 3. Check the modified work_mem value. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -- 4. Run explain with options that help us to analyses and indetifies bottlenecks. SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps INNER JOIN players p ON p.player_id = ps.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000; -- QUERY PLAN | --------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Limit (cost=18978.96..18983.96 rows=2000 width=12) (actual time=81.589..81.840 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Buffers: shared hit=667, temp read=860 written=977 | -> Sort (cost=18978.96..19003.96 rows=10000 width=12) (actual time=81.587..81.724 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Sort Key: (sum((ps.goals + ps.assists))) DESC | Sort Method: external merge Disk: 280kB | Buffers: shared hit=667, temp read=860 written=977 | -> GroupAggregate (cost=15076.66..17971.58 rows=10000 width=12) (actual time=40.293..79.264 rows=9998 loops=1) | Output: p.player_id, sum((ps.goals + ps.assists)) | Group Key: p.player_id | Buffers: shared hit=667, temp read=816 written=900 | -> Merge Join (cost=15076.66..17121.58 rows=100000 width=12) (actual time=40.281..71.313 rows=100000 loops=1) | Output: p.player_id, ps.goals, ps.assists | Merge Cond: (p.player_id = ps.player_id) | Buffers: shared hit=667, temp read=816 written=900 | -> Index Only Scan using players_pkey on public.players p (cost=0.29..270.29 rows=10000 width=4) (actual time=0.025..1.014 rows=10000 loops=1)| Output: p.player_id | Heap Fetches: 0 | Buffers: shared hit=30 | -> Materialize (cost=15076.32..15576.32 rows=100000 width=12) (actual time=40.250..57.942 rows=100000 loops=1) | Output: ps.goals, ps.assists, ps.player_id | Buffers: shared hit=637, temp read=816 written=900 | -> Sort (cost=15076.32..15326.32 rows=100000 width=12) (actual time=40.247..49.339 rows=100000 loops=1) | Output: ps.goals, ps.assists, ps.player_id | Sort Key: ps.player_id | Sort Method: external merge Disk: 2208kB | Buffers: shared hit=637, temp read=816 written=900 | -> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.011..8.378 rows=100000 loops=1) | Output: ps.goals, ps.assists, ps.player_id | Buffers: shared hit=637 | Planning: | Buffers: shared hit=6 | Planning Time: 0.309 ms | Execution Time: 82.718 ms | COMMIT; -- 5. You can also execute a ROLLBACK, in case you want to analyze queries like INSERT, UPDATE and DELETE. -
테스트 끝점: /optimized-work-mem - work_mem=4MB
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps INNER JOIN players p ON p.player_id = ps.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000; -- QUERY PLAN | ----------------------------------------------------------------------------------------------------------------------------------------------------+ Limit (cost=3646.90..3651.90 rows=2000 width=12) (actual time=41.672..41.871 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Buffers: shared hit=711 | -> Sort (cost=3646.90..3671.90 rows=10000 width=12) (actual time=41.670..41.758 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Sort Key: (sum((ps.goals + ps.assists))) DESC | Sort Method: top-N heapsort Memory: 227kB | Buffers: shared hit=711 | -> HashAggregate (cost=2948.61..3048.61 rows=10000 width=12) (actual time=38.760..40.073 rows=9998 loops=1) | Output: p.player_id, sum((ps.goals + ps.assists)) | Group Key: p.player_id | Batches: 1 Memory Usage: 1169kB | Buffers: shared hit=711 | -> Hash Join (cost=299.00..2198.61 rows=100000 width=12) (actual time=2.322..24.273 rows=100000 loops=1) | Output: p.player_id, ps.goals, ps.assists | Inner Unique: true | Hash Cond: (ps.player_id = p.player_id) | Buffers: shared hit=711 | -> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.008..4.831 rows=100000 loops=1)| Output: ps.player_stat_id, ps.player_id, ps.match_id, ps.goals, ps.assists, ps.minutes_played | Buffers: shared hit=637 | -> Hash (cost=174.00..174.00 rows=10000 width=4) (actual time=2.298..2.299 rows=10000 loops=1) | Output: p.player_id | Buckets: 16384 Batches: 1 Memory Usage: 480kB | Buffers: shared hit=74 | -> Seq Scan on public.players p (cost=0.00..174.00 rows=10000 width=4) (actual time=0.004..0.944 rows=10000 loops=1) | Output: p.player_id | Buffers: shared hit=74 | Planning: | Buffers: shared hit=6 | Planning Time: 0.236 ms | Execution Time: 41.998 ms |
결과는 work_mem이 높은 엔드포인트가 구성이 낮은 엔드포인트보다 성능이 우수하다는 것을 보여줍니다. p90 대기 시간은 43ms 이상 감소했으며 테스트 작업 부하에서 처리량이 크게 향상되었습니다.
백분위수 측정항목이 처음이라면 학습하고 이해하는 것이 좋습니다. 이러한 지표는 성능 분석을 안내하는 데 매우 유용합니다. 시작하는 데 도움이 되는 몇 가지 리소스는 다음과 같습니다.
- k6 응답 시간
- p90 대 p99
결론
문제에 대한 꿈을 꾸고, 여러 번 깨어나 새로운 솔루션을 시도하고, 마침내 work_mem이 도움이 될 수 있다는 것을 발견한 후, 다음 과제는 이 구성에 적합한 값을 찾는 것입니다. ?
다른 많은 PostgreSQL 설정과 마찬가지로 work_mem의 기본값인 4MB는 보수적입니다. 이를 통해 PostgreSQL은 제한된 계산 능력을 갖춘 더 작은 시스템에서 실행될 수 있습니다. 그러나 메모리 부족 오류로 인해 PostgreSQL 인스턴스가 충돌하지 않도록 주의해야 합니다. 단일 쿼리가 충분히 복잡할 경우 정렬, 병합 조인, 해시 조인(hash_mem_multiplier의 영향을 받음)과 같은 작업 수에 따라 work_mem에서 지정한 메모리의 여러 배를 소비할 수 있습니다. 그리고 더. 공식 문서에 명시된 바와 같이:
값을 선택할 때 이 사실을 명심해야 합니다. 정렬 작업은 ORDER BY, DISTINCT 및 병합 조인에 사용됩니다. 해시 테이블은 해시 조인, 해시 기반 집계, 메모화 노드 및 IN 하위 쿼리의 해시 기반 처리에 사용됩니다.
안타깝게도 work_mem을 설정하는 마법의 공식은 없습니다. 이는 시스템의 사용 가능한 메모리, 작업 부하 및 쿼리 패턴에 따라 다릅니다. TimescaleDB 팀에는 자동 조정 도구가 있으며 이 주제는 널리 논의됩니다. 다음은 여러분을 안내할 몇 가지 훌륭한 자료입니다.
- work_mem에 대해 당신이 알고 있는 모든 것이 잘못되었습니다
- 특정 시스템에 맞게 work_mem을 어떻게 조정해야 하나요?
하지만 결국 IMHO의 대답은 TEST입니다. 오늘 테스트하세요. 내일 테스트하세요. 영원히 테스트하세요. 데이터베이스를 폭파하지 않고 쿼리 성능을 향상시키는 사용 사례에 적합한 값을 찾을 때까지 계속 테스트하세요. ?
위 내용은 PostgreSQL 성능 튜닝: work_mem의 힘의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!