PostgreSQL의 Fillfactor와 영향도

PostgreSQL Page, Fillfactor, Heap-only-Tuple란?

PostgreSQL의 테이블과 인덱스는 8KB 크기의 페이지에 데이터를 저장합니다. 각각의 페이지에는 헤더 영역인 24bytes 크기의 PageHeaderData와 실제 데이터를 포인팅하는 ItemIdData 라는 포인터, 그리고 아직 아무것도 할당되지 않은 FreeSpace와 실제 데이터가 저장되는 Items 으로 구성됩니다. INSERT나 UPDATE 통해 아이템이 추가되면 페이지의 FreeSpace 끝 부분부터 순서대로 쌓이게 됩니다.

테이블과 인덱스에는 Fillfactor라는 스토리지 속성이 있습니다. 테이블의 기본값은 100이고 인덱스는 90입니다. Fillfactor는 페이지에 데이터를 얼마만큼 채울지를 지정하는 속성으로, Fillfactor가 80인 테이블은 INSERT 시 데이터를 페이지의 80% 까지만 저장하도록 합니다. Fillfactor를 100보다 작은 값으로 설정하게되면, UPDATE를 통해 행의 업데이트된 복사본이 같은 페이지에 저장될 기회를 제공합니다. 동일한 페이지에 업데이트된 행이 저장되면 데이터를 효율적으로 조회할 수 있고, 또한 HOT (Heap-only-Tuple) Update의 빈도가 높아지게 됩니다. 변경이 없는 테이블이라면 높은 Fillfactor가 적합할 것이고, 변경이 많은 테이블이라면 작은 Fillfactor가 적합합니다.

Heap-only-Tuple은 UPDATE 구문이 인덱싱된 컬럼이 아닌 컬럼만을 업데이트하거나, 페이지에 업데이트된 행을 저장할 수 있는 충분한 여유 공간이 있을 때, UPDATE의 오버헤드를 줄이기 위한 최적화 기능입니다. 따라서 HOT Update는 Fillfactor가 작을수록 사용될 빈도가 높아집니다.

테스트 환경

이번 기고에서는 Fillfactor가 워크로드에 미치는 영향을 HammerDB의 OLTP 워크로드를 이용하여 알아보겠습니다.

HammerDB는 PostgreSQL, MySQL, Oracle, MSSQL Server 등을 지원하는 데이터베이스 벤치마킹 및 로드 테스트 소프트웨어입니다. HammerDB의 OLTP는 TPC-C 에서 파생된 TPROC-C 워크로드를 사용합니다.

테스트를 위해 Amazon Aurora PostgreSQL 15.4 db.r6g.4xlarge 를 사용하였습니다. 그리고 테스트의 목적인 Fillfactor에 의한 영향을 확인하기 위해, 각기 다른 Fillfactor를 설정할 4개의 데이터베이스를 생성하였습니다.

Fillfactor를 100으로 설정할 데이터베이스는 FF100, 80은 FF80, 60은 FF60 그리고 40은 FF40 으로 생성합니다. 그리고 각각의 데이터베이스에 HammerDB를 이용하여 스키마를 생성하고 데이터를 로딩하였습니다.

다음은 TPROC-C 스키마와 데이터베이스를 생성하고 데이터를 로딩하는 TCL 스크립트 예시입니다. Warehouse는 500개이고, FF100 데이터베이스를 생성하고 FF100 데이터베이스에 TPROC-C 스키마를 생성 후 50개의 VU (Virtual User)가 데이터를 로딩합니다.

puts "SETTING CONFIGURATION"

dbset db pg
diset connection pg_host <Your Aurora Cluster Endpoint>
diset connection pg_port <Your Aurora Cluster Port Number>
diset tpcc pg_count_ware 500
diset tpcc pg_num_vu 50
diset tpcc pg_partition false
diset tpcc pg_superuser <Your Aurora PostgreSQL Admin Username>

diset tpcc pg_superuserpass <Your Aurora PostgreSQL Admin User Password>
diset tpcc pg_defaultdbase postgres
diset tpcc pg_user <Your HammerDB Username>
diset tpcc pg_pass <Your HammerDB User Password>
diset tpcc pg_dbase FF100

print dict
buildschema
waittocomplete

puts "SCHEMA READY"

다음은 TPROC-C 워크로드를 위한 스키마이고, Warehouse의 수에 의해 Stock, District, Orders, OrderLine, Customer 등의 테이블의 행 수가 정해지는 것을 확인할 수 있습니다.

설정한 Fillfactor에 따라 TPROC-C 스키마의 크기에도 영향을 미칩니다.

Database	Fillfactor	Data Size (GB)	FF100 대비 증분량 (%)	Loading Duration
FF100	100	48	–	1시간 34초
FF80	80	58	21	1시간 31초
FF60	60	74	54	1시간 39초
FF40	40	109	127	1시간 25초

Fillfactor가 각기 설정된 데이터베이스와 스키마가 만들어졌고, 데이터 로딩이 완료가 되었습니다. 이제 각각의 데이터베이스에 HammerDB를 사용하여 부하를 발생합니다. 부하는 100 VU, 200 VU, 300 VU, 400 VU, 500 VU로 5회에 걸쳐 각각 300분 씩 실행합니다.

다음은 HammerDB를 이용하여 TPROC-C 워크로드를 실행하는 TCL 스크립트 예시입니다. 해당 스크립트는 FF100 데이터베이스에 500개의 VU를 사용하여 300분 동안 부하를 실행합니다.

#!/bin/tclsh

puts "SETTING CONFIGURATION"

dbset db pg
diset connection pg_host <Your Aurora Cluster Endpoint>
diset connection pg_port <Your Aurora Cluster Port Number>
diset tpcc pg_count_ware 500
diset tpcc pg_num_vu 500
diset tpcc pg_partition false
diset tpcc pg_superuser <Your Aurora PostgreSQL Admin Username>

diset tpcc pg_superuserpass <Your Aurora PostgreSQL Admin User Password>
diset tpcc pg_defaultdbase postgres
diset tpcc pg_user <Your HammerDB Username>
diset tpcc pg_pass <Your HammerDB User Password>
diset tpcc pg_dbase FF100

diset tpcc pg_driver timed
diset tpcc pg_rampup 2
diset tpcc pg_duration 300
diset tpcc pg_vacuum true

loadscript
puts "SEQUENCE STARTED"
vuset logtotemp 1
vuset vu 500
vucreate
vurun
runtimer 1500
vudestroy
after 1500
puts "TEST SEQUENCE COMPLETE"
exit

TPROC-C 워크로드가 실행되는 동안 운영 환경과 최대한 유사한 환경을 구현하기 위해 Auto Vacuum은 Aurora PostgreSQL의 기본 설정을 그대로 활용하여 동작하도록 하였습니다. 설치형 PostgreSQL이나 RDS PostgreSQL 이었다면 Checkpoint도 발생했겠지만, Aurora PostgreSQL은 로그 기반 스토리지로 Checkpoint가 발생하지 않습니다. 따라서 Checkpoint로 인한 대량의 쓰기 I/O가 발생하지 않아, 이를 통한 성능 오버헤드를 제거할 수 있어 지속적인 Throughput을 기대할 수 있습니다.

테스트 결과 분석

Aurora의 CPU 사용률은 Fillfactor의 영향은 없었고, VU 수가 증가함에 따라 증가하는 것을 확인할 수 있습니다.

하지만 TPM (Transaction Per Minute)의 경우는, VU 수가 증가할 수록 TPM도 증가하였고 Fillfactor에 의한 TPM의 차이가 나타났습니다. 같은 수의 VU에서 Fillfactor가 낮을 수록 TPM이 높게 나타나는 것을 확인할 수 있습니다.

왜 Fillfactor에 의한 TPM의 차이가 발생할까요? 그 답을 찾기 위해서는 TPROC-C 워크로드가 어떻게 구성되는지 알아볼 필요가 있습니다. TPROC-C 워크로드는 다음과 같은 업무 트랜잭션이 발생합니다.

신규 주문 (45%) : 고객으로부터 새로운 주문을 받음
결제 (43%) : 고객 잔액을 업데이트하여 결제 기록
배송 (4%) : 주문을 비동기식으로 배송
주문 상태 (4%) : 고객의 최근 주문 상태 검색
재고 수준 (4%) : 창고 재고 상태 반환

이로 인해 customer, district, stock, order_line 테이블에 대량의 INSERT, SELECT, UPDATE가 발생을 합니다.

이러한 대량의 INSERT, SELECT, UPDATE가 발생하는 환경에서 HOT Update는 큰 효과를 나타냅니다. HOT Update는 일반 UPDATE 구문에서 발생할 수 있는 오버헤드를 최소화하는 최적화 기법입니다. 아래 그래프에서 나타나듯 Fillfactor가 낮아질수록 HOT Update가 일어나는 빈도가 높아지는 것을 확인할 수 있습니다.

특히 500 VU가 부하를 발생한 경우, stock과 order_line 테이블의 경우 시간당 약 94M의 업데이트가 발생하고, customer와 district 테이블은 시간당 약 19M의 업데이트가 발생했습니다.

Fillfactor를 통해 HOT Update를 할 수 있는 환경을 만들어줌으로서 더 많은 HOT Update를 통해 Update를 최적화하여 TPROC-C 워크로드와 같이 UPDATE가 많은 워크로드의 TPM에 영향을 줄 수 있음을 확인하였습니다.

또한 HOT Update 비율이 높다는 것은 INSERT된 데이터와 같은 페이지에서 업데이트가 이루어졌다는 것을 의미하기 때문에, 해당 Update 구문의 실행으로 새로운 페이지를 할당 받을 필요는 없이 스토리지 효율적으로 실행이 되었다는 의미이기도 합니다.

하지만 Fillfactor가 장점만 있는 것은 아닙니다. TPROC-C 워크로드의 경우, 업데이트도 많지만 order_line 테이블에 대한 INSERT가 많이 발생한다는 것을 위의 쿼리 결과를 통해 확인할 수 있습니다. Fillfactor를 작게 설정할수록 INSERT 구문에 대해 페이지에 업데이트를 위한 공간의 비중이 높아지게 되고, 동일한 양의 INSERT를 처리하기 위해서는 더 많은 페이지를 필요로하게 됨을 의미합니다.

Amazon Aurora의 OLTP 성능 향상을 돕는 기능들

Aurora Storage Architecture

Aurora는 Log-Structured Distributed Storage System을 사용합니다. 즉, DML을 통해 만들어진 데이터는 메모리에서 체크포인트를 통해 스토리지에 써지는 것이 아니라, 오직 트랜잭션 로그만 스토리지로 써지도록 하여, 체크포인트로 인한 랜덤 I/O가 발생하지 않습니다. 이를 통해 워크로드가 실행되는 동안에도 발생하는 체크포인트로 인한 부정적인 성능 영향이 없습니다.

Aurora Optimized Reads for Aurora PostgreSQL

Aurora Optimized Reads는 읽기 집중적인 워크로드에 특히 유용합니다. 이 기능은 NVMe 스토리지를 지원하는 AWS Graviton 기반 db.r6gd 및 Intel 기반 db.r6id 인스턴스에서 사용할 수 있습니다. Aurora 최적화 읽기는 다음 두 가지 기능을 지원합니다.

Tiered cache – 이를 통해 로컬 NVMe 스토리지를 활용하여 DB 인스턴스 캐싱 용량을 확장할 수 있습니다. 인 메모리 데이터베이스 버퍼 풀에서 제거될 데이터베이스 페이지를 자동으로 캐시하여 이전에 Aurora 스토리지에서 데이터를 가져오는 쿼리에 대해 최대 8배 더 나은 대기 시간을 제공합니다.
Temporary Objects – Amazon Elastic Block Store(Amazon EBS) 기반 스토리지 대신 로컬 NVMe 스토리지에서 호스팅됩니다. 이를 통해 해당 작업에 대해 구성된 메모리에 맞지 않는 대용량 데이터를 정렬, 조인 또는 병합하는 쿼리에 대한 대기 시간과 처리량이 향상됩니다.

결론

이번 테스트는 HammerDB를 이용하여 OLTP와 유사한 TPROC-C 워크로드를 최대 500 VU를 사용하여 5시간 씩 실행하였습니다. 최대한 운영 환경과 유사하게 하기 위해 Autovacuum 도 활성화하여 빈번하게 발생하는 INSERT/UPDATE/DELETE로 인해 발생하는 dead tuple을 정리하고 재사용할 수 있도록 하였고, 워크로드가 실행하면서 나타난 다양한 수치들을 분석하였습니다. 이를 통해 PostgreSQL의 Fillfactor가 워크로드에 미치는 영향을 확인하였습니다.

스토리지 용량 측면

Fillfactor는 INSERT에 대해 페이지에 얼마만큼을 채울지를 정하는 스토리지 속성입니다. Fillfactor가 작아질수록 페이지에 UPDATE를 위한 여유 공간을 더 많이 할당하게 됩니다. 테스트 결과를 통해서도 확인을 하였듯이 Fillfactor가 작을수록 Fillfactor 기본값인 100 보다 스토리지를 더 많이 사용하게 됩니다.

처리 성능 측면

TPROC-C 워크로드와 같은 OLTP 워크로드는 INSERT, UPDATE, DELETE가 매우 빈번하게 발생합니다. Fillfactor가 작아질수록 UPDATE를 위한 공간이 확보되고, 이로 인해 HOT Update의 발생 빈도가 높아져 UPDATE의 최적화를 통해 처리량이 늘어납니다.

PostgreSQL의 Fillfactor를 작게 조절하면 HOT Update 빈도를 높일 수 있어, Update 구문에 대한 최적화를 할 수 있어 처리량을 높일 수 있는 장점을 가지고 있습니다. 하지만 Fillfactor를 낮추는 것은 INSERT 구문에 대한 스토리지 공간 활용성이 낮아지는 반대 급부가 있기 때문에, 우리 워크로드에 적합한 적절한 설정값을 찾아 적용하는 것이 중요합니다.

AWS 기술 블로그