[PostgreSQL] 쿼리 실행 통계 확인을 위한 pg_stat_monitor

• target:  쿼리 수행 통계 확인을 위한 pg_stat_monitor 모듈

 

---

• method: 

PostgreSQL에는 pg_stat_statements 라는 DB서버에서 수행된 모든 SQL문의 실행 통계를 제공하는 유용한 extension이 있습니다.

그러나 몇가지 단점이 있는데

• 수행된 시간대가 표시안되어 pg_stat_statements 만으로는 피크시간대 수행된 쿼리를 확인하기 어려움
• 쿼리에서 접근한 테이블이 따로 저장안됨
• 쿼리를 수행한 client IP 표시가 안됨

이러한 단점들을 모두 해결한 것이 percona에서 나온 pg_stat_monitor라는 extension 입니다.

 

---

1. Installation

1) 'pg_stat_monitor' 모듈 다운로드

$ sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ sudo percona-release setup ppg-12
$ sudo yum install -y percona-postgresql12-devel

 

2) 'pg_stat_monitor' 모듈을 PostgreSQL 서버 라이브러리에 추가

# postgresql.conf
shared_preload_libraries = 'pg_stat_monitor'

 

3) PostgreSQL 서버 재기동

 

4) 'pg_stat_monitor' EXTENSION 생성

# CREATE EXTENSION pg_stat_monitor;

# select * from pg_extension;
      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql            |       10 |           11 | f              | 1.0        |           |
 pg_stat_statements |       10 |         2200 | t              | 1.6        |           |
 pgpool_recovery    |       10 |         2200 | t              | 1.2        |           |
 pg_stat_monitor    |       10 |         2200 | t              | 1.0        |           |
(4 rows)

# \dv
		List of relations
 Schema |           Name           | Type | Owner
--------+--------------------------+------+--------
 public | pg_stat_monitor          | view | irteam
 public | pg_stat_monitor_settings | view | irteam
 public | pg_stat_statements       | view | irteam
(3 rows)

 

---

2. Setting

# select * from pg_stat_monitor_settings;
                  name                  | value | default_value |                                               description                                                | minimum |  maximum   | restart
----------------------------------------+-------+---------------+----------------------------------------------------------------------------------------------------------+---------+------------+---------
 pg_stat_monitor.pgsm_max               |   100 |           100 | Sets the maximum size of shared memory in (MB) used for statement's metadata tracked by pg_stat_monitor. |       1 |       1000 |       1
 pg_stat_monitor.pgsm_query_max_len     |  1024 |          1024 | Sets the maximum length of query.                                                                        |    1024 | 2147483647 |       1
 pg_stat_monitor.pgsm_enable            |     1 |             1 | Enable/Disable statistics collector.                                                                     |       0 |          0 |       1
 pg_stat_monitor.pgsm_track_utility     |     1 |             0 | Selects whether utility commands are tracked.                                                            |       0 |          0 |       0
 pg_stat_monitor.pgsm_normalized_query  |     1 |             1 | Selects whether save query in normalized format.                                                         |       0 |          0 |       0
 pg_stat_monitor.pgsm_max_buckets       |    10 |            10 | Sets the maximum number of buckets.                                                                      |       1 |         10 |       1
 pg_stat_monitor.pgsm_bucket_time       |    60 |            60 | Sets the time in seconds per bucket.                                                                     |       1 | 2147483647 |       1
 pg_stat_monitor.pgsm_histogram_min     |     0 |             0 | Sets the time in millisecond.                                                                            |       0 | 2147483647 |       1
 pg_stat_monitor.pgsm_histogram_max     |    10 |            10 | Sets the time in millisecond.                                                                            |      10 | 2147483647 |       1
 pg_stat_monitor.pgsm_histogram_buckets |    10 |            10 | Sets the maximum number of histogram buckets                                                             |       2 | 2147483647 |       1

※ 위 설정은 postgresql.conf에서 설정 변경 후 PostgreSQL 서버 재기동

※ (원글참조) bucket에 쿼리 실행 통계를 저장하고, pgsm_bucket_time마다 bucket을 바꿔쓰는데, bucket을 다쓰면 데이터를 덮어쓰기 때문에 pgsm_bucket_time 시간을 늘려서 쓰는게 좋아보임

 

---

3. Use case

# \d pg_stat_monitor
                      View "public.pg_stat_monitor"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 bucket              | integer          |           |          |
 bucket_start_time   | text             |           |          |
 userid              | regrole          |           |          |
 datname             | name             |           |          |
 client_ip           | inet             |           |          |
 queryid             | text             |           |          |
 query               | text             |           |          |
 application_name    | text             |           |          |
 relations           | regclass[]       |           |          |
 cmd_type            | integer          |           |          |
 cmd_type_text       | text             |           |          |
 elevel              | integer          |           |          |
 sqlcode             | text             |           |          |
 message             | text             |           |          |
 plans               | bigint           |           |          |
 plan_total_time     | double precision |           |          |
 plan_min_time       | double precision |           |          |
 plan_max_time       | double precision |           |          |
 plan_mean_time      | double precision |           |          |
 plan_stddev_time    | double precision |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |
 resp_calls          | text[]           |           |          |
 cpu_user_time       | numeric          |           |          |
 cpu_sys_time        | numeric          |           |          |
 wal_records         | bigint           |           |          |
 wal_fpi             | bigint           |           |          |
 wal_bytes           | numeric          |           |          |

    
postgres=# SELECT application_name, userid AS user_name, datname AS database_name, substr(query,0, 50) AS query, calls, client_ip
postgres-#            FROM pg_stat_monitor limit 5;
-[ RECORD 1 ]----+-----------------------------------------------------
application_name |
user_name        | testuser
database_name    | postgres
query            | SELECT name, setting, COALESCE(unit, $1), short_d
calls            | 2
client_ip        | 10.161.78.33
-[ RECORD 2 ]----+-----------------------------------------------------
.
.

※ (원글참조) relation, bucket_start_time, client_ip, cpu 관련 컬럼을 추가로 조회해도 유용할 것으로 보임

 

• 에러난 쿼리 확인

# SELECT   decode_error_level (elevel) AS elevel, sqlcode, query, message FROM pg_stat_monitor WHERE elevel != 0 ;
 elevel | sqlcode |                       query                       |                            message
--------+---------+---------------------------------------------------+---------------------------------------------------------------
 ERROR  | 22021   |                                                   | invalid byte sequence for encoding "UTF8": 0xe3 0x0a 0x73
 ERROR  | 22021   |                                                   | invalid byte sequence for encoding "UTF8": 0xe3 0x20 0x6c
 ERROR  | 22021   |                                                   | invalid byte sequence for encoding "UTF8": 0xe3 0x6c 0x69
 ERROR  | 42601   | desc pg_stat_monitor;                             | syntax error at or near "desc"
 ERROR  | 42601   | select * from pg_stat_monitor limit 100000,10;    | LIMIT #,# syntax is not supported
 ERROR  | 23505   | insert into tb_test values(13097329,'','','',''); | duplicate key value violates unique constraint "tb_test_pkey"
 ERROR  | 22012   | select 1/0;                                       | division by zero

 

---

• warning: 

'pg_stat_statements' 모듈과 'pg_stat_monitor' 모듈 버전에 따라서 상이한 내용이 있을 수 있음

ex) 'pg_stat_statements'의 경우, PG 12 에서 생성되는 VIEW의 컬럼과 PG 13 에서 생성되는 VIEW 컬럼이 상이함

 

---

• source:

https://kimdubi.github.io/postgresql/pg_stat_monitor/

쿼리 실행 통계 확인을 위한 pg_stat_monitor

 

https://pu3vig.tistory.com/106

[PostgreSQL] Slow Query를 감지하기 위한 3가지 방법