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                  |

=#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 도어진 로우 개수

 

보통 다음과 같은 쿼를 많이 쓴다. 

=#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 |삭제 개수

 

위의 테이블로 요약해서 데이터베이스의 인서트, 업데이트, 삭제 개수를 알 수 있다.

=#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)

이렇게 하면 byte 단위로 나온다.

이것을 아래와 같이하면 더 쉽게 볼수 있다.

=#SELECT pg_size_pretty(pg_database_size('abcd')) AS size;

 size 
-------
 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

 

]#du -hs data/base/247690/556171

12M

[출처] PostgreSQL 통계 정보 보기.|작성자 님프