PostgreSQL architecture (shared Memory)

 

 

Shared Memory (공유 메모리) 

ㄴ 디스크 I/O를 최소화하기 위해 사용되는 메모리 공간

ㄴ PAGE SIZE 8K(Default)이다. 파라미터는 shared_buffers

ㄴ oracle 의 SGA와 유사

 

• shared buffer = data : page (block)단위로 구성이 되고 기본이 8k 크기
  • oracle의 buffer cache 와 유사하고 postgresql.conf 의 shared_buffers 파라미터 값으로 크기 조정 가능
  • 기본이 128MB 
  • 1GB이상의 RAM이 있는 서버에는 25%를 권장한다. ( shared_buffers = RAM * 0.25 )

4GB RAM: 1GB (25%)
8GB RAM: 2GB (25%)
16GB RAM: 4GB (25%)
32GB RAM: 8GB (25%)
64GB RAM: 16GB (25%)

• shard buffer 크기 확인

 

페이지 16384 개 * 8k / 1024 = 128MB

 

 

 

CREATE DATABASE pgbenchtest;

--pgbench는 성능테스트를 위한 툴임
pgbench -i -s 10 -U postgres pgbenchtest

-i: 데이터베이스에 기본 테스트 테이블을 초기화

--메모리 read , 물리 read 개수 확인
SELECT datname, blks_hit, blks_read 
FROM pg_stat_database;

 

blks_hit	메모리 버퍼 캐시에서 찾아 바로 제공된 블록 수	논리적 I/O
blks_read	디스크에서 직접 읽어들인 블록 수	물리적 I/O

• blks_hit가 높을수록 메모리 캐시 활용도가 높아 디스크 I/O 비용이 절감되므로 성능이 좋습니다.
• blks_read가 낮을수록 디스크 I/O 작업이 줄어들어 성능 향상에 도움이 됩니다.

 

 

여러번 실행하고 나서 물리적 io 대신 논리적 io만 증가하는 것을 확인 할 수 있다.

---

Wal buffers (Write-Ahead Logging)

ㄴ 데이터베이스의 변경사항을 디스크에 기록하기 전에 임시로 저장하는 공간  

ㄴ 장애 발생 시 복구를 위한 로그 보존

• 트랜잭션에 대한 변경 로그를 캐싱하는 공간
• PostgreSQL의 ACID 보장을 위한 중요한 구성요소
• postgresql.conf 의 wal_buffers 파라미터 값
• 기본이 -1 ( shared_buffers의 1/32 크기로 시스템이 자동 계산)
• 일반적으로 16MB가 대부분의 시스템에 적합

 

 

 

 

[TEST] DB를 정상종료할때와 비정상 종료할때의 wal buffer 역할 확인 (기동시간, log확인)

 

1. pgbench 로 50만건 insert

pgbench -h localhost -U postgres -i -s 50 pgbenchtest

2. DB 종료

2.1 정상 종료

 

정상 종료 후 기동했을때 

 

정상종료 log

정상기동 log

2.2 비정상 종료

 

비정상종료 후 기동 log에서 자동 recovery

 

---

CLOG buffers (commit Log)

• 트랜잭션 상태정보를 캐싱하는 공간
• 모든 트랜잭션의 상태가 있고 (커밋/롤백 여부)완료여부를 확인할 수 있는 공간
• $PGDATA/pg_xact(이전 버전에서는 pg_clog) 디렉토리에 물리적으로 저장
• 별도의 parameter는 없다

00: 진행 중(IN_PROGRESS)
01: 커밋됨(COMMITTED)
10: 롤백됨(ABORTED)
11: 서브트랜잭션

-- CLOG 크기 확인
SELECT pg_size_pretty(pg_xact_dirsize());

-- 오래된 트랜잭션 확인
SELECT datname, age(datfrozenxid)
FROM pg_database;

 

ㄴ 성능 고려사항

• CLOG 버퍼가 부족하면 디스크 I/O 증가
• 너무 많은 동시 트랜잭션은 CLOG 부하 증가
• 오래된 트랜잭션은 CLOG 공간 낭비

 

 

clog 관련파일 위치

---

Lock space

• 테이블, 로우, 트랜잭션 등의 잠금 정보 저장
• shared_memory의 일부로 할당됨
• 유지할 수 있는 lock 의 갯수는 > max_locks_per_transaction * (max_connections + max_prepared_transactions)

ㄴ Lock정보를 저장하는 공간

ㄴ 관련 파라미터 max_locks_per_transaction 최대 락 수

 

 

[TEST] 

세션 1에서 테이블 A에서 특정 Tuple 대해 A에서 D로 UPDATE를 하고
세션 2에서 테이블 A에 대해 DROP 할 경우 DROP이 될까?

 

1. 데이터 insert 3건

 

2.세션1에서 update

 

3. 세션2에서 drop & 대기 발생

 

4. lock에 걸려있음

 

lock 관련 쿼리

-- 테이블 레벨 락
ACCESS SHARE           -- SELECT
ROW SHARE             -- SELECT FOR UPDATE
ROW EXCLUSIVE         -- UPDATE, DELETE
SHARE UPDATE EXCLUSIVE -- VACUUM, CREATE INDEX CONCURRENTLY
SHARE                 -- CREATE INDEX
SHARE ROW EXCLUSIVE   -- 특수한 경우의 DDL
EXCLUSIVE             -- 테이블 스키마 변경
ACCESS EXCLUSIVE      -- DROP TABLE, TRUNCATE

 

-- 현재 락 상태 확인
SELECT * FROM pg_locks;

-- 락 대기 상태 확인
SELECT
blocked.pid,
blocked.usename,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM
pg_stat_activity AS blocked
JOIN
pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE
CARDINALITY(pg_blocking_pids(blocked.pid)) > 0;

주요 lock 레벨
ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE


-- 현재 Lock 상태 확인
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.granted = true;
    
-- 데드락 처리 (postgresql.conf 에서 설정)
deadlock_timeout = 1s   

-- 오래 실행 중인 쿼리 확인
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' 
AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;


-- 특정 프로세스 종료
SELECT pg_terminate_backend(PID);