postgresql 통계 정보
시스템을 운영하다보면 시스템의 상태를 확인하고 싶을때가 많다. 시스템의 구석구석을 아는것도 중요하지만 시스템을 모니터링을 통해 시스템의 상태를 정확하게 파악하고 있느것도 매운 중요한 일이다.
PostgreSQL도 마찬가지다. 현재 난 PostgreSQL에 대해서 잘 모른다. 하지만 PostgreSQL의 상태를 알고 싶은 마음은 굴뚝같다. 그래서 여기저기 알아보고 내용을 정리해봤다.
■ PostgreSQL 의 통계정보.
PosgtgreSQL의 통계 정보는 시스템 카탈로그의 pg_stat 로 시작하는 테이블에 저장된다. 현재 데이터베이스 리스트와 OID, 데이터베이스별 사용용량등을 쿼리문을 통해서 확인할수 있다. 이뿐아니라 통계관련 함수도 많이 지원한다.
postgres=# SELECT * FROM pg_stat
pg_stat_activity pg_statio_all_indexes pg_statio_sys_sequences pg_statio_user_tables pg_stat_sys_tables pg_stat_all_indexes pg_statio_all_sequences pg_statio_sys_tables pg_statistic pg_stat_user_indexes
pg_stat_all_tables pg_statio_all_tables pg_statio_user_indexes pg_stats pg_stat_user_tables
pg_stat_database pg_statio_sys_indexes pg_statio_user_sequences pg_stat_sys_indexes
많은 통계 테이블이 존재한다. 모든 것을 알면 좋지만 많이 활용하는 순으로 간단하게 하나씩 살펴보도록 하자.
1.pg_stat_activity
이 테이블은 현재 PostgreSQL에서 실행되어지는 쿼리문들을 보여준다. mysql 에서는 show processlist 와 유사하다.
=#\d pg_stat_activity;
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
---------------+--------------------------+-----------
datid | oid |
datname | name | procpid | integer | usesysid | oid |
usename | name | current_query | text | waiting | boolean |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
backend_start | timestamp with time zone |
client_addr | inet |
client_port | integer |
Column | Type | Modifiers
---------------+--------------------------+-----------
datid | oid |
datname | name | procpid | integer | usesysid | oid |
usename | name | current_query | text | waiting | boolean |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
backend_start | timestamp with time zone |
client_addr | inet |
client_port | integer |
=#SELECT datname, procpid, usename, current_query FROM pg_stat_activity;
이것을 보면 누가 어디에서 접속해서 어느 데이터베이스에 무슨 쿼리를 쓰고 있는지를 알수 있다.
>현재 데이터베이스에 접속한 사람 수.
=#SELECT COUNT(*) FROM pg_stat_activity;
현재 쿼리 개수. 그런데 한쿼리당 클라이언트 포트를 할당하므로 접속자수라고 할수 있다.
쿼리를 날렸는데 느리거나 할때는 한번 최대 접속자 수(max_connections) 설정값과 비교해 보자
2.pg_stat_database
데이터베이스의 상태 통계를 가지고 있다. 각 컬럼별 의미는 다음과 같다.
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
---------------+---------+-----------
datid | oid | 데이터베이스 OID
datname | name | 데이터베이스 이름
numbackends | integer | 활성화된 서버프로세스 개수
xact_commit | bigint | 데이터베이스에서 커밋된 트랜잭션 개수
xact_rollback | bigint | 데이터베이스에서 롤백된 트랜잭션 개수
blks_read | bigint | 읽힌 총 디스크 블록 수
blks_hit | bigint | 총 버퍼 히트 수
tup_returned | bigint | 반환된 로우(row) 개수
tup_fetched | bigint | 가져간 로우(row) 개수
tup_inserted | bigint | Insert 되어진 로우 개수
tup_updated | bigint | Update 되어진 로우 개수
tup_deleted | bigint | Delete 도어진 로우 개수
Column | Type | Modifiers
---------------+---------+-----------
datid | oid | 데이터베이스 OID
datname | name | 데이터베이스 이름
numbackends | integer | 활성화된 서버프로세스 개수
xact_commit | bigint | 데이터베이스에서 커밋된 트랜잭션 개수
xact_rollback | bigint | 데이터베이스에서 롤백된 트랜잭션 개수
blks_read | bigint | 읽힌 총 디스크 블록 수
blks_hit | bigint | 총 버퍼 히트 수
tup_returned | bigint | 반환된 로우(row) 개수
tup_fetched | bigint | 가져간 로우(row) 개수
tup_inserted | bigint | Insert 되어진 로우 개수
tup_updated | bigint | Update 되어진 로우 개수
tup_deleted | bigint | Delete 도어진 로우 개수
보통 다음과 같은 쿼를 많이 쓴다.
=#SELECT datname, xact_commit, xact_rollback, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database ORDER BY tup_inserted DESC;
위의 쿼리를 수행하면 누가 데이터베이스를 많이 쓰는지를 알수가 있다.
3.pg_stat_all_tables
현재 접속한 데이터베이스의 각 테이블별 통계를 볼수가 있다. 근데 시스템 카탈로그 테이블도 포함해서 나온다.
View "pg_catalog.pg_stat_all_tables"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid | 테이블의 OID
schemaname | name | 스키마 이름
relname | name | 테이블 이름
seq_scan | bigint | number of sequential scans initiated
seq_tup_read | bigint | number of live rows fetched by sequential scans
idx_scan | bigint | number of index scans initiated
idx_tup_fetch | bigint | number of live rows fetched by index scans
n_tup_ins | bigint | 인서트 개수
n_tup_upd | bigint | 업데이트 개수
n_tup_del | bigint |삭제 개수
Column | Type | Modifiers
---------------+--------+-----------
relid | oid | 테이블의 OID
schemaname | name | 스키마 이름
relname | name | 테이블 이름
seq_scan | bigint | number of sequential scans initiated
seq_tup_read | bigint | number of live rows fetched by sequential scans
idx_scan | bigint | number of index scans initiated
idx_tup_fetch | bigint | number of live rows fetched by index scans
n_tup_ins | bigint | 인서트 개수
n_tup_upd | bigint | 업데이트 개수
n_tup_del | bigint |삭제 개수
위의 테이블로 요약해서 데이터베이스의 인서트, 업데이트, 삭제 개수를 알 수 있다.
=#SELECT SUM(n_tup_ins), SUM(n_tup_upd), SUM(n_tup_del) FROM pg_stat_all_tables;
4.pg_database_size('name')
데이터베이스 사이즈 사용하는 사이즈를 알수 있다.
=#SELECT pg_database_size('abcd');
pg_database_size
------------------
12979820
(1 row)
------------------
12979820
(1 row)
이렇게 하면 byte 단위로 나온다.
이것을 아래와 같이하면 더 쉽게 볼수 있다.
=#SELECT pg_size_pretty(pg_database_size('abcd')) AS size;
size
-------
12 MB
(1 row)
-------
12 MB
(1 row)
그런데 문제가 있다. pg_database_size는 시스템테이블 크기도 포함한 크기이다. 그래서 실제 순수 데이터베이스 크기를 알기 위해서는 다음과 같이 한다.
=#select pg_size_pretty(cast (sum(pg_total_relation_size(tablename)) as bigint)) as size from pg_tables where schemaname in (current_database());
이렇게 하면 순수 데이터베이스 크기를 알수 있다.
5.pg_tablespace_size('name')
테이블 스페이스의 사이즈를 알수가 있다. 사용법은 위와 같다.
6.특정 namespace(PostgreSQL에서는 스키마라고 함)속한 테이블의 사이즈 알기
각 테이블별 사이즈를 보여준다.
=#select tablename, pg_size_pretty(pg_relation_size(tablename)) as size from pg_tables where schemaname=current_schema() order by pg_relation_size(tablename) desc;
=#select tablename, pg_size_pretty(pg_total_relation_size(tablename)) as size from pg_tables where schemaname=current_schema() order by pg_total_relation_size(tablename) desc;
첫번째거와 두번째에 사이즈 차이가 존재한다. 첫번째에 것이 물리적이 디스크 크기와 같다. 두번째거는 아마 인덱스라든지 스키마정보등의 크기를 모두 합한 사이즈를 나타내는듯하다.
7.PostgreSQL 8.0 버전.
PostgresQL 8.0 버전에서는 위의 몇가지가 동작하지 않는다. 이럴때는 어쩔수 없이 수동으로 해야만 한다.
7-1.데이터베이스 크기 알아내기.
=#SELECT oid, datname FROM pg_database;
abcd | 247690
]#du -hs data/base/247690
13.5M
7-2.테이블 사이즈 알기.
=#SELECT relname, relnamespzce, relfilenode FROM pg_class WHERE relname='abcd';
relname | relnamespace | relfilenode
-------------------+--------------+-------------
abcd | 555703 | 556171
-------------------+--------------+-------------
abcd | 555703 | 556171
]#du -hs data/base/247690/556171
