PPAS Drita(성능 분석 툴) 설정 방법

오라클의 awr Report 와 유사한 기능으로, PPAS에서 지원하는 성능 분석 툴

(Postgresql에서는 지원하지 않는 기능)

 

 

1. postgresql.conf 파일 수정

timed_statistics=on ->설정 이후 DB 재기동 필요

 

 

2. Snapshot 생성

edb=# SELECT * from edbsnap();

edbsnap

----------------------

Statement processed.

 

 

3. Snapshot 생성정보 확인

edb=# select * from get_snaps();

get_snaps

-----------------------------

1 22-MAY-13 17:44:28.3835

2 22-MAY-14 17:44:50.736477

 

 

4. System Wait Information 조회

--parameter 1 : snapshot 시작번호

--parametet 2 : snapshot 종료번호

--parameter 3 : snapshot top 이벤트 개수

 

edb=# select * From sys_rpt(1,2,20);

                                   sys_rpt

-----------------------------------------------------------------------------

WAIT NAME                                COUNT      WAIT TIME       % WAIT

---------------------------------------------------------------------------

db file read                             63         0.195492        79.58

query plan                               16         0.048833        19.88

db file extend                           48         0.001331        0.54

 

5. 성능분석 리포트 조회

edb=# select * From edbreport(1,2);

 

EnterpriseDB Report for database edb        22-MAY-14

Version: EnterpriseDB 9.2.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit

 

      Begin snapshot: 1 at 22-MAY-14 15:46:50.000223

      End snapshot:   2 at 22-MAY-14 15:47:04.398233

 

Size of database edb is 9402 kB

      Tablespace: pg_default Size: 34 MB Owner: enterprisedb

      Tablespace: pg_global Size: 461 kB Owner: enterprisedb

      Tablespace: kyh Size: 0 bytes Owner: enterprisedb

 

Schema: pg_toast_temp_1                Size: 0 bytes         Owner: enterprisedb

Schema: public                         Size: 0 bytes         Owner: enterprisedb

Schema: enterprisedb                   Size: 8192 bytes      Owner: enterprisedb

Schema: kyh                            Size: 0 bytes         Owner: enterprisedb

 

6. Snapshot 정보 삭제

--parameter 1 : snapshot 시작번호

--parametet 2 : snapshot 종료번호

 

edb=# select * From purgesnap(1,1);

             purgesnap

------------------------------------

Snapshots in range 1 to 1 deleted.

(1 row)

 

7. Snapshot 정보 전체 삭제

edb=# select * From truncsnap();

      truncsnap

----------------------

Snapshots truncated.

(1 row)

                                                                                                             by  김용하 (2014.06.27)