RDB를 작업 큐로 활용할 때의 처리 과정과 지침

Kafka 컨슈머 그룹과 비교하면

작업을 저장해 두고 여러 워커가 나눠 처리한다는 점에서 RDB 작업 큐는 Kafka 컨슈머 그룹과 비슷한 역할을 합니다. 이 글의 워커가 컨슈머에 해당하고, DB 테이블과 작업 선점 로직이 작업의 저장/배분/처리 상태 관리를 담당하게 됩니다.

일반적인 Kafka 컨슈머 그룹은 파티션을 컨슈머에게 할당하고, 파티션별 커밋 오프셋으로 다시 읽을 위치를 관리합니다. 이 글의 RDB 큐는 워커가 처리 가능한 행을 선점한 뒤 개별 작업의 상태와 소유자를 기록하는 방식으로, 작업 배분 단위가 파티션/행으로 달라집니다. (Kafka 설계 문서)

그럼에도 RDB 큐는 기존 업무 데이터의 변경과 작업 등록을, 같은 DB 트랜잭션으로 처리할 수 있다는 장점을 가지고 있습니다. 또한 관리 인프라가 없는 환경에서, 별도 메시징 시스템을 운영하지 않고 시작할 수도 있습니다. 대신 Stale 작업의 회수, 재시도 정책, 완료 이력 관리는 애플리케이션에서 구현해야 합니다.

실제 작업과 완료 기록을 별도로 처리하면 중복 실행 가능성도 존재합니다. 외부 API 호출을 마친 직후 워커가 죽으면, 완료 기록이 없어 같은 작업을 다시 실행할 수 있습니다. Kafka에서도 처리 후 오프셋 커밋 전에 장애가 나면 재처리가 발생할 수 있으므로, 외부 부수 효과에는 멱등성 처리가 필요합니다 (Kafka 전달 보장).

이번 포스트에서는 이런 작업 큐를 InnoDB로 구현할 때 인덱스, 트랜잭션, 워커 수에 따라 비용이 어떻게 달라지는지 측정합니다.

RDB 작업 큐를 검토할 때의 전제

단일 DB 인스턴스에서 작업 하나를 처리하는 데 트랜잭션 2~3개를 사용하는 큐를 대상으로 했습니다. 큐 테이블은 버퍼 풀에 사전 로드 되어 있고, 작업 지연은 초 단위 이상 허용하는 조건입니다.

아래 표는 이번 실험과 뒤에서 소개할 공개 측정 사례를 참고한 설계 기준입니다. 환경 별로 적용 가능한 정책이 다를 수 있음을 참고해 주세요

지연 요구가 100 ms 미만이거나, fan-out/streaming semantic이 필요하거나, 큐 자체에 rate-limit이 필요하면 처리량과 무관하게 다른 도구를 고려하는 것이 적합합니다.

측정 환경

클라이언트 하네스와 DB 서버가 같은 2 vCPU를 나눠 쓰는 환경에서 측정했습니다. 실제 적용시 장비별로 환경 구성이 상이할 수 있기 때문에, 같은 조건에서 설정 별 차이를 중심으로 비교하겠습니다. 락 개수와 카운터는 동작을 확인하는 데 사용하고, 지연 곡선에서는 처리량이나 동시성이 늘 때의 변화를 중점적으로 확인하였습니다.

세 가지 스키마를 사용하도록 구성했습니다.

-- v1: 흔한 형태. status가 보조 인덱스 선두 컬럼.
CREATE TABLE q_v1 (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  status   TINYINT UNSIGNED NOT NULL DEFAULT 0,   -- 0=ready 1=running 2=done
  run_at   BIGINT UNSIGNED NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  owner    VARCHAR(40) NULL,
  payload  VARBINARY(256) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_ready (status, run_at, id)
) ENGINE=InnoDB;

-- v2: 필터 컬럼을 PK 선두 컬럼으로 설정.
CREATE TABLE q_v2 (
  ... PRIMARY KEY (status, run_at, id), KEY idx_id (id)
) ENGINE=InnoDB;

-- v3: 샤드 프리픽스로 핫 페이지를 분산.
CREATE TABLE q_v3 (
  shard TINYINT UNSIGNED NOT NULL, ...
  PRIMARY KEY (shard, status, run_at, id), KEY idx_id (id)
) ENGINE=InnoDB;

작업을 선점하는 트랜잭션을 짧게 나눈다

작업 선점(claim)에서는 FOR UPDATE SKIP LOCKED로 후보 행을 잠근 뒤, PK로 해당 행의 상태를 갱신합니다. 실제 작업은 커밋 이후에 처리하고, 완료 기록은 별도 트랜잭션으로 남깁니다.

-- 클레임 트랜잭션. 짧게 끊는다.
START TRANSACTION;
SELECT id FROM q WHERE shard = ? AND status = 0
  ORDER BY run_at, id LIMIT 20 FOR UPDATE SKIP LOCKED;
UPDATE q SET status = 1, owner = ?, lease_until = ?
  WHERE shard = ? AND id IN (...);
COMMIT;
-- 실제 작업은 트랜잭션 밖에서 한다.
-- 완료 트랜잭션.
START TRANSACTION;
DELETE FROM q WHERE shard = ? AND id IN (...);
COMMIT;

조회와 상태 갱신 사이에 잠금이 없으면 두 워커가 같은 작업을 가져갈 수 있습니다. 후보를 읽는 시점에 잠그는 이유는 이 중복 선점을 막기 위해서입니다.

SKIP LOCKED 는 어디서 판정되는가

여러 워커가 같은 행에 접근하면 먼저 작업을 읽고 잠근 worker가 그 작업을 소비합니다. SKIP LOCKED를 사용하는 다른 워커는 잠금이 걸린 행을 건너뛰고 다음 후보를 찾습니다.

SELECT_SKIP_LOCKED 는 충돌을 확인하는 지점에서 즉시 반환합니다.

const auto conflicting = lock_rec_other_has_conflicting(mode, block, heap_no, trx);
if (conflicting.wait_for != nullptr) {
  switch (sel_mode) {
    case SELECT_SKIP_LOCKED:  return (DB_SKIP_LOCKED);
    case SELECT_NOWAIT:       return (DB_LOCK_NOWAIT);
    case SELECT_ORDINARY: ... rec_lock.add_to_waitq(conflicting.wait_for); ...
  }
}

— lock/lock0lock.cc

row_search_mvcc()는 이 반환값을 받으면 스캔 루프에서 다음 레코드로 이동합니다.

case DB_SKIP_LOCKED:
  if (prebuilt->select_mode == SELECT_SKIP_LOCKED) {
    goto next_rec;
  }

— row/row0sel.cc

MariaDB 11.4의 db0err.h, lock0lock.cc, row0sel.cc에는 DB_SKIP_LOCKED 에러 코드가 없습니다. 대신 락 대기 경로에서 skip_locked 플래그를 확인해 다음 레코드로 이동합니다.

case DB_LOCK_WAIT:
        err = DB_SUCCESS;
        if (prebuilt->skip_locked) { goto next_rec; }
        break;
case DB_LOCK_WAIT_TIMEOUT:
        if (prebuilt->skip_locked) { err = DB_SUCCESS; goto next_rec; }

— MariaDB 11.4 row/row0sel.cc. 클러스터 인덱스 경로에서는 lock_trx_handle_wait(trx) 호출이 앞에 하나 더 붙습니다.

이 경로에서 레코드를 건너뛰기로 결정할 때는 이미 일부 작업을 마친 상태입니다. B-tree 커서로 레코드에 접근하고 페이지 래치를 획득한 뒤, rec_get_offsets()를 계산하고 락 큐의 해시 체인에서 충돌을 확인합니다. 따라서 잠금을 기다리지 않아도 레코드를 읽고 충돌을 판정하는 비용은 발생하게 됩니다.

next_rec는 같은 스캔 루프 안에 있습니다. 건너뛴 행은 SQL 계층으로 전달되지 않으므로 LIMIT의 반환 행 수나 핸들러 카운터에도 포함되지 않습니다.

lock_rec_other_has_conflicting() 안쪽은 해시 체인 선형 탐색입니다.

lock_t *find_in_cell(size_t cell_id, F &&f) {
  lock_t *lock = (lock_t *)hash_get_first(ht.get(), cell_id);
  while (lock != nullptr) { ... lock = lock->hash; }
}

— lock0priv.h

한 행을 건너뛰는 비용은 O(1) 로 고정되지 않고 그 셀에 달린 락 구조체 수에 비례합니다..

필터 컬럼을 PK 선두에 두기

status를 보조 인덱스에서 조회하는 구조와 PRIMARY KEY (status, run_at, id)로 조회하는 구조를 비교했습니다. 복합 PK를 사용하는 경우 클레임당 락 요청이 20개에서 10개로 감소했고, MariaDB에서는 처리량이 67% 증가했습니다. id만으로 조회해야 한다면 KEY (id)를 별도로 설정했습니다.

보조 인덱스로 찾으면 클러스터 인덱스 레코드에도 락이 사용됩니다. 이 내용은 메뉴얼에서도 확인이 가능합니다. “검색에 보조 인덱스가 쓰이고 설정할 인덱스 레코드 락이 배타적이면, InnoDB 는 대응하는 클러스터 인덱스 레코드도 가져와 락을 건다” (Locks Set by Different SQL Statements).

LIMIT 10 클레임 질의가 실제로 잡는 락을 performance_schema.data_locks 로 측정했습니다.

행 10개를 잡는데 락 요청이 20개인 부분을 확인할 수 있습니다. 하지만 필터 컬럼을 PK 선두로 올리면, 예상한 10개를 확인할 수 있습니다. Shopify 와 Kir Shatrov 가 각자 도달한 결론과 같습니다. Shatrov 는 PRIMARY KEY (id) + INDEX (event_id) 를 PRIMARY KEY (event_id, id) 로 바꿔 “락 두 개를 하나로 줄였다”고 글을 남겼고, (Mastering SKIP LOCKED in MySQL), Shopify사는 재고 예약 테이블 PK 를 auto-increment 에서 (shop_id, inventory_item_id, inventory_group_id, id) 로 바꿔 “행당 락 하나로 줄였다”고 기술 블로그에 작성한 이력이 있습니다.(We replaced Redis with MySQL for inventory reservations—and it scaled).

Repeatable Read(이하 RR) 과 Read Committed(이하 RC) 는 락 개수는 같지만, 모드가 다릅니다. RR 에서 idx_ready 락의 모드는 X(next-key), RC 에서는 X,REC_NOT_GAP 입니다. 앞의 갭까지 잠그느냐에서 차이가 발생하는 것으로 확인됩니다.

상태 컬럼을 보조 인덱스에 넣지 않는다

상태 컬럼이 보조 인덱스에 있으면 상태를 바꿀 때마다 기존 엔트리를 delete-mark하고 새 엔트리를 삽입합니다. purge 를 정지시킨 상태에서 5만 행 테이블에 2만 번 쓰기를 한 뒤, 뷰를 풀고 purge 가 실제로 제거한 레코드 수를 INNODB_METRICS 로 측정했습니다.

MariaDB 10.11.14 에서 같은 실험을 돌리면 인덱스 크기 변화는 동일하지만(1.52 → 2.52 → 2.08 / 변화 없음 / 1.52 → 1.08) purge_upd_exist_or_extern_records 가 두 UPDATE 모두 20,000 으로 측정됨을 확인할 수 있습니다.

MySQL에서 UPDATE status는 2.0초, UPDATE attempts는 115.2초, DELETE는 119.2초로, purge 작업량이 더 적을 것으로 예상한 쪽이 오히려 58배 오래 걸렸습니다.

워커 수보다 큰 샤드 프리픽스를 둔다

여러 워커가 자주 접근하는 행을 서로 다른 페이지에 배치하려고 PK 앞에 샤드 컬럼을 추가했습니다. 이 실험에서 최대 지연은 104 ms에서 51.7 ms로 줄었습니다. 워커 수보다 많은 샤드로 나누는 이유는 락 큐와 뮤텍스의 경합이 페이지 단위로 모이기 때문입니다.

물리 테이블을 나눈 외부 사례도 있습니다. AWS는 같은 워크로드를 10개 테이블로 분리했을 때 history list length가 300만에서 50만으로 줄었다고 보고했습니다 (aws.amazon.com).

완료된 작업을 폴링 대상 테이블에 남기지 않는다

완료 이력을 같은 테이블에 쌓으면 워커가 폴링하는 테이블도 계속 커집니다. Solid Queue는 이를 피하기 위해 상태별로 테이블을 나눕니다 (dev.37signals.com).

이력 보관이 필요하다면 별도 테이블로 옮기는 구성을 검토할 수 있습니다. 보관 테이블에 시간 기준 RANGE 파티션을 두면 DROP PARTITION으로 오래된 이력을 지울 수 있고, 행 단위 삭제에 따른 undo와 purge 작업도 피할 수 있습니다.

클레임의 ORDER BY 가 인덱스로 해결되는지 확인한다

정렬에 인덱스를 사용할 수 없는 클레임 질의에서는 반환 행 수보다 훨씬 많은 행이 잠겼습니다. 이번 실험에서는 filesort가 발생하자 락이 20개에서 200,000개로 늘었습니다.

클레임 질의를 바꿀 때는 EXPLAIN으로 정렬까지 인덱스가 처리하는지 확인해야 합니다. 우선순위 큐라면 (queue, priority, id)처럼 정렬 컬럼을 순서대로 넣거나, 우선순위별로 인덱스 선두 컬럼을 상수 조건으로 고정하는 구조를 검토할 수 있습니다.

락 힌트를 어떻게 붙이든 잠기는 범위는 Execution plan이 결정하게 됩니다. 10만 행 테이블에서 같은 LIMIT 10 FOR UPDATE SKIP LOCKED 을 ORDER BY 만 바꿔 실행했습니다.

MariaDB 10.11 에서도 같은 두 질의가 각각 락 20개 / 200,000개, 0.55 ms / 108 ms 로, 10,000배 차이를 확인할 수 있습니다. 우선순위 큐를 만드는 과정에서 ORDER BY priority, id 를 쓰고, 인덱스가 (status, run_at, id) 뿐이면 테이블 전체가 잠기게 됩니다. SKIP LOCKED 이 붙어 있어도 동일한 결과를 반환합니다. “잠금 읽기, UPDATE, DELETE 는 일반적으로 SQL 문 처리 과정에서 스캔한 모든 인덱스 레코드에 레코드 락을 건다. 그 행을 제외시킬 WHERE 조건이 있는지는 상관없다” (Locks Set by Different SQL Statements).

클레임 트랜잭션을 READ COMMITTED 로 내린다

클레임 트랜잭션에는 READ COMMITTED를 권합니다. 아래 실험에서는 갭 락으로 producer가 막히는 문제와 MariaDB의 ERROR 1020을 피할 수 있었습니다. 조건에 맞지 않는 행의 락도 즉시 해제합니다.

READ COMMITTED는 row-based binlog를 요구하고 팬텀 읽기를 허용합니다. 다만 SKIP LOCKED를 사용하는 클레임은 이미 잠긴 행을 건너뛰므로, 큐 전체의 일관된 뷰를 제공하는 용도로는 사용할 수 없습니다.

if (prebuilt->table->skip_gap_locks() ||
    (trx->skip_gap_locks() && prebuilt->select_lock_type != LOCK_NONE && ...)) {
  set_also_gap_locks = false;
}

— row/row0sel.cc (mysql-server 8.0)

trx->skip_gap_locks() 는 격리 수준이 READ COMMITTED 이하인지만 확인합니다(trx0trx.h). SKIP LOCKED 를 붙여도 이 값은 동일합니다. RR 에서 FOR UPDATE SKIP LOCKED 는 여전히 next-key 락을 요청합니다.

큐가 비었을 때 측정 결과입니다. 결과 0건인 클레임 질의를 열어둔 채 producer 가 INSERT 를 시도합니다.

MariaDB 10.11에서도 같은 결과를 확인할 수 있었습니다. RR에서는 2,001 ms 뒤에 1205 오류가 발생했고, RC에서는 1.5 ms에 INSERT를 완료했습니다.

작업이 쌓인 상태의 부하 테스트에서는 이 차이를 확인할 수 없었는데요, 20초 동안 RR과 RC의 처리량은 각각 5,252와 5,254 jobs/s였고, 락 대기도 1건과 0건으로 비슷했습니다. 이러한 현상은 스캔 범위 끝에 도달하기 전에 LIMIT 10을 채울 수 있었기 때문입니다.

큐가 비었을 때는 스캔이 끝까지 진행돼 갭 락을 설정합니다. 작업이 충분한 경우만 테스트 할 경우, 빈 큐에 producer가 새 작업을 넣지 못하는 상황을 놓칠 수 있습니다.

MariaDB 11.6.2 이후에는 스냅샷 격리 설정도 확인해야 합니다. innodb_snapshot_isolation 이 11.6.2 에서 기본값 ON 으로 바뀌었습니다 (MDEV-35124, Fix Version 11.6.2). 11.8 은 이 기본값을 물려받습니다. 10.11.14 에서 세션 변수를 직접 켜 11.8 의 기본 동작을 재현했습니다.

시나리오는 흔한 클레임 경합입니다. 워커 A 가 트랜잭션을 열고 후보를 읽는 사이 워커 B 가 같은 행을 클레임하고 커밋합니다. 그다음 A 가 그 행을 FOR UPDATE 로 잡으려 합니다.

1020 은 데드락과 같은 취급이라 트랜잭션 전체 롤백이 발생합니다. 다만, MySQL에는 대응하는 동작이 없습니다. Laravel, DOMjudge, Friendica, webtrees 가 이 변경으로 깨졌다는 Report를 확인할 수 있습니다 (Percona 분석, laravel/framework#56944).

따라서 MariaDB 11.8에서는 클레임을 READ COMMITTED로 실행하거나, 1020을 재시도하거나, innodb_snapshot_isolation=OFF로 설정할 수 있습니다. 이 큐에서는 READ COMMITTED를 권장합니다. 앞서 확인한 빈 큐의 갭 락 문제도 함께 줄일 수 있기 때문입니다.

작업 처리를 트랜잭션 밖에서 한다

작업을 처리하는 동안 트랜잭션의 읽기 뷰가 유지되면, 그 뷰가 필요한 undo를 purge가 제거하지 못합니다. 작업 처리를 클레임 트랜잭션 밖으로 분리해야 하는 이유를 유휴 트랜잭션 실험을 통해 확인해 보겠습니다.

리스 컬럼(lease_until)을 두고 만료된 클레임을 회수하는 방식으로 대신합니다. 이때 깨어난 워커가 재처리 할 수 있기 때문에, 회수와 완료 처리에는 클레임 토큰을 조건으로 사용해야 합니다.

단일 스레드로 SELECT ... FOR UPDATE SKIP LOCKED 클레임을 반복하면서 서버 어딘가에 유휴 REPEATABLE READ 트랜잭션 하나를 열어둔 채로 10만 건을 처리하도록 구성했습니다. 클레임 질의의 p50 지연입니다.

유휴 트랜잭션을 커밋한 직후 다시 재면 1.61 / 0.88 / 0.48 ms 입니다. 같은 조건에서 그 트랜잭션만 열지 않으면 10만 건을 처리하는 동안 0.356~0.424 ms 지연시간을 유지합니다.

생각해보면 당연하지만, insert undo 와 update undo 는 각각 다른 생명 주기를 갖고 있습니다. “insert undo 로그는 트랜잭션 롤백에만 필요하므로 트랜잭션이 커밋되는 즉시 버릴 수 있다.” “update undo 로그는 일관된 읽기에도 쓰이므로, InnoDB 가 스냅샷을 할당한 트랜잭션이 하나도 남지 않은 뒤에야 버릴 수 있다” (Multi-Versioning).

앞의 실험은 단일 스레드로 실행했고 별도 producer를 두지 않았습니다. 같은 조건에서 유휴 트랜잭션의 유무만 바꾼 결과를 MariaDB와 함께 비교하면 다음과 같습니다.

MariaDB 에서도 같은 실험을 진행한 결과, 정상 상태는 0.909~1.773 ms 안에서 큰 변화가 없었고, 차단을 푼 직후 1.57 / 0.92 / 0.63 ms 로 돌아왔습니다.

정상 상태는 10만 건을 처리하는 내내 큰 변화가 없지만, 차단 상태는 처리 건수에 비례하여 선형으로 증가하는 것을 확인할 수 있습니다. MariaDB 와 MySQL 의 비율은 2,000건에서 1.32배, 그 뒤로는 2.10 / 2.27 / 2.16 배였습니다. 초기 구간을 빼면 두 배 남짓한 차이를 확인할 수 있습니다.

이 실험에서는 읽기 뷰를 유지한 트랜잭션이 purge를 지연시켰습니다. 차단군에만 START TRANSACTION; SELECT COUNT(*) FROM q_v1; 을 실행하고 종료하지 않은 세션을 두었습니다. 데이터 변경이나 행 잠금 없이 조회만 한 트랜잭션도, 열린 채로 남아 있으면 purge에 영향을 줄 수 있습니다.

배치를 10~50 사이에 둔다

이 환경에서는 배치 크기 10~50이 적절했습니다. 배치를 키우면 작업당 커밋 비용을 줄일 수 있지만, 큐에 남은 작업보다 너무 크면 일부 워커가 작업을 모두 가져가고 나머지는 빈 폴링을 반복합니다.

배치를 1에서 50으로 늘리자 처리량은 8.9배로 늘고, 작업당 fsync는 1.289에서 0.114로 줄었습니다.

표의 값은 Innodb_os_log_fsyncs 변화량(델타)을 작업 수로 나눈 것이고 이 카운터는 redo fsync 만 측정합니다.

배치 200 에서 처리량이 다시 떨어지고 빈 폴링이 20,216 회로 늘어납니다. 배치가 커지면 워커 하나가 큐를 전체 소비한 뒤, 나머지 워커가 빈 폴링을 수행하기 때문입니다. 작업 처리 시간을 추가한 실험에서는 워커를 늘렸을 때 처리량이 개선됐습니다. 작업당 2 ms 를 CPU 로 소모하게 하면 8 워커 4,097 jobs/s, 32 워커 5,304 jobs/s 로 역전됩니다. 클레임이 병목일 때와 처리가 병목일 때의 최적 동시성이 다르기 때문에, 상황에 맞게 튜닝하여 설정하면 좋을 것 같습니다.

오류 처리와 지연을 확인하는 방법

1205 는 트랜잭션을 롤백하지 않는다

innodb_rollback_on_timeout 의 기본값은 OFF 이고 이 값은 전역이며 재시작이 필요합니다. MySQL 8.0.46 과 MariaDB 10.11.14 양쪽에서 확인했습니다. 락 대기 타임아웃이 나면 마지막 문장만 롤백되고 트랜잭션은 살아 있습니다.

트랜잭션 B가 행 11개를 잠근 뒤, A가 잠근 행에도 접근하도록 했습니다. 1205 오류가 발생한 직후에도 B가 먼저 획득한 락 11개는 유지됐습니다.

innodb_rollback_on_timeout = 0
error                      = 1205
타임아웃 직전 B 가 쥔 락    = 11
타임아웃 직후 B 가 쥔 락    = 11
B 의 트랜잭션 여전히 열림   = True

1205 오류를 잡은 뒤 로그만 남기면 커넥션에 트랜잭션과 락이 남아 다른 워커를 막을 수 있습니다. ROLLBACK을 실행한 뒤 재시도해야 합니다. 데드락 오류인 1213은 트랜잭션 전체를 롤백한다는 점에서 차이가 존재합니다.

세션 단위로 innodb_lock_wait_timeout 을 1~5초로 내려둡니다. 이 변수는 세션 범위이고 동적이라 전역을 건드리지 않고 워커만 바꿀 수 있습니다. 기본값 50초는 큐 워커에게 너무 깁니다.

재시도 대상은 1205, 1213, 그리고 MariaDB 11.8 이면 1020 입니다. 지수 백오프에 지터를 넣습니다.

행 수 카운터에 나타나지 않는 지연을 확인한다

먼저 history list length가 계속 증가하는지 확인합니다. 한 번 측정한 절대값보다 시간에 따른 증가 추이를 봐야 purge가 밀리는 상황을 찾을 수 있습니다.

SELECT NAME, COUNT FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

15분 이상 단조 증가하면 알림을 설정합니다. 고정 임계값을 둔다면 10만~50만 사이가 무난합니다. 값이 계속 늘면 오래 열린 트랜잭션을 조회합니다. 앞의 실험처럼 큐 테이블에 쓰지 않는 세션도 purge를 막을 수 있으므로 서버 전체를 확인해야 합니다.

SELECT t.trx_id, t.trx_started,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS age_secs,
       t.trx_isolation_level, t.trx_rows_modified,
       p.ID, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME, p.STATE
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.ID
ORDER BY t.trx_started ASC;

COMMAND가 Sleep인데 age_secs가 크다면 유휴 트랜잭션인지 확인할 필요가 있습니다.

슬로우 쿼리 로그의 Rows_examined 도 같은 값입니다.

따라서 Rows_examined만으로 클레임 비용을 판단하기는 어렵습니다. 애플리케이션에서 지연 분포를 수집하고, DB에서는 다음 질의로 평균/최대 지연과 검사 행 수를 함께 확인합니다.

SELECT DIGEST_TEXT, COUNT_STAR,
       AVG_TIMER_WAIT/1e9 AS avg_ms, MAX_TIMER_WAIT/1e9 AS max_ms,
       SUM_ROWS_EXAMINED/COUNT_STAR AS rows_per_call
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%FOR UPDATE SKIP LOCKED%'
ORDER BY SUM_TIMER_WAIT DESC;

락 대기가 어느 인덱스에서 나는지는 MySQL 이면 sys.innodb_lock_waits 의 locked_index, MariaDB 이면 information_schema.innodb_trx.trx_rows_locked 로 판단합니다.

측정 결과와 맞지 않았던 가설

잠긴 행을 건너뛰는 데 얼마나 시간이 드는지, 락을 해제할 때 대기자를 처리하는 코드가 병목이 되는지 확인했습니다.

잠긴 행을 건너뛰는 데에도 시간이 든다

다른 세션에서 앞쪽 K개 행을 잠근 뒤 LIMIT 10 FOR UPDATE SKIP LOCKED 질의를 실행했습니다. 잠긴 행 수를 바꿔 가며 같은 조건에서 7회씩 측정했고, 아래 표에는 중앙값을 적었습니다.

잠긴 행이 많을수록 질의가 오래 걸렸고, 증가 폭은 거의 일정했습니다. 회귀로 구한 행당 비용은 MySQL 0.130 µs, MariaDB 0.171 µs였습니다.

goto next_rec로 다음 행으로 넘어가더라도, 그 전에 레코드를 읽고 잠금 충돌을 확인해야 합니다. 잠금이 풀리기를 기다리는 시간은 없어도 이 작업에 드는 시간은 남습니다.

여러 워커가 같은 시작점에서 스캔하면 이 비용이 반복됩니다. 워커 N개가 각각 B건을 잠근다고 하면, N번째 워커는 앞선 워커들이 잠근 (N−1)·B개 행을 지나쳐야 합니다. 한 라운드의 총 스캔량은 O(N²·B)이므로 워커 수를 늘리는 만큼 스캔 비용도 빠르게 커집니다.

뒤에서 다룰 동시성 실험에서는 워커 16개를 넘기자 처리량이 떨어졌습니다. 이 구간을 해석할 때는 작업을 나눠 처리하는 효과와 함께, 같은 행을 반복해서 스캔하는 비용도 고려해야 합니다.

복합 PK(v2)로 바꾸면 나아질지도 확인했지만, 지연이 늘어나는 기울기는 거의 같았습니다. 락 요청은 절반으로 줄어도 스캔할 인덱스 엔트리 수는 그대로였기 때문입니다. 락 요청 수가 줄었다는 결과만으로 스캔 비용까지 줄었다고 볼 수는 없었습니다.

앞서 확인한 행 수 카운터에도 이 스캔은 잡히지 않았습니다. 행을 건너뛰는 처리가 InnoDB 내부에서 끝나므로 SQL 계층의 핸들러 카운터에는 나타나지 않습니다.

J-F Gagné 도 delete-marked 행 스캔에서 같은 지적을 하며 “rows examined blindspot” 이라고 부릅니다 (Mind the InnoDB Purge). 그가 제시한 우회로는 buffer_pool_read_requests 와 Percona 의 innodb_pages_distinct 인데 이 실험에서는 그 카운터를 기록하지 않아 실제로 반응하는지 확인하지 못했습니다. 질의 지연 분포만 확실히 쓸 수 있습니다.

락 해제 경로의 대기자 처리는 원인이 아니었다

락을 해제하는 코드에도 비용이 커질 만한 부분이 있었습니다. lock_rec_grant_by_heap_no()는 락 큐를 한 번 훑어 granted / waiting / low-priority로 나눕니다. 이어서 가중치가 큰 저우선순위 대기자를 정렬하고, 각 대기자가 이미 허가된 락과 충돌하는지 확인합니다.

in_lock->hash_table().find_on_record(rec_id, [&](lock_t *lock) {
  ...
  return false;                 // 절대 조기 종료하지 않는다 -> 큐 전체 순회
});
std::stable_sort(low_priority_heavier.begin(), low_priority_heavier.end(), ...);
for (lock_t *wait_lock : waiting) {
  const lock_t *blocking_lock =
      lock_rec_has_to_wait_for_granted(wait_lock, granted, new_granted_index);
  ...
}

— lock/lock0lock.cc

큐 길이를 Q, 대기자 수를 W, 허가된 락 수를 G, 정렬 대상 수를 k로 두면 비용은 O(Q) + O(k log k) + O(W × G)입니다. 이 코드를 보고 워커가 늘어날수록 락 해제 비용도 커져 처리량이 떨어지는 것은 아닐까 생각했습니다.

측정 결과는 이 예상과 달랐습니다. 워커를 1개부터 64개까지 늘려도 Innodb_row_lock_waits의 증가량은 모두 0이었습니다. SKIP LOCKED를 사용해 락 대기자가 생기지 않았고, W가 0이면 위 함수는 정렬과 이중 루프에 들어가기 전에 반환합니다.

따라서 이번 처리량 감소를 대기자 정렬이나 락을 넘기는 비용으로 설명하기는 어려웠습니다. MySQL의 CATS와 MariaDB의 FCFS도 처리할 대기자가 없었다는 점은 같아서, 두 엔진의 결과가 달랐던 이유를 스케줄러 차이에서 찾을 수도 없었습니다.

클레임 방식과 워커 수에 따른 처리량

클레임 전략별 처리량

워커 8개, 배치 크기 10, READ COMMITTED 조건에서 20초씩 실행했습니다. producer 2개가 실행 중에 새 작업을 계속 적재하도록 했습니다.

MySQL에서 v1의 SKIP LOCKED 처리량은 FOR UPDATE의 약 2.2배였고, p99 지연은 약 9분의 1이었습니다. 락 대기는 각각 0건과 16,818건으로, 대기를 건너뛰는 효과가 이 비교에서 드러났습니다.

복합 PK 를 넣었을 때 MySQL 은 5,094 대 5,002 로 차이가 없는데 MariaDB 는 3,740 대 6,252 로 67% 차이가 납니다. 두 엔진에서 효과가 갈립니다. MariaDB 에서 락 요청 두 개를 하나로 줄이는 이득이 더 큽니다. 스케줄러 차이(CATS 대 FCFS)와 데드락 탐지 위치 차이가 후보지만 이 실험만으로는 원인을 특정하지 못했습니다.

샤드 프리픽스의 차이는 처리량보다 최대 지연에서 나타났습니다. MySQL의 최대 지연은 v1 104 ms, v2 145 ms, v3 51.7 ms였습니다. 자주 접근하는 행을 여러 페이지로 나누려던 의도와 맞는 결과였지만, 페이지 분산만의 효과로 단정하기에는 아래의 빈 폴링 차이가 남습니다.

다만 이 수치에는 교란 요인이 있습니다. v3 실행에서는 빈 폴링이 MySQL 317회, MariaDB 1,343회 나왔고 v1 은 0회입니다. 워커가 자기 샤드에서 빈손으로 돌아오는 만큼 경합 자체가 줄어든 것이라 개선분 중 얼마가 페이지 분산 덕분인지 분리하지 못했습니다.

2단계 방식이 FOR UPDATE 와 같은 수준인 점은 예상 밖이었습니다. UPDATE ... WHERE status=0 ORDER BY run_at, id LIMIT 10 자체가 스캔한 모든 레코드에 배타 next-key 락을 거는 잠금 문이기 때문에 락을 안 쥔 채 읽는다는 이점이 사라집니다. 작업당 InnoDB 행 읽기도 4.37 로 SKIP LOCKED 의 3.0 보다 큽니다.

워커를 늘려도 처리량이 계속 늘지는 않는다

워커를 1개에서 16개로 늘리자 MySQL 처리량은 3.2배가 됐지만 p50은 5.9배, p99는 6.5배 늘었습니다. 32개부터는 처리량도 떨어졌습니다. 이 결과는 클라이언트와 서버가 2 vCPU를 공유하는 조건에서 나온 값이므로, 다른 환경의 적정 워커 수는 별도로 측정해야 합니다.

두 엔진 모두 16 에서 정점이지만 32 에서의 붕괴 폭이 다릅니다. MySQL 은 5,442 → 3,866 (29% 하락), MariaDB 는 3,953 → 1,667 (58% 하락)입니다.

앞서 조사한 락 대기자 처리 경로는 이 실행에서 사용되지 않았습니다. 워커 32개와 producer 2개가 2 vCPU를 두고 경합했을 가능성이 남지만, CPU 경합을 따로 분리해 측정하지는 못했습니다. 두 엔진에서 처리량 감소 폭이 다른 이유도 이 실험만으로 설명하기 어렵습니다.

PlanetScale 이 Vitess/MySQL 에서 같은 형태를 관측하고 Gunther 의 Universal Scalability Law 로 설명했습니다. 요청이 서로를 느리게 만드는 비용이 N(N−1) 로 늘어나므로 동시성이 두 배가 되면 코히런시 비용은 네 배가 된다는 것입니다. 그들은 커넥션 풀 슬롯을 늘리는 대신 줄였습니다. 1,000 슬롯을 두고 초당 26,000 건의 슬롯 요청을 큐잉해 “어느 순간에도 MySQL 안에서 실행 중인 문장이 200개 미만”을 유지했습니다 (Concurrency vs throughput).

공개된 실측치

이 실험은 2 vCPU 컨테이너입니다. 규모 판단에는 실제 운영 수치가 필요합니다.

37signals 사례에서는 질의당 검사 행이 0.02라는 점을 함께 봐야 합니다. 작업 처리량과 별개로 빈 폴링이 큰 비중을 차지하는 환경입니다. 운영 부하를 비교할 때는 초당 작업 수뿐 아니라 폴링 빈도와 빈 결과의 비율도 필요합니다.

Percona 의 13,325 TPS 는 sysbench OLTP read-write 한 점이고, 40코어(2소켓 Xeon Gold 6230) 장비에서 512 클라이언트 스레드, 버퍼 풀 32 GB, 데이터셋 24 GB 로 돌린 값입니다. 더 큰 하드웨어에서는 더 높은 수치가 보고되므로 절대 상한으로 읽으면 안 됩니다. 다만 규모 감각을 잡는 기준으로는 쓸 만합니다.

큐가 작업당 23 트랜잭션을 쓰고 큐 트랜잭션 비용이 sysbench OLTP-RW 트랜잭션과 비슷하다고 가정합니다. 그러면 같은 급 장비에서 4,0006,500 jobs/s 근처가 나옵니다. 그 가정 자체는 재보지 않았습니다. 같은 벤치마크에서 버퍼 풀 2 GB 대 데이터셋 24 GB 라는 극단적 메모리 압박 구간의 값은 530 TPS 였습니다.

이 구성에서 감수해야 할 제약

앞에서 제안한 인덱스와 트랜잭션 구성을 적용하더라도, 조회 결과를 해석하는 방식과 작업 순서에는 제약이 남습니다. 이력 보관과 purge 감시도 별도로 운영해야 합니다.

SKIP LOCKED를 사용하면 다른 워커가 잠근 행은 조회 결과에서 빠집니다. 작업을 가져오는 데는 맞는 동작이지만, 이 결과를 보고 큐에 남은 작업이 전부 조회됐다고 생각해서는 안 됩니다.

클레임에 사용하는 READ COMMITTED에서는 팬텀 읽기도 허용합니다. 작업 선점에 허용한 조건을 집계나 현황 조회에 그대로 적용할 수는 없으므로, 조회 목적에 맞게 구분해야 합니다. row-based binlog를 사용하면서 늘어날 수 있는 복제 대역폭도 확인해야 합니다.

앞서 제안한 3,000 jobs/s 이상 구간에서는 purge 지연을 계속 감시해야 합니다. 지연이 생겼을 때 큐 테이블만 확인해서는 원인을 놓칠 수 있습니다. 큐에 접근하지 않는 세션도 purge를 막을 수 있어, 서버 전체에서 오래 열린 트랜잭션을 찾아야 합니다.

완료된 작업을 별도 테이블로 옮기면 워커가 폴링하는 테이블은 작게 유지할 수 있습니다. 진행 중인 작업과 완료 이력의 저장 위치가 달라지므로 조회 경로도 그에 맞춰 나눠야 합니다.

샤드 프리픽스를 두면 작업 순서는 샤드 안에서만 유지됩니다. WHERE shard = ? AND status = 0 ORDER BY run_at, id는 해당 샤드의 작업을 정렬할 뿐, 전체 큐의 선입선출 순서까지 보장하지는 않습니다. 전체 작업의 순서를 지켜야 한다면 이 방식으로 페이지를 분산하기는 어렵습니다.

남은 한계

• purge: 클레임 지연이 늘어난 것은 확인했지만, 세부 비용을 분리하거나 purge 완료 시간을 신뢰할 만큼 검증하지는 못했습니다.
• 처리량: 워커 32개에서 처리량이 떨어진 원인은 확정하지 못했습니다. 서버와 클라이언트가 공유한 2 vCPU의 영향도 남아 있습니다.
• 엔진: MySQL과 MariaDB에서 복합 PK의 효과가 달랐지만 원인은 구분하지 못했습니다. MariaDB 11.8은 직접 측정하지 않았습니다.
• 스토리지: 컨테이너의 가상 디스크에서 측정했으므로, 운영 환경의 NVMe나 EBS에서는 처리량과 fsync 지연을 다시 확인해야 합니다.

참고

InnoDB 소스는 mysql/mysql-server 의 8.0 / 8.4 브랜치와 MariaDB/server 의 11.4 브랜치를 2026-09-08 기준으로 확인했습니다. 인용한 함수명은 브랜치 사이에서 안정적이지만 줄 번호는 다를 수 있습니다.

매뉴얼

• InnoDB Locking Reads — SKIP LOCKED 의 큐 용도 언급, SBR 비안전성
• Locks Set by Different SQL Statements — 스캔한 모든 레코드를 잠근다는 서술
• Transaction Isolation Levels
• InnoDB Multi-Versioning — insert/update undo 수명, 커버링 인덱스 무효화
• Purge Configuration — purge 지연 공식, innodb_purge_threads 8.4 기본값
• MERGE_THRESHOLD
• Transaction Scheduling — CATS
• ORDER BY Optimization

워크로그와 버그

• WL#8919 — NOWAIT / SKIP LOCKED 구현
• WL#10314 — lock_sys 샤딩
• WL#13468 — CATS 개선과 FCFS 제거
• Bug #115226 — ORDER BY 와 SKIP LOCKED 의 과다 락 (Not a Bug)
• Bug #93115 — SKIP LOCKED 이 다음 행 UPDATE 를 막는 건 (Not a Bug)
• Bug #72269 — innodb_max_purge_lag_delay 없이는 스로틀이 걸리지 않음
• Bug #49047 — 단일 행에 락이 몰릴 때 데드락 탐지 비용

MariaDB

• MDEV-13115 — SKIP LOCKED (10.6.0)
• MDEV-20612, MDEV-24952 — lock_sys 확장성 (10.6.0)
• MDEV-16664 — VATS 제거 (10.6.0). 문서 반영 후속은 MDEV-23877
• MDEV-29694 — change buffer 제거 (11.0.1)
• MDEV-35124 — innodb_snapshot_isolation 기본 ON (11.6.2)
• MDEV-36103 — WHERE / ORDER BY 과다 락 (Not a Bug)
• MDEV-33940 / MDEV-5092 — UPDATE ... RETURNING, 13.0.1 에서 구현
• MDEV-25338 — UPDATE ... SKIP LOCKED (Open)
• MDEV-20487 — 적응형 해시 인덱스 기본 OFF (10.5)
• MDEV-29401, MDEV-32050 — 10.6 이후 purge 지연 회귀

논문과 분석

• Tian, Huang, Mozafari, Schoenebeck. Contention-Aware Lock Scheduling for Transactional Databases. PVLDB 11(5), 2018
• InnoDB Data Locking Part 2.5 / 3 / 4 — Oracle 의 락 시스템 해설
• J-F Gagné, Mind the InnoDB Purge on Queue or Row Deletion Job
• J-F Gagné, InnoDB Basics - Compaction: when and when not
• Kir Shatrov, Mastering SKIP LOCKED in MySQL
• Shopify, We replaced Redis with MySQL for inventory reservations—and it scaled
• PlanetScale, Concurrency vs throughput
• Percona, Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back
• Percona, MariaDB’s Snapshot Isolation
• Percona, Using SKIP LOCK For Queue Processing in MySQL
• 37signals, Introducing Solid Queue
• Meta, FOQS: Scaling a distributed priority queue
• AWS, Achieve a high-speed InnoDB purge
• Mark Callaghan, InnoDB, fsync and fdatasync