PostgreSQL New Feature는 시리즈로 구성됩니다.
- PostgreSQL 17 주요 기능 설명과 파라미터, 시스템 카탈로그 변화
- PostgreSQL 17 성능 최적화(Shared Buffer Flush, NOT NULL과 NULL 조회 최적화, 조건절에 따른 실행계획 최적화)
- PostgreSQL 17 Vacuum 성능 향상
- PostgreSQL 17 증분백업(Incremental Backup)
본 문서에서는 PostgreSQL 17에서 Vacuum 성능 향상에 대한 내용을 기술합니다.
Vacuum 성능 향상
PostgreSQL 16 버전까지는 구현상의 이유로 maintenance_work_mem 또는 autovacuum_work_mem설정 값을 크게 설정하더라도 Vacuum 수행 시 최대 1GB까지만 메모리를 사용할 수 있었습니다. 이로 인해 처리해야 할 Dead Tuple을 기억할 메모리 영역이 부족해져 불필요한 테이블 재스캔이 발생하는 경우가 있었습니다. 또한, 발견된 Dead Tuple을 저장하는 데이터 구조가 단순한 배열 형태였기 때문에 Dead Tuple의 개수가 많을 경우 효율적이라고 할 수 없었습니다.
PostgreSQL 17 버전에서 이러한 문제를 해결하는 것을 포함하여 Vacuum 구현의 변경이 있습니다.
- Vacuum의 효율성을 높이기 위해 Radix Tree 구조를 도입하여 메모리 사용량을 기존 대비 최대 20배 절약할 수 있고, Vacuum 수행 시 사용 가능한 메모리 크기의 제한이 제거되었습니다. 또한, 대량 데이터 처리의 성능 향상 시켜 Vacuum 처리 속도가 향상되었습니다.
- Dead Tuple 정리와 Tuple Freeze 작업을 한 번에 처리하도록 변경하여 WAL 생성량을 감소 시켰으며, 인덱스가 없는 테이블의 Vacuum 작업을 효율화 하여 WAL 생성량을 감소 시켰습니다.
Vacuum 성능 테스트
동일한 환경에서 PostgreSQL 16.4와 PostgreSQL 17.2의 Vacuum 성능 차이를 확인합니다. 테스트에 사용되는 PostgreSQL 파라미터는 autovacuum과 maintenance_work_mem 파라미터만 변경하며, 다른 파라미터는 기본값으로 사용합니다.
## postgresql.conf 설정
autovacuum = off
maintenance_work_mem = 2GB테스트는 총 6개의 Case로 진행 하며, 1억건의 데이터 생성 후 인덱스 존재 여부에 따라 5%, 50%, 95%의 데이터를 UPDATE 합니다. 각 UPDATE 수행 후 Vacuum 명령어에 VERBOSE 옵션을 추가하여 Vacuum 소요 시간과 WAL 생성량을 확인 하고, OS에서 메모리 사용량을 확인합니다. 추가적으로 5억건의 데이터로 Vacuum 수행 시 메모리 사용량이 최대가 되는 경우에 대해 확인합니다.
📢 Vacuum 수행 시 메모리 사용량 확인은 Vacuum을 수행한 세션의 PID를 통해 smaps을 3초마다 확인하여 Vacuum 수행 중 메모리 사용량이 가능 높았던 수치를 사용합니다.$ P={"Vacuum 수행 세션 PID"} $ while true ; do sleep 3; echo -n "private mem(kB): " ; \ cat /proc/$P/smaps | grep Private | sed -e 's/^.*://' -e 's/kB//' | \ awk '{ sum += $1 } ; END { print sum }'; done
📢 VACUUM VERBOSE에서는 TOAST 테이블에 대한 소요 시간과 WAL 생성량도 포함되어 출력 됩니다. 본 테스트에서는 TOAST 테이블에 저장되는 길이의 데이터가 존재하지 않으므로 TOAST에 대한 내용은 다루지 않습니다.
Vacuum 성능 테스트 Case
| Case | 상황 | 비고 |
| Case 1. | 1억건 데이터 + 전체 데이터 5% UPDATE | |
| Case 2. | 1억건 데이터 + 전체 데이터 50% UPDATE | |
| Case 3. | 1억건 데이터 + 전체 데이터 95% UPDATE | |
| Case 4. | 1억건 데이터 + 인덱스 생성 + 전체 데이터 5% UPDATE | |
| Case 5. | 1억건 데이터 + 인덱스 생성 + 전체 데이터 50% UPDATE | |
| Case 6. | 1억건 데이터 + 인덱스 생성 + 전체 데이터 95% UPDATE | |
| Case 7. | 5억건 데이터 + 인덱스 생성 + 전체 데이터 95% UPDATE | Vacuum 수행 시 메모리 최대 사용 |
Vacuum 성능 테스트 데이터 생성
아래 설명 되는 테스트는 PostgreSQL 16과 PostgreSQL 17에서 동일하게 수행합니다.
vacuum_tab01 ~ vacuum_tab06 테이블에는 1억건의 데이터를 생성하며, 인덱스 존재 여부에 따라 Vacuum 성능을 확인 하기 위해 vacuum_tab04 ~ vacuum_tab06 테이블에는 인덱스를 생성합니다. 추가적으로 vacuum_tab10 테이블에는 Vacuum 수행 시 메모리를 최대로 사용하는 상황을 만들기 위해 5억건의 데이터를 생성합니다.
-- 인덱스 존재 여부에 따른 테스트 테이블 생성
CREATE TABLE vacuum_tab01 AS SELECT i AS c1 , 'xxxxx' AS c2 FROM generate_series( 1, 100000000 ) AS i ;
CREATE TABLE vacuum_tab02 AS SELECT i AS c1 , 'xxxxx' AS c2 FROM generate_series( 1, 100000000 ) AS i ;
CREATE TABLE vacuum_tab03 AS SELECT i AS c1 , 'xxxxx' AS c2 FROM generate_series( 1, 100000000 ) AS i ;
CREATE TABLE vacuum_tab04 AS SELECT i AS c1 , 'xxxxx' AS c2 FROM generate_series( 1, 100000000 ) AS i ;
CREATE TABLE vacuum_tab05 AS SELECT i AS c1 , 'xxxxx' AS c2 FROM generate_series( 1, 100000000 ) AS i ;
CREATE TABLE vacuum_tab06 AS SELECT i AS c1 , 'xxxxx' AS c2 FROM generate_series( 1, 100000000 ) AS i ;
CREATE INDEX vacuum_tab04_idx ON vacuum_tab04 ( c1 ) ;
CREATE INDEX vacuum_tab05_idx ON vacuum_tab05 ( c1 ) ;
CREATE INDEX vacuum_tab06_idx ON vacuum_tab06 ( c1 ) ;
-- 추가 테스트 테이블 생성(Vacuum 수행 시 메모리 사용율 최대 1G 상황)
CREATE TABLE vacuum_tab10 AS SELECT i AS c1 , 'xxxxx' AS c2 FROM generate_series( 1, 500000000 ) AS i ;
CREATE INDEX vacuum_tab10_idx ON vacuum_tab10 ( c1 ) ;각 테스트 Case 별로 생성된 테이블에 데이터 갱신을 합니다.
UPDATE vacuum_tab01 SET c2 = 'yyyyy' WHERE c1 % 20 = 1 ; -- CASE 1. 5%(500만건) 데이터 갱신
UPDATE vacuum_tab02 SET c2 = 'yyyyy' WHERE c1 % 2 = 1 ; -- CASE 2. 50%(5,000만건) 데이터 갱신
UPDATE vacuum_tab03 SET c2 = 'yyyyy' WHERE c1 % 20 != 1 ; -- CASE 3. 95%(9,500만건) 데이터 갱신
UPDATE vacuum_tab04 SET c2 = 'yyyyy' WHERE c1 % 20 = 1 ; -- CASE 4. 5%(500만건) 데이터 갱신 (인덱스 존재)
UPDATE vacuum_tab05 SET c2 = 'yyyyy' WHERE c1 % 2 = 1 ; -- CASE 5. 50%(5,000만건) 데이터 갱신 (인덱스 존재)
UPDATE vacuum_tab06 SET c2 = 'yyyyy' WHERE c1 % 20 != 1 ; -- CASE 6. 95%(9,500만건) 데이터 갱신 (인덱스 존재)
UPDATE vacuum_tab10 SET c2 = 'yyyyy' WHERE c1 % 20 != 1 ; -- CASE 7. 5억건 데이터 + 95%(47,500만건) 데이터 갱신 (인덱스 존재)Vacuum 수행 전 테이블데 대한 Vacuum 수행 내역을 확인합니다. autovacuum=off이고 별도로 Vacuum을 수행 하지 않았기 때문에 Vacuum 수행 이력이 존재하지 않습니다.
SELECT relname , last_vacuum , vacuum_count , last_autovacuum , autovacuum_count , last_analyze , analyze_count , last_autoanalyze , autoanalyze_count
FROM pg_stat_all_tables
WHERE relname IN ( 'vacuum_tab01' , 'vacuum_tab02' , 'vacuum_tab03' , 'vacuum_tab04' , 'vacuum_tab05' , 'vacuum_tab06' , 'vacuum_tab10' ) ;
relname | last_vacuum | vacuum_count | last_autovacuum | autovacuum_count | last_analyze | analyze_count | last_autoanalyze | autoanalyze_count
--------------+-------------+--------------+-----------------+------------------+--------------+---------------+------------------+-------------------
vacuum_tab01 | | 0 | | 0 | | 0 | | 0
vacuum_tab02 | | 0 | | 0 | | 0 | | 0
vacuum_tab03 | | 0 | | 0 | | 0 | | 0
vacuum_tab04 | | 0 | | 0 | | 0 | | 0
vacuum_tab05 | | 0 | | 0 | | 0 | | 0
vacuum_tab06 | | 0 | | 0 | | 0 | | 0
vacuum_tab10 | | 0 | | 0 | | 0 | | 0
(7 rows)Vacuum 수행 시 메모리 사용량을 확인 하기 위해 새로운 터미널에서 smaps을 수행합니다.
## Vacuum 수행 할 세션에서 PID 조회
postgres=# SELECT pg_backend_pid() ;
pg_backend_pid
----------------
1306291## [새로운 터미널]
$ P=1306291
$ while true ; do sleep 3; echo -n "private mem(kB): " ; \
cat /proc/$P/smaps | grep Private | sed -e 's/^.*://' -e 's/kB//' | \
awk '{ sum += $1 } ; END { print sum }'; done
private mem(kB): 3916
... (이하생략) ...
Vacuum 수행
각 테스트 Case별로 Vacuum을 수행합니다.
VACUUM VERBOSE vacuum_tab01 ;
VACUUM VERBOSE vacuum_tab02 ;
VACUUM VERBOSE vacuum_tab03 ;
VACUUM VERBOSE vacuum_tab04 ;
VACUUM VERBOSE vacuum_tab05 ;
VACUUM VERBOSE vacuum_tab06 ;
VACUUM VERBOSE vacuum_tab10 ;Vacuum 수행 시 결과는 아래와 같습니다. Vacuum 수행 결과에서 Vacuum 소요 시간과 WAL 생성량을 확인하고 앞서 수행 한 smaps 결과 중 가장 높은 수치를 확인합니다.
## VACUUM VERBOSE vacuum_tab01 예시
INFO: vacuuming "postgres.public.vacuum_tab01"
INFO: finished vacuuming "postgres.public.vacuum_tab01": index scans: 0
pages: 0 removed, 567568 remain, 567568 scanned (100.00% of total)
tuples: 5000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 808, which was 0 XIDs old when operation ended
new relfrozenxid: 802, which is 10 XIDs ahead of previous value
frozen: 540541 pages from table (95.24% of total) had 95000085 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
I/O timings: read: 3920.129 ms, write: 2988.223 ms
avg read rate: 120.796 MB/s, avg write rate: 120.799 MB/s
buffer usage: 567605 hits, 567712 misses, 567724 dirtied
WAL usage: 1108110 records, 540568 full page images, 4277493264 bytes
system usage: CPU: user: 12.26 s, system: 8.70 s, elapsed: 36.72 s📢 VACUUM VERBOSE로 출력되는 내용은 본 문서 하단에서 설명합니다.
Vacuum 수행 후 테이블데 대한 Vacuum 수행 내역을 확인합니다. Vacuum을 1회 수행 했기 때문에 vacuum_count=1로 조회 되며, Vacuum 수행이 완료된 시간이 last_vacuum에 조회됩니다.
SELECT relname , last_vacuum , vacuum_count , last_autovacuum , autovacuum_count , last_analyze , analyze_count , last_autoanalyze , autoanalyze_count
FROM pg_stat_all_tables
WHERE relname IN ( 'vacuum_tab01' , 'vacuum_tab02' , 'vacuum_tab03' , 'vacuum_tab04' , 'vacuum_tab05' , 'vacuum_tab06' , 'vacuum_tab10' ) ;
relname | last_vacuum | vacuum_count | last_autovacuum | autovacuum_count | last_analyze | analyze_count | last_autoanalyze | autoanalyze_count
--------------+-------------------------------+--------------+-----------------+------------------+--------------+---------------+------------------+-------------------
vacuum_tab01 | 2024-12-10 14:29:35.461037+09 | 1 | | 0 | | 0 | | 0
vacuum_tab02 | 2024-12-10 14:31:33.575974+09 | 1 | | 0 | | 0 | | 0
vacuum_tab03 | 2024-12-10 14:33:19.233813+09 | 1 | | 0 | | 0 | | 0
vacuum_tab04 | 2024-12-10 14:35:38.332993+09 | 1 | | 0 | | 0 | | 0
vacuum_tab05 | 2024-12-10 15:13:19.135013+09 | 1 | | 0 | | 0 | | 0
vacuum_tab06 | 2024-12-10 15:16:37.782346+09 | 1 | | 0 | | 0 | | 0
vacuum_tab10 | 2024-12-10 17:16:37.782346+09 | 1 | | 0 | | 0 | | 0
(7 rows)
Vacuum 성능 테스트 결과
PostgreSQL 16.4와 PostgreSQL 17.2에서 Vacuum 수행 결과는 아래 표와 같습니다.
소요 시간 감소: Vaccum 소요 시간은 최대 30% 감소 되었습니다.WAL 생성량 감소: Vacuum 수행 시 WAL 생성량은 최대 30% 감소 되었습니다.메모리 사용량 감소: Vacuum 수행 시 메모리 사용량은 최대 95% 감소 되었습니다.
PostgreSQL 17에서 Vacuum 성능 향상을 테스트를 통하여 확인 할 수 있으며, 그 중 메모리 사용량 감소가 가장 큰 성능 향상을 보였습니다. 이는 Vacuum에 사용되던 메모리를 다른 작업에 사용할 수 있어 메모리 경합을 감소 시킬 수 있어 업무 성능 향상을 가져올 수 있을 것으로 예상됩니다.
📢 테스트 과정에서는 full_page_write=on으로 설정 되어 있었으며, full_page_write=off로 변경 시 WAL 생성이 증가 할 수 있습니다.
부록 1. VACUUM VERBOSE 해석
Vauum 수행 시 VERBOSE 옵션을 추가하면 Vacuum 처리 과정 및 통계 등을 확인 할 수 있습니다. 아래 예시를 통해 해석 방법을 설명합니다.
① INFO: vacuuming "postgres.public.vacuum_tab04"
② INFO: finished vacuuming "postgres.public.vacuum_tab04": index scans: 1
③ pages: 0 removed, 567570 remain, 567570 scanned (100.00% of total)
④ tuples: 5000000 removed, 100000000 remain, 0 are dead but not yet removable
⑤ removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 777, which is 13 XIDs ahead of previous value
⑥ frozen: 540541 pages from table (95.24% of total) had 95000005 tuples frozen
⑦ index scan needed: 540540 pages from table (95.24% of total) had 4999995 dead item identifiers removed
⑧ index "vacuum_tab04_idx": pages: 274194 in total, 0 newly deleted, 0 currently deleted, 0 reusable
⑨ avg read rate: 113.535 MB/s, avg write rate: 113.457 MB/s
⑩ buffer usage: 567660 hits, 1382447 misses, 1381488 dirtied
⑪ WAL usage: 2462415 records, 1354334 full page images, 10777301340 bytes
⑫ system usage: CPU: user: 25.40 s, system: 15.94 s, elapsed: 95.12 s
① Vacuum 작업 정보
INFO: vacuuming "postgres.public.vacuum_tab04"- postgres Database의 public 스키마에 속한 vacuum_tab04 테이블을 Vacuum 수행
② Vaccum 작업 완료 정보
INFO: finished vacuuming "postgres.public.vacuum_tab04": index scans: 1- postgres Database의 public 스키마에 속한 vacuum_tab04 테이블을 Vacuum을 완료하였고, 관련된 인덱스 Vacuum을 위해 인덱스를 한 번 스캔
index scans: 0의 경우 테이블에 관련된 인덱스가 없음을 추측 가능
③ 페이지 통계
pages: 0 removed, 567570 remain, 567570 scanned (100.00% of total)0 removed: 제거된 Page 수 0567570 remain: 남아 있는 Page 수 567,570567570 scanned (100.00% of total): 스캔 된 Page 수 567,570 (전체의 100%)
④ 튜플 통계
tuples: 5000000 removed, 100000000 remain, 0 are dead but not yet removable5000000 removed: 제거된 Tuple 수 5,000,000100000000 remain: 남아 있는 Tuple 수 100,000,0000 are dead but not yet removable: 아직 제거되지 않은 Tuple 수 0
⑤ XID 정보
removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 777, which is 13 XIDs ahead of previous valueremovable cutoff: 780 XID는 제거 가능한 트랜잭션의 기준점이며, 작업 종료 시 기준으로 0개의 트랜잭션이 제거 조건에 해당new relfrozenxid: 새로 설정된 relfrozenxid는 777로, 이전 값보다 13 트랜잭션 앞서 있으며, 이는 Freeze된 트랜잭션 ID 범위가 확장되었음을 의미
⑥ Frozen Tuple
frozen: 540541 pages from table (95.24% of total) had 95000005 tuples frozen- 540,541 페이지가 Frozen 되었고(전체의 95.24%) 95,000,005 Tuple이 Frozen
⑦ 인덱스 통계
index scan needed: 540540 pages from table (95.24% of total) had 4999995 dead item identifiers removed- 인덱스 스캔이 필요한 페이지 수는 540,540개 이고(전체의 95.24%) 제거된 항목은 4,999,995개(Dead Tuple과 관련된 인덱스 항목 정리)
⑧ 인덱스 상태
index "vacuum_tab04_idx": pages: 274194 in total, 0 newly deleted, 0 currently deleted, 0 reusable- vacuum_tab04_idx 인덱스 상태를 표현
274194 in total: 총 Page 수 274,1940 newly deleted: 새로 삭제된 Page 수 00 currently deleted: 현재 삭제된 Page 수 00 reusable: 재사용 가능한 Page 수 0
⑨ I/O 속도
avg read rate: 113.535 MB/s, avg write rate: 113.457 MB/savg read rate: 113.535 MB/s: 평균 읽기 속도는 113.535 MB/savg write rate: 113.457 MB/s: 평균 쓰기 속도는 113.457 MB/s
⑩ 버퍼 사용량
buffer usage: 567660 hits, 1382447 misses, 1381488 dirtied567660 hits: 캐시에서 데이터를 읽은 횟수 567,6601382447 misses: 디스크에서 데이터를 읽어온 횟수 1,382,4471381488 dirtied: Vaccum 작업으로 수정된 버퍼 수 1,381,488
⑪ WAL 통계
WAL usage: 2462415 records, 1354334 full page images, 10777301340 bytes2462415 records: 기록된 WAL Record 수 2,462,415개1354334 full page images: 전체 페이지 데이터가 WAL에 기록된 횟수는 1,354,334 (full_page_write=on 경우에만 수치 존재)10777301340 bytes: WAL의 전체 크기는 약 10.78GB
⑫ 시스템 리소스 사용량
system usage: CPU: user: 25.40 s, system: 15.94 s, elapsed: 95.12 sCPU: user: 25.40 s, system: 15.94 s: User CPU 사용 시간 25.40s, System CPU 사용 시간 15.94selapsed: 95.12 s: Vacuum 수행 시간 95.12s
부록 2. 각 테스트 별 VACUUM VERBOSE 내용
PostgreSQL 16.4 VACUUM VERBOSE 내용
더보기
## PostgreSQL 16.4
INFO: vacuuming "postgres.public.vacuum_tab01"
INFO: finished vacuuming "postgres.public.vacuum_tab01": index scans: 0
pages: 0 removed, 567568 remain, 567568 scanned (100.00% of total)
tuples: 5000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 774, which is 14 XIDs ahead of previous value
frozen: 540541 pages from table (95.24% of total) had 95000085 tuples frozen
index scan not needed: 540541 pages from table (95.24% of total) had 5000000 dead item identifiers removed
avg read rate: 99.921 MB/s, avg write rate: 99.924 MB/s
buffer usage: 567604 hits, 567710 misses, 567725 dirtied
WAL usage: 2189192 records, 540569 full page images, 4540204971 bytes
system usage: CPU: user: 13.65 s, system: 6.82 s, elapsed: 44.55 s
INFO: vacuuming "postgres.public.vacuum_tab02"
INFO: finished vacuuming "postgres.public.vacuum_tab02": index scans: 0
pages: 0 removed, 810813 remain, 810813 scanned (100.00% of total)
tuples: 50000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 775, which is 13 XIDs ahead of previous value
frozen: 540541 pages from table (66.67% of total) had 50000050 tuples frozen
index scan not needed: 540540 pages from table (66.67% of total) had 49999950 dead item identifiers removed
avg read rate: 121.840 MB/s, avg write rate: 121.833 MB/s
buffer usage: 810865 hits, 811015 misses, 810972 dirtied
WAL usage: 2432434 records, 540572 full page images, 2754579893 bytes
system usage: CPU: user: 16.47 s, system: 8.02 s, elapsed: 52.00 s
INFO: vacuuming "postgres.public.vacuum_tab03"
INFO: finished vacuuming "postgres.public.vacuum_tab03": index scans: 0
pages: 0 removed, 1054057 remain, 1054057 scanned (100.00% of total)
tuples: 95000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 776, which is 13 XIDs ahead of previous value
frozen: 540541 pages from table (51.28% of total) had 5000085 tuples frozen
index scan not needed: 540541 pages from table (51.28% of total) had 94999915 dead item identifiers removed
avg read rate: 160.689 MB/s, avg write rate: 160.675 MB/s
buffer usage: 1054125 hits, 1054319 misses, 1054225 dirtied
WAL usage: 2675679 records, 540578 full page images, 968974665 bytes
system usage: CPU: user: 18.18 s, system: 9.12 s, elapsed: 51.25 s
INFO: vacuuming "postgres.public.vacuum_tab04"
INFO: finished vacuuming "postgres.public.vacuum_tab04": index scans: 1
pages: 0 removed, 567570 remain, 567570 scanned (100.00% of total)
tuples: 5000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 777, which is 13 XIDs ahead of previous value
frozen: 540541 pages from table (95.24% of total) had 95000005 tuples frozen
index scan needed: 540540 pages from table (95.24% of total) had 4999995 dead item identifiers removed
index "vacuum_tab04_idx": pages: 274194 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 113.535 MB/s, avg write rate: 113.457 MB/s
buffer usage: 567660 hits, 1382447 misses, 1381488 dirtied
WAL usage: 2462415 records, 1354334 full page images, 10777301340 bytes
system usage: CPU: user: 25.40 s, system: 15.94 s, elapsed: 95.12 s
INFO: vacuuming "postgres.public.vacuum_tab05"
INFO: finished vacuuming "postgres.public.vacuum_tab05": index scans: 1
pages: 0 removed, 810813 remain, 810813 scanned (100.00% of total)
tuples: 50000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 778, which is 13 XIDs ahead of previous value
frozen: 540541 pages from table (66.67% of total) had 50000050 tuples frozen
index scan needed: 540540 pages from table (66.67% of total) had 49999950 dead item identifiers removed
index "vacuum_tab05_idx": pages: 548383 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 107.614 MB/s, avg write rate: 107.502 MB/s
buffer usage: 810899 hits, 1899938 misses, 1897962 dirtied
WAL usage: 2978883 records, 1627560 full page images, 7132013561 bytes
system usage: CPU: user: 40.83 s, system: 20.75 s, elapsed: 137.93 s
INFO: vacuuming "postgres.public.vacuum_tab06"
INFO: finished vacuuming "postgres.public.vacuum_tab06": index scans: 1
pages: 0 removed, 1054057 remain, 1054057 scanned (100.00% of total)
tuples: 95000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 780, which was 0 XIDs old when operation ended
new relfrozenxid: 779, which is 13 XIDs ahead of previous value
frozen: 540541 pages from table (51.28% of total) had 5000085 tuples frozen
index scan needed: 540541 pages from table (51.28% of total) had 94999915 dead item identifiers removed
index "vacuum_tab06_idx": pages: 548383 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 123.297 MB/s, avg write rate: 124.142 MB/s
buffer usage: 1070771 hits, 2126631 misses, 2141217 dirtied
WAL usage: 3222129 records, 1627567 full page images, 3432630424 bytes
system usage: CPU: user: 45.14 s, system: 20.23 s, elapsed: 134.75 s
INFO: vacuuming "postgres.public.vacuum_tab10"
INFO: finished vacuuming "postgres.public.vacuum_tab10": index scans: 3
pages: 0 removed, 5270287 remain, 5270287 scanned (100.00% of total)
tuples: 475000000 removed, 500000000 remain, 0 are dead but not yet removable
removable cutoff: 783, which was 0 XIDs old when operation ended
new relfrozenxid: 782, which is 2 XIDs ahead of previous value
frozen: 2702703 pages from table (51.28% of total) had 25000055 tuples frozen
index scan needed: 2702703 pages from table (51.28% of total) had 474999945 dead item identifiers removed
index "vacuum_tab10_idx": pages: 2741898 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 145.477 MB/s, avg write rate: 96.326 MB/s
buffer usage: 5301834 hits, 16168885 misses, 10706057 dirtied
WAL usage: 16110625 records, 8137820 full page images, 17163053741 bytes
system usage: CPU: user: 250.63 s, system: 130.93 s, elapsed: 868.31 sPostgreSQL 17.2 VACUUM VERBOSE 내용
더보기
## PostgreSQL 17.2
INFO: vacuuming "postgres.public.vacuum_tab01"
INFO: finished vacuuming "postgres.public.vacuum_tab01": index scans: 0
pages: 0 removed, 567568 remain, 567568 scanned (100.00% of total)
tuples: 5000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 808, which was 0 XIDs old when operation ended
new relfrozenxid: 802, which is 10 XIDs ahead of previous value
frozen: 540541 pages from table (95.24% of total) had 95000085 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
I/O timings: read: 3920.129 ms, write: 2988.223 ms
avg read rate: 120.796 MB/s, avg write rate: 120.799 MB/s
buffer usage: 567605 hits, 567712 misses, 567724 dirtied
WAL usage: 1108110 records, 540568 full page images, 4277493264 bytes
system usage: CPU: user: 12.26 s, system: 8.70 s, elapsed: 36.72 s
INFO: vacuuming "postgres.public.vacuum_tab02"
INFO: finished vacuuming "postgres.public.vacuum_tab02": index scans: 0
pages: 0 removed, 810813 remain, 810813 scanned (100.00% of total)
tuples: 50000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 808, which was 0 XIDs old when operation ended
new relfrozenxid: 803, which is 9 XIDs ahead of previous value
frozen: 540541 pages from table (66.67% of total) had 50000050 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
I/O timings: read: 8437.800 ms, write: 3977.691 ms
avg read rate: 154.396 MB/s, avg write rate: 154.388 MB/s
buffer usage: 810868 hits, 811015 misses, 810972 dirtied
WAL usage: 1351353 records, 540571 full page images, 2490786572 bytes
system usage: CPU: user: 14.98 s, system: 9.00 s, elapsed: 41.03 s
INFO: vacuuming "postgres.public.vacuum_tab03"
INFO: finished vacuuming "postgres.public.vacuum_tab03": index scans: 0
pages: 0 removed, 1054057 remain, 1054057 scanned (100.00% of total)
tuples: 95000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 808, which was 0 XIDs old when operation ended
new relfrozenxid: 804, which is 9 XIDs ahead of previous value
frozen: 540541 pages from table (51.28% of total) had 5000085 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
I/O timings: read: 19744.482 ms, write: 5462.195 ms
avg read rate: 154.923 MB/s, avg write rate: 154.909 MB/s
buffer usage: 1054128 hits, 1054319 misses, 1054225 dirtied
WAL usage: 1594597 records, 540576 full page images, 681396733 bytes
system usage: CPU: user: 18.29 s, system: 11.41 s, elapsed: 53.16 s
INFO: vacuuming "postgres.public.vacuum_tab04"
INFO: finished vacuuming "postgres.public.vacuum_tab04": index scans: 1
pages: 0 removed, 567570 remain, 567570 scanned (100.00% of total)
tuples: 5000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 808, which was 0 XIDs old when operation ended
new relfrozenxid: 805, which is 9 XIDs ahead of previous value
frozen: 540541 pages from table (95.24% of total) had 95000005 tuples frozen
index scan needed: 540540 pages from table (95.24% of total) had 4999995 dead item identifiers removed
index "vacuum_tab04_idx": pages: 274194 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 9984.299 ms, write: 7153.624 ms
avg read rate: 132.904 MB/s, avg write rate: 132.812 MB/s
buffer usage: 567631 hits, 1382446 misses, 1381488 dirtied
WAL usage: 1921874 records, 1354332 full page images, 10550527564 bytes
system usage: CPU: user: 24.37 s, system: 21.67 s, elapsed: 81.26 s
INFO: vacuuming "postgres.public.vacuum_tab05"
INFO: finished vacuuming "postgres.public.vacuum_tab05": index scans: 1
pages: 0 removed, 810813 remain, 810813 scanned (100.00% of total)
tuples: 50000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 808, which was 0 XIDs old when operation ended
frozen: 540540 pages from table (66.67% of total) had 49999958 tuples frozen
index scan needed: 540540 pages from table (66.67% of total) had 49999950 dead item identifiers removed
index "vacuum_tab05_idx": pages: 548383 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 32950.256 ms, write: 10123.853 ms
avg read rate: 119.521 MB/s, avg write rate: 119.397 MB/s
buffer usage: 810916 hits, 1899943 misses, 1897962 dirtied
WAL usage: 2438342 records, 1627560 full page images, 6995255337 bytes
system usage: CPU: user: 36.10 s, system: 26.27 s, elapsed: 124.18 s
INFO: vacuuming "postgres.public.vacuum_tab06"
INFO: finished vacuuming "postgres.public.vacuum_tab06": index scans: 1
pages: 0 removed, 1054057 remain, 1054057 scanned (100.00% of total)
tuples: 95000000 removed, 100000000 remain, 0 are dead but not yet removable
removable cutoff: 808, which was 0 XIDs old when operation ended
new relfrozenxid: 807, which is 9 XIDs ahead of previous value
frozen: 540541 pages from table (51.28% of total) had 5000085 tuples frozen
index scan needed: 540541 pages from table (51.28% of total) had 94999915 dead item identifiers removed
index "vacuum_tab06_idx": pages: 548383 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 37294.878 ms, write: 10985.760 ms
avg read rate: 134.917 MB/s, avg write rate: 135.730 MB/s
buffer usage: 1069023 hits, 2128381 misses, 2141217 dirtied
WAL usage: 2681588 records, 1627567 full page images, 3385872130 bytes
system usage: CPU: user: 39.75 s, system: 24.59 s, elapsed: 123.24 s
INFO: vacuuming "postgres.public.vacuum_tab10"
INFO: finished vacuuming "postgres.public.vacuum_tab10": index scans: 1
pages: 0 removed, 5270287 remain, 5270287 scanned (100.00% of total)
tuples: 322547596 removed, 500000000 remain, 0 are dead but not yet removable
removable cutoff: 811, which was 0 XIDs old when operation ended
frozen: 1835264 pages from table (34.82% of total) had 16976244 tuples frozen
index scan needed: 2702703 pages from table (51.28% of total) had 474999945 dead item identifiers removed
index "vacuum_tab10_idx": pages: 2741898 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 179283.580 ms, write: 52287.691 ms
avg read rate: 142.589 MB/s, avg write rate: 131.292 MB/s
buffer usage: 5301716 hits, 10685188 misses, 9838609 dirtied
WAL usage: 12540481 records, 7270378 full page images, 15896133937 bytes
system usage: CPU: user: 191.75 s, system: 120.36 s, elapsed: 585.44 s
기획 및 글 | 플랫폼기술연구팀
'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글
| DB 인사이드 | PostgreSQL New Feature - 17 Release (2) (0) | 2024.12.30 |
|---|---|
| DB 인사이드 | PostgreSQL New Feature - 17 Release (1) (0) | 2024.12.30 |
| DB 인사이드 | PWI - Shared Buffer > Wait Event (0) | 2024.11.27 |
| DB 인사이드 | PWI - Shared Buffer > 동작원리 (0) | 2024.10.25 |
| DB 인사이드 | PWI - LOCK > Other Locks (0) | 2024.09.25 |
댓글