PostgreSQL에서의 Vacuum이 하는 역할 ??
- 공간 재 사용
- 트랙잭션 ID 관리
- 통계정보 갱신
- 인덱스 검색 성능 향상
명령어
• 표준 Vacuum :
• Vacuum 수행 중에도, SELECT, DML 작업이 가능.
• 단순히 사용 가능한 공간을 Free Space Map에 반환.
• 이전 vacuum 작업 이후 부터, 변경된 페이지들만을 대상으로 Vacuum을 진행
• 표준 vacuum 명령이라도 가장 뒤쪽의 페이지부터 연속된 빈 페이지가 존재 할 경우, 해당 페이지를 삭제 하여 Disk의 공간을 확보 한다.
• Vacuum Full :
• 테이블에서 사용할 수 있는 데이터만을 따로 모아서, 새로운 공간에 저장하는 방식
--> create as select 작업이라고 생각하면 됨. 그래서 공간이 두 배 필요하고, 기존의 테이블 사이즈보다 작아짐.
(디스크 상의 여유 공간이 있어야 작업이 가능 함)
• 이로 인하여 시간도 오래 걸리며, 수행되는 테이블에 대한 exclusive Lock이 발생 됨.
• 디스크 상의 여유 공간이 확보 됨.
파라미터 (postgresql.conf)
Parameter :
autovacuum
autovacuum_analyze_scale_factor
autovacuum_analyze_threshold
autovacuum_freeze_max_age
autovacuum_max_workers
autovacuum_multixact_freeze_max_age
autovacuum_naptime
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
autovacuum_vacuum_scale_factor
autovacuum_vacuum_threshold
autovacuum_work_mem
log_autovacuum_min_duration
Create table or Alter table command :
autovacuum_enabled
autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor
autovacuum_freeze_max_age
vacuum_freeze_min_age
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
CREATE TABLE test ( id int ) with (autovacuum_enabled=TRUE) ;
select relname, reloptions from pg_class
where relname = 'test' ;
relname |reloptions |
--------|------------------------|
test |autovacuum_enabled=true |
ALTER TABLE test SET (autovacuum_enabled = FALSE);
select relname, reloptions from pg_class
where relname = 'test' ;
relname |reloptions |
--------|-------------------------|
test |autovacuum_enabled=false |
1. 공간 재 사용
PostgreSQL은 Update가 발생 할 경우 기존 Row 데이터 영역의 데이터를 변경 하지 않고, 새로운 데이터를 insert 한다.
이는 다중 버전 동시성 제어(Multiversion Concurrency Control, MVCC)를 구현하기 위함이며, update나 delete의 대상이 되었던 이전 데이터를 다른 세션에서 사용 중일 수 있으므로, Update나 Delete가 끝난 이후에도 해당 공간을 삭제 하지 않는다. (MVCC는 트랜잭션 ID를 기반으로 한다.)
새로운 Insert나 update가 발생 할 경우 PostgreSQL은 FSM(Free Space Map)에서 사용 가능한 공간을 할당 받아서 데이터를 저장한다.
Update나 Delete에 의하여 데이터는 삭제 되었으나, 실제 물리적인 공간을 차지하고 있는 dead tuple들은 Vacuum 명령을 이용하여 FSM에 반환을 해 주어야 한다.
Vacuum명령을 이용하여 사용되지 않는 공간을 정리하지 않을 경우, insert 나 update가 빈번히 발생하는 테이블의 크기가 dead tuple로 인하여 비 정상적으로 커 질 수 있다.
Free Space Map (FSM)
- 8.4 이전
PG가 Startup될 때 shared memory로 구성(pg_fsm.cache)되며, shutdown되거나 instance crash가 될 경우 분실 된다.
max_fsm_pages, max_fsm_relations 파라미터로 구성 됨.
- 8.4 이후
Disk에 8k 짜리 파일로 관리. shared_buffer에 cache 된다.
vacuum 명령 수행 후 생성 된다. (매번 재 생성)
Binary tree 형태의 파일로 존재 함
max_fsm_pages, max_fsm_relations 파라미터 사용 안함.
Visibility Map(VM)
Free Space Map은 사용 가능한 공간의 정보가 저장되는 map인 반면, Visibility Map(VM)은 페이지 안에 dead tuple이 없는 페이지를 나타내는 Map이다.
Vacuum수행 시 해당 페이지를 읽어야 하는지 말아야 하는지 판단 하는데 사용되며, Only index 스캔으로 쿼리에 대한 결과를 가져올 때 참조되는 파일이다.
테이블의 하나의 페이지당 하나의 Bit로 표현하며, 1이 의미하는 것은 이 페이지의 모든 tuple들은 모든 트랜잭션에서 보여진다는 뜻이다.
다른 말로 표현해 보면 모든 트랜잭션에서 다 볼 수 있다는 것은, 해당 페이지의 모든 데이터가 변경이 없는 상태란 뜻으로 해석 할 수도 있다.
페이지에 insert, update, delete된 tuple이 있으면 해당 페이지의 bit는 지워 진다.
그래서 변경이 없는 페이지(bit가 1로 설정 된)는 이후 vacuum 수행 시 해당 페이지는 skip 된다. 또 연속된 32개의 all-visible page가 있으면, vacuum을 Skip한다.
최초 vacuum 명령으로 생성되고, vacuum 명령으로 update 된다.
2. 트랜잭션 ID관리
PostgreSQL에서는 하나의 tuple은 두 개의 트랜잭션 ID(XID)를 갖는다.
tuple이 Insert 되었을 때 해당 tuple은 이후 트랜잭션에서 볼 수 있어야 하므로 xmin 이라는 트랜잭션 ID값을 가지게 된다.
이후 해당 tuple이 update 되거나 delete가 되면 해당 시점의 트랜잭션 ID가 xmax값이 된다. (xmin : created / xmax : expired)
그래서 다른 세션에서 해당 데이터를 SELECT 할때 xmin ~ xmax 사이의 트랜잭션 ID를 가진 세션이 엑세스를 할 경우는 데이터가 보여지고, 그 사이에 없는 트랜잭션 ID를 가진 세션이 엑세스를 할 경우 보여지지 않는다. (MVCC는 트랜잭션 ID를 기반으로 한다.)
PostgreSQL에서는 트랜잭션 ID로 4billion(40억)개를 사용할 수 있다. 이중 20억개를 (older) , 20억개를 (newer)로 하여 사용하며, 40억개를 다 사용하면 다시 1부터 트랜잭션 ID를 부여해서 사용한다.
만약 테이블의 tuple이 트랜잭션 ID 99999를 가진 시점에 insert 된 후, 다량의 트랜잭션이 발생하여서 트랜잭션 ID를 다 사용한 후, 다시 1부터 트랜잭션 ID가 사용되어 지는 시점이라고 할 때, 트랜잭션 ID 1을 사용 중인 세션은 xmin 99999인 데이터를 조회할 수가 없다.
분명 이전 데이터 이기에 조회가 가능해야 하지만, 현재 자신의 트랜잭션 ID보다 크므로 조회를 할 수 없게 된다.
이는 큰 문제를 발생 시킬 수 있으므로, 해당 tuple에 freeze 마크(FrozenTransactionId)를 해 주면, freeze 된 tuple은 항상 그 어떤 트랜잭션 ID 보다 older가 되어서 항상 조회를 할 수 있게 된다.
자동으로 freeze될 수도 있고, 변화가 없는 테이블일 경우 명시적으로 analyze freeze 명령을 이용하여 FREEZE 시킬 수 있다.
3. Autovacuum
vacuum, freeze, analyze명령을 자동으로 수행해 준다.
Luncher 프로세스와 Worker 프로세스로 구성되어 있으며, autovacuum_max_workers 파라미터 값으로 조정.
launcher 프로세스는 autovacuum_naptime(초)값이 지날 때 마다, 각각의 데이터 베이스에 Worker 프로세스를 하나씩 시작 시킨다. 시작된 Worker프로세스는 insert, update, delete된 테이블들을 check 해서 analyze가 필요한지 체크 한다. (track_counts=true 되어 있어야 함), Temporary table은 autovacuum이 수행되지 않음.
Autovacuum은 오브젝트 사이즈를 줄 이진 않는다.
scale_factor와 threshold 파라미터는 autovacuum이 더 자주 돌아서, autovacuum시 테이블에 더 적은 부분만 vacuum을 할 수 있도록 설정 하기 위한 파라미터들이다.
데이터 베이스 레벨에서 설정을 할 수도 있고, 테이블 별로 설정을 해 줄 수도 있다.
|
파라미터 |
설 명 |
|
log_autovacuum_min_duration |
autovacuum 결과를 log 파일에 저장할 지 여부를 결정 (-1 :not logged, 0: log all, 시간: 해당 시간 이상 autovacuum이 수행된 것만 log) |
|
autovacuum_vacuum_threshold |
이전 vacuum이후 update나 delete된 행 수 |
|
autovacuum_analyze_threshold |
이전 vacuum이후 insert, update, delete된 행 수 |
|
autovacuum_vacuum_scale_factor |
얼마나 변경이 되어야지 autovacuum이 수행될지 여부 결정(예를 들어서 0.01이면 autovacuum이 돌기 전에 테이블 사이즈의 1%가 변해야 함) |
|
autovacuum_analyze_scale_factor |
얼마나 변경 되어야지 auto analyze가 수행될지 여부 결정 |
|
auto vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples (number of tuples = pg_class.reltuples) |
|
auto analyze threshold = analyze base threshold + analyze scale factor * number of tuples (number of tuples = pg_class.reltuples) |
Autovacuum이 수행 되어야 할 오브젝트 선정이나, Autovacuum시 일을 하면서 얼마나 자원을 사용하게 되는지는 다음의 파라미터로 설정 한다.
|
파라미터 |
설 명 |
|
autovacuum_naptime |
autovacuum 대상이 되는 오브젝트를 선정하는 주기(?). 해당 시간이 되면 launcher 프로세스가 한 번에 하나의 데이터베이스를 처리 할 수 있도록 worker프로세스를 하나씩 깨운다. 깨어난 worker프로세스는 대상 오브젝트들이 있는지 확인 함 |
|
autovacuum_vacuum_cost_limt |
autovacuum 대상이 되는 오브젝트를 autovacuum할때 시스템 자원을 얼마나 사용하면서 autovacuum을 수행하게 할 지 설정하는 limit값. 동시에 여러 worker가 수행이 되고 있을 때, 수행되고 있는 모든 worker의 자원 사용량에 대한 제한이다. |
|
autovacuum_vacuum_cost_delay |
autovacuum을 수행하다가 cost_limt값을 초과할 경우, cost_delay 설정 시간 동안 쉬었다가 다시 autovacuum을 수행 함 |
vacuum이 항상 수행중이라면 maintenance_work_mem 이 작게 설정 되어 있는지 확인이 필요 함. 기본적으로 시스템 메모리 1G당 50M로 계산 하면 됨.
vaccum worker 프로세스 마다 maintenance_work_mem 메모리를 사용하게 되므로, 지나치게 크게 설정이 되어 있으면 autovacuum_naptime마다 worker가 수행될 때
해당 메모리가 없으면 메모리 부족 메세지를 출력시킬 수 있다.
표준 Vacuum 수행 시는 이전 Vacuum 작업 이후 변경된 페이지들에 대해서만 작업을 한다. 그러나 다음의 경우는 표준 Vacuum이라도 Full Scan이 발생 함
1. relfrozenxid값이 vacuum_freeze_table_age 보다 클 경우 모든 테이블의 데이터를 다 읽어서 Frozen Transaction Id를 tuple에 설정 함
2. vacuum freeze 명령 수행 시
위의 두가지 경우 이외에도 XID wrap around를 방지 하기 위하여 autovacuum이 수행 될 때 테이블에 대한 Full Scan이 발생 한다.
autovacuum이 수행 되어야 하는데 무슨 이유에서 autovacuum이 수행되지 않을 경우, 다음과 같은 메시지를 출력 한다.
|
WARNING: database "mydb" must be vacuumed within 177009986 transactions HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb". |
위와 같은 메시지가 출력 될 경우, 서버를 중지하고, 관리자 모드(postgres --single -D /..... )로 서버를 실행해서 수동으로 vacuum을 수행해 줘서 datfrozenxid값 까지 변경을 시켜 줘야 한다.
그런데 위의 경고를 무시하고 있다가 트랜잭션 XID가 wrap around가 될 경우 다음과 같은 에러 메시지가 출력 된다.
|
ERROR:
database is not accepting commands to avoid wraparound data loss in
database "mydb" |
4. Age
age의 개념 (http://oraschool.tistory.com/70)
PostgreSQL에서는 Object가 생성되면, 생성되는 시점의 트랜잭션 ID를 가지고 Object의 relfrozenxid를 설정 하고, age는 1이 된다.
생성 이후 데이터베이스에 트랜잭션이 하나씩 발생할 때마다(해당 Object와 상관이 없어도 데이터 베이스 전체로 따짐) 모든 Object들의 나이가 1씩 증가가 된다.
Age를 가지고 트랜잭션 아이디가 wrap around 되었을 때도, 데이터를 볼 수 있도록 데이터들을 freeze하는데 사용 된다. 그래서 파라미터들이 다 freeze라는 단어를 포함하고 있음.
Autovacuum_enabled가 false로 되어 있어도, XID Wrap Around용으로 수행되는 autovacuum은 수행이 된다.
autovacuum_freeze_max_age값은 Object나이가 설정 값에 도달하면 autovacuum을 수행한 후 vacuum_freeze_min_age 파라미터 값을 참조하여 젊게 해 주는 역할을 한다.
예를 들어보면 ,
현재 트랜잭션 ID가 100 인 시점에 테이블 A를 생성 했다고 하면,
autovacuum_freeze_max_age = 1000 (자동으로 autovacuum수행 할 나이 값)
vacuum_freeze_min_age = 100
autovacuum 수행 후
relfrozenxid = currentXID - vacuum_freeze_min_age
age = currentXID - Object relfrozenxid
위의 그림을 설명해 보면 실제 A테이블은 XID가 100인 시점에 생성이 되어서, XID가 1100 시점에 autovacuum 대상이 되어 autovacuum을 수행하게 된다.
그러면 A테이블은 XID가 1000인 시점에 생성된 것처럼 relfrozenxid가 1000으로 변경이 되고, 나이는 100으로 줄어든다.
이 말은 테이블 A가 마치 트랜잭션 1000인 시점에 생성 된 것 처럼 다시 태어난다(?) 젊어진다(?)
최초 생성 -------> autovacuum_freeze_max_age 값만큼 트랜잭션 증가 후 1차 autovacuum 발생
-------> 이후 autovacuum_freeze_max_age - vacuum_freeze_min_age 만큼의 트랜잭션 수행 후 계속 autovacuum 발생
-------> age가 점점 증가 하다가, vacuum_freeze_table_age가 되면 해당 테이블의 tuple을 모두 읽어서 freeze 시킨다.
|
pg_class.relfrozenxid |
해당 테이블에 vacuum작업을 진행 했던 트랜잭션 ID |
|
pg_database.datfrozenxid |
해당 데이터베이스 안의 테이블들의 relfrozenxid 중 가장 오래된 값 |
|
age |
현재 트랜잭션 XID 와 relfrozenxid 값을 가지고 계산한 나이 |
|
- 1M 이상되는 것들 중에서 나이가 가장 오래된 20개 테이블 목록 리스트 업
SELECT c.relname, age(relfrozenxid) as age, pg_size_pretty(pg_table_size(oid)) as table_size , p.setting::numeric - age(c.relfrozenxid) before_autovacuum FROM pg_class c , (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age' ) p WHERE c.relkind = 'r' and pg_table_size(c.oid) > 1024000 ORDER BY age(c.relfrozenxid) DESC LIMIT 20;
relname |age |table_size |before_autovacuum | -----------|--------|-----------|------------------| pg_proc |1383701 |1128 kB |198616299 | pg_rewrite |1383701 |1120 kB |198616299 | test |119 |48 MB |199999881 |
필요시 vacuum freeze pg_proc ;
- 데이터베이스의 가장 오래된 나이 SELECT datname, age(datfrozenxid) FROM pg_database;
datname |age | ----------|--------| template1 |1383701 | template0 |1383701 | postgres |1383701 | edb |1383701 | tpch |1383701 | tpcc |1383701 | qatest |1383701 |
|
relkind values r = table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table |