본문 바로가기
엑셈 경쟁력/DB 인사이드

DB 인사이드 | PostgreSQL Extension - PG_STAT_MONITOR

by EXEM 2023. 9. 21.

 

 

시작하며

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의 이름을 확인한 후 설치 하면 됩니다.
본 문서에서는 PostgreSQL PGDG Repository를 이용합니다.

1. PG_STAT_MONITOR 다운로드 및 설치

## PostgreSQL PGDG yum Repository 설치
[root@tech-230 ~] yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

## PostgreSQL 15 버전 용 PG_STAT_MONITOR 설치
## yum install -y pg_stat_monitor_{VERSION}
[root@tech-230 ~] yum install -y pg_stat_monitor_15
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirror.kakao.com
 * extras: mirror.kakao.com
 * updates: mirror.kakao.com
Resolving Dependencies
--> Running transaction check
---> Package pg_stat_monitor_15.x86_64 0:2.0.1-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================
 Package                                   Arch                          Version                                  Repository                     Size
======================================================================================================================================================
Installing:
 pg_stat_monitor_15                        x86_64                        2.0.1-1PGDG.rhel7                        pgdg15                         34 k

Transaction Summary
======================================================================================================================================================
Install  1 Package

Total download size: 34 k
Installed size: 95 k
Downloading packages:
pg_stat_monitor_15-2.0.1-1PGDG.rhel7.x86_64.rpm                                                                                |  34 kB  00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_stat_monitor_15-2.0.1-1PGDG.rhel7.x86_64                                                                                        1/1
  Verifying  : pg_stat_monitor_15-2.0.1-1PGDG.rhel7.x86_64                                                                                        1/1

Installed:
  pg_stat_monitor_15.x86_64 0:2.0.1-1PGDG.rhel7

Complete!

2. Configuration(postgresql.conf) 변경 후 재기동

## postgresql.conf 변경
shared_preload_libraries = 'pg_stat_monitor'         # (change requires restart)

## PostgreSQL 재기동
[root@tech-230 ~] systemctl restart postgresql-15

3. PG_STAT_MONITOR Extension 생성

CREATE EXTENSION pg_stat_monitor ;
📢 테스트 환경에서는 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

SELECT queryid ,                                                                  -- 1
       substring( MAX( query ) , 0 , 40 ) AS query ,                              -- 2
       query_plan ,                                                               -- 3
       SUM( calls ) AS tot_calls ,                                                -- 4
       SUM( total_exec_time ) AS tot_exec_time_ms ,                               -- 5
       SUM( total_plan_time ) AS tot_plan_time_ms ,                               -- 6
       SUM( cpu_user_time ) AS tot_cpu_user_time_ms ,                             -- 7
       SUM( cpu_sys_time ) AS tot_cpu_sys_time_ms ,                               -- 8
       SUM( total_exec_time ) / SUM( calls ) AS exec_time_ms_per_sql ,            -- 9
       SUM( total_plan_time ) / SUM( calls ) AS plan_time_ms_per_sql ,            -- 10
       SUM( cpu_user_time ) / SUM( calls ) AS cpu_user_time_ms_per_sql ,          -- 11
       SUM( cpu_sys_time ) / SUM( calls ) AS cpu_sys_time_ms_per_sql ,            -- 12
       SUM( shared_blks_hit ) AS tot_shared_blks_hit ,                            -- 13
       SUM( shared_blks_read ) AS tot_shared_blks_read ,                          -- 14
       SUM( shared_blks_dirtied ) AS tot_shared_blks_dirtied ,                    -- 15
       SUM( shared_blks_written ) AS tot_shared_blks_written ,                    -- 16
       SUM( shared_blks_hit ) / SUM( calls ) AS shared_blks_hit_per_sql ,         -- 17
       SUM( shared_blks_read ) / SUM( calls ) AS shared_blks_read_per_sql ,       -- 18
       SUM( shared_blks_dirtied ) / SUM( calls ) AS shared_blks_dirtied_per_sql , -- 19
       SUM( shared_blks_written ) / SUM( calls ) AS shared_blks_written_per_sql   -- 20
FROM   pg_stat_monitor psm
WHERE  bucket_start_time BETWEEN '2023-09-01 14:30:00' AND '2023-09-01 18:00:00'
AND    cmd_type_text != ''
GROUP  BY queryid ,
       query_plan
HAVING SUM( calls ) > 0
ORDER  BY 9 DESC 
limit 100 ;

Script 2. 특정 SQL의 시간 별 수행정보

SELECT date_trunc( 'hour' , bucket_start_time::timestamp ) AS sample_time ,
       SUM( calls ) AS tot_calls ,
       SUM( total_exec_time ) / SUM( calls ) AS exec_time_ms_per_sql ,
       SUM( total_plan_time ) / SUM( calls ) AS plan_time_ms_per_sql ,
       SUM( cpu_user_time ) / SUM( calls ) AS cpu_user_time_ms_per_sql ,
       SUM( cpu_sys_time ) / SUM( calls ) AS cpu_sys_time_ms_per_sql ,
       SUM( shared_blks_hit ) / SUM( calls ) AS shared_blks_hit_per_sql ,
       SUM( shared_blks_read ) / SUM( calls ) AS shared_blks_read_per_sql ,
       SUM( shared_blks_dirtied ) / SUM( calls ) AS shared_blks_dirtied_per_sql ,
       SUM( shared_blks_written ) / SUM( calls ) AS shared_blks_written_per_sql
FROM   pg_stat_monitor psm
WHERE  queryid = '59172EFDADCDB302'
AND    bucket_start_time BETWEEN '2023-09-01 14:30:00' AND '2023-09-01 18:00:00'
GROUP  BY date_trunc( 'hour' , bucket_start_time::timestamp )
ORDER  BY 1 ;

 

 

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 수행

## Random한 시간을 Sleep하는 SQL을 10회 수행
#!/bin/bash
for var in {1..10}
do
  psql -p 54329 -c "SELECT pg_sleep(random() * 20 );"
done

SQL 통계 정보 확인

-- 수행한 SQL 통계 정보 조회
SELECT bucket ,
       queryid ,
       query ,
       total_exec_time ,
       min_exec_time ,
       max_exec_time ,
       resp_calls
FROM   pg_stat_monitor
WHERE  query like '%pg_sleep%' ;

 bucket |       queryid        |              query              |  total_exec_time  | min_exec_time | max_exec_time |                  resp_calls
--------+----------------------+---------------------------------+-------------------+---------------+---------------+-----------------------------------------------
      8 | -4590637993478381504 | SELECT pg_sleep(random() * 20 ) | 63632.93201700001 |    435.669133 |  17386.381134 | {0,0,0,0,0,0,0,0,0,0,0,1,0,0,3,1,3,2,0,0,0,0}

-- 수행된 SQL은 8번 Bucket(Snapshot)에 저장되어 있으며, query_id는 -4590637993478381504
-- Histogram(resp_calls)을 통해 수행시간 구간별 수행 횟수 저장 → {0,0,0,0,0,0,0,0,0,0,0,1,0,0,3,1,3,2,0,0,0,0}

Histogram Bucket의 Range 확인

-- 8번 Bucket(Snapshot)에 존재하는 특정 Query(-4590637993478381504)의 resp_calls 정보를 Histogram Function을 통해 확인
SELECT * 
FROM   histogram(8, '-4590637993478381504') AS a(range TEXT, freq INT, bar TEXT);

           range           | freq |              bar
---------------------------+------+--------------------------------
 {{0.000 - 1.000}          |    0 |
  (1.000 - 2.778}          |    0 |
  (2.778 - 4.162}          |    0 |
  (4.162 - 6.623}          |    0 |
  (6.623 - 11.000}         |    0 |
  (11.000 - 18.783}        |    0 |
  (18.783 - 32.623}        |    0 |
  (32.623 - 57.234}        |    0 |
  (57.234 - 101.000}       |    0 |
  (101.000 - 178.827}      |    0 |
  (178.827 - 317.226}      |    0 |
  (317.226 - 563.338}      |    1 | ■■■■■■■■■■
  (563.338 - 1000.994}     |    0 |
  (1000.994 - 1779.268}    |    0 |
  (1779.268 - 3163.256}    |    3 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
  (3163.256 - 5624.371}    |    1 | ■■■■■■■■■■
  (5624.371 - 10000.920}   |    3 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
  (10000.920 - 17783.643}  |    2 | ■■■■■■■■■■■■■■■■■■■■
  (17783.643 - 31623.492}  |    0 |
  (31623.492 - 56234.598}  |    0 |
  (56234.598 - 100000.000} |    0 |
  (100000.000 - ...}}      |    0 |
📢 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 생성

-- Top Level 추적을 위한 Test Function 생성 및 수행
CREATE OR REPLACE FUNCTION pg_sleep_loop() 
RETURNS void 
AS
$do$
BEGIN
   FOR i IN 1..10 LOOP
      PERFORM pg_sleep(1 * i) ;          -- Recursive SQL 1
      PERFORM pg_sleep(1 * i / 2) ;      -- Recursive SQL 2
   END LOOP ;
END
$do$
LANGUAGE plpgsql ;

PG_STAT_MONITOR.PGSM_TRACK Parameter 변경

-- Recursive SQL을 추적하기 위해 PG_STAT_MONITOR.PGSM_TRACK Parameter 변경
ALTER SYSTEM SET PG_STAT_MONITOR.PGSM_TRACK = 'ALL' ;
SELECT pg_reload_conf() ;

Test Function 수행

-- 생성한 Function 수행
SELECT pg_sleep_loop() ;          -- Top Level SQL

PG_STAT_MONITOR에서 Top Level Query 확인

-- Top Level SQL 수행 정보 및 내부적으로 수행된 2종류의 Recursive SQL 정보 확인
-- queryid(-2804603968659816821)와 top_queryid(-2804603968659816821) 관계 확인
SELECT bucket ,
       queryid ,
       query ,
       toplevel ,
       top_queryid ,
       top_query ,
       calls
FROM   pg_stat_monitor
WHERE  query LIKE '%pg_sleep%' ;

 bucket |       queryid        |              query              | toplevel |     top_queryid      |        top_query         | calls
--------+----------------------+---------------------------------+----------+----------------------+--------------------------+-------
      3 |  4231695254387206145 | SELECT pg_sleep(1*i/2)          | f        | -2804603968659816821 | SELECT pg_sleep_loop() ; |    10
      3 |  8482056316454589354 | SELECT pg_sleep(1*i)            | f        | -2804603968659816821 | SELECT pg_sleep_loop() ; |    10
      3 | -2804603968659816821 | SELECT pg_sleep_loop()          | t        |                      |                          |     1
📢 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정보를 추출할 수 있으므로 매우 유용하게 사용 가능합니다.

테스트 테이블 생성

CREATE TABLE test_table01 ( c1 INTEGER ) ;

테스트 SQL 수행

INSERT INTO TEST_TABLE01 VALUES (10) ;
DELETE FROM TEST_TABLE01 ;
SELECT * FROM TEST_TABLE01 ;

PG_STAT_MONITOR에서 Object Access 확인

-- 특정 Object(public.test_table01)를 Access한 SQL정보 추출
SELECT queryid ,
       userid ,
       datname ,
       substring( application_name , 0 , 20 ) AS app ,
       MAX( query ) AS query ,
       MAX( cmd_type_text ) AS cmd_type_text ,
       SUM( calls ) AS calls   -- etc columns..
FROM   pg_stat_monitor psm
WHERE  'public.test_table01' = ANY ( relations )
GROUP  BY queryid ,
       userid ,
       datname ,
       application_name ;

       queryid        | userid | datname  |         app         |                query                 | cmd_type_text | calls
----------------------+--------+----------+---------------------+--------------------------------------+---------------+-------
 -5767025315249789400 |     10 | postgres | pgAdmin 4 - CONN:78 | SELECT * FROM TEST_TABLE01           | SELECT        |     1
  1939908226864726315 |     10 | postgres | pgAdmin 4 - CONN:78 | INSERT INTO TEST_TABLE01 VALUES (10) | INSERT        |     1
  5941954116867054564 |     10 | postgres | pgAdmin 4 - CONN:78 | DELETE FROM TEST_TABLE01             | DELETE        |     1


또한, 해당 컬럼과 cmd_type_text, calls 컬럼을 조합할 경우 ① DML발생량이나 ② 테이블 별 CRUD Pattern 등 다양한 활용이 가능합니다.

① DML 발생량 확인

SELECT CASE WHEN crud IS NULL THEN '=== Total Count ===' ELSE crud END AS crud_type ,
       SUM( calls ) AS cnt
FROM   (
        SELECT a.elval ,
               calls ,
               CASE WHEN cmd_type_text != 'SELECT'
                    THEN CASE WHEN array_length( relations , 1 ) > 1
                              THEN CASE WHEN a.elnum = 1
                                        THEN cmd_type_text ELSE 'SELECT' END
                              ELSE cmd_type_text END
                    ELSE cmd_type_text END AS crud
        FROM   pg_stat_monitor psm ,
               unnest( psm.relations ) WITH ordinality a( elval , elnum )
        WHERE  1 = 1
        AND    cmd_type_text != ''
       ) m
GROUP  BY rollup( crud ) ;

      crud_type      | cnt
---------------------+-----
 === Total Count === | 515
 SELECT              | 134
 DELETE              | 120
 INSERT              | 201
 UPDATE              |  60

② 테이블 별 CRUD Pattern 확인

SELECT elval AS relation ,
       MAX( n_ins ) AS n_ins ,
       MAX( n_upd ) AS n_upd ,
       MAX( n_del ) AS n_del ,
       MAX( n_sel ) AS n_sel
FROM   (
        SELECT  elval ,
                CASE WHEN crud = 'INSERT' THEN calls END AS n_ins ,
                CASE WHEN crud = 'UPDATE' THEN calls END AS n_upd ,
                CASE WHEN crud = 'DELETE' THEN calls END AS n_del ,
                CASE WHEN crud = 'SELECT' THEN calls END AS n_sel
        FROM   (
                SELECT elval ,
                       crud ,
                       SUM( calls ) AS calls
                FROM   (
                        SELECT a.elval ,
                               calls ,
                               CASE WHEN cmd_type_text != 'SELECT' THEN
                                    CASE WHEN array_length( relations , 1 ) > 1 THEN
                                         CASE WHEN a.elnum = 1 THEN cmd_type_text
                                              ELSE 'SELECT'
                                         END
                                         ELSE cmd_type_text
                                    END
                               ELSE cmd_type_text END AS crud
                        FROM   pg_stat_monitor psm ,
                               unnest( psm.relations ) WITH ordinality a( elval , elnum )
                        WHERE  cmd_type_text != ''
                       ) m
                GROUP  BY elval ,
                       crud
               ) mm
       ) mmm
GROUP  BY elval ;

        relation         | n_ins | n_upd | n_del | n_sel
-------------------------+-------+-------+-------+-------
 public.pg_stat_monitor* |       |       |       |     5
 public.test_table03     |    80 |       |    80 |    80
 public.test_table02     |    80 |    80 |       |    80

 

 

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 변경

ALTER SYSTEM SET PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY = 'YES' ;  -- 'NO' 또는 'YES'
SELECT pg_reload_conf() ;

PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY = ‘NO’ 일 때 Query Text Sample

SELECT 1, COUNT(*) FROM test_table01 WHERE c1 = 2 ;
SELECT query FROM pg_stat_monitor WHERE query like '%test_table01%' ;

                        query
-----------------------------------------------------
 SELECT 1, COUNT(*) FROM test_table01 WHERE c1 = 2

PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY = ‘YES’ 일 때 Query Text Sample

SELECT 1, COUNT(*) FROM test_table01 WHERE c1 = 2 ;
SELECT query FROM pg_stat_monitor WHERE query like '%test_table01%' ;

                        query
-----------------------------------------------------
 SELECT $1, COUNT(*) FROM test_table01 WHERE c1 = $2
📢 단, 수행된 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_MONITOR
Column
PG_STAT_STATEMENTS
Exist
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)

 

 

 

 

 

 

 

 

 

 

기획 및 글 | DB기술기획팀

 

 

 

 

 

 

 

 

댓글