개요
Vacuum Full 작업을 온라인으로 진행 할 수 있게 도와주며, exclusive lock 을 생성하지 않아 운영 중인 서비스에 적합 합니다.
postgreSQL 에서는 reorg 작업이 따로 있지 않기에 해당 기능을 extension 으로 대체 하였습니다.
- Vacuum 이라는 특별한 시스템으로 인해 reorg 기능이 따로 존재하지 않음
- URL : /https://reorg.github.io/pg_repack/
다음 방법 중 하나를 선택하여 실행 할 수 있음
- Online CLUSTER ( ordered by cluster index )
- 지정된 열 기준으로 정렬
- 온라인 Vacuum Full
- 테이블의 인덱스만 재구축 또는 재배치
필수 조건
- Super user 여야만 한다
- 해당 Table 에는 Primary Key or NOT NULL 컬럼에 Unique index 존재
- postgresql 개발 패키지 설치 ( postgresql-devel )
설치
pg_repack은 Linux 환경에서 make를 사용하여 빌드
$ cd pg_repack
$ make
$ make install
$ psql -c "create extension pg_repack" -d [Database_name]사용법
pg_repack [옵션] ... [DB_Name][옵션] 에는 다음 내용들을 지정할 수 있습니다.
옵션
| -a, –all | 모든 데이터베이스를 실행 |
| -t, –table=TABLE | 특정 테이블만 실행 |
| -l, –parent-table=TALBE | 특정 상위 테이블만 실행 |
| -c, –schema=SCHEMA | 특정 스키마의 테이블만 실행 |
| -s, –tablespace=TBLSPC | 실행된 테이블을 새로운 테이블 스페이스로 이동 |
| -S, –moveidx | 실행된 인덱스를 새로운 테이블 스페이스로 이동 |
| -o, –order-by=COLUMNS | 클러스터 키 열 별로 정렬 |
| -n, –no-order | 클러스터 대신 vacuum 전체 실행 |
| -N, –dry-run | 다시 실행된 내용을 프린트 후 종료 |
| -j, –jobs=NUM | 각 테이블에 대해 병렬 작업을 실행 |
| -i, –index=INDEX | 지정된 인덱스만 실행 |
| -x, –only-indexs | 지정된 테이블의 인덱스만 실행 |
| -T, –wait-timeout=SECS | 충돌 시 다른 백엔드 종료 |
| -D, –no-kill-backend | 시간 초과 시 다른 백엔드를 종료 |
| -Z, –no-analyze | analyze 하지 않음 |
| -k, –no-superuser-check | 슈퍼 유저 확인을 건너 뜀 |
| -C, –exclude-extension | 특정 확장자에 속하는 테이블을 다시 압축하지 않음 |
Connection 옵션
| -d, –dbname=DBNAME | 연결할 데이터베이스 이름 |
| -h, –host=HOSTNAME | 데이터베이스 서버 호스트 또는 소켓 디렉토리 |
| -p, –port=PORT | 데이터베이스 서버 포트 |
| -U, –username=USERNAME | 연결할 사용자 이름 |
| -w, –no-password | 비밀번호를 입력하지 않아도 됨 |
| -W, –password | 비밀번호를 입력해야 함 |
Generic 옵션
| -e, –echo | 에코 쿼리 |
| -E, –elevel=LEVEL | 출력 메세지 레벨 설정 |
| –help | 도움말 확인 |
| –version | 버전 정보 출력 |
Example
데이터베이스에서 모든 클러스터링 된 테이블에 대해 온라인 CLUSTER를 수행 하고, 모든 비 클러스터링 된 테이블에 대해 온라인 VACUUM FULL을 수행
$ pg_repack test 데이터베이스에서 foo 및 bar 테이블에 대해 온라인 VACUUM FULL을 수행
$ pg_repack --no-order --table foo -table bar test 테이블 foo 의 모든 인덱스를 테이블 스페이스 tbs로 이동합니다.
$ pg_repack -d test --table foo --only-indexes --tablespace tbs 지정된 인덱스를 테이블 스페이스 tbs 로 이동합니다.
$ pg_repack -d test --index idx --tablespace tbs 제약사항
- temp table 들은 pg_repack 동작을 할 수 없음
- GiST indexes 를 사용하는 테이블은 pg_repack 동작을 할 수 없음
- pg_repack이 작업 중인 동안에는 대상 테이블의 DDL 명령을 VACUUM이나 ANALYZE를 제외하고 수행 할 수 없습니다. 전체 테이블 재패킹 중에는 ACCESS SHARE 락이 대상 테이블에 걸려 이 제한을 강제하며 버전 1.18 이전을 사용하는 경우 pg_repack이 실행 중인 동안 대상 테이블에 대한 어떤 DDL 명령도 시도해서는 안됩니다. 대부분의 경우 pg_repack은 올바르게 실패하고 롤백할 것이지만, 이전 버전에서는 몇 가지 경우에 데이터 손상이 발생할 수 있는 경우가 있음
동작 방식
전체 테이블 재패킹을 수행하려면, pg_repack은 다음과 같은 단계를 따릅니다.
- 원본 테이블에 대한 변경 내용을 기록하기 위한 로그 테이블을 생성합니다.
- 원본 테이블에 트리거를 추가하여 INSERT, UPDATE 및 DELETE 작업을 로그 테이블에 기록합니다.
- 이전 테이블의 모든 행을 포함하는 새로운 테이블을 생성합니다.
- 이 새로운 테이블에 인덱스를 구축합니다.
- 로그 테이블에 발생한 모든 변경 사항을 새 테이블에 적용합니다.
- 시스템 카탈로그를 사용하여 테이블, 인덱스 및 toast 테이블을 포함하여 테이블을 교체합니다.
- 원본 테이블을 삭제합니다.
pg_repack은 초기 설정 단계 ( 위의 단계 1과 2 ) 및 최종 교체 및 삭제 단계 ( 위의 단계 6과 7 ) 중에 ACCESS EXCLUSIVE 락을 짧은 시간 동안만 보유합니다. 나머지 시간 동안 pg_repack은 원본 테이블에 대해 ACCESS SHARE 락만 보유해야 하므로 INSERT, UPDATE 및 DELETE 작업은 계속 진행 될 수 있습니다.
추가 테스트
Vacuum Full
- vaccum full 수행 시 select 불가
- 2번 사진에서 vacuum full 수행 후 1번 사진에서 select 문 수행 후 확인 시 SELECT 문이 응답을 기다리고 있음
2. 확인
Lock, Dead_tuple 확인
-- Lock 확인
SELECT
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
cast(date_trunc('second',query_start) AS timestamp) AS query_start,
substr(query,1,25) AS query
FROM
pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.
relnamespace),
pg_stat_activity
WHERE
NOT pg_locks.pid=pg_backend_pid() AND
pg_locks.pid=pg_stat_activity.pid ;
-- dead_tuple 확인
SELECT
schemaname || '.' || relname AS table_name,
n_dead_tup,
n_live_tup,
CASE
WHEN (n_dead_tup + n_live_tup) = 0 THEN -1
ELSE ROUND((n_dead_tup::decimal / (n_dead_tup + n_live_tup)) * 100, 2)
END AS dead_tuple_ratio
FROM
pg_stat_all_tables
WHERE
schemaname NOT LIKE 'pg_%' AND
schemaname != 'information_schema'
ORDER BY
dead_tuple_ratio DESC;
pg_repack
1. pg_repack 수행 중 SELECT 문 수행
pg_repack은 accessExclusive 잡지 않기 때문에 SELECT 문이 수행 됨
pg_repack 수행 후 SELECT 문 수행
2. pg_repack 수행 중 UPDATE 진행
pg_repack 수행이 끝나기전까지 UPDATE 문이 끝나야 UPDATE 문이 수행이 됨 그렇지 않으면 pg 에서 CANCEL 커맨드를 날림
pg_repack 수행 후 해당 테이블에 UPDATE 문 수행
pg_repack 수행이 끝나기 전에 UPDATE 문이 끝나지 않는다면 pg 에서 해당 UPDATE 문 트랜잭션을 CANCEL 하게 됨
CANCEL 수행 후 ANALYZE를 수행
3. pg_repack 수행이 끝나기 전에 UPDATE 문이 끝날 시에는 문제가 없다
Failover
테스트 환경 사전 준비
pg_repack 도 구성 완료
autovacuum = off
데드 튜플 생성
1. pg_repack 수행 중 DB 다운
-- Primary (192.168.1.222)
nohup pg_repack -t bmsql_stock -d benchmarksql -U benchmarksql &pg_ctl stop 전
pg_ctl stop 후 Failover
failover 후 Primary ( 192.168.1.223 ) 에서 조회 시 통계 정보를 갱신이 필요
-- Primary (192.168.1.222)
psql benchmarksql #dead_tuple 현황을 보기 위해 analyze
analyze public.bmsql_stock ;
analyze public.bmsql_item ;
analyze public.bmsql_order_line;
analyze public.bmsql_new_order ;
analyze public.bmsql_config ;
analyze public.bmsql_warehouse ;
analyze public.bmsql_history ;
analyze public.bmsql_district ;
analyze public.bmsql_oorder ;
analyze public.bmsql_customer ;통계 정보 갱신 후 tuple 비교
pg_repack 수행 시 생성됐던 테이블은 그대로 넘어온 걸 확인
해당 문제가 발생하고 Primary ( 192.168.1.223 )에서 오류 발생
pg_repack 도중 failover를 했기에 pg_repack 을 수행하기 위해 생성됐던 오브젝트들은 그대로 전재하기에 관련된 오브젝트를 다 Drop 해줘야 합니다.
삭제해야하는 목록
– table
– trigger
– type
Drop extension 후 다시 Create extension 진행
pg_repack 수행할 때 -e 옵션을 주고 수행하게 되면 세부 동작을 확인 가능
