[benchmark] post_likes 재실험: sysbench 기반 인덱스/랜덤I/O/insert 검증

[seonghooncho]

[DB-ENGINE] post_likes PK 전략 재검증 (복합 PK vs 단일 PK)

1. 목적

본 이슈의 목적은 post_likes 테이블의 PK 전략(복합 PK vs 단일 PK)이
대규모 데이터 및 다중 인덱스 환경에서 장기적으로 어떤 구조가 더 확장성 있는지
엔진 레벨에서 검증하는 것이다.

단순 평균 latency 비교가 아니라, 다음 질문에 답하는 것이 목표다.

1. 현재 src 하위에서 사용 중인 문제 쿼리가 실제로 covering 인덱스로 동작하는가?
2. 복합 PK의 클러스터드 인덱스와 단일 PK 전략의 보조 인덱스 간 크기 및 page당 row 밀집도 차이는
   실제 데이터 적재 시 어느 수준인가?
3. 그 밀집도 차이가 랜덤 I/O 발생 시 어느 정도의 완화 효과를 만들어내는가?
4. 보조 인덱스 개수가 증가할수록 단일 PK 전략이 공간/IO 측면에서 역전하는
   임계점(break-even)은 존재하는가?

이 실험은 "어느 쪽이 빠르다"가 아니라,
"어느 구조가 장기적으로 더 유리한가"를 판단하기 위한 것이다.

---

사전 가설

가설 1: 보조 인덱스에서 PK 길이 감소는 leaf 밀도 증가로 이어진다

InnoDB 보조 인덱스 leaf entry는 다음 구조를 가진다.

secondary_key_columns + PK

복합 PK 전략에서는 PK 전체(post_id + member_id)가 leaf에 포함되고,
단일 PK 전략에서는 PK(id)만 포함된다.

가정:

secondary_key ≈ 28B
PK_composite ≈ 20B  (BINARY(12) + BIGINT)
PK_single    ≈ 8B   (BIGINT auto_increment)

leaf entry 크기:

Composite = 48B
Single    = 36B

16KB page 기준 entry 수:

Composite ≈ 341 entries
Single    ≈ 455 entries

따라서 단일 PK 전략은
보조 인덱스 leaf에서 페이지당 entry 밀도를 약 25~35% 증가시킬 가능성이 있다.

이 효과는 B+Tree 높이 감소보다는
leaf page 밀도 개선에서 발생할 것으로 예상된다.

---

가설 2: 클러스터드 인덱스 page 밀집도는 단일 PK 전략에서 약간 감소할 수 있다

클러스터드 인덱스 leaf는 row 전체 데이터를 포함한다.

복합 PK 전략에서는 별도의 id 컬럼이 없으므로 row가 더 작고,
단일 PK 전략에서는 id 컬럼이 추가되므로 row가 약간 커진다.

row 크기 추정:

Composite: PK(20B) + deleted_at(5B) + row header  ≈ ~45B
Single:    PK(8B)  + post_id(12B) + member_id(8B) + deleted_at(5B) + row header ≈ ~53B

단일 PK 전략은 클러스터드 인덱스 page당 row 밀집도가
복합 PK 대비 소폭 낮아질 수 있다.

이 차이가 보조 인덱스에서의 밀집도 이득을 상쇄하는 수준인지는 실측이 필요하다.

---

가설 3: leaf 밀도 증가는 랜덤 I/O 확률을 감소시킬 수 있다

랜덤 I/O 발생은 다음 요인의 영향을 받는다.

• working set 크기
• buffer pool 크기
• 접근 분포(skew)
• leaf page 재사용 빈도

leaf entry 밀도가 증가하면
동일 row 수에서 필요한 leaf page 수가 감소한다.

보조 인덱스 기준:

entry 밀도 증가율 ≈ 33%
필요 leaf page 수 감소 가능성 ≈ 20~30%

따라서 단일 PK 전략은
동일 조건에서 random leaf page 접근 횟수를
유의미하게 감소시킬 가능성이 있다.

---

가설 4: 보조 인덱스 수 증가 시 PK 길이 차이는 선형적으로 누적된다

보조 인덱스 k개일 때,
PK 길이 차이에 의한 누적 비용은 다음과 같다.

difference_per_index = PK_composite - PK_single ≈ 12B

total_difference ≈
  k × difference_per_index × row_count

따라서 보조 인덱스 개수가 증가할수록
PK 길이 차이에 의한 인덱스 공간 및 buffer pool 압박 차이는
선형적으로 증가한다.

이로 인해 특정 인덱스 개수 이상에서는
단일 PK 전략이 공간 및 I/O 측면에서 유리해질 가능성이 있다.

---

가설 5: 단일 PK 전략은 랜덤 I/O를 제거하는 것이 아니라 위치를 이동시킨다

단일 PK 전략은
랜덤 I/O를 제거하는 것이 아니라,

• 복합 PK 전략에서 클러스터드 인덱스에 발생하던 랜덤 I/O를
• 상대적으로 더 작은 보조 인덱스로 이동시킨다.

핵심 검증 포인트는 다음과 같다.

1. 보조 인덱스 leaf 밀도 증가가 실제 buffer_pool_reads 감소로 이어지는가?
2. 보조 인덱스가 많아질수록 구조적 차이가 확대되는가?
3. 특정 인덱스 개수 이상에서 단일 PK 전략이 확실히 유리해지는가?

---

2. 실험 전제

• post_id: BINARY(12) 고정 (Mongo ObjectId 형태 가정)
• member_id: BIGINT
• deleted_at: DATETIME NULL
• 데이터셋은 buffer pool 대비 충분히 크게 설정 (랜덤 I/O 유발 조건 유지)
• 두 전략 간 비교 시, 적재 데이터의 row 수와 값 분포는 동일하게 유지
• 단일 PK 전략은 auto_increment(monotonic) 기반으로만 수행
  • 랜덤 ID(UUID 등) 기반 단일 PK는 클러스터드 인덱스의 page split 비용이
    복합 PK 대비 명백히 불리하므로 비교 대상에서 제외
• k6는 사용하지 않음 (HTTP/애플리케이션 노이즈 제거)
• sysbench 기반 엔진 레벨 실험 수행

---

3. 1단계: 실제 쿼리 기준 covering 여부 선검증

대상 쿼리 (src 기준 최소 세트)

1. Feed 집계

SELECT post_id,
       COUNT(*) AS like_count,
       MAX(CASE WHEN member_id = ? THEN TRUE ELSE FALSE END) AS amILiking
FROM post_likes
WHERE post_id IN (...)
  AND deleted_at IS NULL
GROUP BY post_id;

2. 좋아요 중복 체크

SELECT 1
FROM post_likes
WHERE post_id = ?
  AND member_id = ?
  AND deleted_at IS NULL
LIMIT 1;

3. soft delete / bulk update 경로

수행

• src 기준 실제 SQL 추출
• 각 스키마 케이스별 EXPLAIN ANALYZE 실행
• 다음 항목 기록

- 사용 인덱스
- Extra (Using index 여부)
- rows examined
- actual rows
- actual time

검증 목적

• 단일 PK 전략이 실제로 index-only scan(covering)으로 수행되는지 확인
• 복합 PK 전략이 클러스터드 인덱스만으로 충분히 수행되는지 확인
• 조회 성능이 전략 선택의 주요 변수가 되는지 판단

조회 성능이 사실상 동등하다면,
이후 판단은 insert 확장성과 인덱스 구조 차이에 집중한다.

---

4. 2단계: 인덱스 구조 및 공간 차이 정량 분석

분석 목표

1. 복합 PK 클러스터드 인덱스 크기 및 page당 row 밀집도 측정
2. 단일 PK 전략의 클러스터드 인덱스 크기 및 page당 row 밀집도 측정
3. 각 전략의 보조 인덱스 크기 및 page당 row 밀집도 측정
4. 보조 인덱스 leaf에 포함되는 PK 크기 차이 정량화
5. 인덱스 수 증가 시 누적 효과 계산

수집 쿼리

SELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_name = 'post_likes';

SELECT index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE table_name = 'post_likes'
  AND stat_name IN ('n_leaf_pages', 'size', 'n_diff_pfx01');

page당 row 밀집도 산출

rows_per_leaf_page = total_rows / n_leaf_pages

이 값을 클러스터드 인덱스와 보조 인덱스 각각에 대해 두 전략 모두에서 측정한다.

	Composite PK	Single PK (AI)
Clustered index rows/page	?	?
Secondary index rows/page	?	?

Break-even 계산

보조 인덱스 1개당 절감량:

saving_per_secondary =
  (복합PK의 PK 크기 - 단일PK의 PK 크기) × row_count

단일 PK 채택 시 추가 비용:

extra_single_cost =
  단일 PK 전략에서 추가되는 feed용 보조 인덱스 크기
  + 클러스터드 인덱스 row 크기 증가분 × row_count

Break-even:

break_even_k =
  ceil(extra_single_cost / saving_per_secondary)

목표는 다음을 제시하는 것이다.

• 보조 인덱스 개수 k가 몇 개부터 단일 PK가 공간상 유리해지는지
• 인덱스 크기 차이가 데이터 증가에 따라 어떻게 누적되는지

---

5. 3단계: 랜덤 I/O 완화 효과 실측 (sysbench)

스키마 매트릭스

C (Composite PK)

PK(post_id, member_id)
+ 필요 secondary indexes

S (Single PK, auto_increment)

PK(id auto_increment)
+ 동일 secondary set

인덱스 단계

• L1: 최소 인덱스 세트
• L2: src 유사 인덱스 세트
• L3: 확장 인덱스 세트 (향후 인덱스 증가 가정)

측정 지표

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_pages_written';
SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';

Latency:

• mean
• stddev
• CV
• 95% CI
• p50 / p95 / p99

랜덤 I/O 완화율 계산:

io_reduction_rate =
  (composite_reads - single_reads)
  / composite_reads

목표는 단일 PK 전략이 실제로 랜덤 I/O를 몇 % 완화하는지
엔진 지표 기준으로 수치화하는 것이다.

---

6. 최종 판단 구조

이 실험은 다음 중 하나로 귀결된다.

A. 조회 성능 동등 + 랜덤 I/O 유의미 감소 + break-even 명확
→ 단일 PK 전략 채택

B. insert 및 I/O 차이 미미 + 공간 차이 미미
→ 복합 PK 유지

C. auto_increment에서만 유의미한 개선
→ monotonic clustering이 핵심 요인

---

7. 산출물

• sysbench Lua 스크립트
• DDL 및 데이터 적재 스크립트
• raw 결과(csv/json)
• 인덱스 크기 및 page 밀집도 분석표
• break-even 계산표
• 최종 엔지니어링 리포트 (가정 / 한계 / 운영 적용 권고 포함)

---

본 실험은 단순 벤치마크가 아니라,
랜덤 I/O 위치 이동과 PK 크기 차이가
대규모 환경에서 구조적 차이를 만드는지 검증하는 것이 목적이다.