Contrib Extension 에서 시스템 카탈로그를 보완하는 ‘VIEW’ 기능을 제공하는 Extension을 모아서 정리해봤습니다.
- pg_buffercache
- pg_stat_statements
pg_buffercache
실시간으로 shared buffer cache에서 일어나는 일을 pg_buffercache 라는 view 형태로 아래 row를 제공합니다.
- 뷰 컬럼 설명 컬럼명 데이터타입 설명 bufferid integer ID값이며, 1 ~ shared_buffers값의 범위로 부여됨 relfilenode oid pg_class 카탈로그의 relfilenode를 참조 reltablespace oid pg_tablespace 카탈로그의 oid를 참조 reldatabase oid pg_database 카탈로그의 oid를 참조 relforknumber smallint relation 내의 포크 번호 relblocknumber bigint relation 내 페이지 번호 isdirty boolean 페이지 dirty 여부 확인 usagecount smallint 클록 스윕 액세스 수 pinning_backends integer 이 버퍼를 고정하는 백엔드 수
예시
select count(*) from pg_buffercache;
select setting from pg_settings where name = 'shared_buffers'
위 두 쿼리의 결과값(buffer 페이지 수)이 같으므로, 시스템 카탈로그와의 join을 통해 relation 별로 shared buffer 사용 현황을 확인 할 수 있습니다.
다음은, pg_class 및 pg_database, pg_namespace와의 join을 통해, shared buffer를 점유하는 top 20 relation을 확인하는 쿼리 예시입니다.
with
all_tables as — 총 shared buffer 사이즈를 보여주는 부분
(
SELECT
*
FROM (
SELECT
‘all_nsp’ AS nspname,
‘cluster_all_page_size’ AS relname,
pg_size_pretty(count(*) * 8192) AS buffer_size
— pg_size_pretty를 이용해 postgresql의 버퍼캐시에 있는 모든 페이지 수 -> Byte 단위로 치환
— 실제 사용하는 메모리 양과 일치하지 않을 수 있음에 유의
FROM pg_buffercache
)a),
tables as — relation 별 shared buffer 사이즈를 보여주는 부분
(
SELECT
*
FROM (
SELECT n.nspname as nspname, c.relname as relname, pg_size_pretty(count(*) * 8192) ASbuffer_size
— pg_size_pretty를 이용해 페이지 수 -> Byte 단위로 치환
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid
FROM pg_database
WHERE datname = current_database()))
JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY nspname, relname
ORDER BY count(*) DESC
limit 20
)a)
SELECT
nspname as nspname,
relname as relname,
buffer_size as buffer_size
FROM
(SELECT *
FROM all_tables
UNION ALL
SELECT * FROM tables) a
;
쿼리의 결과입니다.
nspname | relname | buffer_size
————+———————————+————-
all_nsp | cluster_all_page_size | 128 MB
pg_catalog | pg_attribute | 344 kB
pg_catalog | pg_proc | 288 kB
pg_catalog | pg_class | 136 kB
pg_catalog | pg_depend_reference_index | 112 kB
pg_catalog | pg_rewrite | 104 kB
pg_catalog | pg_proc_proname_args_nsp_index | 96 kB
pg_catalog | pg_proc_oid_index | 88 kB
pg_catalog | pg_depend | 80 kB
pg_catalog | pg_attribute_relid_attnum_index | 72 kB
pg_toast | pg_toast_2618 | 56 kB
pg_catalog | pg_class_relname_nsp_index | 48 kB
pg_catalog | pg_type | 48 kB
pg_catalog | pg_statistic | 48 kB
pg_catalog | pg_amproc | 40 kB
pg_catalog | pg_description | 40 kB
pg_catalog | pg_init_privs | 40 kB
pg_catalog | pg_index | 40 kB
pg_catalog | pg_amop | 40 kB
pg_catalog | pg_depend_depender_index | 40 kB
pg_catalog | pg_extension | 40 kB
(21개 행)
pg_stat_statements
서버에서 실행되었던 쿼리들에 대한 실행 통계 정보를 view 형태로 제공합니다.
추가 제공 함수
- pg_stat_statements_reset() returns void
pg_stat_statements에서 사용하기 위해 수집된 모든 정보를 삭제합니다(실행 권한 : superuser).
- pg_stat_statements(showtext boolean) returns setof record
pg_stat _statements view의 정보를 함수로도 조회가 가능합니다. showtext := false 로 설정하면 쿼리의 내용을 조회하지 않고 출력합니다. 해당 함수를 이용하면 예측할 수 없는 쿼리의 길이를 출력하여 생기는 오버헤드에 대한 부담을 줄일 수 있습니다.
설정 파라미터
postgresql.conf에서 설정할 수 있는 파라미터입니다.
| 파라미터명 | 설명 |
|---|---|
| pg_stat_statements.max | |
| (integer) | pg_stat_statements에서 처리하는 쿼리의 최대수를 지정. 해당 파라미터를 넘게 되면 제일 마지막에 실행된 쿼리가 삭제(default = 1000). 변경 시 서버 재시작 필요 |
| pg_stat_statements.track | |
| (enum) | 수집할 쿼리문의 사용빈도에 따른 쿼리문 사용 통계 정보 수집범위를 지정. top 은 자주 사용되는 쿼리를 대상으로 하고, all 은 모든 쿼리들을 대상으로, none 으로 지정하면 어떤 쿼리도 수집하지 않음(default = top). 변경 권한은 superuser이며, 동적 변경 가능 |
| pg_stat_statements.track_utility(boolean) | SELECT, INSERT, UPDATE, DELETE 구문 외에 다른 구문들도 수집 대상으로 포함시킬 지 지정(default = on). 변경 권한은 superuser이며, 동적 변경 가능 |
| pg_stat_statements.save | |
| (boolean) | 서버가 중지되고 재실행되었을 때 마지막 중지 시점의 쿼리문 통계정보를 저장할 것인지를 지정. off로 설정하면, 서버가 재실행될 때 모든 쿼리문에 대한 통계정보를 초기화(default = on). |
| 변경은 postgresql.conf 또는 실행 시 파라미터 옵션으로만 설정 가능 |
뷰
뷰 컬럼 설명
| 컬럼명 | 데이터타입 | 설명 |
| userid | oid | 해당 쿼리를 실행했던 사용자의 oid |
| dbid | oid | 해당 쿼리를 실행했던 데이터베이스 oid |
| toplevel | boolean | 쿼리가 top-level 구문으로 실행된 경우 true (pg_stat_statement.track 세팅 시 항상 true) |
| queryid | bigint | internal hash code, 구문의 계산된 parse tree |
| query | text | 해당 쿼리 내용(track_activity_query_size 크기만큼 저장) |
| plans | bigint | 쿼리문이 계획된 횟수 |
| total_plan_time | double precision | 쿼리문 계획에 소요된 총 시간(ms) |
| min_plan_time | double precision | 쿼리문 계획에 소요된 최소 시간(ms) |
| max_plan_time | double precision | 쿼리문 계획에 소요된 최대 시간(ms) |
| mean_plan_time | double precision | 쿼리문 계획에 소요된 평균 시간(ms) |
| stddev_plan_time | double precision | 쿼리문 계획에 소요된 시간의 표준 편차(ms) |
| calls | bigint | 쿼리문이 실행된 횟수 |
| total_exec_time | double precision | 쿼리문의 총 실행 시간(ms) |
| min_exec_time | double precision | 쿼리문 실행에 소요된 최소 시간(ms) |
| max_exec_time | double precision | 쿼리문 실행에 소요된 최대 시간(ms) |
| mean_exec_time | double precision | 쿼리문 실행에 소요된 평균 시간(ms) |
| stddev_exec_time | double precision | 쿼리문 실행에 소요된 시간의 표준 편차(ms) |
| rows | bigint | 쿼리문에 의해 검색되거나 영향을 받는 총 행수 |
| shared_blks_hit | bigint | 쿼리문에 의한 총 공유 블록 캐시 적중 수 |
| shared_blks_read | bigint | 쿼리문이 읽은 총 공유 블록 수 |
| shared_blks_dirtied | bigint | 쿼리문에 의해 dirty 블록이 된 수 |
| shared_blks_written | bigint | 쿼리문이 작성한 총 공유 블록 수 |
| local_blks_hit | bigint | 쿼리문에 의한 총 로컬 블록 캐시 적중 수 |
| local_blks_read | bigint | 쿼리문이 읽은 총 로컬 블록 수 |
| local_blks_dirtied | bigint | 쿼리문에 의해 로컬 dirty 블록이 된 수 |
| local_blks_written | bigint | 쿼리문이 작성한 총 로컬 블록 수 |
| temp_blks_read | bigint | 쿼리문이 읽은 총 임시 블록 수 |
| temp_blks_written | bigint | 쿼리문이 작성한 총 임시 블록 수 |
| blk_read_time | double_precision | 쿼리문이 블록을 읽을 때 소요한 총 시간(ms) |
| blk_write_time | double_precision | 쿼리문이 블록을 쓸 때 소요한 총 시간(ms) |
| wal_records | bigint | 쿼리문에 의해 생성된 총 WAL 레코드 수 |
| wal_fpi | bigint | 쿼리문에 의해 생성된 WAL 전체 페이지 이미지의 총 수 |
| wal_bytes | numeric | 쿼리문에 의해 생성된 WAL의 총량(byte) |
queryid는 superuser 및 해당 구성원 만 조회할 수 있습니다. postgresql.conf에서 compute_query_id 파라미터가 활성화 되어 있어야 합니다.
예시
수집된 정보를 초기화 하고, pgbench를 이용하여 데이터를 생성 하여 pg_stat_statements를 통해 공유메모리 hit percent까지 계산해서 추가로 확인하는 예시입니다.
postgres=# select pg_stat_statements_reset(); — 수집정보 초기화
$ pgbench -i -s 1 –scale factor가 1인 데이터를 생성(default 100000 rows)
$ pgbench -c 10 -t 300 — 10개의 클라이언트에서 각 클라이언트 별로 300개의 트랜잭션을 실행postgres=# SELECT query,
calls,
total_exec_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
— hit percent 계산하는 부분을 추가해 내림차순으로 5 rows 까지 보여줌
쿼리 결과는 아래와 같습니다.
query | calls | total_exec_time | rows | hit_percent
———————————————————————+——-+——————–+——–+———————-
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 | 3000 | 9825.648093000027 | 3000 | 99.9982651839772392
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 | 3000 | 7448.384336000014 | 3000 | 99.9966524955645566
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 | 3000 | 106.78840300000023 | 3000 | 98.7657091561938959
copy pgbench_accounts from stdin | 1 | 81.206247 | 100000 | 100.0000000000000000
vacuum analyze pgbench_accounts | 1 | 52.918577 | 0 | 99.9202710783336655
(5 rows)
참고사이트
공식 홈페이지 :
https://www.postgresql.org/docs/current/pgbuffercache.html
https://www.postgresql.org/docs/current/pgstatstatements.html
지금까지 ‘PostgreSQL Contrib Extensions – view’에 관해 알아보았습니다
‘PostgreSQL의 Contrib Extensions – 연산자’를 바로 이어서 확인해보세요!
