ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • PostgreSQL 모니터링
    Database/PostgreSQL 2018.12.31 23:19



    PostgreSQL 모니터링


    pg_catalog : tables, views, functions, indexes, fdw, trigger, constraints, rules, users, groups 등등 정보 저장

    pg_attribute : 테이블 및 다른 pg_class object columns에 대한 정보 저장

    pg_index : 인덱스에 대한 정보 저장

    pg_depend : object dependent에 대한 정보

    pg_rewrite : 테이블 및 view 에 대한 rules 정보 

    pg_constraint : 테이블에 constraint 정보 

    pg_stat_statements : db에서 수행된 쿼리에 대한 통계 정보

    pg_buffercache : cache에 대한 deeper 한 정보

    information_schema : 메타 정보를 담고 있는 view



    1. 접속된 사용자 확인


    SELECT datname, usename, client_addr, client_port, application_name FROM pg_stat_activity;


    5초 간격으로 위에 SQL를 수행 => \watch 5



    2. Active 세션 확인


    SELECT datname, usename, state, query FROM pg_stat_activity WHERE state = 'active';


    * 세션에 대한 detail한 분석은 pg_stat_statement 설치 (real-time performance for query)



    3. long 실행 쿼리 확인


    SELECT

        current_timestamp - query_start AS runtime,

        datname, usename, query

    FROM pg_stat_activity 

    WHERE state = 'active' ORDER BY 1 DESC;



    1분 이상 실행되는 쿼리 확인


    SELECT

        current_timestamp - query_start AS runtime,

        datname, usename, query

    FROM pg_stat_activity

    WHERE state = 'active'

        AND current_timestamp - query_start > '1 min'

    ORDER BY 1 DESC;



    4. query를 process title에 보이도록 설정 (postgresql.conf)


      update_process_title = on



    5. wait 또는 blocking 되는 세션 확인


    SELECT datname, usename, query FROM pg_stat_activity WHERE waiting = true;




    6. query block user 찾기


    SELECT

        w.query AS waiting_query,

        w.pid AS waiting_pid,

        w.usename AS waiting_user,

        l.query AS locking_query,

        l.pid AS locking_pid,

        l.usename AS locking_user,

        t.schemaname || '.' || t.relname AS tablename

     FROM pg_stat_activity w

           JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted

           JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted

           JOIN pg_stat_activity l ON l2.pid = l.pid

           JOIN pg_stat_user_tables t ON l1.relation = t.relid

     WHERE w.waiting;



    7. kill session


      postgresql에 세션 kill 아래 3가지 순서로 차례로 진행하면 됨 (1이 안되면 2, 2 안되면 3)


      1) pg_cancel_backend(pid)          -- current query kill and not disconnect

      2) pg_terminate_backend(pid)        -- connection disconnect. 

      3) kill -9 process



      10분동안 유휴 상태인 세션 kill


      SELECT pg_terminate_backend(pid) FROM pg_stat_activity

      WHERE state = 'idle in transaction' AND current_timestamp - query_start > '10 min';



      현재 세션을 제외한 모든 세션 kill.


      SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();



      작업을 위해 해당 데이터베이스 접속을 막음.


      UPDATE pg_database set datallowconn = 'false' WHERE datname = 'database to drop';



    8. long 쿼리 수행시 timeout 설정


    set statement_timeout to '10 s'


    select ~~ from ~ : long query..



    9.  2pc 사용시 lock 확인 


    SELECT t.schemaname || '.' || t.relname AS tablename,

           l.pid, l.granted

           FROM pg_locks l JOIN pg_stat_user_tables t

           ON l.relation = t.relid;


    아래 결과중 pid가 표시 되지 않는 부분


     tablename | pid | granted 

    -----------+-----+---------

     test1     |     | t

     test1     | 111 | f


     

    10. 테이블 사용량 확인


    초기 사용량 저장


    CREATE TEMPORARY TABLE tmp1_201512 AS  SELECT * FROM pg_stat_user_tables;




    현시점 사용량이 비교하여 수치가 달라진 테이블 출력 


    SELECT * FROM pg_stat_user_tables n

      JOIN tmp1_201512 t

        ON n.relid=t.relid

       AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)

        <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);



    기존 통계 데이터 초기화


    SELECT pg_stat_reset();



    테이블 정보를 snapshot 생성 (cron 또는 pg_agent, bgworker 활용)


    INSERT INTO stat1_201512

    SELECT current_timestamp AS snaptime, * FROM pg_stat_user_tables;




    11. 테이블 마지막 사용 일자 확인


    -- 아래 function 수행


    do $$

             PERFORM table_file_access_info('test1', 'test');

    $$  -- output이 없음



    select table_file_access_info('test1', 'test');


    -- 일자 확인 function 생성


    CREATE OR REPLACE FUNCTION table_file_access_info(

       IN schemaname text, IN tablename text,

       OUT last_access timestamp with time zone,

       OUT last_change timestamp with time zone

       ) LANGUAGE plpgsql AS $func$

    DECLARE

        tabledir text;

        filenode text;

    BEGIN

        SELECT regexp_replace(

            current_setting('data_directory') || '/' || pg_relation_filepath(c.oid),

            pg_relation_filenode(c.oid) || '$', ''),

            pg_relation_filenode(c.oid)

          INTO tabledir, filenode

          FROM pg_class c

          JOIN pg_namespace ns

            ON c.relnamespace = ns.oid

           AND c.relname = tablename

           AND ns.nspname = schemaname;

        RAISE NOTICE 'tabledir: % - filenode: %', tabledir, filenode;

        -- find latest access and modification times over all segments

        SELECT max((pg_stat_file(tabledir || filename)).access),

               max((pg_stat_file(tabledir || filename)).modification)

          INTO last_access, last_change

          FROM pg_ls_dir(tabledir) AS filename

          -- only use files matching <basefilename>[.segmentnumber]

         WHERE filename ~ ('^' || filenode || '([.]?[0-9]+)?$');

    END;

    $func$;




    12. TEMP 데이터 사용률 확인


    1) 별도 temporary 테이블스페이스 사용 확인


     SELECT current_setting('temp_tablespaces');  -- 결과가 없으면 default tablespace 사용



    2) 1의 결과가 나오면 다음 SQL를 사용하여 사용량 확인


    WITH temporary_tablespaces AS (

      SELECT unnest(string_to_array(

        current_setting('temp_tablespaces'), ',')

      ) AS temp_tablespace

    )   

    SELECT tt.temp_tablespace,

      pg_tablespace_location(t.oid) AS location,

      -- t.spclocation AS location, -- for 9.0 and 9.1 users

      pg_tablespace_size(t.oid) AS size

    FROM temporary_tablespaces tt

    JOIN pg_tablespace t ON t.spcname = tt.temp_tablespace

      ORDER BY 1;


    3) 1의 결과가 안나오면 다음과 같은 방법으로 사용량 확인


      SELECT current_setting('data_directory') || '$PG_HOME/base/pgsql_tmp'

      SELECT datname, temp_files, temp_bytes, stats_reset

      FROM pg_stat_database;



    4) 기타 방법


    - 실제 파일이 있는 디렉토리를 du -sk 로 확인

    - pg_tablespace_location(oid), pg_tablespace_size(oid), pg_tablespace_size(name)함수로 확인



    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 


    'Database > PostgreSQL' 카테고리의 다른 글

    PostgreSQL 아카이브 모드  (0) 2019.01.01
    PostgreSQL 커널 리소스 관리  (0) 2019.01.01
    PostgreSQL 모니터링  (0) 2018.12.31
    PostgreSQL 점검  (0) 2018.12.31
    PostgreSQL 권한 부여 및 해제  (0) 2018.12.31
    PostgreSQL 유저 생성  (0) 2018.12.28

    댓글 0

Designed by black7375.

티스토리 툴바