시작하며
SQL 튜닝보다 중요한 작업은 튜닝대상을 식별하는 일이라고 생각합니다.
DBMS별로 제공되는 정보의 한계를 명확히 알아야 하며, 여기저기 산재해 있는 정보들을 조합하여 온전한 결론에 도달해야 하기 때문입니다. 자칫 부정확한 판단으로 애꿎은 SQL을 선택했다면, 이는 안 하느니만 못한 결과를 초래할 수도 있으므로, 그 중요성은 이루 다 말할 수 없습니다.
PostgreSQL에서는 튜닝대상 선정을 위해 PG_STAT_STATEMENTS라는 Extension을 많이 사용합니다. 해당 Extension은 매우 범용적이지만 제공되는 정보에는 다소 아쉬운 부분들이 존재합니다.
본 문서에서는 PG_STAT_STATEMENTS를 확장한 Percona의 PG_STAT_MONITOR Extension에 대해 알아보고, 튜닝대상을 선정하거나 SQL성능을 모니터링하기 위한 활용 방안에 대해 알아보도록 하겠습니다.
📢 본 문서에서는 PostgreSQL 15 버전을 기준으로 작성되었습니다. 따라서 이하 버전에서는 일부 기능을 제공하지 않을 수 있으며 PG_STAT_MONITOR View에 일부 컬이 없을 수 있습니다. 또한 Parameter의 기본값이 다를 수 있습니다.
PG_STAT_MONITOR란 무엇인가?
Percona의 PG_STAT_MONITOR Extension은 PG_STAT_STATEMENTS를 기반으로, 보다 향상된 성능 정보를 제공하고자 만들어진 SQL 성능 모니터링 도구입니다. 수집된 SQL 성능 정보는 동명의 단일 View를 통해 확인 가능한데, SQL 원본, Execution Plan, 성능 및 기타 Meta Data들을 확인 가능합니다.
PG_STAT_MONITOR는 PG_STAT_STATEMENTS와 달리 SQL 별로 누적된 정보를 제공하는데 그치지 않고, Time Bucket이라 불리는 구간 정보를 통해 시간 별 SQL 성능 정보를 표기합니다. 이러한 특징으로 인해 사용자는 보다 더 면밀하게 SQL에 대한 분석이 가능합니다. PG_STAT_MONITOR의 주요 특징 및 제약사항은 다음과 같습니다.
PG_STAT_MONITOR 특징
- Time Buckets : PG_STAT_MONITOR는 구성된 시간 간격에 대한 통계 정보를 저장합니다.
- SQL별 테이블 및 인덱스 Access 통계 : 특정 테이블에 Access 하는 모든 SQL을 쉽게 식별할 수 있습니다.
- Client 정보 : SQL을 수행한 사용자 이름(
username), Application 이름(application_name), Client IP(client_ip)와 같은 Client의 세부 연결 정보를 확인할 수 있습니다.
- SQL 실행 계획 : SQL의 실행 계획을 확인할 수 있습니다.(
query_plan)
- SQL 유형 별 모니터링 : SELECT, INSERT, UPDATE, DELETE 별로 SQL 모니터링이 가능합니다.(
cmd_type,cmd_type_test)
- Top SQL 추적 : Function이나 Procedure 내부에서 수행되는 SQL에 대한 추적이 가능합니다.
- SQL 오류 분석 : ERROR, WARNING, LOG 상태로 종료된 SQL의 문제를 확인할 수 있습니다.(
elevel,sqlcode,message)
- Histrogram : 수행 시간구간 별 수행 횟수 분포 정보를 Histogram형식으로 제공하여 보다 면밀한 분석이 가능합니다.
PG_STAT_MONITOR 제약
- PG_STAT_MONITOR은 PostgreSQL 11 버전부터 사용할 수 있습니다.
- PostgreSQL 엔진 설치 시 기본으로 포함되지 않는 Extension으로 별도로 설치를 해야 하며, 이러한 이유로 RDS와 같은 관리형 Database에서는 사용이 불가합니다.
Extension Setup
테스트를 위한 환경은 다음과 같으며, PG_STAT_MONITOR Extension은 인터넷이 가능한 환경에서 Package Manager(yum)를 통해 Database 엔진과 동일한 버전(15)으로 설치합니다.
- CentOS 7.9
- PostgreSQL 15
📢 yum Repository로는 Percona Repository, PostgreSQL PGDG Repository, PGXN, github 등 여러 Repository를 사용할 수 있으며, 각 Repository에서 PG_STAT_MONITOR Package의 이름을 확인한 후 설치 하면 됩니다.
1. PG_STAT_MONITOR 다운로드 및 설치
2. Configuration(postgresql.conf) 변경 후 재기동
3. PG_STAT_MONITOR Extension 생성
📢 테스트 환경에서는 postgres Database에 PG_STAT_MONITOR Extension을 생성하였습니다. Database가 여러 개인 경우 모든 Database에 대해 Extension을 생성해야 각 Database의 통계 정보를 수집합니다.
PG_STAT_MONITOR 활용
1. Time Grouping
미리 설정된 수집 기간(시간) 별로 SQL을 Summary 하여 성능 정보를 확인하는 것은 매우 유용한 기능이지만, 아쉽게도 해당 정보를 제공하는 DBMS는 거의 없습니다. PG_STAT_MONITOR는 순환 구조로 사용되는 Time Bucket의 개수와, Time Bucket 별 보관 주기(시간)를 설정 가능한데, 이를 통해 Oracle AWR과 유사하게 기간별 SQL 성능정보를 확인 가능합니다.
Time Bucket의 수는
PG_STAT_MONITOR.PGSM_MAX_BUCKETS Parameter로 설정 가능하며 기본값은 10입니다(최대 20,000까지 설정가능). 또한 각 Time Bucket이 표현하는 기간은 PG_STAT_MONITOR.PGSM_BUCKET_TIME Parameter로 설정 가능하며 기본값은 60 Sec입니다(최대 2,147,483,647 설정가능).이를 활용하면 사용자가 원하는 기간의 SQL 성능 Data를 PG_STAT_STATEMENTS 형태로 확인할 수 있으며, 누적 Data가 아닌 시점 Data들의 합으로 계산되는 만큼 PG_STAT_STATEMENTS에 비해 정확도가 높다는 장점이 있습니다. (Script 1 참조)
또한
bucket_start_time 컬럼을 활용하면 사용자가 원하는 주기(30분, 1시간 등) 별 SQL의 성능 변화도 확인 가능합니다. (Script 2 참조)Script 1. 특정 기간 동안의 Top SQL
Script 2. 특정 SQL의 시간 별 수행정보
2. Histogram
PG_STAT_STATEMENTS에서 제공하는 수행시간 정보로는 total, max, min, mean, stddev 등 타 DBMS에 비해 비교적 다양한 편에 속합니다. 하지만, PG_STAT_MONITOR는 추가적으로
resp_calls라는 Array Type 컬럼을 통해 수행시간의 구간 별 수행 횟수를 Histogram방식으로 제공하여 보다 면밀한 분석이 가능합니다.PG_STAT_MONITOR.PGSM_HISTOGRAM_MIN = 1
PG_STAT_MONITOR.PGSM_HISTOGRAM_MAX = 100000
PG_STAT_MONITOR.PGSM_HISTOGRAM_BUCKETS = 20
Histogram 관련 기본 설정은 위와 같으며, 이는 1 ~ 100000ms (10sec) 구간을 20개의 Bucket으로 나누었음을 의미합니다. Histogram Bucket의 구간(Range)은 일정하게 나누어지지 않는데, 각 Histogram Bucket의 Range는 range() 함수나 histogram() 함수를 통해 확인이 가능합니다.
Test SQL 수행
SQL 통계 정보 확인
Histogram Bucket의 Range 확인
📢 Bucket이라는 단어가 중복사용되어 혼란스러울 수 있는데, 앞서 이야기한
PG_STAT_MONITOR.PGSM_MAX_BUCKETS에서 사용된 Bucket이란 PG_STAT_MONITOR의 Snapshot 구간으로서 사용되며, 본 단락에서 사용된 Bucket은 resp_call의 Histogram을 구성하는 구간정보를 의미합니다.3. Top Level Tracing
PG_STAT_MONITOR에서는 Procedure나 Function 내부에서 수행되는 SQL에 대한 추적이 가능하며
PG_STAT_MONITOR.PGSM_TRACKParameter를 통해 제어됩니다.해당 Parameter의 기본값은
TOP으로, 기본적으로 최상위 Level의 SQL만 수집하지만, ALL로 변경 시 Recursive SQL들에 대한 추적이 가능합니다. 이 경우toplevel 컬럼을 통해 Top Level SQL 여부를 알 수 있으며, Recursive SQL이라면 top_queryid, top_query를 통해 호출 SQL을 추적할 수 있습니다.해당 기능을 활용하면 ①Function 내부에서 지연되는 SQL을 특정하거나, ②지연되는 SQL을 호출한 SP(Stored Procedure)에 대한 역추적이 용이합니다.
Test Function 생성
PG_STAT_MONITOR.PGSM_TRACK Parameter 변경
Test Function 수행
PG_STAT_MONITOR에서 Top Level Query 확인
📢
PG_STAT_MONITOR.PGSM_BUCKET_TIME Parameter 값에 따라(기본값 : 1min) Bucket이 유지되므로 여러 번 SQL 수행 시 Bucket이 달라 여러 Row로 표시될 수 있습니다.📢 Function 및 Procedure는 수행 완료 시점의 Bucket 번호로 저장됩니다. (ex. Function 수행 시 Bucket = 3, 수행 완료 시 Bucket = 5 일 경우 Function 수행 SQL은 Bucket = 5에 기록)
📢 SP(Stored Procedure)가 아닌 Anonymous Block으로 수행된 경우, Top Query를 추적하지 못합니다.
4. Object Access
PG_STAT_MONITOR의 유용한 컬럼 중 하나로 SQL에 사용된 Relation들을 Array 형태로 관리하는
relations 컬럼을 뽑을 수 있습니다.해당 정보를 이용하면 SQL Text에 대한 like 검색 없이 사용자가 지정한 Relation을 Access 한 SQL정보를 추출할 수 있으므로 매우 유용하게 사용 가능합니다.
테스트 테이블 생성
테스트 SQL 수행
PG_STAT_MONITOR에서 Object Access 확인
또한, 해당 컬럼과
cmd_type_text, calls 컬럼을 조합할 경우 ① DML발생량이나 ② 테이블 별 CRUD Pattern 등 다양한 활용이 가능합니다.① DML 발생량 확인
② 테이블 별 CRUD Pattern 확인
5. SQL Nomalize
PG_STAT_MONITOR의 마지막 특징은 SQL에 사용된 상수값을 정규화(Nomalize)해서 저장한다는 것입니다.
사실 SQL 정규화는 민감할 수 있는 상수 정보를 변수처리하기 때문에 보안적으로 우수한 측면이 있고, 유사 SQL들을 통합해 볼 수 있다는 장점이 있습니다.
하지만 정규화된 SQL은 PG_STAT_ACTIVITY(Session Level)의 Query와 매칭이 불가하고(14 이전 버전), 사용된 값에 따른 성능 편차가 큰 경우, 이를 추적할 방안이 전무하다는 문제점도 있습니다.
PG_STAT_MONITOR의 경우 SQL 정규화 여부를 사용자가 선택할 수 있으며,
PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY Parameter로 조정 가능합니다.(기본값은 NO)PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY Parameter 변경
PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY = ‘NO’ 일 때 Query Text Sample
PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY = ‘YES’ 일 때 Query Text Sample
📢 단, 수행된 Query가 Application 내에서 이미 변수처리 되어 수행되는 경우라면, 설정에 상관없이 $n 형태로 표시됩니다.
참고 사항
PG_STAT_MONITOR Prameter
Extension 설치 후 postgresql.conf파일에 다음과 같은 Parameter들을 설정할 수 있습니다.
또한 재시작이 필요한 Parameter가 아니라면
ALTER SYSTEM으로 변경, pg_reload_conf()를 호출하여 적용 가능합니다.Parameter Name | Default | Desc | Restart |
pg_stat_monitor.pgsm_max | 256 | PG_STAT_MONITOR 사용 공유 메모리의 최대크기(MB) | yes |
pg_stat_monitor.pgsm_query_max_len | 2048 | SQL의 최대 길이 설정 | yes |
pg_stat_monitor.pgsm_track_utility | yes | SELECT, DML 제외한 명령문을 추적할지 여부 | no |
pg_stat_monitor.pgsm_normalized_query | no | Selects whether save query in normalized format. | no |
pg_stat_monitor.pgsm_max_buckets | 10 | Bucket의 최대 수 설정 | yes |
pg_stat_monitor.pgsm_bucket_time | 60 | Bucket의 유지시간(수명) 설정 | yes |
pg_stat_monitor.pgsm_histogram_min | 1 | Histogram 출력에 표시되는 SQL의 최소 실행 시간(ms) | yes |
pg_stat_monitor.pgsm_histogram_max | 100000 | Histogram 출력에 표시되는 SQL의 최대 실행 시간(ms) | yes |
pg_stat_monitor.pgsm_histogram_buckets | 20 | Histogram Bucket의 최대 수 | yes |
pg_stat_monitor.pgsm_query_shared_buffer | 20 | SQL 추적에 사용되는 공유 메모리의 최대크기(MB) | yes |
pg_stat_monitor.pgsm_enable_overflow | on | 공유 메모리를 넘어 Swap으로 확장 할것인지를 제어 | yes |
pg_stat_monitor.pgsm_overflow_target | 1 | 오버플로우 대상을 설정. 2.0.0 버전부터 사용하지 않음(현재 2.0.1) | yes |
pg_stat_monitor.pgsm_enable_pgsm_query_id | on | pgsm_query_id를 사용할지 여부 | yes |
pg_stat_monitor.pgsm_enable_query_plan | no | SQL 실행계획을 모니터링할지 여부(사용할 경우 Database 성능에 영향을 미칠 수 있음.) | no |
pg_stat_monitor.pgsm_track | top | 명령문을 추적할 대상을 지정(top, all, none) | no |
pg_stat_monitor.pgsm_extract_comments | no | SQL에서 주석(comments) 추출의 활성화 여부 | no |
pg_stat_monitor.pgsm_track_planning | no | 실행계획 통계를 추적할지 여부 | yes |
PG_STAT_MONITOR View
PG_STAT_MONITORColumn | PG_STAT_STATEMENTSExist | Desc |
bucket | 조회되는 레코드가 속한 Bucket 번호 | |
bucket_start_time | Bucket의 시작시간 | |
userid | O | SQL을 수행한 사용자 ID |
username | SQL을 수행한 사용자 이름 | |
dbid | O | SQL이 실행된 Database ID |
datname | SQL이 실행된 Database 이름 | |
client_ip | SQL을 수행한 Client IP | |
pgsm_query_id | PG_STAT_MONITOR Query ID. 정규화된 SQL을 기준으로 해시 계산한 값.pg_stat_monitor.pgsm_enable_pgsm_query_id Parameter로 제어 | |
queryid | O | SQL ID |
toplevel | O | Top Level SQL 여부 표시(TRUE는 최상위 Level로 수행됨을 의미) |
top_queryid | 상위 SQL의 Query ID를 표시. 기본적으로 최상위 Level의 SQL에 대해서만 수집. Recursive SQL에 대한 추적을 위해서는 Parameter 변경 필요. (pg_stat_monitor.pgsm_track = ALL) | |
query | O | 수행된 SQL 문장 |
comments | SQL에 대한 설명 | |
planid | SQL 실행 계획 ID | |
query_plan | SQL 실행 계획. 단순 실행 계획만 표시. 기본은 수집하지 않으며 Parameter 변경 필요.(pg_stat_monitor.pgsm_enable_query_plan = yes) | |
top_query | 상위 SQL 문장을 표시 | |
application_name | SQL을 수행한 Application 이름 | |
relations | SQL에서 참조한 테이블 목 (schema.table_name) | |
cmd_type | 실행된 SQL 유형 ID(1:SELECT, 2:UPDATE, 3:INSERT, 4:DELETE) | |
cmd_type_text | 실행된 SQL 유형 | |
elevel | SQL 오류 수준 기록(WARNING, ERROR, LOG) | |
sqlcode | SQL 오류 코드 기록(https://www.postgresql.org/docs/current/errcodes-appendix.html 에서 오류 코드내용 확인 가능) | |
message | SQL 오류 메시지 기록 | |
calls | O | SQL이 실행된 횟수 |
total_exec_time | O | SQL 실행에 소요된 총 시간(ms) |
min_exec_time | O | SQL 실행에 소요된 최소 시간(ms) |
max_exec_time | O | SQL 실행에 소요된 최대 시간(ms) |
mean_exec_time | O | SQL 실행에 소요된 평균 시간(ms) |
stddev_exec_time | O | SQL 실행에 소요된 시간의 표준편차(ms) |
rows | O | SQL 실행 시 검색된 총 Row |
shared_blks_hit | O | Cache에서 반환된 공유 메모리 블록의 총 수 |
shared_blks_read | O | Cache에서 반환되지 않은 공유 메모리 블록의 총 수 |
shared_blks_dirtied | O | SQL 실행으로 인해 “dirtied”된 공유메모리 블록의 수 |
shared_blks_written | O | SQL 실행 중 드라이브에 동시에 기록된 공유메모리 블록 수 |
local_blks_hit | O | The number of blocks which are considered as local by the backend and thus are used for temporary tables |
local_blks_read | O | SQL 실행 중 읽은 총 Local 블록 수 |
local_blks_dirtied | O | SQL 실행으로인해 “dirtied”된 Local 블록의 수 |
local_blks_written | O | SQL 실행 중 드라이브에 동시에 기록된 Local 블록 수 |
temp_blks_read | O | 드라이브에서 읽은 Temp File의 총 블록 수 |
temp_blks_written | O | 드라이브에 기록된 Temp File의 총 블록 수 |
blk_read_time | O | 블록 읽기에 대한 총 대기 시간(ms) |
blk_write_time | O | 블록 쓰기에 대한 총 대기 시간(ms) |
temp_blk_read_time | O | 명령문이 읽은 총 Temp 블록 수 |
temp_blk_write_time | O | 명령문에 의해 기록된 총 Temp 블록 수 |
resp_calls | Histogram 호출 | |
cpu_user_time | SQL을 실행하는데 소비한 CPU 시간(ms) | |
cpu_sys_time | 커널 코드를 실행하는데 소비한 CPU 시간(ms) | |
wal_records | O | SQL에 의해 생성된 WAL의 총 개수 |
wal_fpi | O | SQL로 생성된 WAL FPI(Full Page Images)의 총 개수 |
wal_bytes | O | SQL에 의해 사용한 WAL의 크기(Byte) |
bucket_done | Bucket의 상태- false : 활성화. Bucket에 통계정보가 추가될 수 있음.- true : 완료. Bucket에 통계정보가 추가되지 않음. | |
plans | O | 명령문을 계획한 횟수 |
total_plan_time | O | 명령문을 계획하는데 소요된 총 시간(ms) |
min_plan_time | O | 명령문을 계획하는데 소요된 최소 시간(ms) |
max_plan_time | O | 명령문을 계획하는데 소요된 최대 시간(ms) |
mean_plan_time | O | 명령문을 계획하는데 소요된 평균 시간(ms) |
stddev_plan_time | O | 명령문을 계획하는데 소요된 시간의 표준편차(ms) |
jit_functions | O | 명령문에 의해 JIT 컴파일된 총 함수 수 |
jit_generation_time | O | JIT 코드 생성에 명령문이 소비한 총 시간(ms) |
jit_inlining_count | O | 함수가 인라인 된 횟수 |
jit_inlining_time | O | 명령문에서 인라인 함수에 대한 총 소요시간(ms) |
jit_optimization_count | O | 명령문이 최적화된 횟수 |
jit_optimization_time | O | 명령문 최적화에 대한 총 소요시간(ms) |
jit_emission_count | O | 코드를 내보낸 횟수 |
jit_emission_time | O | 명령문이 코드를 내보내는데 소요된 총 시간(ms) |
함께 보면 좋은 아티클
