들어가며
안녕하세요! 혹시 데이터베이스가 쿼리를 실행할 때 어떻게 길을 찾는지 궁금하신 적 없으셨나요?
만약 옵티마이저가 테이블에 대한 사전 정보가 전혀 없다면, 매번 쿼리를 실행할 때마다 "이 테이블엔 데이터가 얼마나 있지?"라며 정보를 수집하느라 바쁠 거예요.
이런 과정이 반복되면 서비스가 느려지는 원인이 되기도 하죠.
그래서 많은 DB들은 주기적으로 정보를 모아 보관해 두는데, 이걸 바로 통계정보라고 부릅니다.
이번 게시글에서는 PostgreSQL이 어떤 정보를 수집하고 어떻게 관리하는지, 특히 replication 환경에서 Standby 서버는 어떻게 정보를 맞추는지 준비했습니다.
특히 15 버전부터 바뀐 흥미로운 점들을 콕콕 집어 드릴 테니, 각자의 DB 버전에 맞는 꿀팁을 확인해 보세요!
PostgreSQL 통계정보의 두 얼굴
PostgreSQL은 크게 두 가지 데이터를 수집합니다.
1.
시스템 누적 통계정보: 시스템이 얼마나 열심히 일했는지 보여주는 지표예요.
2.
테이블 통계정보: 옵티마이저가 쿼리 실행 계획을 세울 때 참고하는 데이터의 특징들이죠.
1. 시스템 누적 통계정보
쉽게 말해 '시스템 활동 일기'입니다. 디스크 I/O가 얼마나 일어났는지, VACUUM 작업은 잘 되었는지 등을 기록하죠.
track_counts 설정을 통해 켤 수 있고, 여러 파라미터로 상세 조절이 가능합니다.
주요 설정들을 표로 정리해 보았습니다.
파라미터 | 기본값 | 설명 |
track_counts | ON | 데이터베이스 활동에 대한 통계 수집을 활성화 (수퍼유저만 설정 변경 가능)
이 파라미터는 autovacuum 데몬이 수집된 통계정보를 필요로 하기 때문에 "ON"으로 기본 설정
"OFF"일 경우, 테이블 및 인덱스 관련 추적 통계정보가 수집되지 않고, autovacuum이 수행 기준을 참조할 수 없음 |
autovacuum | ON | 서버가 autovacuum 데몬을 실행할지 여부를 제어
autovacuum 데몬은 track_counts과 함께 활성화되어야 auto_vacuum과 auto_analyze를 수행함 |
autovacuum_analyze_threshold | 50 | 테이블에서 변경된 행(삽입, 업데이트, 삭제된 행)의 최소 개수를 지정하여 autoanalyze 수행 기준을 고정할 수 있음
autovacuum_analyze_scale_factor와 함께 계산되어 autoanalyze 임계치가 설정됨
analyze threshold = analyze base threshold + analyze scale factor * number of tuples |
autovacuum_analyze_scale_factor | 0.1 (10%) | 테이블 크기에 비례하여 변경된 행의 비율을 지정하여 autoanalyze 수행 기준을 고정할 수 있음
autovacuum_analyze_threshold와 함께 계산되어 autoanalyze 임계치가 설정됨 |
track_activities | ON | 모든 서버 프로세스에서 현재 실행 중인 쿼리를 활성화 |
track_functions | - | 사용자 정의 함수 사용량 추적을 활성화 |
track_io_timing | OFF | 블록 읽기, 쓰기, 확장 및 fsync 시간 모니터링을 활성화 |
track_wal_io_timing | OFF | WAL 쓰기 및 fsync 시간 모니터링을 활성화 |
stats_temp_directory | pg_stat_tmp | (15 버전 삭제) 통계 임시 데이터를 저장할 디렉토리를 설정함 |
15 버전에서 달라진 점! (중요)예전(14 이하)에는 stats collector라는 전담 프로세스가 지정된 경로(stats_temp_directory)에 임시 파일을 만들며 일했습니다. 하지만 이 방식은 가끔 병목 현상을 만들곤 했죠.
그래서 15 버전부터는 이 프로세스가 사라졌습니다! 대신 각 프로세스가 로컬에서 정보를 모았다가 공유 메모리에 바로 쏴주는 방식으로 바뀌어 훨씬 빨라졌어요.
대신 읽기 일관성을 위해 stats_fetch_consistency라는 새로운 파라미터가 등장했으니 기억해 주세요!
stats_fetch_consistency
값 | 설명 |
none | 통계에 접근할 때마다 공유 메모리의 카운터를 다시 가져옴 |
cache(default) | 통계에 처음 접근할 때 해당 통계가 트랜잭션이 끝날 때까지 저장되며, pg_stat_clear_snapshot() 함수가 실행되지 않는 한 유지됨 |
snapshot | 처음 통계에 접근할 때 현재 데이터베이스에서 사용 가능한 모든 통계가 트랜잭션이 끝날 때까지 캐시되며, pg_stat_clear_snapshot() 함수가 실행되지 않는 한 유지됨 |
2. 테이블 통계정보
우리가 흔히 '통계정보'라고 하면 떠올리는 바로 그 녀석입니다. "NULL 값이 얼마나 있지?", "가장 자주 나오는 값은 뭐지?" 같은 상세 정보를 담고 있죠.
이 정보는 ANALYZE 명령어로 수집됩니다. 사용자가 직접 실행할 수도 있고, autovacuum 데몬이 "데이터가 많이 변했네?" 싶을 때 알아서 수집하기도 하죠.
이렇게 모인 데이터는 pg_statistic이라는 테이블에 물리적으로 저장되고, 우리는 pg_stats 뷰를 통해 편하게 조회할 수 있습니다.
복제(Replication) 환경에선 어떻게 될까요?
"Primary에서 수집한 정보가 Standby에도 잘 넘어갈까?" 궁금하시죠?
•
시스템 누적 통계정보: 이건 복제되지 않습니다. 각 서버가 어떻게 활동했는지 서로 다르니까요. 그래서 두 서버의 pg_stat_* 조회 결과는 서로 다를 수 있습니다.
•
테이블 통계정보: Standby는 읽기 전용이라 직접 ANALYZE를 할 수 없어요. 대신! Primary에서 ANALYZE를 하면 그 변경 사항이 WAL 로그에 기록되어 Standby로 전송됩니다. 덕분에 Standby도 Primady와 동일한 실행 계획을 유지할 수 있는 거죠.
직접 확인해 보는 통계정보 반영 예제
백문이 불여일견! Standby 서버가 어떻게 정보를 받아오는지 살짝 들여다볼까요?
1.
자동 수집 off : set track_counts = off;
2.
데이터 넣기: test_tbl에 100만 건의 데이터를 insert.
3.
확인: 아직 ANALYZE 전이라 pg_statistic을 조회해도 아무것도 안 나옵니다.그리고 옵티마이저는 대량의 데이터를 조회하는 쿼리임에도 엉뚱한 예측치(rows=2000)로 인해 비효율적인 인덱스 스캔 방식을 선택하죠.
4.
수동으로 통계 수집 : Primary에서 ANALYZE test_tbl; 실행! Standby 서버에서는 수행하지 않았습니다.
5.
결과: 짜잔! Standby에서도 이제 100만 건에 가까운 예측치(rows=897518)를 내놓으며 효율적인 플랜을 세우는 걸 볼 수 있습니다.
구분 | Primary | Standby |
1. 자동 수집 off | postgres=# set track_counts = off;
SET
postgres=# show track_counts;
track_counts
--------------
off
(1개 행) | postgres=# set track_counts = off;
SET
postgres=# show track_counts;
track_counts
--------------
off
(1개 행) |
2. 테스트 데이터 생성 | postgres=# create table test_tbl(c1 int, c2 varchar(30));
CREATE TABLE
postgres=# create index test_idx on test_tbl(c1);
CREATE INDEX
postgres=# insert into test_tbl select i, 'text'
postgres-# || i from generate_series(1,1000000) i;
INSERT 0 1000000 | postgres=# \d test_tbl
"public.test_tbl" 테이블
필드명 | 형태 | 정렬규칙 | NULL허용 | 초기값
--------+-----------------------+----------+----------+--------
c1 | integer | | |
c2 | character varying(30) | | |
인덱스들:
"test_idx" btree (c1)
postgres=# select count(*) from test_tbl;
count
---------
1000000
(1개 행) |
3. 통계정보 수집 전
통계정보 조회 및 쿼리 플랜 확인 | -- 통계정보 조회
postgres=# select * from pg_statistic
postgres-# where starelid = (
postgres(# select oid from pg_class
postgres(# where relname = 'test_tbl');
(0개 행)
-- 플랜 확인
postgres=# explain select c2 from test_tbl where c1 between 3000 and 900000;
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on test_tbl (cost=76.92..4029.21 rows=2000 width=78)
Recheck Cond: ((c1 >= 3000) AND (c1 <= 900000))
-> Bitmap Index Scan on test_idx (cost=0.00..76.42 rows=2000 width=0)
Index Cond: ((c1 >= 3000) AND (c1 <= 900000))
(4개 행) | -- 통계정보 조회
postgres=# select * from pg_statistic
postgres-# where starelid = (
postgres(# select oid from pg_class
postgres(# where relname = 'test_tbl');
(0개 행)
-- 플랜 확인
postgres=# explain select c2 from test_tbl where c1 between 3000 and 900000;
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on test_tbl (cost=76.92..4029.21 rows=2000 width=78)
Recheck Cond: ((c1 >= 3000) AND (c1 <= 900000))
-> Bitmap Index Scan on test_idx (cost=0.00..76.42 rows=2000 width=0)
Index Cond: ((c1 >= 3000) AND (c1 <= 900000))
(4개 행) |
4.통계정보 수집 | postgres=# ANALYZE test_tbl;
ANALYZE | -- standby 서버 ANALYZE 불가
postgres=# ANALYZE test_tbl;
ERROR: cannot execute ANALYZE during recovery |
5. 통계정보 수집 후
통계정보 조회 및 쿼리 플랜 확인 | -- 통계정보 조회
postgres=# select * from pg_statistic
postgres-# where starelid = (
postgres(# select oid from pg_class
postgres(# where relname = 'test_tbl');
-[ RECORD 1 ]---------------------------------------------------------------
starelid | 24773
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | -1
stakind1 | 2
......
stacoll5 | 0
stanumbers1 |
stanumbers2 | {1}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {130,10028,20378, ... ,980284,989935,999971}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
-[ RECORD 2 ]---------------------------------------------------------------
starelid | 24773
staattnum | 2
stainherit | f
stanullfrac | 0
stawidth | 10
stadistinct | -1
stakind1 | 2
......
stacoll5 | 0
stanumbers1 |
stanumbers2 | {0.815805}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {text100031,text108509, ... ,text99076,text999971}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
-- 플랜 확인
postgres=# explain select c2 from test_tbl where c1 between 3000 and 900000;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on test_tbl (cost=0.00..20406.00 rows=897518 width=10)
Filter: ((c1 >= 3000) AND (c1 <= 900000))
(2개 행) | -- 통계정보 조회
postgres=# select * from pg_statistic
postgres-# where starelid = (
postgres(# select oid from pg_class
postgres(# where relname = 'test_tbl');
-[ RECORD 1 ]---------------------------------------------------------------
starelid | 24773
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | -1
stakind1 | 2
......
stacoll5 | 0
stanumbers1 |
stanumbers2 | {1}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {130,10028,20378, ... ,980284,989935,999971}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
-[ RECORD 2 ]---------------------------------------------------------------
starelid | 24773
staattnum | 2
stainherit | f
stanullfrac | 0
stawidth | 10
stadistinct | -1
stakind1 | 2
......
stacoll5 | 0
stanumbers1 |
stanumbers2 | {0.815805}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {text100031,text108509, ... ,text99076,text999971}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
-- 플랜 확인
postgres=# explain select c2 from test_tbl where c1 between 3000 and 900000;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on test_tbl (cost=0.00..20406.00 rows=897518 width=10)
Filter: ((c1 >= 3000) AND (c1 <= 900000))
(2개 행) |
통계정보가 전부는 아니랍니다
플랜을 수립하는데 물론 통계정보가 제일 중요하지만, 옵티마이저의 마음을 흔드는 다른 요소들도 있어요.
•
시스템 카탈로그: 인덱스가 갑자기 사라지거나(invalid), 테이블 구조가 바뀌면 플랜도 당연히 바뀝니다.
•
DB 파라미터: work_mem이나 effective_cache_size 같은 메모리 설정, 혹은 parallel 관련 설정들이 "이번엔 해시 조인을 써볼까?" 하고 옵티마이저를 유혹하곤 하죠.
마치며
지금까지 PostgreSQL의 통계정보 수집과 관리법을 살펴보았습니다.
DB 운영자에게 통계정보는 마치 자동차의 내비게이션 데이터와 같습니다. 주기적으로 업데이트해 주지 않으면 엉뚱한 길로 돌아가게 되거든요.
오늘 알려드린 내용이 여러분의 안정적인 DB 운영에 작은 보탬이 되길 바랍니다.
감사합니다!
참고
•
https://www.postgresql.org/docs/current/catalog-pg-statistic.html
•
https://www.postgresql.org/docs/current/view-pg-stats.html
•
https://www.postgresql.org/docs/current/monitoring-stats.html
•
https://www.postgresql.org/docs/current/runtime-config-statistics.htm
•
https://www.postgresql.org/docs/current/runtime-config-query.html
윤현준 프로
오픈소스사업부 오픈소스전환그룹
오픈소스 DBMS 엔지니어로서, 삼성 그룹 계열사 및 대외 금융권 기업들의 DBMS 기술지원 업무를 수행하고 있습니다.