[PostgreSql] 기본 모니터링 방법(2)

5) 템프 파일 사용량 로깅 

   conf파일에  log_temp_files = 0 or 특정값으로 설정하면 해당 값을 넘어갈때 postgresql log    파일에 로깅됨

   템프 파일 사용량이 증가하면 work_mem값을 크게 설정하여 메모리 사용하도록 유도..

 

 

13. Slow query 분석  

     postgresql에 pg_stat_statements 모듈 설치 (create extension pg_stat_statements)

     (해당 모듈 load를 위해 shared_preload_libraries = 'pg_stat_statements' 설정)

     1)  analyze    : db 전체 성능 향상을 위해 해당 명령어 수행 

         (모든 테이블에 통계 정보 Update 및 데이터 정리 작업 수행)

     2) 1번 수행후 별 다른 개선사항이 없으면

        \x

        select * from pg_stat_statements 수행후 결과중 shared_blks_hit 및 shared_blks_read 

        항목 확인

       SELECT pg_relation_size(relid) AS tablesize,schemaname,relname,n_live_tup
       FROM pg_stat_user_tables WHERE relname = 'album';

        n_live_tup 대비 tablesize가 너무 크면 가비지 데이터가 정리가 안된 상황 

        (vacuum 기준 확인 필요)

    3) slow query 자동 실행 계획 log print

        load 'auto_explain'

        (해당 모듈 load를 위해 shared_preload_libraries = 'auto_explain' 설정)

        SET auto_explain.log_min_duration = '5s';    (5초 이상 소요 되는 sql)

        SET auto_explain.log_analyze = true;

        SELECT count(*) FROM pg_class, pg_index
        WHERE oid = indrelid AND indisunique;        -- log 파일에 실행계획이 남음.

        (pg_stat_plans설치 : https://github.com/2ndQuadrant/pg_stat_plans)

 

14. 버그 분석

     반복적이고 재현 되는 문제에 대해서 pg_dump로 dump file 생성

     pageinspect 패키지로 해당 테이블 데이터 분석

    

     아래 사이트에서 해당 문제를 찾아보고 해당 문제에 대해서 질의함

     http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

     http://wiki.postgresql.org/wiki/SlowQueryQuestions

 

 

15. postgresql 로그 관리

     rsyslog 및 logrotate 툴을 사용하여 postgresql log 주기적으로 정리 작업

     (postgresql에 log rotation은  postgresql.conf 파일에  log_rotation_age를 설정)

      => 분석은 pgBadger를 사용하여 분석 

     * postgresql.conf 파일에 로그 정보 출력과 관련된 설정 정보를 설정

 

16.  쿼리 성능 분석

     위에 pg_stat_statements 설치 되어 있는 상태에서

     1) 자주 호출 되는 SQL 순으로 확인

       SELECT query FROM pg_stat_statements ORDER BY calls DESC;

    2) 평균 수행 시간이 높은 순으로 확인

       SELECT query, total_time/calls AS avg, calls FROM pg_stat_statements 
       ORDER BY 2 DESC;

    
     * SQL에 대한 통계 정보를 초기화 하기 위해서 

     select pg_stat_statements_reset() 호출

 

17. 명령어 (/h, /?) 수행시 내부 쿼리 확인방법

    \set ECHO_HIDDEN

    \z test

    SELECT * FROM information_schema.views 
    where table_schema IN ('pg_catlog', 'information_schema');   -- 내부 view 확인

    select 'account'::regclass::oid;  -- oid 조회

    select '17260'::regclass::text;

    

    -- stat 정보 table 및 view 확인

    SELECT relname, case relkind when 'r' then 'table'  
          WHEN 'v' THEN 'VIEW' END as type
     FROM pg_class WHERE relname like 'pg_sta%' AND relkind IN ('r','v');

 

18. 현재 파라미터 셋팅 내용 확인 및 변경

    select current_setting('work_mem');
    show work_mem;

    select set_config('work_mem', '8 MB', true);  --> 로컬 파타미터 값 설정 (false : global 설정)
    select set_config('shared_buffers', '1 GB', false);

    alter system set configuration_parameter { to | = } { value | 'value' | default }
    alter system reset configuration_parameter
    alter system reset all

    SELECT name, current_setting(name), source 
    FROM pg_settings WHERE source IN ('configuration file');

 

19. 데이터 사용량 확인
    1) 데이터베이스 사용량 확인
    SELECT pg_database.datname
         , pg_size_pretty(pg_database_size(pg_database.datname)) AS size 
      FROM pg_database;
 

    select oid from pg_database;

    du -h /data_dir/base/oid
    
    
     2) 테이블 사용량 확인

    SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) 
     FROM pg_tables WHERE schemaname = 'car_portal_app' LIMIT 2;

    

   3) 인덱스 사용량 확인

     SELECT indexrelid::regclass,  pg_size_pretty(pg_relation_size(indexrelid::regclass))  
     FROM pg_index WHERE indexrelid::regclass::text like 'car_portal_app.%' limit 2;

 

 

20. Object 사용여부 확인

     사용하지 않은 테이블 2개 출력

    SELECT relname FROM pg_stat_user_tables WHERE n_live_tup= 0 limit 2;

     SELECT schemaname, tablename, attname 
      FROM pg_stats 
     WHERE null_frac= 1 and schemaname NOT IN ('pg_catalog', 'information_schema') limit 1;

     인덱스가 사용되는지 확인

     SELECT schemaname, relname, indexrelname 
       FROM pg_stat_user_indexes s 
      JOIN pg_index i ON s.indexrelid = i.indexrelid 
      WHERE idx_scan=0 
       AND NOT indisunique 
       AND NOT indisprimary;

    foreign key 확인
    SELECT * FROM pg_constraint WHERE contype = 'f';

 

 인덱스 중복 확인 SQL

WITH index_info AS
(SELECT pg_get_indexdef(indexrelid) AS index_def, indexrelid::regclass index_name , indrelid::regclass table_name, array_agg(attname) AS index_att
FROM
  pg_index i JOIN
  pg_attribute a ON i.indexrelid = a.attrelid
GROUP BY pg_get_indexdef(indexrelid), indrelid,  indexrelid
)
SELECT DISTINCT
  CASE WHEN a.index_name > b.index_name THEN a.index_def ELSE b.index_def END AS index_def,
  CASE WHEN a.index_name > b.index_name THEN a.index_name ELSE b.index_name END AS index_name,
  CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_def,
  CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_name,
  a.table_name
FROM
  index_info a 
INNER JOIN index_info b ON (a.index_name != b.index_name AND a.table_name = b.table_name AND a.index_att && b.index_att );

 

PK 및 Unique 인덱스를 가지고 있지 않은 테이블 확인

SELECT table_catalog, table_schema, table_name
FROM
  information_schema.tables
WHERE
  table_schema NOT IN ('information_schema', 'pg_catalog')
EXCEPT
SELECT
  table_catalog, table_schema, table_name
FROM
  information_schema.table_constraints
WHERE
  constraint_type IN ('PRIMARY KEY', 'UNIQUE') AND
  table_schema NOT IN ('information_schema', 'pg_catalog');


중복된 키를 테이블에서 삭제.
with should_not_delete as (
  SELECT min(ctid) FROM duplicate group by f1, f2
) DELETE FROM duplicate WHERE ctid NOT IN (SELECT min FROM should_not_delete);

CREATE TABLE <tmp> AS SELECT DISTINCT * FROM <orig_tbl>;
DROP TABLE <orig_tbl>;
ALTER TABLE <tmp> RENAME TO <orig_tbl>;


DELETE FROM dup_table a USING dup_table b
WHERE a.tt1 = b.tt1 AND ... AND b.attn= b.attn
AND a.pk < p.pk.

 

21. 데이터베이스 lock 관리

lock를 가지고 있는 쿼리 확인

\x

SELECT
  lock1.pid as locked_pid,
  stat1.usename as locked_user,
  stat1.query as locked_statement,
  stat1.state as state,
  stat2.query as locking_statement,
  stat2.state as state,
  now() - stat1.query_start as locking_duration,
  lock2.pid as locking_pid,
  stat2.usename as locking_user
FROM pg_catalog.pg_locks lock1
     JOIN pg_catalog.pg_stat_activity stat1 on lock1.pid = stat1.pid
     JOIN pg_catalog.pg_locks lock2 on
  (lock1.locktype,lock1.database,lock1.relation, lock1.page,lock1.tuple,lock1.virtualxid, lock1.transactionid,lock1.classid,lock1.objid, lock1.objsubid) 
  IS NOT DISTINCT FROM
        (lock2.locktype,lock2.DATABASE, lock2.relation,lock2.page, lock2.tuple,lock2.virtualxid, lock2.transactionid,lock2.classid, lock2.objid,lock2.objsubid)
 JOIN pg_catalog.pg_stat_activity stat2 on lock2.pid = stat2.pid
WHERE NOT lock1.granted AND lock2.granted;

 

22. 메모리 설정

  1) shared buffers (shared_buffers, default 32MB) : 전체 메모리에 25% 설정

  2) working memory (work_mem, default 1MB) : work_mem * 전체 커넥션수

  3) checkpoint_segments : 값을 높이면 쓰기 성능이 좋아지고, 낮추면 복구 시간이 빨라짐.

  4) effective_cache_size : disk caching, 전체에 50 ~ 70%

  5) random_page_cost (default 4.0) : ssd, san 3, 1.5 ~ 2.5