PostgreSQL에는 pg_stat_statements 라는 DB서버에서 수행된 모든 SQL문의 실행 통계를 제공하는 유용한 extension이 있습니다.
그러나 몇가지 단점이 있는데
- 수행된 시간대가 표시안되어 pg_stat_statements 만으로는 피크시간대 수행된 쿼리를 확인하기 어려움
- 쿼리에서 접근한 테이블이 따로 저장안됨
- 쿼리를 수행한 client IP 표시가 안됨
이러한 단점들을 모두 해결한 것이 percona에서 나온 pt_stat_monitor라는 extension 입니다.
installation
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
### postgresql stop 후
$ vi postgresql.conf
shared_preload_libraries = 'pg_stat_monitor' -- 추가
### postgresql 기동 후
CREATE EXTENSION pg_stat_monitor;
postgres=# 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)
postgres=# \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)
setting
postgres=# 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 설정 변경 후 재기동 필요함
bucket에 쿼리 실행 통계를 저장하고 pgsm_bucket_time 마다 bucket을 바꿔쓰는데 bucket을 다쓰면 데이터를 덮어쓰기 때문에 pgsm_bucket_time 시간을 늘려서 쓰는 게 좋아보임
use case
- 실행된 쿼리 확인
postgres=# \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 관련 컬럼을 추가로 조회해도 유용할것으로보임
- 에러난 쿼리 확인
postgres=# 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