Postgres Vaccum

꽉오·2021년 11월 28일

Database MVCC PostgreSQL vaccum

어쩌다 발견했어요?

급하게 작성한 코드를 재작성하면서, 이전 로그 발견. 약 100건이 넘는 Insert / Update 가 10분마다 동시에 발생함.

이때, RDS 성능향상 도우미에는 항상 autovacuum 이라는 Query 가 자동으로 실행되었음.

이게 뭘까 싶어서 확인해봤던 내용. (요즘 파일처리 듣고있는데, 지금 구현하는거랑 비슷해서 재미있네요, 이번 과제는 조지긴 했지만..)

Vacuum

Postgres 에만 있는 내용이고, SQLite 에도 비슷한 개념이 있다.

Vacuum 이 실행되기 위한 조건이 네가지가 있는데 그 중 2가지 정도만 소개해볼 예정.

공간 재사용 Vacuum

MVCC (다중 버전 동시성 제어) 구현에 따른 튜플 개념으로 인해 발생한 문제를 해결하기 위한 방법. (삭제되어 커밋된 자료가 다른 트랜잭션에서 사용되고 있을 경우, 이 때문에 발생하는 문제를 해결하기 위함.)

PostgreSQL 에서 모든 데이터는 tuple 형태로 저장됨. 모든 tuple 은 live tuple dead tuple 로 나뉘며, 더이상 참조되지 않는 tuple 을 dead tuple 이라고 함.

만약 특정 Column, Row 를 업데이트 하는 트랜잭션이 수행되면, MVCC 를 위해 다음과 같이 동작한다.

FSM 에 여유가 있는지 확인, 없으면 FSM 을 추가적으로 확보 (FSM 은 파일형태로 저장됨)
FSM 의 빈 공간에 업데이트된 데이터를 기록, 이때 tuple 이 추가됨.
기존 포인터를 새로운 tuple 로 갈아끼움
이전 정보에 대한 튜플은 참조를 안하기에, dead tuple 로 변함.

**VM(Visibility map): live tuple 에 대한 정보를 가지고 있음

**FSM(Free space map): 빈공간에 대한 정보를 가지고 있음

여기서의 Vacuum

⇒ dead tuple 을 FSM 으로 반환하여 다시 사용가능하게 하는 작업

⇒ 모든 Update 는 사실상 Insert 와 동일. Delete 또한, Vacuum 없이는 FSM 으로 반환되거나 저장소에서 삭제되지 않으며, 무한정 불어나는 문제가 발생한다.

dead tuple 이 너무 많아져서 파일 한 페이지 읽을때 찾는 튜플이 없어서 I/O 가 더 많이 발생.

dead tuple 이 너무 많으니, 에널라이저가 그냥 index 쓰지마! 하는 판단을 내리기도 함.

Tuple 정리 측면에서의 Vacuum 동작

Vacuum

테이블이나 인덱스에서 삭제된 자료를 정리하고 그 데이터 공간을 FSM 으로 반환 (빈공간으로 만듦) 특정 페이지를 삭제해서 운영체제 입장에서 용량 확보도 가능함.

Full Vacuum

자료를 모아모아 새로운 테이블을 만드는 작업 → 무진장 오래걸리며 테이블 Lock 이 걸림 → Deprecate 된 테이블 위주로 작업하기

Autovacuum

자동으로 analyze 와 freeze 명령을 실행함. (두가지를 필요에 따라 알아서 사용) 기본적으로 autovacuum 을 실행하는 옵션이 켜져있으며, config 파일에 기본적인 설정 값들이 들어있음

autovacuum_vacuum_threshold: vacuum 이 일어나기 위한 dead tuple 의 최소 갯수. 기본 값은 50.
autovacuum_vacuum_scale_factor: vacuum 이 일어나기 위한 live tuple 대비 dead tuple 의 최소 비율. 기본 값은 0.2.

100,000 건의 데이터에서 autovacuum 이 일어나기 위한 dead tuple 개수

(100,000 * 0.2) + 50 = 20,050

아래 명령어로 특정 테이블에서 언제 vacuum 이 실행되었는지 확인이 가능하다.

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname asc;

트랜잭션 ID 관리 Vacuum (vacuum freeze)

PostgreSQL 에서 튜플 하나는 두개의 xid 를 가지게 된다.

xmin: 튜플이 Insert 될 때 이후 트랜잭션이볼 수 있도록 가지는 Id
xmax: Update, Delete 가 될 때 해당 시점 Id

위 사진처럼, min ~ max 사이의 트랜잭션 ID 를 가진 친구만, 데이터를 볼 수 있고, 그 이외의 상황에선 데이터를 볼 수 없음.

40억개의 ID 를 사용할 수 있는데, 다 쓰면 ID 가 1이 됨. 이렇게 될 경우, Id 가 1인 친구는, xmin 40억인 친구가 자신보다 크기에, 조회할 수가 없다.

이렇게 나보다 큰 친구들을 Freeze 마크를 표기해서 무조건 older 로 볼 수 있도록 변경

XID 측면에서의 vaccum

autovacuum_freeze_max_age = 1000

vacuum_freeze_min_age = 100

만약 특정 테이블의 age 가 1000 에 도달하게 된다면, (xid 가 1 증가할때마다 age 도 1 씩 증가) 최근 100 개의 xid 로그를 남겨두고, 나머지는 삭제한다. 이후 age 는 100 이 됨

→ 100 개 이전의 xid 에서 생성된 table 처럼 됨

**relfrozenxid: vacuum 작업을 수행한 xid (실제 수행된 시점의 xid - vacuum_freeze_min_age 값을 가짐)

Analyze

쿼리 실행기는 최적의 쿼리를 위해 각 테이블에 저장된 통계 자료를 생성, pg_statistic 에 저장함, 이 과정은 analyze 를 사용해서 생성하거나 autovacuum 시에 자동으로 실행된다.

select * from pg_stats where tablename = 'persons' and attname = 'first_name';
schemaname        | public
tablename         | persons
attname           | first_name
inherited         | f
null_frac         | 0
avg_width         | 11
n_distinct        | -0.484307
most_common_vals  | {"John","Mary"...
most_common_freqs | {0.00867898,0.00640832...
histogram_bounds  | {-,"AAA"...
correlation       | 0.00826469