Postgresql 의 통계 수집 및 확인
Database 의 통계 기능은 무척 중요하다.
각종 장애나 성능 분석 시에 DB 내부의 다양한 상태 정보들을 보여주는 이러한 통계 데이터들이 없다면, 단순히 Trace Log 만으로 미묘한 성능 하락 등의 이유들을 밝혀내거나 사전에 그러한 현상을 방어하는 것이 거의 불가능하기 때문이다.
Database 의 내부 구조에 대한 이해와 각종 성능 통계 자료들을 분석해내는 능력을 가진 자만이 안정적이고 Upgrade 된 성능의 Database 를 이용한 시스템을 제대로 관리해낼 수 있다.
postgres 의 utility process 중 stat collector 가 있는데, 이는 postgres 의 각 process 들로부터 각종 통계 정보를 수집하여 취합하는 역할을 하는 놈인데, 그놈이 update 하는 통계 데이터들은 아래의 view 들에서 확인할 수 있다.
맨 아래의 pg_stat_statements view (확장 view)를 제외하고 모두 default 로 제공되는 system view 이다.
mydb=# select viewname from pg_views where viewname like 'pg_stat%';
viewname
-----------------------------
pg_stats
pg_stat_all_tables
pg_stat_xact_all_tables
pg_stat_sys_tables
pg_stat_xact_sys_tables
pg_stat_user_tables
pg_stat_xact_user_tables
pg_statio_all_tables
pg_statio_sys_tables
pg_statio_user_tables
pg_stat_all_indexes
pg_stat_sys_indexes
pg_stat_user_indexes
pg_statio_all_sequences
pg_statio_sys_sequences
pg_statio_user_sequences
pg_stat_activity
pg_stat_replication
pg_stat_database
pg_stat_database_conflicts
pg_stat_user_functions
pg_stat_xact_user_functions
pg_stat_bgwriter
pg_statio_all_indexes
pg_statio_sys_indexes
pg_statio_user_indexes
pg_stat_statements
(27 rows)
위의 것들 중에서 pg_statio_all_tables 의 스키마가 어떻게 생겼는지 확인해보자.
DB 내의 모든 table 들에 대해 각 relation ID 별로 block hit/read 통계들을 보여준다.
View 의 내부 definition 도 볼 수 있다.
mydb=# d+ pg_statio_all_tables
View "pg_catalog.pg_statio_all_tables"
Column | Type | Modifiers | Storage | Description
-----------------+--------+-----------+---------+-------------
relid | oid | | plain |
schemaname | name | | plain |
relname | name | | plain |
heap_blks_read | bigint | | plain |
heap_blks_hit | bigint | | plain |
idx_blks_read | bigint | | plain |
idx_blks_hit | bigint | | plain |
toast_blks_read | bigint | | plain |
toast_blks_hit | bigint | | plain |
tidx_blks_read | bigint | | plain |
tidx_blks_hit | bigint | | plain |
View definition:
SELECT c.oid AS relid, n.nspname AS schemaname, c.relname,
pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid) AS heap_blks_read,
pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,
sum(pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))::bigint AS idx_blks_read,
sum(pg_stat_get_blocks_hit(i.indexrelid))::bigint AS idx_blks_hit,
pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid) AS toast_blks_read,
pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid) AS tidx_blks_read,
pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit
FROM pg_class c
LEFT JOIN pg_index i ON c.oid = i.indrelid
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
LEFT JOIN pg_class x ON t.reltoastidxid = x.oid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid;
위의 System view 를 포함하여 Postgres 에서 통계 자료를 분석해볼 수 있는 방법은 몇가지가 있다.
- stat collector 프로세스가 수집하는 각종 통계 자료가 기록되는 pg_stat_xxxx system view
- extention preload library (pg_stat_statements)를 통한 query 별 block 접근 및 시간 통계
- dtrace 와 유사하게 probes.h 에 정의된 각 probe 지점에서 원하는 통계 값을
handling 하고 보여줄 수 있는 기능 - log_statement_stats 을 on 으로 설정하여 query 수행 시 아래와 같은 통계를 볼 수 있는 기능
mydb=# select count(*) from dplee1;
LOG: QUERY STATISTICS
DETAIL: ! system usage stats:
! 0.000518 elapsed 0.000000 user 0.000000 system sec
! [0.000000 user 0.000999 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/265 [0/959] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [2/0] voluntary/involuntary context switches
STATEMENT: select count(*) from dplee1;
count
-------
1
(1 row)
위의 3 번은 아래와 같이 사용하면 되는 방법이다.
1.
postgres 를 컴파일할 때 gmake 를 통해 compile 하는 과정에서 postgresql-9.2.4/src/backend/utils/probes.d
파일에서 필요한 내용을 sed 를 통해 추출하여 probes.h 파일을 자동으로 만들어주게 된다.
2.
probes.h 파일의 내용은 이런식으로 생겼다.
즉, Trace 할 Point 에 대한 Define 들만 정의되어 있다.
#define TRACE_POSTGRESQL_SORT_START_ENABLED() (0) #define TRACE_POSTGRESQL_SORT_DONE(INT1, INT2) #define TRACE_POSTGRESQL_SORT_DONE_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_READ_START(INT1, INT2, INT3, INT4, INT5, INT6, INT7) #define TRACE_POSTGRESQL_BUFFER_READ_START_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_READ_DONE(INT1, INT2, INT3, INT4, INT5, INT6, INT7, INT8) #define TRACE_POSTGRESQL_BUFFER_READ_DONE_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_FLUSH_START(INT1, INT2, INT3, INT4, INT5) #define TRACE_POSTGRESQL_BUFFER_FLUSH_START_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_FLUSH_DONE(INT1, INT2, INT3, INT4, INT5) #define TRACE_POSTGRESQL_BUFFER_FLUSH_DONE_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_CHECKPOINT_START(INT1) #define TRACE_POSTGRESQL_BUFFER_CHECKPOINT_START_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_CHECKPOINT_SYNC_START() #define TRACE_POSTGRESQL_BUFFER_CHECKPOINT_SYNC_START_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_CHECKPOINT_DONE() #define TRACE_POSTGRESQL_BUFFER_CHECKPOINT_DONE_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_SYNC_START(INT1, INT2) #define TRACE_POSTGRESQL_BUFFER_SYNC_START_ENABLED() (0) #define TRACE_POSTGRESQL_BUFFER_SYNC_WRITTEN(INT1) #define TRACE_POSTGRESQL_BUFFER_SYNC_WRITTEN_ENABLED() (0) ... #define TRACE_POSTGRESQL_BUFFER_WRITE_DIRTY_START(INT1, INT2, INT3, INT4, INT5) ...
3.
위의 Define 은 소스에는 다음과 같이 구현되어 있다.
vi src/backend/storage/buffer/bufmgr.c
TRACE_POSTGRESQL_BUFFER_WRITE_DIRTY_START(forkNum, blockNum,
smgr->smgr_rnode.node.spcNode,
smgr->smgr_rnode.node.dbNode,
smgr->smgr_rnode.node.relNode);
4.
위의 probes.h 파일의 define 을 우리는 다음과 같이 변경하면 실행 시 소스에서 넘겨주는 인자들을 찍어볼 수 있다.
#define TRACE_POSTGRESQL_BUFFER_WRITE_DIRTY_START(INT1, INT2, INT3, INT4, INT5)
{
elog(LOG, "forkNum : %d, blockNum : %d, spcNode : %d, dbNode : %d, relNode : %dn", INT1, INT2, INT3, INT4, INT5);
}
5.
gmake 시마다 probes.h 를 probes.d 파일로부터 다시 만들기 때문에 만약 probes.h 을 수동으로 수정한 것이 있다면 다시 원상복구되지 않도록 postgresql-9.2.4/src/backend/utils/Makefile 에서 sed 로 probes.h 파일 만드는 부분을 막아두어야 한다.
** 추가적인 위치에 더 찍어보고 싶을 때는 위처럼 Trace 위치 이름에 대한 Define 을 하나 더
정의하고 소스의 원하는 위치에 이를 추가하면 된다.
