Retoday의 방문 기록 저장 기능은 메타 데이터의 변경을 반영하기 위해 매번 웹사이트 및 페이지를 Upsert하는데요. 동시 요청이 늘어나자 Upsert에서 대기가 길어졌습니다.

이번 글에서는 요청 추적과 MySQL의 락 정보를 연결해 어디에서 기다리는지 확인합니다. 같은 페이지뿐 아니라 서로 다른 페이지의 요청도 영향을 주는 이유를 살펴보고, 트랜잭션 분리와 변경 여부 확인을 비교해 불필요한 Upsert를 줄인 과정을 정리했습니다.

성능 측정

사용자 1,000명이 동시에 기록 생성 요청을 보내는 테스트를 통해 성능을 측정했습니다. 모두 같은 페이지를 방문하는 경우와 사용자마다 다른 페이지를 방문하는 경우를 비교했습니다.

동시 사용자가 많아지면 그만큼 응답 시간이 크게 늘어나는 것을 확인할 수 있었습니다.

병목 원인

요청이 같은 페이지에 집중될 때 지연이 커진다는 점에 주목해, 어느 구간이 지연 시간을 가장 크게 가지는지 확인했습니다. 어느 구간에서 응답이 늦어지는지 OpenTelemetry로 가장 오래 걸린 요청을 추적했습니다.

해당 요청에서는 응답 시간의 약 96%가 커넥션을 기다리는 데 쓰였습니다. 또한 비즈니스 로직 중에서는 웹사이트 Upsert 쿼리가 가장 오래 걸렸습니다.

동일 페이지에서는 여러 요청이 같은 웹사이트와 페이지 레코드를 Upsert합니다.

1override fun upsertByDomain(website: Website): Website {
2    dsl
3        .insertInto(WEBSITE)
4        .columns(
5            WEBSITE.ID,
6            WEBSITE.DOMAIN,
7            WEBSITE.FAVICON_URL
8        )
9        .values(
10            website.id ?: createUuid(),
11            website.domain,
12            website.faviconUrl
13        )
14        .onDuplicateKeyUpdate()
15        .set(WEBSITE.FAVICON_URL,website.faviconUrl)
16        .execute()
17
18    return dsl
19        .selectFrom(WEBSITE)
20        .where(WEBSITE.DOMAIN.equal(website.domain))
21        .fetchSingleInto()
22}

Upsert는 내부적으로 INSERT ... ON DUPLICATE KEY UPDATE와 SELECT 기반으로 구현되어 있는데요. INSERT ... ON DUPLICATE KEY UPDATE는 내부적으로 락을 사용합니다.

앞서 동일 페이지 재방문에서의 응답 시간이 더 길었던 점이나 요청 추적에서 Upsert 지연이 두드러졌던 점을 함께 보면 Upsert의 락 경합을 원인으로 생각할 수 있는데요. 물론 이러한 근거만으로 락이 원인이라고 확정할 수는 없으므로 실제로 어떤 락을 보유하고 무엇을 기다리는지 확인했습니다.

첫 번째 검증: 같은 페이지의 레코드 락 경합

먼저 동일 페이지 재방문 케이스에서 Upsert가 실행되는 동안 어떤 락 경합이 있었는지 조회했습니다.

1SELECT r.OBJECT_NAME AS table_name,
2       r.INDEX_NAME AS index_name,
3       r.LOCK_MODE AS requested_lock,
4       b.LOCK_MODE AS blocking_lock
5FROM performance_schema.data_lock_waits w
6JOIN performance_schema.data_locks r
7    ON r.ENGINE = w.ENGINE
8   AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
9JOIN performance_schema.data_locks b
10    ON b.ENGINE = w.ENGINE
11   AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
12WHERE r.OBJECT_SCHEMA = 'retoday';

같은 요청에서 uk_website_domain의 배타 락을 기다리는 것을 확인했습니다.

An exclusive next-key lock is taken for a duplicate unique key value.
- MySQL 공식 문서

INSERT ... ON DUPLICATE KEY UPDATE는 중복 검사 과정에서 공유 락을 쓰는 일반 INSERT와 달리 배타 락을 사용합니다. 그렇기에 중복 검사 과정부터 락 경합이 발생할 수 있습니다.

두 번째 검증: 서로 다른 페이지의 갭 락 경합

이번엔 서로 다른 페이지 재방문 케이스에서 발생하는 락 경합을 알아보겠습니다. 사용자마다 페이지가 다르므로, 락 경합이 없을거라 예상했는데요.

Upsert가 실행되는 동안 앞서 사용한 쿼리로 어떤 락 경합이 있었는지 조회했습니다.

1SELECT r.OBJECT_NAME AS table_name,
2       r.INDEX_NAME AS index_name,
3       r.LOCK_MODE AS requested_lock,
4       b.LOCK_MODE AS blocking_lock
5FROM performance_schema.data_lock_waits w
6JOIN performance_schema.data_locks r
7    ON r.ENGINE = w.ENGINE
8   AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
9JOIN performance_schema.data_locks b
10    ON b.ENGINE = w.ENGINE
11   AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
12WHERE r.OBJECT_SCHEMA = 'retoday';

예상과 달리 웹사이트의 클러스터드 인덱스(Clusterd Index)에서 락 대기가 관찰됐는데요. 락 경합은 Insert Intention Lock과 갭 락(Gap Lock) 사이에 발생했습니다. Insert Intention Lock은 삽입 전에 걸리는 락으로 일반 갭 락과는 충돌합니다. 여기서 이 일반 갭 락이 걸리는 원인을 분석해보겠습니다.

PRIMARY 레코드를 먼저 삽입하는 과정

먼저 INSERT가 각 인덱스를 처리하는 순서부터 살펴보겠습니다. row_ins()는 first_index()로 클러스터드 인덱스를 가져온 뒤, 인덱스를 하나씩 순회하며 엔트리를 삽입합니다. website에서는 PRIMARY가 첫 번째입니다.

1static dberr_t row_ins(ins_node_t *node, que_thr_t *thr) {
2  ...
3  if (node->state == INS_NODE_ALLOC_ROW_ID) {
4    node->index = node->table->first_index();
5    ...
6  }
7  ...
8  while (node->index != nullptr) {
9    if (node->index->type != DICT_FTS) {
10      err = row_ins_index_entry_step(node, thr);
11
12      switch (err) {
13        case DB_SUCCESS:
14          break;
15        ...
16        default:
17          return err;
18      }
19    }
20    node->index = node->index->next();
21    ...
22  }
23  ...
24}

row_ins_index_entry_step()은 삽입할 엔트리를 구성하고 row_ins_index_entry()를 호출합니다. 여기서 대상이 클러스터드 인덱스라면 row_ins_clust_index_entry()로 들어갑니다.

1static dberr_t row_ins_index_entry(...) {
2  ...
3  if (index->is_clustered()) {
4    return (row_ins_clust_index_entry(index, entry, thr, false));
5  } else if (index->is_multi_value()) {
6    ...
7  } else {
8    return (row_ins_sec_index_entry(index, entry, thr, false));
9  }
10}

마지막 인자인 dup_chk_only가 false이므로, PRIMARY에서는 중복 검사뿐 아니라 실제 삽입까지 수행합니다. 이번 요청은 기존 도메인에 새 UUID를 전달했으므로 PRIMARY의 중복 검사를 통과하고 새 레코드를 삽입할 수 있었습니다.

PRIMARY 삽입이 성공하면 다음 인덱스인 uk_website_domain을 처리합니다. 이때 row_ins_sec_index_entry() 내부에서 호출하는 row_ins_scan_sec_index_for_duplicate()가 기존 도메인과의 중복을 발견합니다. 즉, 모든 인덱스의 중복 검사를 마친 뒤 행을 삽입하는 것이 아니라, PRIMARY에 먼저 삽입한 상태에서 보조 유니크 인덱스의 중복을 확인하는 순서였습니다.

중복 키를 발견한 뒤의 부분 롤백

이어서 중복 키 오류를 처리하는 row_mysql_handle_errors()의 중단점에서 다음 값을 확인했습니다.

1bool row_mysql_handle_errors(..., trx_savept_t *savept) {
2  ...
3  switch (err) {
4    case DB_DUPLICATE_KEY:
5      ...
6      if (savept) {
7        trx_rollback_to_savepoint(trx, savept);
8        ...
9      }
10      break;
11    ...
12  }
13  ...
14}

(dberr_t) err = DB_DUPLICATE_KEY
(trx_id_t) trx->id = 2087

Upsert는 새 UUID로 PRIMARY 레코드를 먼저 삽입한 뒤, uk_website_domain에서 기존 도메인과 중복되는 것을 발견했습니다. 이때 트랜잭션 전체를 취소하는 대신, INSERT 전에 저장한 내부 savepoint까지 부분 롤백합니다. 이미 삽입한 새 PRIMARY 레코드를 제거한 뒤 기존 행의 UPDATE로 넘어가는 과정입니다.

호출 스택을 따라가니 새 레코드를 제거하는 row_undo_ins_remove_clust_rec()에서 row_convert_impl_to_expl_if_needed()를 호출하고 있었습니다.

1void row_convert_impl_to_expl_if_needed(btr_cur_t *cursor, undo_node_t *node) {
2  if (!node->partial || node->trx.isolation_level < trx_t::REPEATABLE_READ) {
3    return;
4  }
5  ...
6  auto index = cursor->index;
7  auto rec = btr_cur_get_rec(cursor);
8  ...
9  if (heap_no != PAGE_HEAP_NO_SUPREMUM && !dict_index_is_spatial(index) &&
10      !index->table->is_temporary() && !index->table->is_intrinsic()) {
11    lock_rec_convert_impl_to_expl(block, rec, index,
12                                  Rec_offsets().compute(rec, index));
13  }
14}

LLDB에서 부분 롤백 여부와 대상 인덱스를 확인했습니다.

(bool) node->partial = true
(trx_id_t) node->trx.id = 2087
(trx_t::isolation_level_t) node->trx.isolation_level = REPEATABLE_READ
(bool) node->trx.in_rollback = true
(id_name_t) index->name = (m_name = "PRIMARY")
(table_name_t) index->table->name = (m_name = "retoday/website")
(ulint) heap_no = 4

heap_no = 4는 이번 INSERT가 만든 임시 PRIMARY 레코드였습니다. 레코드 메모리의 첫 16바이트도 INSERT에 전달한 새 UUID와 일치했습니다. 즉, 기존 도메인의 행이 아니라 삽입했다가 되돌리는 새 레코드를 처리하고 있었습니다.

InnoDB는 삽입한 레코드의 DB_TRX_ID로 소유 트랜잭션을 판단할 수 있어, 처음부터 별도 락 객체를 만들지 않을 수 있습니다. 하지만 부분 롤백으로 레코드를 제거하면 이 정보도 사라집니다. 이 경로에서는 트랜잭션이 계속되는 동안 보호를 유지하기 위해, 레코드를 제거하기 전에 암시적 락을 명시적 락으로 바꿉니다.

1static void lock_rec_convert_impl_to_expl_for_trx(...) {
2  ...
3  if (!trx_state_eq(trx, TRX_STATE_COMMITTED_IN_MEMORY) &&
4      !lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP, block, heap_no, trx)) {
5    ulint type_mode;
6    type_mode = (LOCK_REC | LOCK_X | LOCK_REC_NOT_GAP);
7    lock_rec_add_to_queue(type_mode, block, heap_no, index, trx, true);
8  }
9  ...
10}

여기서 임시 레코드에 X,REC_NOT_GAP을 등록합니다. 아직 갭 락은 아닌데요. 갭 락으로 바뀌는 지점은 그 뒤 레코드를 제거할 때 호출되는 lock_update_delete()였습니다.

1void lock_update_delete(const buf_block_t *block, const rec_t *rec) {
2  ...
3  lock_rec_inherit_to_gap(block, block, next_heap_no, heap_no);
4  ...
5  lock_rec_reset_and_release_wait(block, heap_no);
6}
7
8static void lock_rec_inherit_to_gap(...) {
9  ...
10  lock_sys->rec_hash.find_on_record(RecID{block, heap_no}, [&](lock_t *lock) {
11    if (...) {
12      lock_rec_add_to_queue(LOCK_REC | LOCK_GAP | lock_get_mode(lock),
13                            heir_block, heir_heap_no, lock->index, lock->trx);
14    }
15    return false;
16  });
17}

lock_rec_inherit_to_gap()은 제거할 레코드의 락을 다음 레코드에 넘기면서 LOCK_GAP을 붙입니다. 결국 삽입된 레코드에 걸린 락이 해당 위치의 갭 락으로 바뀌게 됩니다.

Upsert가 끝난 뒤 ROW_COUNT()는 0이었지만, 커밋 전에는 이 갭 락이 유지됐습니다. 다른 도메인의 Upsert도 새 UUID를 같은 갭에 삽입하려 하면 X,GAP,INSERT_INTENTION을 요청한 채 기다렸고, 앞선 트랜잭션을 커밋하자 실행을 이어갔습니다.

결국 서로 다른 페이지라 해도 새 PRIMARY 레코드를 넣었다가 부분 롤백하는 과정에서 갭 락이 남을 수 있으며 해당 갭 락이 다른 기본 키 삽입을 막을 수 있는 것이 원인이었습니다.

문제 해결

값이 같은 재방문에서도 Upsert를 실행하면서 락을 기다리는 문제가 있었는데요. 이를 해결하기 위해 락을 빨리 해제하는 방법과 Upsert 자체를 생략하는 방법을 비교했습니다.

첫 번째 방법: 트랜잭션 분리

웹사이트·페이지·방문 기록을 묶던 외부 트랜잭션의 전파 수준을 NOT_SUPPORTED로 바꾸고, 각 작업에서 커밋하도록 구성했습니다. 웹사이트의 락을 방문 기록 저장까지 유지하지 않는 방법입니다.

다만 웹사이트와 페이지가 커밋된 뒤 방문 기록 저장만 실패할 수 있으므로, 전체 작업을 함께 되돌리는 원자성은 유지되지 않습니다.

두 번째 방법: 변경 여부 확인

일반 SELECT로 웹사이트와 페이지를 먼저 조회하고, 요청의 값과 같으면 바로 반환하도록 구성했습니다. 행이 없거나 값이 바뀐 경우에만 Upsert를 실행하며, 전체 트랜잭션은 유지했습니다.

웹사이트는 정규화한 도메인으로 조회한 뒤 파비콘을 비교합니다. 같으면 UUID 생성과 Upsert를 생략합니다.

1@Transactional
2fun upsertWebsite(command: UpsertWebsiteCommand): Website =
3    with(command) {
4        val canonicalizedDomain = canonicalizeDomain(domain)
5        val website = websiteRepository.findByDomain(canonicalizedDomain)
6
7        if (website != null && website.faviconUrl == faviconUrl) {
8            return website
9        }
10        ...
11    }

페이지도 같은 방식으로 처리하되, 제목과 설명을 함께 비교했습니다.

1@Transactional
2fun upsertPage(command: UpsertPageCommand): Page =
3    with(command) {
4        val page = pageRepository.findByUrl(url)
5
6        if (page != null && page.title == title && page.description == description) {
7            return page
8        }
9
10        pageRepository.upsertByUrl(
11            Page(websiteId = websiteId, url = url, title = title, description = description)
12        )
13    }

값이 같은 재방문에서는 웹사이트와 페이지 각각의 Upsert와 잠금 읽기가 일반 SELECT 하나로 줄어듭니다. 방문 기록 INSERT는 그대로 실행합니다.

행이 없거나 메타데이터가 바뀌었다면 기존 Upsert를 실행합니다. 두 요청이 모두 행이 없다고 조회해도 실제 삽입 시의 중복은 유니크 제약조건이 처리합니다. 이 경로의 SELECT FOR SHARE도 유지해, 기존 스냅샷에 없던 행이 다른 트랜잭션에서 생성됐더라도 Upsert 이후에 조회할 수 있도록 했습니다.

성능 테스트

두 방법과 두 변경을 함께 적용한 경우를 기존 구현과 비교했습니다. 서버 수와 커넥션 풀, DB 리소스는 그대로 유지했습니다. 재방문은 사용자 1,000명이 60초 동안 요청하는 조건으로 진행했고, 최초 방문도 별도로 비교했습니다.

요청 조건별 응답 시간은 다음과 같았습니다.

동일 페이지 재방문

서로 다른 페이지 재방문

두 조건 모두 평균 응답 시간이 가장 짧았던 변경 여부 확인 방식을 선택했습니다. 재방문에서는 p95와 p99도 가장 낮았고, 처리량은 동일 페이지에서 559req/s에서 1,724req/s로, 서로 다른 페이지에서 714req/s에서 1,740req/s로 늘었습니다.

두 변경을 모두 적용했을 때 최초 방문의 p95는 조금 더 낮았습니다. 다만 변경 여부만 확인하는 방법이 세 조건의 평균 응답 시간이 더 짧았고, 전체 작업의 원자성도 유지할 수 있어 이 방법을 선택했습니다.