Louis.Kim 의 제 3막

이 Study 는 기본적으로 [막힘없이 PostgreSQL - 액셈] 을 기본으로 하며, 각종 Open AI 및 공식 홈페이지, The Internals of PostgreSQL 를 참고하여 정리한 글입니다.

https://www.postgresql.org/docs/current/explicit-locking.html

정의

• PostgreSQL Lock은 데이터베이스에서 동시성 제어와 데이터 무결성을 보장하기 위해 사용하는 잠금(락) 메커니즘
• 여러 트랜잭션이 동시에 데이터에 접근하거나 수정할 때 충돌을 방지

Lock level

• Object level lock
  • Object에 대한 변경이 발생할 때 object를 보호하는 역할
  • Shared memory lock space 영역에 저장하며, 최대 동시 접속 수와 Transaction 최대 Lock 개수의 곱으로 정의
    • (max_connectgions + max_jprepared_transactions) * max_locks_per_transaction
  • max_locks_per_transaction
    • default 64
    • 하나의 Transaction 당 최대로 획득할 수 있는 object lock 수
    • 하나의 Transaction 에서 파티션 테이블에 여러개인 파티션을 한번에 Query 하는 경우, 하나의 쿼리에서 Lock 이 조회한 파티션 수만큼 발생 가능성이 존재(유의)
  • pg_catalog.pg_locks view 를 통해 object lock 정보를 확인이 가능
• Row level lock
  • Tuple 에 대한 Lock
  • MVCC 기반으로 작동하기 때문에 write 충돌 방지에만 사용
  • shared memory 에 저장되는 것이 아닌 Page 내부의 Touple version 에 저장
    • 메모리에 저장되는 것이 아니기 때문에, Lock 개수에는 영향을 받지 않음
    • Lock 을 대기하는 프로세스를 대기 큐에 넣을 수 없기 때문에 모니터링이 힘듦
• Memory level lock
  • PostgreSQL에서 말하는 Memory-level lock은 공유 메모리(shared memory) 내에서 이루어지는 경량 잠금(Lightweight Lock, LWLock) 또는 스핀락(spinlock) 계열의 잠금
  • 서버 내부 동기화를 위한 메모리 구조 보호용

Memory level lock 종류

-- 현재 프로세스의 blocking 정보 (간접 확인 가능)
-- 메모리 수준 락은 pg_locks 에 안 나옴
-- row/table-level lock만 기록됨
SELECT * FROM pg_blocking_pids(<pid>);

• Advisory Lock (사용자 정의 Lock)
  • Transaction 외부에서도 사용할 수 있는 Application level 의 lock
  • Table이나 row와 직접 연결되지 않음

-- 세션 단위 Advisory Lock 획득
SELECT pg_advisory_lock(12345);

-- 세션 단위 Lock 해제
SELECT pg_advisory_unlock(12345);

#현재 Lock 확인: pg_locks 뷰와 pg_stat_activity를 조인하여 어떤 세션이 어떤 Lock을 보유/대기 중인지 확인
SELECT a.pid, a.usename, a.datname, a.state, a.query, l.locktype, l.mode, l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE a.state != 'idle';

주요 Lock 종류

Conflicting Lock Modes

-- waiting_pid, waiting_query	대기 중인 쿼리 및 세션 정보
-- holder_pid, holder_query	해당 잠금을 보유 중인 쿼리 및 세션
-- locked_relation	충돌 중인 테이블 이름
-- waiting_mode, holder_mode	잠금 모드 (RowExclusiveLock 등)
-- kill_holder_query	해당 holder 세션을 강제 종료하는 SQL 문

WITH lock_conflicts AS (
  SELECT
    w.pid        AS waiting_pid,
    w.query      AS waiting_query,
    w.usename    AS waiting_user,
    w.application_name AS waiting_app,
    w.state      AS waiting_state,
    w.query_start AS waiting_query_start,
    l1.relation::regclass AS locked_relation,
    l1.mode      AS waiting_mode,
    h.pid        AS holder_pid,
    h.query      AS holder_query,
    h.usename    AS holder_user,
    h.application_name AS holder_app,
    h.state      AS holder_state,
    h.query_start AS holder_query_start,
    l2.mode      AS holder_mode,
    -- Kill query 자동 생성
    'SELECT pg_terminate_backend(' || h.pid || ');' AS kill_holder_query
  FROM
    pg_locks l1
    JOIN pg_stat_activity w ON l1.pid = w.pid
    JOIN pg_locks l2 ON l1.locktype = l2.locktype
                    AND l1.database IS NOT DISTINCT FROM l2.database
                    AND l1.relation IS NOT DISTINCT FROM l2.relation
                    AND l1.page IS NOT DISTINCT FROM l2.page
                    AND l1.tuple IS NOT DISTINCT FROM l2.tuple
                    AND l1.virtualxid IS NOT DISTINCT FROM l2.virtualxid
                    AND l1.transactionid IS NOT DISTINCT FROM l2.transactionid
                    AND l1.classid IS NOT DISTINCT FROM l2.classid
                    AND l1.objid IS NOT DISTINCT FROM l2.objid
                    AND l1.objsubid IS NOT DISTINCT FROM l2.objsubid
                    AND l1.pid <> l2.pid
    JOIN pg_stat_activity h ON l2.pid = h.pid
  WHERE NOT l1.granted AND l2.granted
)
SELECT * FROM lock_conflicts
ORDER BY waiting_query_start;

Multi Transactions

• Shared lock mode는 서로 호환 가능한 lock mode 로, 요청할 경우 Multi transaction을 허용
  • Multixact ID (multi transaction id)를 별도로 적용하여 shared lock상태를 저장
• Tuple 은 어느 시점에 Transaction ID와 Multixact ID가 동일한 값을 가질 수 있음
• Multi transaction 상태는 pgrowlocks extension 을 통해서도 확인 가능
  • pgrowlocks를 조회하면 lock을 획득한 process, lock mode, tuple 정보 및 multi transaction 상태 등을 확인 가능
  • 현재 상태만 확인이 가능하고, 종료되면 확인이 불가능

Tuple lock waiting

• Exclusive lock으로 인해 동일 모드로 lock을 요청하는 다른 Transaction 은 락을 대기할 수 밖에 없음.
• pg_locks View를 통해 확인이 가능

# pg_stat_activity : 현재 데이터베이스의 모든 세션 상태(쿼리, 접속자, 실행 시간 등)를 보여주는 뷰
# pg_locks : 현재 데이터베이스 서버의 모든 활성 세션이 보유하거나 대기 중인 Lock 정보를 보여주는 시스템 뷰
# 어떤 세션이 어떤 Lock을 보유하거나 대기 중인지, 쿼리 내용과 Lock 보유 시간까지 한 번에 확인
# datname: 데이터베이스명
# locked_relation: 잠금 대상(테이블명 등)
# mode: Lock 종류
# granted: Lock 보유 여부(true/false)
# query: 실행 중인 쿼리
# lock_age: Lock 보유 시간(경과 시간)
# pid: 프로세스 ID

SELECT
  a.datname,
  l.relation::regclass AS locked_relation,
  l.transactionid,
  l.mode,
  l.granted,
  a.usename,
  a.query,
  a.query_start,
  age(now(), a.query_start) AS lock_age,
  a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

# blocking session 
# Lock으로 인해 대기 중인 세션과 이를 블로킹하는 세션을 한 번에 확인
SELECT
  COALESCE(blockingl.relation::regclass::text, blockingl.locktype) AS locked_item,
  now() - blockeda.query_start AS waiting_duration,
  blockeda.pid AS blocked_pid,
  blockeda.query AS blocked_query,
  blockedl.mode AS blocked_mode,
  blockinga.pid AS blocking_pid,
  blockinga.query AS blocking_query,
  blockingl.mode AS blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl
  ON (
    (blockingl.transactionid = blockedl.transactionid)
    OR (blockingl.relation = blockedl.relation AND blockingl.locktype = blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
  AND blockinga.datname = current_database();


# https://blog.ex-em.com/1928
SELECT current_timestamp AS db_time,
       waiter_pid,
       w_info.usename AS waiter_user ,
       w_info.query   AS waiter_query ,
       w_info.query_start AS waiter_query_start ,
       case
           when EXTRACT(EPOCH from current_timestamp - w_info.query_start ) < 0 then 0
           else EXTRACT(EPOCH from current_timestamp - w_info.query_start ) 
       end as waiter_elapsed_time, 
       holder_pid ,
       h_info.usename AS holder_user ,
       h_info.query   AS holder_query ,
       h_info.query_start AS holder_query_start,
       case
           when EXTRACT(EPOCH from current_timestamp - h_info.query_start ) < 0 then 0
           else EXTRACT(EPOCH from current_timestamp - h_info.query_start )
       end as holder_elapsed_time
FROM   (
              SELECT snaptime,
                     locktype,
                     waiter_pid,
                     w_cnt,
                     h_cnt ,
                     CASE
                            WHEN h_cnt=Max(h_cnt) OVER(partition BY waiter_pid) THEN holder_pid
                     END AS holder_pid
              FROM   (
                            SELECT current_timestamp                             AS snaptime,
                                   blocked_locks.locktype                        AS locktype,
                                   blocked_locks.pid                             AS waiter_pid,
                                   count(*) over(partition BY blocked_locks.pid) AS w_cnt,
                                   count(*) over(partition BY blocking_locks.pid)    h_cnt,
                                   blocking_locks.pid                             AS holder_pid
                            FROM   pg_catalog.pg_locks blocked_locks
                            JOIN   pg_catalog.pg_locks blocking_locks
                            ON     blocking_locks.locktype = blocked_locks.locktype
                            AND    blocking_locks.DATABASE IS NOT DISTINCT
                            FROM   blocked_locks.DATABASE
                            AND    blocking_locks.relation IS NOT DISTINCT
                            FROM   blocked_locks.relation
                            AND    blocking_locks.page IS NOT DISTINCT
                            FROM   blocked_locks.page
                            AND    blocking_locks.tuple IS NOT DISTINCT
                            FROM   blocked_locks.tuple
                            AND    blocking_locks.virtualxid IS NOT DISTINCT
                            FROM   blocked_locks.virtualxid
                            AND    blocking_locks.transactionid IS NOT DISTINCT
                            FROM   blocked_locks.transactionid
                            AND    blocking_locks.classid IS NOT DISTINCT
                            FROM   blocked_locks.classid
                            AND    blocking_locks.objid IS NOT DISTINCT
                            FROM   blocked_locks.objid
                            AND    blocking_locks.objsubid IS NOT DISTINCT
                            FROM   blocked_locks.objsubid
                            AND    blocking_locks.pid != blocked_locks.pid
                            WHERE  NOT blocked_locks.granted ) t ) t2
JOIN   pg_catalog.pg_stat_activity w_info
ON     w_info.pid = t2.waiter_pid
JOIN   pg_catalog.pg_stat_activity h_info
ON     h_info.pid = t2.holder_pid
WHERE  holder_pid IS NOT null;

db_time                      |waiter_pid|waiter_user|waiter_query                                        |waiter_query_start           |waiter_elapsed_time|holder_pid|holder_user|holder_query                                        |holder_query_start           |holder_elapsed_time|
-----------------------------+----------+-----------+----------------------------------------------------+-----------------------------+-------------------+----------+-----------+----------------------------------------------------+-----------------------------+-------------------+
2024-04-30 12:08:49.078 +0900|   2914359|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:55.777 +0900|          53.301024|   2914369|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:50.784 +0900|          58.294596|
2024-04-30 12:08:49.078 +0900|   2914369|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:50.784 +0900|          58.294596|   2914377|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:45.711 +0900|          63.366759|

• Lock_timeout
  • lock_timeout은 PostgreSQL에서 쿼리가 Table, Index , tuple 등 데이터베이스 객체의 잠금(Lock)을 획득하기 위해 대기하는 최대 시간(밀리초 단위)을 설정하는 parameter
  • 설정 단위: 세션(Session) 또는 트랜잭션(Transaction) 단위로 설정 가능
  • Default: 0 (disable, 무한정 대기)
  • 적용 대상: 명시적/암시적 Lock 요청(예: DML, DDL, LOCK TABLE 등)
  • 동작: 지정한 시간 내에 Lock을 획득하지 못하면 쿼리가 취소되고, canceling statement due to lock timeout 오류가 발생
  • statement_timeout과 차이: statement_timeout은 쿼리 전체 실행 시간을 제한하지만, lock_timeout은 Lock 획득 대기 시간만 제한 ->  둘 다 설정되어 있으면, 먼저 도달한 타임아웃이 우선 적용

# ms 단위
set lock_timeout = 5000;
set lock_timeout = '5s';
ALTER TABLE mytable ADD COLUMN newcol int;

• Deadlock
  • PostgreSQL은 데드락 상황을 자동으로 감지하고, 관련 트랜잭션 중 하나를 강제로 중단(ROLLBACK)시켜 교착상태를 해소.
  • 어떤 트랜잭션이 중단될지는 예측할 수 없음
  • deadlock_timeout parameter 
    • Deadlock 상황을 일정한 주기로 체크하여 감시하는 parameter
    • Default : 1000ms
  • Deadlock 방지 및 해결 전략
    • 락 획득 순서 일관성 유지: 모든 트랜잭션이 동일한 순서로 Lock을 획득하도록 설계하면 데드락 발생 가능성을 크게 줄일 수 있음
    • 작은 배치 크기: 한 번에 처리하는 데이터의 범위를 줄여 Lock 충돌 가능성을 낮춤
    • 트랜잭션 분리: DDL(DROP, TRUNCATE 등) 작업과 DML(INSERT, UPDATE 등)을 분리하여 실행
    • 재시도 메커니즘: 데드락 발생 시 자동으로 트랜잭션을 재시도하도록 애플리케이션을 설계
    • Lock 대기 시간 제한: lock_timeout 파라미터를 설정해 장기 대기를 방지

Architecture 중 WAL 에 대해 정리 하였습니다.

내용이 중복되는 내용도 있지만, 공식 문서에만 있는 수준의 정보도 있습니다. 쉽게 정리하는 것이 중요하지만, 이해를 못하더라도 연관 되는 내용이 반복되는 경우가 존재하기 때문에, 이런 내용도 있구나 하고 넘어 가는 것도 좋다고 생각합니다.

Postgresql Architecture

WAL 동작에 대한 간략한 이해도

• 세그먼트 파일은 단조 증가하는 파일 이름을 사용하고 가급적 많은 WAL 레코드를 포함
• 파일 크기는 구성 가능하며 기본값은 16MiB
• 크기 또는 개수 측면에서 대량 트랜잭션이 예상되는 경우 생성되는 세그먼트 파일의 총 수를 줄이고 파일 관리 부담을 줄이기 위해 더 큰 크기를 선택할 수 있습니다.
• MongoDB Journal log (https://hyunki1019.tistory.com/155)
• MySQL 은 WAL 전략을 채택하여 Redo Log Buffer에 transaction log 를 작성(redo log를 뜻함)
• WAL Buffers 내용을 WAL Files에 기록
• WAL Log / Transaction Log / Redo log
• sync / async mode 존재
• Segment file size : 16mb
• Log file 로 분할된 segment 개수 64개 (wal_keep_segments -> Logical log file)
  • max_wal_size 만큼 생성 (64개 넘어가면 한개 삭제되고 한개 생성되는 형태 -> rotation 이 아님)
  • 64*16 = 1024mb ( max_wal_size 1gb)
  • min_wal_size (80mb = 16*5 = 5개 생성되고 이후 한개 삭제, 한개 생성)
  • 해당 File 를 이용하면 CDC 프로그램도 개발이 가능
  • pg_resetxlog
    • 9.6 이하에서 사용하였으며, pg_resetwal 로 변경
    • WAL 파일을 reset 하는 명령어
    • pg_resetwal
      • WAL 초기화 진행 및 pg_control 파일에 저장된 일부 제어 정보도 초기화
      • 서버가 crash 로 인해 손상되어 시작이 안될 때 강제 부팅하는 역할이 필요할때만 진행
      • 데이터 일관성 문제 발생할 수 있으며, 별도 복구가 반드시 필요
  • pg_switch_xlog()
    • 강제로 WALog swiching
  • pg_xlogdlump
    • wal log 작성된 내용 확인
    • pg_xlogdump ./000000001000000012341223 (WAL log file)
    • 데이터 번호/ schema 번호 / 디비번호 형태로 결과
      
  • 병목 현상
    • block 단위로 WAL log 에 기록 다음 checkpoint 가 발생하기 전까지 레코드 단위로 WAL log 기록
      
    • log size 늘리거나 디스크를 좋은 것 사용 외에는 해결 불가
      
    • full_page_writes parameter 를 off 하여 속도 향상
      
    • Vacuum freeze를 하면 XID frozen 작업으로 인해 레코드 변경이 발생
      
    • 1bit 변경됐지만 full_page_writes 기능으로 인해 block 단위 redo가 생성
• 모든 변경 기록을 보관(transaction log)
  • 무결성을 보장하기 위한 작업
  • Tip - Journaled file 은 불필요
    
    • WAL 가 안정적으로 저장하기 때문에, Journaled file이 필요없음
      
      • journal 
        
        • pg_journal 를 사용 하여 journal log에 직접 로그 메시지를 작성
        • DB 활동을 기록하는 파일로, 문제 해결 및 성능 분석에 사용
        • https://pgxn.org/dist/pg_journal/doc/pg_journal.html
      • Journal 은 오버헤드로 인해 성능 저하를 유발(Disk flush / 단, ext3 일 경우 data=writeback 옵션을 사용하여 data flush 진행 시 발생할 수 있는 오버헤드를 줄일 수 있음)
      • Journal 은 DB Crash 발생 시 부팅 속도를 향상할 수 있는 효과
• 복구를 위해 기록하는 것으로, DB가 crash 되었을 경우 check point 동작 전이라고 하면 디스크에 저장이 안되어 유실되는데, 이것을 방지하기 위해 WAL 로그를 읽어와 복구 진행
  • os buffer cache 영역에는 자주 요청되는 디스크 블록을 저장하지만, 이건 Disk 로 바로 write 되도록 postgresql 에서 강제로 할 수 있음.(wal_sync_method parameter 참고)
  • Disk 의 실제 페이지에 DML하기전에 주기적으로 전체 페이지 이미지를 WAL에 영구적으로 저장
  • 그래서 Crash가 발생하더라도 WAL에서 부분적으로 작성된 페이지를 복원이 가능
  • WAL 파일의 각 레코드는 레코드 내용이 올바른지 확인할 수 있는 CRC-32(32bit)검사로 작성 및 보호 (복구 시 확인)
    • WAL 레코드에 기록된 전체 페이지 이미지는 보호되지만, 데이터 페이지는 기본적으로 chechsum 되지 않음 
  • 데이터만 WAL에 작성이 되고, 통계정보 관련된 각종 시스템 정보들은 저장되지 않고, 복구 진행 시 최근 변경사항으로 재구축-재설정 되도록 동작
  • Temptable 정보도 저장되지 않음
• WAL 를 사용하면 Disk write 수가 줄어드는데, Transaction 에 의해 변경된 모든 데이터가 아닌, commit 보장하기 위한 WAL 파일만 disk 에 flush 하면 되기 때문 -> 데이터 페이지를 Flush 하는 비용보다 더 저렴
  • 특히, 소규모 transaction 을 동시에 처리하는 시스템에서 WAL 파일의 fsync 하나만으로 많은 transaction을 commit 하는데 충분하기 때문
  • fsync (boolean)
    • https://minsql.com/postgres/PostgreSQL-synchronous_commit-%EA%B0%9C%EB%85%90%EB%8F%84/
    • fsync가 on이면, PostgreSQL서버는 fsync()시스템콜을 통해서 변경분을 디스크에 물리적으로 바로 쓴다. 이는 데이터베이스클러스터가 OS나 하드웨어 장애시 consistent한 상태로 복구가 가능함을 보장한다.
    • fsync를 off한다면, OS가 알아서 메모리에 있는 것을 디스크로 내려쓰게 된다. 언제 무엇이 디스크에 쓰여졌는지 아닌지 알수 없다. 그러므로 성능상 이득을 볼수는 있겠지만, 전원장애나 system crash로가 발생했을때 복구가 불가능할수 있다. 만약 전체 데이터베이스를 쉽게 재생성할수 있는 경우에만 off하도록 한다. 예를들어 백업본으로부터 새로운 데이터베이스 클러스트를 초기 구축하는 경우, 버리고 재생성할 데이터베이스의 데이터 처리, 자주 재생성되는 read-only 데이터베이스 복제본으로 failover에 사용되지 않는 데이터베이스인 경우 사용할 수 있다. 고성능의 하드웨어 장비라고해서 fsync를 끄는 것은 올바르지 않다.
  • 성능을 위해서라면 synchronous_commit을 off하는 것만으로 충분할 것이다.
  • 만약 off하기로 했다면, full_page_writes도 off하는 것을 고려하도록 한다.
• WAL 를 사용하여, PITR 지원이 가능
  • WAL 를 재생하여 복구가 가능
• Asynchronous Commit
  • DB Crash 발생 시, 가장 최근의 Transaction은 유실 가능성 존재(Risk)
  • 다만,  transaction 을 더 빠르게 처리가 가능
  • Default 는 synchronous (동기)
    • Client에 transaction result를 return 하기 전에, WAL 의 record가  Disk 에 write될 때까지 기다렸다가 ack 처리. -> commit 된 transaction 에 대한 무결성 보장을 의미
    • 다만, 이것에 대한 비용은 발생
  • 이러한 비용을 조금이라도 줄이기 위한 방법으로, disk flush 응답을 받기 전에 WAL buffer에서만 작성 후 ack 하는 방식을 Asynchronous Commit  -> Transaction 처리량 향상
  • synchronous_commit  Parameter 를 이용하여 수정이 가능하며, transaction 마다 설정이 가능
    • 트랜잭션 커밋하기전에 WAL 레코드가 disk까지 쓰인다음에 success를 리턴할 것인가 여부
    • on : default
    • off : 클라이언트에 바로 transaction commit을 보냄. 하지만 실제로 트랜잭션이 안전하게 반영(WAL record가 WAL file에 쓰여짐) 되기까지 딜레이가 존재함. 서버 crash났을때 트랜잭션 손실될 수 있음. (최대 delay는 wal_writer_delay(200ms)의 3배). 하지만 fsync와는 달리 off로 한다고 해서 db 일관성에 문제가 되지는 않음. 최근 커밋되어야하는 트랜잭션이 손실될 수는 있으나 database 상태는 이 트랜잭션들이 정상적으로 롤백된 것과 같아서 일관성에 문제 없음.
  • DROP TABLE 은  synchronous_commit 와 상관없이 동기식으로 처리 : 일관성을 보장하기 위함.
  • PREPARE TRANSACTION 와 같은 2-phase commit 의 경우도 동기식으로 처리
  • wal_writer_delay  ms 마다 WAL Writer 가 Disk 로 flush 진행
    • 최대 유실 시간은 wal_writer_delay milliseconds * 3 의 risk 가 존재
      
      • busy periods(바쁜기간?)에 WAL writer 가 한 번에 Disk 로 flush 를 진행하는 것을 선호로 인해 발생 (정확한 의미는 모르겠습니다. 왜 wal_writer_delay 의 최대 3배의 시간으로 명시했는지.
  • commit_delay parameter 와 유사하지만, commit_delay 는 synchronous commit 의 방법에 속함
    
    • Asynchronous commit 을 설정하면, commit_delay 는 무시
    • commit_delay 는 transaction 이 WAL disk flush 지연을 유발

• WAL Configuration
  • 참고로 checkpoint 는 checkpoint 이전까지의 기록된 transaction 의 sequence 지점
    
    • checkpoint 를 기준으로 모든 Dirty data page가 Disk 로 flush 되고 특수 checkpoint record가 WAL 파일에 기록
    • DB Crash 가 발생하면, 최신 checkpoint 지점을 찾은 후, WAL 에서 시작해야 되는 시점을 찾아 복구 진행
    • 최소 Checkpoint 지점까지는 Disk 에 있는 것을 보장되며, WAL 기준으로는  checkpoint 이전 파일은 필요 없으며, 이전 segment 또한 필요가 없음을 의미하고 재활용 되거나 제거할 수 있음을 의미
    • full_page_writes
      • Default on
      • https://postgresql.kr/docs/9.5/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
      • checkpoint 이후 각 Disk page를 수정(dml로 인해) 하는 도중에 해당 페이지의 전체 내용을 WAL 에 기록
        • os crash 발생 시, 진행 중인 페이지 쓰기가 부분적으로만 완료 되어 디스크 상의 페이지에 과거 데이터와 새 데이터가 공존하기 때문에, 복구 진행 시 올바른 복구가 보장되지만 WAL 에 기록하는 데이터량은 증가될 수 밖에 없음
        • checkpoint 간격을 늘릴 수 있음.
      • off 하면, 운영 속도가 향상되지만, 시스템 crash 발생 시, 손상된 데이터가 복구 불가능 또는 데이 손사이 발생 가능성 존재
        
        • fsync 를 해제했을 때와 유사
        • PITR 진행 시, WAL 아카이빙 사용에는 영향을 미치지 않음
        • postgresql.conf 파일 또는 command 로 설정 가능
    • checkpoint_timeout sec 마다 또는 max_wal_size 가 초과될 경우 checkpoint 발생
      
      • default : 5 min (300 sec) / 1 Gb
      • WAL 에 작성이 안된 경우 checkpoint_timeout 이 되어도 checkpoint 가 발생하지 않음
      • 물론  CHECKPOINT 명령어로 강제 실행은 가능
      • 만약 해당 변수 값들을 줄이며, 더 자주 checkpoint 발생 : DB crash 후 빠른 복구는 가능하지만 Disk I/O 비용 발생
      • full_page_writes 가 설정된 경우 (default 설정) , check point 발생 후 데이터 페이지 수정이 발생하는 경우 전체 페이지가 기록 -> 더 많은 Disk I/O 발생(시간을 짧게 가져갈수록 disk I/O는 더욱더 증가)
      • checkpoint_timeout 시간을 충분히 여유있게 가져가는 것을 책에서는 권장하며, checkpoint_warning 로 checkpoint 에 대해 sanity check (온전성 검사?)가 가능
      • 당연하겠지만, checkpoint timeout 보다 더 잦은 checkpoinrt 가 발생하면 max_wal_size 증가 하라는 메시지가 log 에 남음
    • XLogInsertRecord
      
      • Shared memory 내 WAR buffer 에 new record 를 배치하는데 사용
      • 만약 새로운 데이터가 insert 될 공간이 없으면, kernel cache 로 채워진 WAL buffer 일부를 move 시킴 -> 이것은 exclusive lock (대상 data page) 을 유발하기 때문에, 성능에 좋지 않음
      • 일반적으로 XLogFlush  에 의해 WAL buffer 를 WAL file 에 flush 되어야 함.
      • wal_buffers 를 이용하여 WAL bufferpool 수를 조정 가능하며, full_page_writes 가 설정되어 있고, 시스템이 바쁜경우 wal_buffers 수를 늘려주는 것을 권장
    • XLogFlush
      
      • LogWrite 의 issue_xlog_fsync 의 호출에 의해 XLogFlush 가 WAL buffer 의 내용을 disk 로 flush
      • commit_delay
        • commit_delay (ms)는 XLogFlush 내에서 lock 을 획득 하는동안, group commit 을 기다리는 시간(지연시간) parameter
        • 목적은 동시에 commit 되는 transaction 전체에 걸쳐 flush 작업의 비용을 분할할 수 있는 것(다만 transaction 시간은 증가)
          • commit_delay 가 발생하면, 대기하는 commit record들은 WAL Buffer 에 추가 하여 정합성을 지키는 효과 -> Transaction 대기 시간을 최소화
        • 0 : ( default)그룹 커밋의 효과가 중요
        • 1 : 동시에 commit 이 발생하여 transaction 처리?
        • 2 : 처리량이 commit 속도에 따라 제한 되는 경우 도움 ?
    • wal_debug 를 활성화 하면, XLogInsertRecord, XLogFlush 의 call 횟수를 확인할 수 있음.
    • WAL 데이터를 Disk 에 Writer 하는 함수는 XLogWrite and issue_xlog_fsync  존재
    • WAL 데이터를 동기화하는 데 소요되는 시간은, pg_stat_wal 내의  wal_write_time and wal_sync_time 에 기록
    • wal_sync_method 가  open_sync 또는 open_datasync  이면, XLogWrite는 WAL 데이터를 Disk 로 flush 하고 데이터 정합성을 보장하고, issue_xlog_fsync 는 어떠한 작업도 하지 않음
    • wal_sync_method 가  fdatasync , fsync , 또는 fsync_writethrough 가 되면, write opertaion이 WAL buffer 를 kernal cache로 이동하고 issue_xlog_fsync 는 kernal cache 내용을 Disk로 동기화 진행
    • XLogWrite Write 및 issue_xlog_fsync 가 WAL 데이터를 disk 로 동기화하는 횟수가, pg_stat_wal 에서 wal_write 및 wal_sync 값으로 저장
  • WAL Internal
    
    • WAL 는 rotation 하기 때문에, 관리자가 별도로 disk 관련하여 신경쓰지 않아도 됨
    • LSN 으로 어느 WAL 파일에 작성해야 하는지 (마지막 지점) 확인
    • WAL 파일은 Data directory 아래 pg_wal directory 에 segment file 로 저장되며, 16mb
      • --wal-segsize init db option을 변경하여 수정 가능
      • segement page는 8kb (--with-wal-blocksize option)
      • WAL record header 는 access/xlogrecord.h 에 설명 (https://github.com/postgres/postgres/blob/master/src/include/access/xlogrecord.h)
    • WAL 파일 은 별도의 Disk 공간으로 분리 하는 것이 유리
      
      • pg_wal directory를 symbolic link 로 처리하면 가능
    • Checkpoint 가 발생 시, WAL 내용도 flush 된 후, checkpoint 의 값은 pg_control 에 저장
      
      • DB Crash 이후 recovery 진행할 때, 가장 먼저 pg_control 값을 확인 후, 이후 부터 wal 파일 조회하여 복구 진행
      • pg_control 이 손상되면, 최신 checkpoint 를 찾기 위해  WAL segment 를 역순으로 체크하면 되는데, 이것은 아직 구현되지 않음
      • 또한, pg_control 이 손상되어 DB를 복구 하지 못한 것은 아직 보고가 되지 않음

그 외 좋은 Tip 소개

[Why PostgreSQL WAL Archival is Slow]

• Disk 사용량이 급증하는 경우, WAL (pg_wal) Directory 의 size 가 높은 경우가 종종 발생
• 왜 rotation 안되고, 증가하는 것인지에 대한 의문

1. WAL Archival 의 실패
2. 오래된 WAL의 slot 을 holding 하는 경우

• 최근에는, WAL segment 생성 속도에 비해, 삭제 속도가 느려서 발생하는 현상도 추가
  • Host (Instance) 서버의 처리 능력 증가
  • 파티셔닝 기능의 발전이나 대량 데이터 로딩 이 개선되면서 Postgresql 의 확장성 향상
  • 빠른 차세대 Storage 로 인해 처리량 증가이로 인해 WAL 가 생성되는 속도가 증가
• 이로 인해 WAL 가 생성되는 속도가 증가
• WAL-G 또는 pgBackRest 와 같은 백업 솔루션이 내장된 압축 기능으로 문제 해결도 가능

1. PostgreSQL 이란 ?

- PostgreSQL 은 캘리포니아 대학 버클리의 컴퓨터 과학 학과에서 개발된 POSTGRES, Version 4.2 를 기반으로 한 객체 관계형 데이터베이스 관리 시스템( ORDBMS )

- POSTGRES는 나중에 여러 상용 데이터베이스에서 사용할 수 있게 되었던 많은 개념에 대한 선구자

- PostgreSQL 은 오리지널 버클리 학교의 소스 코드를 인수한 오픈 소스 데이터베이스로 표준 SQL의 대부분과 다른 최신 기능을 지원

PostgreSQL 는, 여러가지 방법으로 유저가 확장 가능

게다가 자유주의적 라이센스(Postgresql license 라고 하는데, 이건 BSD/MIT 라이센스 계열) 조건에 의해, PostgreSQL 은 누구에게나, 그 사용, 변경 , 배포를 개인 사용, 상용, 학술 등, 목적에 한정하지 않고 무상으로 가능

소스를 공개할 의무도 없고 아무런 제약도 없음. (다만 mysql, mariadb 의 경우 GLP 라이센스이라, fork 한 상용 DB라면 소스공개가 의무)

2. Postgres 95

- Postgres 에 SQL 언어를 추가

- ANSI C 로 재작성되어, 기존 코드에서 25% 정리되었으며 많은 내부 성능 향상 및 유지 보수성이 향상

- 기존 POSTGRES 에 비해 Benchmark에서 30~50 성능 향상

3. PostgreSQL

- 기존 Postgres95 이름에 대한 이야기가 많아 새로운 네이밍 생성

- POSTGRES 와 SQL 기능이 탑재된 최신 버전 반영한 PostgreSQL 이라는 네임 변경

- 6.0 으로 시작하는 버전 번호 설정

4. 그 외
- PostgreSQL wiki 는 프로젝트 FAQ (FAQ) 목록, TODO 목록 및 더 많은 주제에 대한 정보를 포함

- PostgreSQL 의 웹 사이트 에는 최신 릴리스에 대한 자세한 내용과 PostgreSQL 의 이용 및 조작을 할 때 생산성을 더욱 높이는 정보 제공

- PostgreSQL 은 오픈 소스 프로젝트라서, 소스 기여도 가능

- 버그 리포트 관련한 내용 (https://www.postgresql.jp/document/16/html/bug-reporting.html)

참고

https://www.postgresql.jp/document/16/html/preface.html

https://wiki.postgresql.org/wiki/Main_Page

Postgresql 에 대해 한번 공부 할때, 확실히 합시다.

무엇이 중요한지 모릅니다. 하지만, DBA 관점에서 시작합니다.

Real mysql 책의 목차를 이용하여 목차 만들었습니다.

Admin 에 대해 고급까지는 아니지만, 중급까지는 공부합니다.
아키텍처 및 동작 방식까지 공부 합니다.
5명(모집자 포함) 모두 발표 합니다. (하루전에 발표 자료 공유 부탁 드립니다.)
모집 인원은 모두 찾았습니다.
Postgresql 운영만 최소 3년 이상 리더 한분 섭외(리더는 내용에 대해 경험에 대해 발표-리더도 발표합니다.) 
2주에 한번 합니다. 현생이 힘듭니다...

목차

• Postgresql 에 대한 소개
• Postgresql 설치
  • rpm 패키징 설치는 간략하게
  • Source 설치에 대해서 진행합니다.
    • 원하는 곳에 Log, Data file 지정이 필요
    • Source 수정까지는 아니지만 필요할때는 코드라는걸 한법 봅시다.
  • 업그레이드 / 다운그레이드
    • 무중단 업그레이드 위주로 테스트
  • HA
  • Major 설정값(parameter) 위주로 확인 (메모리, path, character set 등)
  • Aurora Postgresql 간략하게(자세하게 확인은 맨 마지막에)
• Postgresql Architecture
  • 내부 구조에 대해 두리뭉실하게...
  • client
  • Server
    • process
      • 내부 process (Wal, vacuum 등)
    • memory
    • Files
• 계정 및 권한
  • 내부 Architecture 와 연관
  • Schema,  Database, table 에 대한 권한과 권한에 따른 영향
  • 계정에 대한 관리
• Transaction 과 Lock 관리
• Index
• Optimize 와 Hint, Explain
• 내부 명령어 및 system 테이블
• 백업 복구
  • 반드시 한번씩 해봅시다.
    • Full backup / restore, Incremental backup, PITR
  • 옵션도 공부합시다.
  • Third party 하나 정도 이용해 봅시다.
    • 동작 방식도 이해합니다.
  • DB 이관 (Oracle to Postgresql)
    • 주의 점
    • 단순 Migration 외의 서로 다른 엔진 특성 상 수정 되어야 하는 설계
• 모니터링
  • 모니터링 지표들과 조회 방식
  • Third party tool
• 그 외 추가 되어야 하는 내용들

참고 사이드

개인적으로 북마크하는 곳이지만, 그 외 많은 숨고 사이트가 있습니다.
공홈 : https://www.postgresql.org/
postgresql dba 커뮤니티 : https://www.postgresdba.com/
페이스북 통해서 확인 : https://postgresql.kr/
넘사벽 김두비 : https://kimdubi.github.io/postgresql/