기록지

ALTER ROLE root SET search_path = "$USER" ,shard ;

-- Vacuum threshold(autovacuum 임계값) = vacuum base threshold + vacuum scale factor * number of tuples 

with     avt as ( 
	select setting as autovacuum_vacuum_threshold 
	from pg_settings 
	where name = 'autovacuum_vacuum_threshold' 
)
       , vsf as ( 
	select setting as autovacuum_vacuum_scale_factor 
	from pg_settings 
	where name = 'autovacuum_vacuum_scale_factor' 
)
       , fma as ( 
	select setting as autovacuum_freeze_max_age
	from pg_settings
	where name = 'autovacuum_freeze_max_age' 
)
      , sto as ( 
	select opt_oid, 
	            split_part(setting, '=', 1) as param, 
		 split_part(setting, '=', 2) as value 
	from (
		select oid opt_oid, 
		       unnest(reloptions) setting
		from pg_class
		) opt 
) 
select '''' || ns.nspname || '.' ||c.relname|| '''' as relation, 
           pg_size_pretty(pg_table_size(c.oid)) as table_size, 
           age(relfrozenxid) as xid_age,
           coalesce(cfma.value::float, autovacuum_freeze_max_age::float) as autovacuum_freeze_max_age,
           (coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + 
           coalesce(cvsf.value::float, autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples, 
	    n_dead_tup as dead_tuples
from pg_class c 
join pg_namespace ns 
on   ns.oid = c.relnamespace 
join pg_stat_all_tables stat 
on   stat.relid = c.oid 
join avt 
on   (1=1)
join vsf  
on   (1=1) 
join fma 
on   (1=1) 
left join sto cavt
on   cavt.param = 'autovacuum_vacuum_threshold'
and  c.oid = cavt.opt_oid 
left join sto cvsf 
on   cvsf.param = 'autovacuum_vacuum_scale_factor'
and  c.oid = cvsf.opt_oid 
left join sto cfma 
on cfma.param = 'autovacuum_freeze_max_age'
and c.oid = cfma.opt_oid 
where c.relkind = 'r' and nspname <> 'pg_catalog' 
and ( 
	age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
	or 
	coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + 
	coalesce(cvsf.value::float, autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup
    )       
order by age(relfrozenxid) desc ;

Create a file called ~/.psqlrc and add the line:

\timing

fsync = off

full_page_writes=off

wal_level = minimal

synchronouns_commit=off

autovacuum=off

CREATE TABLE t1 AS 
SELECT gs as idx, '테스트 문자열' || gs AS test_string, md5(random()::text) AS random_string 
FROM generate_series(1, 1000000) AS gs;

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

Postgresql 기본 모니터링을 위해선 다음과 같은 view를 조회한다.

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    | 11111    | 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이 없음

-- 일자 확인 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. 템프 데이터 사용률 확인

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)함수로 확인

Memory 관련 parameter

• shared_buffer

 
PostgreSQL은 자체 버퍼를 사용하며 커널 버퍼 IO도 사용합니다. 이는 데이터가 메모리에 두 번 저장된다는 것을 의미하며, 먼저 Postgres에 저장됩니다. SQL 버퍼 다음으로 커널 버퍼입니다. 다른 데이터베이스와 달리 PostgreSQL은 직접 IO를 제공하지 않습니다. 이를 이중 버퍼링이라고 합니다. PostgreSQL은 버퍼를 shared_buffer라고 하며 대부분의 운영 체제에서 가장 효과적인 조정 가능 매개 변수입니다. 이 매개 변수는 PostgreSQL은에서 사용할 전용 메모리의 양을 설정합니다 캐시에 대한 파라미터로 디스크의 엑세스를 줄여 성능을 확보할 수 있는 파라미터 입니다.
 
shared_buffer의 기본값은 매우 낮게 설정되어 있으므로 큰 이점을 얻을 수 없습니다. 특정 시스템과 운영 체제가 더 높은 값을 지원하지 않기 때문에 이 값이 낮습니다. 그러나 대부분의 최신 시스템에서는 최적의 성능을 위해 이 값을 늘려야 합니다.
 
권장 값은 총 시스템 RAM의 25%입니다. 경우에 따라 25% 이상의 설정으로 양호한 성능을 얻을 수 있으므로 더 낮은 값과 더 높은 값을 시도해야 합니다. 구성은 실제로 시스템과 작업 데이터 세트에 따라 달라집니다. 작업 중인 데이터 세트가 RAM에 쉽게 들어갈 수 있는 경우, 전체 데이터베이스를 포함하도록 shared_buffer 값을 늘려 전체 작업 데이터 세트가 캐시에 상주할 수 있도록 할 수 있습니다. 그렇긴 해도, 당신은 Postgre를 위해 모든 RAM을 사용하고 싶진 않을 겁니다. 그리고 PostgreSQL은 캐싱(이중 버퍼링)을 위해 운영 체제에 크게 의존합니다. 따라서 shared_buffers에 RAM의 40% 이상을 할당할 경우, 더 작은 값을 할당하는 경우에 비해 더 좋은 성능을 발휘할 가능성이 낮습니다.
 
운영 환경에서는 shared_buffer 값이 크면 성능이 매우 우수하지만 항상 벤치마크를 통해 적절한 균형을 찾아야 합니다.
 

• Default : 128MB
• 산정 방법 : 서버 메모리의 1/4~1/2
• 설정 주의 : 장시간에 걸쳐 대량의 데이터 또는 변경된 데이터 쓰기 프로세스를 실행하기 위해 shared_buffers를 더 크게 설정하면 checkpoint_segments(10미만 버전),

                       max_wal_size(10이상) 에서도 그에 맞게 설정을 증가시켜야 합니다.
               * PostgreSQL 10부터 checkpoint_segments가 max_wal_size로 대체되었습니다.
 
 
shared_buffer 확인
 

testdb=# SHOW shared_buffers;

shared_buffers

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

128MB0000000000000

(1 row)

• wal_buffers

 
PostgreSQL은 WAL(Write Ahead Log) 레코드를 버퍼에 기록한 다음 이러한 버퍼를 디스크에 플러시합니다. wal_buffer는 wal 로그를 메모리에 저장할 수 있는 크기를 지정한다.
Default : -1 ( 4MB) shared_buffers에 따라 자동적으로 설정된다. , shared_buffers가 128MB 면 4MB로 설정된다. ( shared_buffers / 32 )
* PostgreSQL 10부터 shared_buffers 크기에 따라 자동적으로 설정이 되기 때문에 설정해 줄 필요가 없어졌습니다.
 

• work_mem

 
이 파라미터는 sort, merge, join 에 사용는 메모리 사용량을 의미합니다. 만약 당신이 복잡한 정렬을 해야 한다면 좋은 결과를 얻기 위해 work_mem의 값을 늘려야 한다. 메모리 내 정렬은 디스크로 유출되는 정렬보다 훨씬 빠릅니다. 이 매개 변수는 사용자 정렬 작업당이므로 매우 높은 값을 설정하면 배포 환경에 메모리 병목 현상이 발생할 수 있습니다. 따라서 정렬 작업을 실행하려는 사용자가 많은 경우 시스템은 모든 사용자에게 (work_mem * 총 정렬 작업 개수)  만큼의 메모리 크기를 할당합니다. 이 매개 변수를 전체적으로 설정하면 메모리 사용량이 매우 높을 수 있습니다. 따라서 세션 수준에서 이를 수정하는 것이 좋습니다.
 

• Default : 4MB
• 산정 방법
  • 방법 1 : ( 시스템 전체 메모리) / ( max_connections * 16 )
  • 방법 2 : ( OS cache memory / max_connections ) * 0.5

testdb=# SET work_mem TO "2MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=509181.84..1706542.14 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=508181.79..514431.86 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)
(5 rows)

초기 쿼리의 정렬 노드의 추정 비용은 514431.86이다. 비용은 임의의 계산 단위이다. 위 쿼리의 경우 work_mem은 2MB밖에 없습니다. 테스트를 위해 256MB로 늘리고 비용에 영향이 있는지 확인해 보겠습니다.
 

testdb=# SET work_mem TO "256MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=355367.34..1552727.64 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=354367.29..360617.36 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)

쿼리 비용이 514431.86에서 360617.36으로 30% 감소했습니다.
 

• maintenance_work_mem

maintenance_work_mem은 Vacuum, Create Index, Alter Table ADD Foreign key와 같은 유지 관리 작업에 사용되는 메모리 설정입니다. 기본값은 64MB입니다. 큰 값을 설정하면 VACUM, RESTORE, CREATE INDEX, ADD Foreign KEY, ALTER TABLE 등의 작업에 도움이 됩니다.
 

• Default : 64MB
• 산정 방법
  • 서버 메모리의 1/16 설정
  • 대형 데이터베이스의 경우 256MB에서 1GB 정도 값을 설정

postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)

postgres=# CHECKPOINT;
postgres=# set maintenance_work_mem to '256MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 111274.903 ms (01:51.275)

maintenance_work_mem을 10MB로 설정한 경우 인덱스 생성 시간은 170091.371ms이지만 maintenance_work_mem 설정을 256MB로 늘리면 111274.903ms로 단축됩니다.

Disk 기록 관련 파라미터

• synchronous_commit

이는 클라이언트에 성공 상태를 반환하기 전에 디스크에 WAL이 기록될 때까지 대기하는 커밋을 시행하는 데 사용됩니다. 이는 성능과 신뢰성 사이의 균형입니다. 애플리케이션이 안정성보다 성능이 더 중요하도록 설계된 경우 synchronous_commit을 해제하십시오. 즉, 성공 상태와 보장된 디스크 쓰기 사이에 시간 차이가 발생합니다. 서버 충돌의 경우 클라이언트가 커밋 시 성공 메시지를 수신했음에도 데이터가 손실될 수 있습니다. 이 경우 트랜잭션은 WAL 파일이 플러시될 때까지 기다리지 않기 때문에 매우 빠르게 커밋되지만 신뢰성은 저하됩니다.
 

• Default : on

• checkpoint_timeout, checkpoint_completion_target

PostgreSQL은 WAL에 변경 사항을 기록합니다. 체크포인트 프로세스는 데이터를 데이터 파일로 플러시합니다. 이 활동은 CHECKPOINT가 발생할 때 수행됩니다. 이는 비용이 많이 드는 작업이며 엄청난 양의 IO를 유발할 수 있습니다. 이 전체 프로세스에는 값비싼 디스크 읽기/쓰기 작업이 수반됩니다. 사용자는 필요할 때마다 CHECKPOINT를 발행하거나 checkpoint_timeout 및 checkpoint_completion_target 매개 변수를 통해 자동화할 수 있습니다.
 
checkpoint_timeout 매개 변수는 WAL 체크포인트 사이의 시간을 설정하는 데 사용됩니다. 이 값을 너무 낮게 설정하면 Disk에 더 많은 데이터가 기록되므로 충돌 복구 시간이 단축되지만 모든 체크포인트가 귀중한 시스템 리소스를 사용하게 되므로 성능도 저하됩니다.
 
  checkpoint_completion_target은 체크포인트 완료를 위한 체크포인트 사이의 시간 비율입니다. 대량의 페이지 쓰기로 I/O 시스템이 플러딩되는 것을 방지하기 위해 체크포인트 동안 더티 버퍼 쓰기는 일정 기간 동안 분산됩니다. 지정된 checkpoint_timeout 초의 일부가 경과하거나 max_wal_size가 초과되기 전 중 더 빠른 시점에 체크포인트가 완료되도록 I/O 속도가 조정됩니다. 체크포인트의 빈도가 높으면 성능에 영향을 줄 수 있습니다. 원활한 체크포인트를 위해 checkpoint_timeout은 낮은 값이어야 합니다. 그렇지 않으면 OS는 비율이 충족될 때까지 모든 더티 페이지를 축적한 다음 큰 플러시를 수행합니다.
 

• Default
• checkpoint_timeout : 5min
• checkpoint_completion_target : 0.5

• fsync

이 매개 변수가 설정되어 있으면 PostgreSQL 서버는 fsync() 시스템 호출 또는 이와 동등한 다양한 메소드를 실행하여 업데이트가 실제로 디스크에 기록되도록 합니다. 이를 통해 운영 체제 또는 하드웨어 충돌 후 데이터베이스 클러스터가 일관된 상태로 복구 될 수 있습니다.
 
fsync= on - PostgreSQL서버는 fsync()시스템 콜을 통해서 변경분을 디스크에 물리적으로 바로 씁니다. 이는 데이터베이스클러스터가 OS나 하드웨어 장애시 consistent한 상태로 복구가 가능함을 보장합니다.
fsync= off - OS가 알아서 메모리에 있는 것을 디스크로 내려쓰게 됩니다. 성능상 이득을 볼수는 있겠지만, 전원장애나 system crash로가 발생했을때 복구가 불가능할 수도 있습니다. 외부 데이터로 전체 데이터베이스를 손쉽게 재생성할 수 있는 경우에만 fsync를 해제하는 것이 바람직합니다.
 

• Default : on
• 설정 주의만약 off하기로 했다면, full_page_writes도 off하는 것을 고려하도록 합니다.

• full_page_writes
• full_page_writes = on

• PostgreSQL 서버는 checkpoint 이후의 각 디스크 페이지를 처음 수정하는 도중에 해당 페이지의 전체 내용을 WAL에 기록합니다.
• 운영 체제 충돌 시 진행 중인 페이지 쓰기가 부분적으로만 완료되어 디스크 상의 페이지에 옛날 데이터와 새 데이터가 공존할 수 있기 때문에 필요합니다.
• 전체 페이지 이미지를 저장하면 페이지의 올바른 복구가 보장되지만 WAL에 기록해야 하는 데이터량의 증가를 감수해야 합니다.

• full_page_writes = off

• 정상적인 운영 속도가 빨라지지만 시스템 장애 발생 시 손상된 데이터가 복구 불가능하게 되거나 데이터 손상이 드러나지 않을 수 있습니다.

• Default : on

Database 접속 세션 수 관련

• max_connections

데이터베이스 서버에 대한 최대 동시 연결 수를 결정합니다.

• Default : 100

Optimizer 관련 파라미터

• random_page_cost

PostgreSQL 옵티마이저가 디스크에서 임의 페이지를 읽는 비용을 추정하고 인덱스 또는 순차 스캔의 사용을 결정할 수 있습니다. 값이 높을수록 순차 스캔이 더 많이 사용됩니다. 값이 낮을 수록 인덱스를 사용할 가능성이 높아집니다.
 

• Default : 4
• 설정 주의

• effective_cache_size

effective_cache_size는 단일 쿼리에 대해 쿼리당 데이터 캐싱을 위한 총 메모리 양을 결정합니다. 다시 말해 디스크 캐시의 효율적인 크기 (캐시할 수 있는 메모리 양) 이것은 정확하게 할당된 메모리나 캐시 크기가 아닌 지침일 뿐이다. 실제 메모리를 할당하지는 않지만 커널에서 사용 가능한 캐시의 양을 옵티마이저에 알려준다. 이 값이 너무 낮게 설정된 경우 쿼리 플래너는 일부 인덱스가 유용하더라도 사용하지 않기로 결정할 수 있습니다. 따라서 큰 값을 설정하는 것이 항상 유익합니다.
 

• Default : 4GB
• 산정 방법
• 설정 주의

• cpu_tuple_cost

쿼리 중 각 행을 처리하는 플래너의 추정 비용을 설정합니다. 기본값은 0.01입니다.

• Default : 0.01

• cpu_index_tuple_cost

인덱스 스캔 중 각 인덱스 항목을 처리하는 비용의 플래너의 추정치를 설정합니다.

• Default : 0.005

• cpu_operator_cost

플래너가 예상한, 쿼리 도중 실행된 각 연산자 또는 함수의 처리 비용을 설정한다.

• Default : 0.0025

실행계획  관련 파라미터

• default_statistics_target

Postgresql은 개별 테이블 통계가 필요합니다. ALTER TABLE SET STATISTICS를 통해 설정 하면 테이블 컬럼에 대한 기본 통계 타겟을 설정한다. 큰 값을 설정 하면 ANALYZE를 수행하는 데 필요한 시간이 늘어나지만 플래너 평가 수준을 높일 수 있다.

• Default : 100
• 설정 주의

• track_count

데이터베이스 작업에 대한 통계 수집을 활성화 합니다.

• Default : on
• 설정 주의

 
결론
더 나은 성능을 얻기 위해 조정할 수 있는 더 많은 매개 변수가 있지만 여기서 강조한 매개 변수보다 영향이 적습니다. 마지막으로, 모든 매개 변수가 모든 애플리케이션 유형과 관련이 있는 것은 아니라는 점을 항상 염두에 두어야 합니다. 또한 데이터베이스 매개 변수는 응용 프로그램 및 응용 프로그램이 실행되는 OS의 특정 요구에 대해 수행되어야 합니다.
 
 

우선 PostgreSQL은 MySQL/MariaDB와는 다르게, 기동 시 여러 개의 필수 프로세스들이 같이 기동되며 수행된다.(Oracle과 유사)  또한 세션 또한 스레드 단위가 아닌 프로세스 단위로 할당을 받는다.

PostgreSQL Engine은 크게 Postmaster, Shared Memory, Backend Memory, Utility Process 영역으로 구성된다. 

세부적은 기능은 아래와 같다. 

   1. Postmaster 

       PostgreSQL 프로세스들의 최상위 프로세스다. Oracle의 Listener처럼 외부 유저 혹은 어플리케이션의 접속 요청을 받

       아 개별 프로세스(Oracle의 서버 프로세스)를 부여하는 데몬 프로세스이다. 

       또한 최상위 프로세스답게 하위 프로세스들의 비정상 작동 유무 등도 체크하며, 하위 프로세스가 강제 종료등의 문제

       가 발생시, 이를 다시 재기동 시켜줄 수 있는 기능도 있다. (즉, Oracle의 PMON 역할을 겸한다.) 

   2. Shared Memory 

       모든 세션들이 공유하여 사용하는 공간이다. Oralce의 SGA와 유사하다. Shared Memory는 아래의 요소들이 있다.

       - Shared Buffer : 데이터 영역에서 참조된 데이터를 저장하는 공간이다. 즉, 일반적인 RDBMS  작업에서 사용되는 

         메모리 버퍼 캐시 영역이다. 공식 Doc에서는 서버 메모리의 25%를 할당하는것을 권장한다. 

       - WAL Buffer(Write Ahead Log Buffer) : 데이터가 변경 될 시, 이에 대하여 변경된 내역을 저장하는 공간, Crash 발생

         시, 복구에 쓰인다. WAL은 다른 DB에서도 사용되는 개념이나, PostgreSQL에서는 명시적으로 Redo Log영역에 대

         대응하는 이름으로 쓴다. 

       - CLOG Buffer : 트랜잭션 상태 정보를 저장하는 공간, 각 트랜잭션의 commit등의 상태값을 저장

       - Lock Space : 트랜잭션 간의 lock 정보를 저장하는 공간

       - Other Buffers : 위의 주요 공간이 외에 다른 기능들을 처리하는 공간들이 있는 곳이다. 통계정보, two-phase-commit

         등의 버퍼공간이 있다. 

    3. Backend Memory 

        각 세션들이 할당 받아 사용하는 공간이다. Oracle의 PGA와 유사하다. Backend Memory는 아래의 요소들이 있다. 

        - Maintenance_work_mem : Vaccum, Create Index 등의 작업을위해 사용하는 공간이다. 파라미터를 통한여 기동 시

          에 각 세션들마다 동일한 크기의 공간이 할당 되나, 작업을 할 세션 단위로 임의 조절이 가능하다. 

        - temp_buffer : DB에서 사용하는 임시테이블들을 저장하는 공간이다. PostgreSQL에서 내부적으로 작업을 처리 할시

          에 임시적으로 생성하는 테이블이 있을 수 있는데 이를 위해서 마련된다. 

        - work_mem : Order by, Distinct 등의 정렬과 관련된 작업, Join, Hash Table 작업을 위해서 사용하는 공간이다. 

          실제 각 세션별 데이터 작업들에는 sort 작업이 많은 편이기 때문에 이 영역도 파라미터 조절을 통해 공간을 확보 해

          주면 좋다. (실제 메모리 영역에 대비하여 필수적으로 계산을 해주는 영역 중 하나다.) 

        - catalog cache : PostgreSQL에서는 DB 내 모든 영역들에 대한 메타데이터를 pg_catalog에 보관하며, Catalog Cac

          he 또한 이를 위해서 존재 하는 공간이다. 

        - optimizer, executor : MySQL, MariaDB 와 동일하게 수행할 쿼리들에 대한 최적의 실행 계획 수립(Optimizer) 및 실

          행 계획에 따른 실행(Executor)을 담당하는 영역이다. 

     4. Utility Process 

        - Writer : Backgroud Writer로 명칭하기로 한다. Shared Buffer에 변경 된 데이터들은 디스크(DB파일)에 써 내리는 역

          할을 맡는다. 타 RDBMS와 같이 Write Ahead Log Writing(선행 기입법)에 근거하여 변경된 내용을 수시로 쓰지 않고

          모아 두면 WAL Writer가 먼저 WAL file에 먼저 지속적으로 기록하다가 특정 시점이 되면 일괄로 디스크에 데이터를

          기록하고 메모리영역에 있는 데이터를 다 써 내렸다는 기록을 DB 내부에 기록 해 둔다. 이를 checkpoint라고 한다. 

        - Wal Writer : wal buffers에 기록 되는 DB트랜잭션, 변경 내역을 디스크(wal file)에 기록하는 프로세스, 디스크에 기록

          하는 조건은 commit 및 로그 파일 공간을 모두 다 채웠을 때다. (관련된 파라미터를 통해 기록 시간을 조절 가능) 

        - checkpointer : 9.2부터 추가된 프로세스, 기존 checkpoint 역할을 수행 하던 wirter 대신하여 checkpoint를 수행한

          다. 

        - archiver : 백업을 통한 시점 복구를 위해서 마련하는 아카이브 파일을 기록하는 프로세스, 파라미터에서 설정이 가

          능한데, on/off를 선택할 수 있으며 기본은 off다. 보통 off는 개발, 테스트 같이 시점 복구등이 전혀 필요없는 사항에 

          설정되고 on 설정은 운영서버에서 필수적으로 적용해야 한다고 보면 된다. (이 설정도 Oracle Archive와 매우 유사하

          다.)

        - logging collector : 시스템 내 시스템 로그들을 기록하는 프로세스, 9.6 버전까지는 default가 pg_log 디렉토리에 로그

          파일 생성 및 내용들을 기록하였으나 10 이후부터는 디렉토리로 비뀌었다.

        - stats collector : 통계 수집을 담당하는 프로세스, archive 같이 on/off 설정이 가능하다. 자체적으로 수집 시점을 탐지

          하다 특정 기록 시점이라 판단되면 통계 데이터를 수집한다. 

        - autovacuum launcher : vacuum 대상 및 시점에 대해서 인지 하다가 vacuum 작업이 필요하다 판단 되면 자동으로 

          vacuum을 수행시켜 주는 프로세스, 파라미터를 통하여 vacuum을 수행 할 세션 개수를 조절 가능하며, 해당 작업을 

          수행하는 세션은 작업 완료 후 자동 종료 된다. 

1. PostgreSQL RPM 다운로드 

yum -y install  https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. PostgreSQL12 설치 

yum -y install postgresql12-server

3. 전용 user 생성 

useradd postgres

4. 환경변수 설정

 vi ./.bash_profile

PGHOME='/usr/pgsql-12'
PGDATA='/home/postgres/pgdata'
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/pgsql-12/bin
     
export PATH

source ./.bash_profile

5. PGDATA 디렉토리 생성 

cd /home/postgres
mkdir pgdata

6. Cluster 생성 

[postgres@localhost ~]$ initdb -D $PGDATA
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D /home/postgres/pgdata -l logfile start

7. Postgresql DB start 

[postgres@localhost ~]$ pg_ctl start -D $PGDATA
waiting for server to start....2022-05-02 10:27:43.366 KST [2533] LOG:  starting PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-02 10:27:43.369 KST [2533] LOG:  listening on IPv6 address "::1", port 5432
2022-05-02 10:27:43.369 KST [2533] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-02 10:27:43.376 KST [2533] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-05-02 10:27:43.385 KST [2533] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-02 10:27:43.403 KST [2533] LOG:  redirecting log output to logging collector process
2022-05-02 10:27:43.403 KST [2533] HINT:  Future log output will appear in directory "log".
 done
server started

8. Login 

[postgres@localhost ~]$ psql -U postgres
psql (12.10)
Type "help" for help.

postgres=#