잠금 통계

Spanner는 특정 기간 동안 데이터베이스에서 트랜잭션 잠금 충돌의 주요 소스인 row key와 테이블 열을 식별할 수 있는 잠금 통계를 제공합니다. SQL 문을 사용하여 SPANNER_SYS.LOCK_STATS* 시스템 테이블에서 이러한 통계를 검색할 수 있습니다.

가용성

SPANNER_SYS 데이터는 SQL 인터페이스를 통해서만 사용할 수 있습니다. 예를 들면 다음과 같습니다.

• Google Cloud 콘솔에 있는 데이터베이스의 Spanner 스튜디오 페이지

• gcloud spanner databases execute-sql 명령어

• 잠금 통계 대시보드

• executeQuery API

Spanner가 제공하는 다른 단일 읽기 메서드는 SPANNER_SYS를 지원하지 않습니다.

row key를 사용한 잠금 통계

다음 테이블에서는 대기 시간이 가장 긴 row key를 추적합니다.

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE: 1분 간격 동안 잠금 대기 시간이 가장 긴 row key

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE: 10분 간격 동안 잠금 대기 시간이 가장 긴 row key

• SPANNER_SYS.LOCK_STATS_TOP_HOUR: 1시간 간격 동안 잠금 대기 시간이 가장 긴 row key

이러한 테이블에는 다음과 같은 속성이 있습니다.

• 각 테이블에는 테이블 이름에 지정된 길이의 겹치지 않는 시간 간격에 대한 데이터가 포함되어 있습니다.

• 간격은 시계 시간을 기준으로 합니다. 1분 간격은 매분 정각에 끝나고 10분 간격은 매시 정각에 시작해서 10분 단위로 끝나며 1시간 간격은 매시 정각에 끝납니다. 각 간격이 끝날 때마다 Spanner는 모든 서버에서 데이터를 수집한 다음 곧 SPANNER_SYS 테이블에 데이터를 제공합니다.

  예를 들어 오전 11:59:30에 SQL 쿼리에 사용할 수 있는 가장 최근 간격은 다음과 같습니다.

  • 1분: 오전 11:58:00–11:58:59
  • 10분: 오전 11:40:00–11:49:59
  • 1시간: 오전 10:00:00~10:59:59

• Spanner는 row key 범위를 시작하여 통계를 그룹화합니다.

• 각 행에는 Spanner가 지정된 간격 동안 통계를 캡처하는 특정 시작 row key 범위의 총 잠금 대기 시간 통계가 포함됩니다.

• Spanner가 특정 간격 동안 잠금 대기의 모든 row key 범위에 대한 정보를 저장할 수 없으면 시스템은 지정된 간격 동안 잠금 대기 시간이 가장 긴 row key 범위의 우선순위를 지정합니다.

• 테이블의 모든 열은 null을 허용합니다.

테이블 스키마

잠금 모드

Spanner 작업은 작업이 읽기-쓰기 트랜잭션의 일부인 경우에 잠금을 획득합니다. 읽기 전용 트랜잭션은 잠금을 획득하지 않습니다. Spanner는 서로 다른 잠금 모드를 사용하여 특정 시점에 특정 데이터 셀에 액세스하는 트랜잭션 수를 극대화합니다. 잠금마다 특성이 다릅니다. 예를 들어 일부 잠금은 여러 트랜잭션 간에 공유될 수 있지만 그렇지 않은 잠금도 있습니다.

트랜잭션에서 다음 잠금 모드 중 하나를 획득하려고 하면 잠금 충돌이 발생할 수 있습니다.

• ReaderShared 잠금 - 트랜잭션이 커밋될 수 있을 때까지 다른 읽기가 계속해서 데이터에 액세스할 수 있는 잠금입니다. 이 공유 잠금은 읽기-쓰기 트랜잭션이 데이터를 읽을 때 획득됩니다.

• WriterShared 잠금 - 이 잠금은 읽기-쓰기 트랜잭션이 쓰기를 커밋하려고 할 때 획득됩니다.

• Exclusive 잠금 - 이미 ReaderShared 잠금을 획득한 읽기-쓰기 트랜잭션은 읽기 완료 후에 데이터를 쓰려고 할 때 획득되는 배타적 잠금입니다. 배타적 잠금은 ReaderShared 잠금에서 업그레이드된 잠금입니다. 배타적 잠금은 ReaderShared 잠금과 WriterShared 잠금을 동시에 보유하는 특별한 경우입니다. 다른 트랜잭션에서는 동일한 셀의 모든 잠금을 획득할 수 없습니다.

• WriterSharedTimestamp 잠금 - 기본 키의 일부인 커밋 타임스탬프가 있는 테이블에 새 행을 삽입할 때 획득하는 특수한 유형의 WriterShared 잠금입니다. 이러한 유형의 잠금에서는 트랜잭션 참여자가 정확하게 동일한 행을 만들 수 없으므로 서로 충돌합니다. Spanner는 삽입된 행의 키를 업데이트하여 삽입을 수행한 트랜잭션의 커밋 타임스탬프와 일치시킵니다.

트랜잭션 유형과 사용 가능한 잠금 종류에 대한 자세한 내용은 트랜잭션을 참조하세요.

잠금 모드 충돌

다음 표에서는 여러 잠금 모드 간에 발생할 수 있는 충돌을 보여줍니다.

WriterSharedTimestamp 잠금은 기본 키의 일부인 타임스탬프가 포함된 새 행을 삽입하는 경우에만 사용됩니다. WriterShared 및 Exclusive 잠금은 기존 셀에 쓰거나 타임스탬프가 없는 새 행을 삽입할 때 사용됩니다. 따라서 WriterSharedTimestamp는 다른 유형의 잠금과 충돌할 수 없으며 이러한 시나리오는 앞의 표에 해당 없음으로 표시됩니다.

유일한 예외는 ReaderShared가 존재하지 않는 행에 적용될 수 있는 경우로, 이러한 경우에는 WriterSharedTimestamp와 충돌할 수 있습니다. 예를 들어 전체 테이블 스캔은 생성되지 않은 행의 전체 테이블을 잠그므로 ReaderShared가 WriterSharedTimestamp와 충돌할 수 있습니다.

행 범위 시작 키란 무엇인가요?

ROW_RANGE_START_KEY 열은 잠금 충돌이 있는 복합 기본 키나 행 범위의 시작 기본 키를 식별합니다. 다음 스키마는 예시를 설명하는 데 사용됩니다.

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  TrackId      INT64 NOT NULL,
  SongName     STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Users (
  UserId     INT64 NOT NULL,
  LastAccess TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  ...
) PRIMARY KEY (UserId, LastAccess);

다음 row key 및 row key 범위의 표에서는 키에 더하기 기호('+')로 표시되어 있는 범위를 보여줍니다. 이러한 경우의 키는 잠금 충돌이 발생한 키 범위의 시작 키를 나타냅니다.

집계 통계

SPANNER_SYS에는 특정 기간에 Spanner에서 캡처한 잠금 통계의 합산 데이터를 저장하는 테이블도 포함되어 있습니다.

• SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: 1분 간격 동안 모든 잠금 대기의 집계 통계입니다.

• SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: 10분 간격 동안 모든 잠금 대기의 집계 통계입니다.

• SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: 1시간 간격 동안 모든 잠금 대기의 집계 통계입니다.

집계 통계 테이블에는 다음과 같은 속성이 있습니다.

• 각 테이블에는 테이블 이름에 지정된 길이의 겹치지 않는 시간 간격에 대한 데이터가 포함되어 있습니다.

• 간격은 시계 시간을 기준으로 합니다. 1분 간격은 매분 정각에 끝나고, 10분 간격은 매시 정각에 시작해서 10분 단위로 끝나며, 1시간 간격은 매시 정각에 끝납니다.

  예를 들어 오전 11:59:30에 집계 잠금 통계에서 SQL 쿼리에 사용할 수 있는 가장 최근 간격은 다음과 같습니다.

  • 1분: 오전 11:58:00–11:58:59
  • 10분: 오전 11:40:00–11:49:59
  • 1시간: 오전 10:00:00~10:59:59

• 각 행에는 지정된 간격 동안 데이터베이스에 대한 모든 잠금 대기의 통계가 함께 집계됩니다. 따라서 시간 간격당 하나의 행만 있습니다.

• SPANNER_SYS.LOCK_STATS_TOTAL_* 테이블에 캡처되는 통계에는 Spanner가 SPANNER_SYS.LOCK_STATS_TOP_* 테이블에서 캡처하지 않은 잠금 대기가 포함됩니다.

• 이러한 테이블의 일부 열은 Cloud Monitoring에서 측정항목으로 노출됩니다. 노출된 측정항목은 다음과 같습니다.

  • 잠금 대기 시간

  자세한 내용은 Spanner 측정항목을 참조하세요.

테이블 스키마

쿼리 예

다음은 잠금 통계를 검색하는 데 사용할 수 있는 SQL 문의 예시입니다. 이러한 SQL 문은 클라이언트 라이브러리, gcloud spanner 또는 Google Cloud Console을 사용하여 실행할 수 있습니다.

이전 1분 간격 동안 잠금 통계 나열

다음 쿼리는 최근 1분 시간 간격 동안 총 잠금 충돌 비율 등 잠금 충돌이 있는 각 row key의 잠금 대기 정보를 반환합니다.

CAST() 함수는 row_range_start_key BYTES 필드를 STRING으로 변환합니다.

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_minute t, spanner_sys.lock_stats_top_minute s
WHERE t.interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.lock_stats_total_minute)
AND s.interval_end = t.interval_end
ORDER BY s.lock_wait_seconds DESC;

쿼리 출력

¹ _exists는 특정 행이 있는지 여부를 확인하는 데 사용되는 내부 필드입니다.

데이터 보관

Spanner는 각 테이블의 데이터를 최소한 다음 기간 동안 보관합니다.

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE 및 SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: 이전 6시간을 포함하는 간격

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE 및 SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: 이전 4일을 포함하는 간격

• SPANNER_SYS.LOCK_STATS_TOP_HOUR 및 SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: 이전 30일을 포함하는 간격

잠금 통계를 사용하여 데이터베이스의 잠금 충돌 문제 해결

SQL 또는 잠금 통계 대시보드를 사용하여 데이터베이스의 잠금 충돌을 볼 수 있습니다.

다음 주제에서는 SQL 코드를 사용하여 이러한 잠금 충돌을 조사하는 방법을 보여줍니다.

조사 기간 선택

Spanner 데이터베이스의 지연 시간 측정항목을 검토하고 앱에서 지연 시간이 길고 CPU 사용량이 많은 기간을 찾습니다. 예를 들어 이 문제는 2020년 11월 12일 오후 10시 50분에 발생하기 시작했습니다.

선택한 기간 동안 잠금 대기 시간과 함께 트랜잭션 커밋 지연 시간이 증가되었는지 확인

잠금은 트랜잭션에 의해 획득되므로 잠금 충돌로 인해 대기 시간이 길어진 경우 잠금 대기 시간 증가와 함께 트랜잭션 커밋 지연 시간 증가를 확인할 수 있어야 합니다.

조사를 시작하는 기간을 선택했으므로 잠금 대기 시간 증가로 인해 평균 커밋 지연 시간이 증가했는지 파악할 수 있도록 트랜잭션 통계 TXN_STATS_TOTAL_10MINUTE를 해당 기간의 잠금 통계 LOCK_STATS_TOTAL_10MINUTE에 조인합니다.

SELECT t.interval_end, t.avg_commit_latency_seconds, l.total_lock_wait_seconds
FROM spanner_sys.txn_stats_total_10minute t
LEFT JOIN spanner_sys.lock_stats_total_10minute l
ON t.interval_end = l.interval_end
WHERE
  t.interval_end >= "2020-11-12T21:50:00Z"
  AND t.interval_end <= "2020-11-12T23:50:00Z"
ORDER BY interval_end;

다음 데이터를 쿼리에서 반환되는 결과의 예시로 살펴보겠습니다.

이 앞선 결과에서는 2020-11-12 22:40:00부터 2020-11-12 22:50:00까지 동일한 기간 동안 avg_commit_latency_seconds 및 total_lock_wait_seconds에서 크게 증가하여 그 이후에 감소했음을 보여줍니다. 한 가지 주목해야 할 점은 avg_commit_latency_seconds는 커밋 단계에만 소비한 평균 시간이라는 점입니다. 반면에 total_lock_wait_seconds는 기간의 집계 잠금 시간이므로 시간이 트랜잭션 커밋 시간보다 길게 잠깁니다.

잠금 대기 시간이 쓰기 지연 시간의 증가와 밀접하게 관련되어 있음을 확인했습니다. 지금부터는 다음 단계에서 어떤 행과 열에서 긴 대기가 유발됐는지 조사합니다.

선택한 기간 동안 잠금 대기 시간이 긴 row key와 열 검색

조사 기간 동안 잠금 대기 시간이 긴 row key와 열을 확인하기 위해 잠금 대기에 가장 많이 기여한 row key와 열이 나열된 LOCK_STAT_TOP_10MINUTE 테이블을 쿼리합니다.

다음 쿼리의 CAST() 함수는 row_range_start_key BYTES 필드를 STRING으로 변환합니다.

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_10minute t, spanner_sys.lock_stats_top_10minute s
WHERE
  t.interval_end = "2020-11-12T22:50:00Z" and s.interval_end = t.interval_end;

이 결과 테이블에서 SingerId=32 키의 Singers 테이블에서 충돌이 발생한 것을 확인할 수 있습니다. Singers.SingerInfo는 ReaderShared와 WriterShared 사이에 잠금 충돌이 발생한 열입니다.

이는 트랜잭션 하나에서 특정 셀을 읽으려고 하고 다른 트랜잭션에서는 동일한 셀에 쓰려고 할 때 흔히 발생하는 충돌 유형입니다. 이제 트랜잭션이 잠금과 경합하는 정확한 데이터 셀을 알 수 있으므로 다음 단계에서 잠금과 경합되는 트랜잭션을 식별합니다.

잠금 충돌과 관련된 열에 액세스하는 트랜잭션 찾기

잠금 충돌로 인해 특정 시간 간격 내에 커밋 지연 시간이 크게 발생한 트랜잭션을 식별하려면 SPANNER_SYS.TXN_STATS_TOTAL_10MINUTE 테이블에서 다음 열을 쿼리해야 합니다.

• fprint
• read_columns
• write_constructive_columns
• avg_commit_latency_seconds

SPANNER_SYS.LOCK_STATS_TOP_10MINUTE 테이블에서 식별된 잠긴 열을 필터링해야 합니다.

• ReaderShared 잠금을 획득하려고 할 때 잠금 충돌이 발생한 열을 읽는 트랜잭션

• WriterShared 잠금을 획득하려고 할 때 잠금 충돌이 발생한 열에 쓰는 트랜잭션

SELECT
  fprint,
  read_columns,
  write_constructive_columns,
  avg_commit_latency_seconds
FROM spanner_sys.txn_stats_top_10minute t2
WHERE (
  EXISTS (
    SELECT * FROM t2.read_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "ReaderShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
OR
  EXISTS (
    SELECT * FROM t2.write_constructive_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "WriterShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
)
AND t2.interval_end ="2020-11-12T23:50:00Z"
ORDER BY avg_commit_latency_seconds DESC;

쿼리 결과는 avg_commit_latency_seconds 열을 기준으로 정렬되므로 커밋 지연 시간이 가장 긴 트랜잭션이 먼저 표시됩니다.

쿼리 결과에는 트랜잭션 2개에서 해당 기간 동안 잠금 충돌이 있는 열인 Singers.SingerInfo 열에 액세스를 시도했음이 표시됩니다. 잠금 충돌의 원인이 되는 트랜잭션을 식별한 후 디지털 지문인 fprint를 사용하여 트랜잭션을 분석하여 잠금 충돌에 기여한 잠재적 문제를 식별할 수 있습니다.

fprint=1866043996151916800을 사용하여 트랜잭션을 검토한 후 read_columns 및 write_constructive_columns 열을 사용하여 트랜잭션을 트리거한 애플리케이션 코드 부분을 식별할 수 있습니다. 그런 다음 기본 키인 SingerId에서 필터링되지 않는 기본 DML을 볼 수 있습니다. 이로 인해 전체 테이블이 스캔된 후 트랜잭션이 커밋될 때까지 테이블이 잠겼습니다.

잠금 충돌을 해결하려면 다음을 수행하면 됩니다.

1. 읽기 전용 트랜잭션을 사용하여 필요한 SingerId 값을 식별합니다.
2. 별도의 읽기-쓰기 트랜잭션을 사용하여 필요한 SingerId 값의 행을 업데이트합니다.

잠금 경합을 줄이도록 권장사항 적용

예시 시나리오에서는 잠금 통계와 트랜잭션 통계를 사용하여 업데이트를 수행할 때 테이블의 기본 키를 사용하지 않는 트랜잭션으로 문제를 좁힐 수 있었습니다. 사전에 업데이트할 행의 키를 알고 있는지 여부를 기준으로 트랜잭션을 개선하기 위한 아이디어를 제시했습니다.

솔루션의 잠재적인 문제를 살펴볼 때 또는 솔루션을 설계할 때에도 데이터베이스에서 잠금 충돌 수를 줄이도록 다음 권장사항을 고려하세요.

• 읽기-쓰기 트랜잭션 내에서 대량 읽기 방지

• 읽기 전용 트랜잭션은 잠금을 획득하지 않으므로 가능한 한 읽기 전용 트랜잭션을 사용합니다.

• 읽기-쓰기 트랜잭션에서 전체 테이블을 스캔하지 않습니다. 여기에는 기본 키에 조건부로 DML 쓰기나 Read API를 사용할 때 특정 키 범위 할당이 포함됩니다.

• 가능한 한 읽기-쓰기 트랜잭션에서 데이터를 읽은 직후에 변경사항을 커밋하여 잠금 기간을 짧게 유지합니다. 읽기-쓰기 트랜잭션은 데이터를 읽은 후 변경사항을 성공적으로 커밋할 때까지 데이터가 변경되지 않음을 보장합니다. 이를 위해 트랜잭션에서 읽는 도중과 커밋 중에 데이터 셀을 잠가야 합니다. 따라서 잠금 기간을 짧게 유지할 수 있으면 트랜잭션에서 잠금 충돌이 발생할 가능성이 줄어듭니다.

• 대규모 트랜잭션보다 작은 트랜잭션을 선호하거나, 장기 실행 DML 트랜잭션에 Partitioned DML을 고려합니다. 장기 실행 트랜잭션은 장시간 잠금을 획득하므로 행 수천 개를 포괄하는 트랜잭션을 작은 트랜잭션 여러 개로 나누어 가능하면 행 수백 개를 업데이트합니다.

• 읽기-쓰기 트랜잭션에서 제공하는 보장이 필요하지 않은 경우 변경사항을 커밋하기 전에 예를 들어 별도의 읽기 전용 트랜잭션에서 데이터를 읽어 읽기-쓰기 트랜잭션에서 데이터를 읽지 마세요. 대부분의 잠금 충돌은 읽기와 커밋 간에 데이터가 변경되지 않도록 보장하는 강력한 보장으로 인해 발생합니다. 따라서 읽기-쓰기 트랜잭션에서 데이터를 읽지 않으면 장시간 셀을 잠글 필요가 없습니다.

• 읽기-쓰기 트랜잭션에 필요한 열 집합만 최소한으로 지정합니다. Spanner 잠금은 데이터 셀별로 적용되므로 읽기-쓰기 트랜잭션이 과도한 열을 읽으면 해당 셀에서 ReaderShared 잠금이 획득됩니다. 이로 인해 다른 트랜잭션이 과도한 열에 대한 쓰기에서 WriterShared 잠금을 획득하면 잠금 충돌이 발생할 수 있습니다. 예를 들어 읽을 때 * 대신 열 집합을 지정하는 것이 좋습니다.

• 읽기-쓰기 트랜잭션에서 API 호출을 최소화합니다. API 호출에 네트워크 지연 및 서비스 측 지연이 발생할 수 있으므로 API 호출의 지연 시간으로 인해 Spanner에서 잠금 경합이 발생할 수 있습니다. 가능하면 읽기-쓰기 트랜잭션 외부에서 API를 호출하는 것이 좋습니다. 읽기-쓰기 트랜잭션 내에서 API 호출을 실행해야 하는 경우에는 API 호출의 지연 시간을 모니터링하여 잠금 획득 기간에 미치는 영향을 최소화해야 합니다.

• 스키마 설계 권장사항을 따릅니다.

다음 단계

• 다른 점검 도구 알아보기
• Spanner가 데이터베이스 정보 스키마 테이블의 각 데이터베이스에 저장하는 다른 정보 알아보기
• Spanner 관련 SQL 권장사항에 대해 자세히 알아보기