명상하는 삶

1. DB파라미터 설정 값 확인

# pg_settings 확인
select name, setting, boot_val, reset_val, unit from pg_settings order by name;


2. DBMS 오브젝트 정보 확인

# 테이블 리스트 확인
select table_name from information_schema.tables where table_schema='public';

# 테이블 컬럼 정보 확인
select 

table_name, column_name, data_type, ordinal_position

from information_schema.columns

where table_schema='public' order by table_name, ordinal_position;

# 인덱스 정보 확인
select 

a.tablename, a.indexname, b.column_name 

from pg_catalog.pg_indexes a, information_schema.columns b 

where a.schemaname = 'public' and a.tablename = b.table_name;


3. 트랜젝션 정보 확인

# 접속된 사용자 확인
SELECT pid, datname, usename, query FROM pg_stat_activity;

# Active 세션 확인
SELECT datname, usename, state, query 

FROM pg_stat_activity 

WHERE state = 'active';

# 현재 실행중인 SQL 상태 정보 확인
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;

# query를 process title에 보이도록 설정
# postgresql.conf
update_process_title = on

# Wait 또는 Blocking 되는 세션 확인
SELECT datname, usename, query FROM pg_stat_activity WHERE waiting = true;

-- soreep: waiting 컬럼이 없음.

# 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;

-- soreep: WHERE w.waiting을 빼야 정상적으로 실행됨.

# Lock 발생 쿼리 확인
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;

# Long run SQL 확인
SELECT 

pid, age(query_start, clock_timestamp()), usename, query 

FROM pg_stat_activity 

WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 

ORDER BY query_start desc limit 50;


4. 사용량 정보 확인

# 데이타베이스 사용량확인
SELECT 

pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size 

FROM pg_database;

# 테이블 스페이스 사이즈 확인
select 

spcname, pg_size_pretty(pg_tablespace_size(spcname)) 

from pg_tablespace;

# 테이블 사이즈 확인
SELECT 

tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) 

FROM pg_tables 

where schemaname NOT IN ('utl_file','information_schema','pg_catalog');

# 인덱스 사이즈 확인
SELECT 

indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid::regclass)) 

FROM pg_index 

where indexrelid > 16000;


5. Session 관리

# Session kill
# 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 > '10min';

# 현재 세션을 제외한 모든 세션 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';


6. SQL PLAN 분석

# Plan 분석
# postgresql에 세션 kill 아래 3가지 순서로 차례로 진행
# postgresql.conf 수정
session_preload_libraries = 'auto_explain’ 

# 수행  SQL     
LOAD 'auto_explain';  
SET auto_explain.log_min_duration = 0;  
SET auto_explain.log_analyze = true;

SELECT count(*) 

FROM pg_class, pg_index 

WHERE oid = indrelid AND indisunique;


7. 통계정보분석

# 통계분석
# postgresql.conf 수정
shared_preload_libraries = 'pg_stat_statements' 
pg_stat_statements.max = 10000 
pg_stat_statements.track = all 

# 수행 SQL
SELECT 

query, calls, total_time, rows 

FROM pg_stat_statements 

ORDER BY total_time DESC LIMIT 10;

# Disk 사용량 기준 Top 50 SQL 추출
SELECT *
from pg_stat_statements

order by local_blks_read, local_blks_written, shared_blks_read, shared_blks_written

desc limit 50;

# 실행 시간 기준 Top 50 SQL 추출
SELECT *
from pg_stat_statements

order by total_time, min_time, max_time, blk_read_time, blk_write_time

desc limit 50;

# 전체 사용중인 Datafile 의 Disk Read, Write 양 확인
select * from pg_stat_database;

8. Vacuum 관련

# Vacuum 을 하는 이유
데이터는 물리적으로 디스크에 저장되고 읽어서 보여주는데, 데이터를 갱신(UPDATE) 혹은 삭제 (DELETE) 시에 디스크에 있던 기존 정보를 갱신하거나 삭제하지 않습니다. 기존 정보는 변경되었다는 표시를 남기고 새롭게 디스크에 갱신(UPDATE)된 정보를 기록합니다. 삭제(DELETE) 했어도 디스크 용량은 줄어들지 않으며 갱신(UPDATE) 시에는 새로운 행이 추가되기 때문에 디스크 용량이 증가하게 됩니다. 이런 개념은 MVCC(다중 버전 동시성 제어) 구현에 따른 Tuple 개념때문에 그렇습니다. 다시 쉽게 말하면 트랜잭션(Transaction)을 사용하기 위해서 기존 데이터를 변경하지 않고 보관하게 된 거라고 생각하면 됩니다. 즉 그 말은 UPDATE, DELETE, Transaction 이벤트가 많아질 수록 Dead Tuple 발생에 따른 Disk I/O 증가가 성능 저하라는 결과를 가져오게 됩니다.

# Vacuum 기대 효과
PostgreSQL의 모든 정보는 pg_catelog에 쌓이고 있는데, 튜플에 대한 정보도 가지고 있으며 그 정보를 기반으로 쿼리 플랜(Query Plan)에도 활용하고 있습니다. 잘 정리된 책상에서 쉽게 물건을 찾을 수 있듯 주기적인 Vacuum 실행은 성능 향상을 가져옵니다.

# Vacuum 명령어

-- DB 전체 풀 실행
vacuum full analyze;

-- DB 전체 간단하게 실행
vacuum verbose analyze;

-- 해당 테이블만 간단하게 실행
vacuum analyse [테이블명];

-- 특정 테이블만 풀 실행
vacuum full [테이블명];


# Autovacuum이 vacuum 가능한 대상으로 분류하는 테이블 목록 확인
WITH vbt 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) 
autovacuum_freeze_max_age
    , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) 
+ coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * 
pg_table_size(c.oid)) 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 vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and 
c.oid = cvbt.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(cvbt.value::float, autovacuum_vacuum_threshold::float) + 
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * 
pg_table_size(c.oid) <= n_dead_tup
   -- or 1 = 1
)
ORDER BY age(relfrozenxid) DESC LIMIT 50;


# 수동으로 Full Vacuum 이 필요한 대상 테이블 확인 방법
select datname, age(datfrozenxid) from pg_database order by age(datfrozenxid) desc limit 20;

데이터베이스 수명이 20억에 도달하면 TransactionID (XID) wraparound가 발생하고 데이터베이스가 읽기 전용 상태가 됩니다. 이 쿼리를 사용하면 측정치를 생성하고 쿼리가 하루에 몇 번 실행되도록 할 수 있습니다. 기본적으로 autovacuum은 트랜잭션 수명을 200,000,000(age) 미만으로 유지하도록 설정 됩니다.

# 현재 Autovacuum 실행 상태 확인
SELECT 

datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query 

FROM pg_stat_activity 

WHERE upper(query) like '%VACUUM%' 

ORDER BY xact_start;

datname 이 autovauum 인 session이 확인되고 xact_runtime은 autovacuum 실행 시간을 나타냅니다. autovacuum 세션이 오래(며칠 간) 실행될 수 있지만 이 문제는 대부분 maintenance_work_mem 파라미터 값이 테이블 크기 또는 업데이트 속도에 대해 너무 낮게 설정된 경우입니다.
산정 공식 :  GREATEST({ DBInstanceClassMemory / 63963136 * 1024 }, 65536 )


9. 그 밖에...

가) 테이블의 테이블스페이스를 변경하는 경우

기존 테이블 명  : t1
테이블 스페이스를 변경할테이블 명 : t2 

ㄱ. 새로운 테이블 스페이스에 새로운 테이블로 데이터 이관
create table t2 tablespace test_space as select * from t1;

ㄴ. 기존 테이블 테이블 네임 변경
ALTER table t1 RENAME TO t1_bk;

ㄷ. 새로 생성한 테이블 네임 변경
ALTER table t2 RENAME TO t1;

ㄹ. 테이블 데이터 확인 후 t1_bk 테이블 삭제
drop table t1_bk;

☞ 단 인덱스는 수동으로 생성해야 합니다.


나) Index의 테이블스페이스 위치를 바꿔주는 Alter 는 없습니다.
-> 인덱스를 새로운 테이블 스페이스로 지정하여 재생성을 해주세요

다) 지정 Tablespace가 바라보는 논리적 Device명, Tablespace속한 Datafile list 
-> 오라클과 PostgreSQL은 데이터 파일 관리 구조가 달라 Datafile list (이하 하위 디렉토리) 는 수시로 달라질 수 있습니다. Datafile list 를 제외한 정보는 아래의 SQL을 통하여 확인이 가능합니다.

SELECTCASE
WHEN 
    pg_tablespace_location(oid)='' AND spcname='pg_default'
THEN
    current_setting('data_directory')||'/base/'
WHEN 
    pg_tablespace_location(oid)='' AND spcname='pg_global'
THEN
    current_setting('data_directory')||'/global/'
ELSE
    pg_tablespace_location(oid)
END

AS spclocation, spcname 
FROM pg_tablespace;