Postgre SQL Architecture Oracle Architecture 2 SQL Server

Postgre. SQL Architecture 정리

참고 : Oracle Architecture 2

참고 : SQL Server Architecture 3

Postgre. SQL Architecure Postgres Instance Client Processes System Memory Server Processes Initial Connection Request and Authentication Client Application Postmaster (Daemon/Listener) PG Shared Memory Create Shared Buffer • WAL Buffer • • CLOG Buffer Lock Space Spawn Server Processes DB Requests and Results via Library API Per Backend Per. Backend Memory maintenace_ work_mem temp_buffer work_mem catalog_cache optimizer/executor Other Buffers OS Cache Attached to PG Shared Memory Read/ Write Utility Process Client Interface Library (Libpg) SQL Queries and Results Postgres Server (backend) BG Writer WAL Writer Archiver WAL Sender Independent Stats Collector Auto Vaccum Storage Manager SYS Logger WAL Receiver Database Cluster Buffer Manager Lock Manager File Manager Semaphore & Shared Memory Page Manager Disk Manager Sub Configure Directory File Lock File 4

Client Processes <Client Application & Client Interface Library> • Client Application이 Client Interface Library를 통해 Server와 통신. • Client Interface Library는 Server가 명령을 이해하고 Parsing할 수 명령어를 SQL로 바꿈. Client Application DB Requests and Results via Library API Client Interface Library (Libpg) 5

Postmaster Server Processes Postmaster (Daemon/Listener) Spawn Server Processes <Postmaster> • 항상 수행되는 데몬 쓰레드. • DB에 대한 모든 요청(Call)을 Listen하기 위한 암시적 호출 (Implicit Invocationi) 아키텍처로 사용(Wait for client requst). • 요청(Call) 수신시 Back-end process를 생성하여 Call에 1: 1로 대응시킴. • Postmaster가 Call과 Postgres를 연결하면 더이상 Postmaster와 통신할 필요 없음. • 5444(PPAS), 5432(PGS) Client Apps Wait for client request Postgres Server (backend) postmaster shared memory archiver bgwriter stats 6

Postgres Server Processes Postmaster (Daemon/Listener) <Postgres Server> • Postgres는 Hybrid pipe & Filter architecure 방식. • 각 Component는 Catalog, Rule, Table의 공유 Repository를 참조. • SQL Query를 전달받고 결과적으로 Result Data로 변환. • Storage Manager와 Utilities 사용 Spawn Server Processes Client Apps postmaster Authentication Postgres Server (backend) Call back to client Wait for SQL postmater work mem shared memory archiver bgwriter stats 7

SQL Process Step <Pipe & filter Architecture> • Parser(syntax) - ASCII로 전달 받아 Query String 파싱. - 키워드, 식별자 인지를 위해 Pattern Matching. - Parse. Tree 생성, SQL Syntax 체크. • Traffic Cop(syntax/complex) - Simple Command를 Executor에 전달. - Complex Command는 Planner/Optimizer에 전달. • Rewirter(rule argument) - 전달된 Parse. Treedml의 lower order 명령어로 재작성 • Planner/Optimizer(optimal plan) - 최적의 쿼리 플랜 결정, Tree 형태의 플랜 생성. • Executor(execute optimal plan) - 실행계획을 수행하여 Data 추출. - Client에 결과 반환. Postgres Server (backend) Client Input(SQL) Postgres Server Data Rows/Output Complex Query Commands Parser Object Definitions Traffic Cop Simple Query commands Rewriter Rule/View Definitions Planner/Optimizer Statistics Executor User Data Symbol Tables, Data Tables, Libraries and so on. (Shared Repository) 8

System Memory PG Shared Memory Shared Buffer • WAL Buffer • • CLOG Buffer Lock Space Other Buffers Per Backend Per. Backend Memory maintenace_ work_mem temp_buffer work_mem catalog_cache optimizer/executor OS Cache <System Memory> • http: //www. postgresql. org/docs/9. 2/interactive/runtime-config-resource. html • Shared Memory는 매우 크고 모든 Buffer는 동기화되지 않음. • Shared Buffer - Postgre. SQL 서버가 사용하는 공유 메모리 버퍼. • temp_buffer - 각각의 세션이 사용하는 임시 버퍼. • work_mem - 임시 디스크 파일에 쓰기 전에 내부 정렬 및 해시 테이블 조작에 사용되는 메모리. • maintenace_work_mem - VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY등 유지 관리에 사용되는 메모리. 9

Shared Buffer System Memory PG Shared Memory Shared Buffer • WAL Buffer • • CLOG Buffer Lock Space Other Buffers Per Backend Per. Backend Memory maintenace_ work_mem temp_buffer work_mem catalog_cache optimizer/executor Client Apps postmater OS Cache Disk Read Buffering • On shred buffer • One read I/O • Manu logical read from buffer chache postmater work mem shared memory Shared Buffer Database Files Log Buffer WAL Files 10

Internal Shared Buffer 11

BGWriter Utility Process BG Writer WAL Writer Archiver WAL Sender Stats Collector Auto Vaccum SYS Logger WAL Receiver Client Apps • 버퍼 쓰기 • 체크포인트 발생 Shared Buffer -> Data Files • 커밋 발생 Log Buffer -> WAL Files • WAL - Write Ahead Log - 트랜잭션 변경 기록 - segment = 16 MB(default) checkpoint_segment = 3(default) checkpoint_timeout = 300초 postmater work mem shared memory Shared Buffer Log Buffer bgwirter checkpoint Database Files commit WAL Files 12

Archiver Utility Process BG Writer WAL Writer Archiver WAL Sender Stats Collector Auto Vaccum SYS Logger WAL Receiver Client Apps postmater • WAL 로그 아카이브 • default = OFF postmater work mem shared memory Shared Buffer Log Buffer bgwirter Database Files WAL Files archiver Archive Files 13

Archiver Utility Process BG Writer WAL Writer Archiver WAL Sender Stats Collector Auto Vaccum SYS Logger WAL Receiver Client Apps • • • Statistics 정보 수집 테이블 엑세스 정보 인덱스 액세스 정보 현재 실행 쿼리 정보 버퍼 캐시 상태 정보 postmater work mem shared memory Shared Buffer Log Buffer bgwirter stats Database Files WAL Files archiver Archive Files 14

Storage Manager Buffer Manager Lock Manager File Manager Semaphore & Shared Memory Page Manager Disk Manager <Storage Manager> • 버퍼에 공유메모리를 제공하고 DB를 Access함. 15

Database Cluster Sub Configure Directory File Lock File • Collection of databases • Managed by Single server instance(Postmaster) • Cluster 구조 - 데이터 디렉토리 - shared catalog tables - port • 클러스터 생성 - directory 생성 - initdb –D /data 2 - port 변경(5432 ->5445) 16

Data Directory Database Cluster Sub Configure Directory File Lock File 17

Tablespace • database 위치 select oid, dataname from pg_database; Database Cluster • ts_data 1 테이블 스페이스 위치 select oid, spcname from pg_tablespace; Sub Configure Directory File Lock File $POSTGRES_HOME (/home/postgres/pgsql) data base 1 (template 1) 12865 (template 0) 12870 (postgres) pg_tblspc 12607 12613 12621 12636 ∙∙∙ 16931 -> /Databases/ts_data 1 68656 -> /Databases/ts_data 2 ∙∙∙ 18

Table • ttt 테이블 정보 select oid, relname, reltablespace, relfilenode from pg_class where relkind = ‘r’ and relname = ‘ttt’; Database Cluster Sub Configure Directory File Lock File $POSTGRES_HOME (/home/postgres/pgsql) data base pg_tblspc 16931 -> /Databases/ts_data 1 PG_9. 2_201204301 12870 77869 19

Data File Database Cluster Sub Configure Directory File Lock File • database는 하나의 디렉토리를 가짐 - /postgres/pgsql/data/base/1 -- template 1 - /postgres/pgsql/data/base/12870 -- postgres • 테이블은 하나의 파일에 저장됨 - /postgres/pgsql/data/pg_tblspc/16931/12870/77869 -- ttt 테이블 - 파일 최대 크기 : 1 GB - 테이블 크기가 늘어나면 파일이 split됨(32 TB까지, TOAST) - 77869 (1 GB) 77869. 1 (1 GB) 77869. 2 (1 GB) 20

Internal Block 23

Q&A