쓰기 성능: batch, transaction size, lock contention, hot row

읽기 최적화만큼 쓰기도 설계가 필요하다

쿼리 튜닝은 대개 SELECT 최적화에 집중하지만, 실제 운영 장애의 상당수는 쓰기 경합에서 시작된다. INSERT가 초당 수천 건 들어오면 행 잠금이 충돌하고, 대용량 배치가 하나의 트랜잭션으로 실행되면 undo log가 폭발하고, AUTO_INCREMENT 키에 여러 스레드가 몰리면 autoinc 잠금 경합이 생긴다.

쓰기 성능 문제는 크게 네 가지 범주로 나뉜다.

1. 배치 크기: row-by-row INSERT vs multi-row INSERT vs COPY
2. 트랜잭션 크기: 너무 작으면 commit overhead, 너무 크면 잠금 시간과 undo 폭발
3. 잠금 경합: row lock, gap lock, autoinc lock, table lock
4. Hot row 문제: 특정 행에 쓰기가 집중되는 패턴

배치 쓰기: row-by-row가 느린 이유

단건 INSERT를 반복 실행하면 매번 다음 오버헤드가 발생한다.

• 네트워크 왕복(RTT): 쿼리 전송 → 서버 파싱 → 실행 → 응답 사이클이 행마다 반복된다.
• 트랜잭션 commit: 기본적으로 autocommit이면 INSERT 하나마다 fsync(WAL/redo log flush)가 발생한다.
• 인덱스 갱신: 행마다 모든 보조 인덱스를 갱신한다.

Multi-row INSERT

-- 나쁜 예: 행마다 별도 쿼리
INSERT INTO events (user_id, event_type, ts) VALUES (1, 'click', NOW());
INSERT INTO events (user_id, event_type, ts) VALUES (2, 'view',  NOW());
-- ... 1000번 반복

-- 좋은 예: 한 번에 묶기
INSERT INTO events (user_id, event_type, ts) VALUES
  (1, 'click', '2026-06-27 03:00:00'),
  (2, 'view',  '2026-06-27 03:00:01'),
  -- ... 500~1000행
  (1000, 'click', '2026-06-27 03:00:10');

INSERT ... VALUES (r1), (r2), ..., (rN)은 네트워크 왕복과 commit 횟수를 1/N으로 줄인다. MySQL에서는 max_allowed_packet이 허용하는 한도 안에서 배치를 구성한다.

PostgreSQL COPY

-- COPY는 INSERT보다 훨씬 빠른 대량 적재 수단
-- 표준 입력 또는 파일에서 직접 읽기
COPY events (user_id, event_type, ts)
FROM '/tmp/events.csv'
WITH (FORMAT csv, DELIMITER ',', HEADER true);

-- psql에서 stdin으로 파이핑
\COPY events FROM events.csv CSV HEADER

COPY는 파서 오버헤드를 줄이고 인덱스 갱신을 배치로 처리한다. 인덱스가 많은 테이블에서는 COPY를 빈 스테이징 테이블에 먼저 적재 → 인덱스 생성 순서가 인덱스 있는 테이블에 직접 COPY하는 것보다 빠를 수 있다.

배치 크기 선택

배치가 너무 작으면 commit overhead가 지배하고, 너무 크면 undo/redo log 폭발과 잠금 시간 증가가 발생한다.

실용 기준:

• MySQL: 배치당 500~2,000행. innodb_flush_log_at_trx_commit=2로 설정하면 commit 빈도를 줄일 수 있지만 비정상 종료 시 최대 1초치 데이터 손실이 생긴다.
• PostgreSQL: 1,000~5,000행. synchronous_commit = off(복제 지연 허용 워크로드)로 fsync 대기를 줄일 수 있다.

트랜잭션 크기와 undo/undo log 폭발

너무 작은 트랜잭션의 문제

autocommit 단건 INSERT를 초당 10,000건 실행하면 fsync가 10,000회/초 발생한다. 디스크 I/O 한계에 도달하거나, innodb_flush_log_at_trx_commit=1(기본값, 가장 안전한 설정)에서는 매 commit마다 redo log를 flush한다.

-- 명시적 트랜잭션으로 묶기
START TRANSACTION;
INSERT INTO events ... ;  -- 500행
INSERT INTO events ... ;  -- 500행
COMMIT;                   -- flush는 1회

너무 큰 트랜잭션의 문제

10만 행을 하나의 트랜잭션으로 DELETE하면:

1. InnoDB undo log: 변경 전 이미지(undo record)를 모두 유지한다. undo tablespace가 크게 증가한다.
2. 잠금 보유 시간 증가: 트랜잭션이 열려있는 동안 관련 행 잠금이 유지된다. 다른 트랜잭션이 같은 행에 접근하면 대기한다.
3. 롤백 비용: 실패 시 undo를 모두 적용해야 한다. 큰 트랜잭션은 롤백도 오래 걸린다.

대용량 변경은 작은 배치로 나눈다.

-- 나쁜 예: 한 번에 전부
DELETE FROM logs WHERE created_at < '2026-01-01';

-- 좋은 예: 배치 루프로 나누기 (애플리케이션 레이어)
DELETE FROM logs
WHERE created_at < '2026-01-01'
LIMIT 1000;
-- 반복, 각 배치마다 commit

PostgreSQL의 경우 DELETE 후 오래된 버전이 vacuum 전까지 남아 bloat이 생긴다. 대량 삭제 후 VACUUM ANALYZE를 수동으로 실행하거나 autovacuum 임계값을 낮춰 빠르게 회수되도록 한다.

잠금 경합의 유형과 진단

Gap Lock과 INSERT 차단

Gap Lock은 인덱스의 없는 범위를 잠근다. REPEATABLE READ 격리 수준(MySQL InnoDB 기본값)에서 범위 조건 UPDATE/SELECT FOR UPDATE는 gap lock을 함께 건다.

-- REPEATABLE READ에서 발생하는 gap lock 예시
-- Tx A: status = 'PENDING'인 모든 행을 잠금
SELECT * FROM orders WHERE status = 'PENDING' FOR UPDATE;
-- 이 시점에 status = 'PENDING'에 해당하는 gap도 잠긴다.

-- Tx B: 새로운 'PENDING' 주문 INSERT 시도 → 대기
INSERT INTO orders (status, amount) VALUES ('PENDING', 1000);
-- Tx A가 커밋하기 전까지 Tx B는 block된다.

READ COMMITTED 격리 수준으로 낮추면 gap lock이 발생하지 않는다. 단, 팬텀 읽기 방지는 포기해야 하므로 애플리케이션 로직에 맞는지 먼저 검토한다.

-- 세션 수준으로 격리 변경
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

AutoInc Lock 경합

MySQL의 AUTO_INCREMENT는 기본적으로 innodb_autoinc_lock_mode=1(연속 모드)로 동작한다. multi-row INSERT에서는 테이블 수준 autoinc 잠금을 잠깐 걸어 연속된 값을 할당한다.

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
-- 0: 고전 모드 (항상 테이블 잠금)
-- 1: 연속 모드 (기본값, bulk INSERT에서만 잠금)
-- 2: 인터리브 모드 (잠금 없음, 가장 빠름. 단 row-based binlog 필요)

-- 쓰기 집중 워크로드에서는 2 고려
SET GLOBAL innodb_autoinc_lock_mode = 2;

innodb_autoinc_lock_mode=2를 사용할 때 binlog_format을 ROW 또는 MIXED로 설정해야 복제가 안전하다.

Hot Row 문제

Hot Row란

특정 행에 쓰기가 집중되는 패턴이다. 대표적인 사례:

• 카운터 테이블: UPDATE stats SET count = count + 1 WHERE id = 1 형태로 모든 쓰기가 같은 행을 갱신한다.
• 재고 차감: UPDATE inventory SET stock = stock - 1 WHERE product_id = 123이 초당 수백 건 들어온다.
• 큐 상태 테이블: 모든 worker가 같은 행(또는 같은 파티션)에서 작업을 가져간다.

Hot row 문제는 row lock이 순차적으로 기다리는 직렬화(serialization)를 유발한다. 한 트랜잭션이 행을 잠근 동안 다른 트랜잭션이 모두 대기한다.

진단

-- MySQL: 잠금 대기 상황 확인
SELECT
    r.trx_id                     AS waiting_trx,
    r.trx_mysql_thread_id        AS waiting_thread,
    r.trx_query                  AS waiting_query,
    b.trx_id                     AS blocking_trx,
    b.trx_mysql_thread_id        AS blocking_thread,
    b.trx_query                  AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- PostgreSQL: 잠금 대기 확인
SELECT
    pid,
    wait_event_type,
    wait_event,
    state,
    left(query, 100) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

완화 전략

1. 카운터 샤딩(Counter Sharding)

단일 카운터 행을 N개의 행으로 쪼개고, 쓰기 시 랜덤하게 선택한다. 읽기 시 합산한다.

-- 쓰기: 0~9 중 랜덤 shard에 +1
UPDATE page_views
SET count = count + 1
WHERE page_id = 42 AND shard = FLOOR(RAND() * 10);

-- 읽기: 합산
SELECT SUM(count) FROM page_views WHERE page_id = 42;

2. 버퍼링 집계

애플리케이션 레이어(Redis incr 등)에서 카운트를 버퍼링하고 주기적으로 DB에 flush한다. DB에는 초당 수천 건이 아니라 초당 1건의 업데이트만 도달한다.

3. 파티셔닝으로 hot partition 분산

시계열 데이터에서 가장 최근 파티션에 INSERT가 집중된다. 파티션을 날짜 단위로 분리하면 같은 날짜의 INSERT는 같은 파티션 내에서 경합하지만, 다른 날짜 파티션은 영향을 받지 않는다.

쓰기 성능 종합 체크리스트

실전 시나리오: 재고 차감 경합

전형적인 Hot Row 장애 패턴이다.

-- 문제 쿼리: 동시 주문 시 race condition + 직렬화
BEGIN;
SELECT stock FROM inventory WHERE product_id = 100 FOR UPDATE;
-- 재고 확인 후 차감
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;

초당 200건의 주문이 들어오면 같은 product_id=100 행에 200개의 트랜잭션이 줄을 선다. 대기 시간이 누적되어 timeout이 발생한다.

개선 방안:

-- 방법 1: 차감과 검증을 한 쿼리로 (락 보유 시간 최소화)
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 100 AND stock > 0;
-- affected rows가 0이면 재고 없음으로 처리

-- 방법 2: 낙관적 잠금 (버전 관리)
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE product_id = 100 AND stock > 0 AND version = :expected_version;
-- 실패하면 재시도 (충돌 드문 워크로드에서 효과적)

SELECT FOR UPDATE → UPDATE의 2단계보다 단일 UPDATE가 잠금 보유 시간을 훨씬 줄인다. 여기에 애플리케이션 레벨 재고 캐싱을 조합하면 DB 부하를 더 줄일 수 있다.

References

• MySQL 8.4 Reference Manual, "InnoDB Locking" — https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html
• MySQL 8.4 Reference Manual, "AUTO_INCREMENT Handling in InnoDB" — https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html
• Percona Blog, "InnoDB's Gap Locks" — https://www.percona.com/blog/innodbs-gap-locks/
• Jahfer's Blog, "A Comprehensive (and Animated) Guide to InnoDB Locking" — https://jahfer.com/posts/innodb-locks/
• cloudraft.io, "Tuning PostgreSQL for Write Heavy Workloads" — https://www.cloudraft.io/blog/tuning-postgresql-for-write-heavy-workloads
• techbuddies.io, "Top 7 PostgreSQL COPY Bulk Load Mistakes That Kill Throughput" — https://www.techbuddies.io/2026/02/11/top-7-postgresql-copy-bulk-load-mistakes-that-kill-throughput/
• mssqltips.com, "Optimize Large SQL Server Insert, Update and Delete Processes by Using Batches" — https://www.mssqltips.com/sqlservertip/5636/optimize-large-sql-server-insert-update-and-delete-processes-by-using-batches/
• halodoc Engineering Blog, "Optimising Database Performance with Efficient Transaction Management" — https://blogs.halodoc.io/db-perf-with-efficient-tran-mgmt/
• CockroachDB Docs, "SQL Performance Best Practices" — https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview