Tag: Lock

MySQL의 User Level Lock를 활용한다면?

Posted on | by gywndi

Overview

DB에는 크게는 두 가지 타입의 Lock이 있습니다. Table Level Lock, Row Level Lock.. 두 가지 타입의 Lock은 RDBMS에서 대표적인 Lock이라고 지칭할 수 있습니다.

Table Level Lock은 데이터 변경 시 테이블 자체를 Lock을 걸어 안전하게 데이터를 변경하는 방식이고, Row Level Lock은 변경되는 칼럼의 Row에만 Lock을 걸어서 데이터를 조작하는 방식입니다. 일반적인 상황에서는 두 가지의 Lock만으로도 충분히 다양한 사용자의 요구사항을 충족할 수가 있습니다.

그러나, 테이블 파티셔닝을 하는 경우나, 혹은 다양한 서버에 데이터가 분산 저장되는 경우 DB 내적인 제약사항 혹은 데이터 공간 자체의 한계로 인해 상황에 따라 더욱 확장된 Lock이 필요한 경우가 있습니다.

MySQL에서는 User Level Lock 기능을 제공하는데, 오늘은 이것에 관련된 내용을 정리해보도록 합니다.

Why User Level Lock?

User Level Lock에 대해 언급하기에 앞서서 조금 전 언급했던 파티셔닝 시 제약 사항에 대해서 간단하게 짚고 넘어가도록 하죠. ^^

MySQL에서 테이블을 파티셔닝 하게 되면, 단일 테이블로 보여지지만 내부적으로는 수 개의 테이블로 쪼개져서 별도의 테이블로 관리가 됩니다. 즉, 특정 테이블을 10개로 파티셔닝을 하였다면, DB내적으로는 10 개의 테이블을 Merge한 형태로 관리하는 모습을 보여줍니다.

MySQL table partition files
MySQL table partition files

그런데 물리적인 저장소를 분산 저장하기 위해서는 가장 중요한 제약 사항이 있는데, 파티셔닝 키 안에 Primary Key 안에 포함이 되어야 한다는 것입니다. Primary Key가 일반적으로 물리적인 저장소의 주소 역할을 일반적으로 수행하기 때문에 당연한 현상일 수 있겠죠.

여기서, 가장 큰 제약 사항 하나!! 바로 Primary Key 외에 추가로 Unique 속성과 같은 제약 사항을 추가할 수 없다는 것입니다. Foreign Key 도 당연히 추가할 수 없습니다. 어찌 보면, 거대 테이블을 처리하기 위한 일부 기능적인 부분 포기(?)라고 볼 수도 있겠네요. ^^;;

그런 상황 속에서 User Level Lock을 잘~ 활용한다면 단순히 파티셔닝 제약 조건을 뛰어넘어 다수의 서버 환경에서도 적용할 수 있습니다.

User Level Lock?

서론이 너무 길었네요. 이제 User Level Lock에 대해서 정리해보도록 하겠습니다.

User Level Lock이란 사용자가 특정 “문자열”에 Lock을 걸 수 있는 Lock을 의미합니다. 그리고 User Levl Lock 관련 메쏘드는 아래와 같습니다.

  • GET_LOCK(str,timeout)
    문자열 str에 해당하는 Lock을 획득하는 메쏘드. Lock 획득 성공 시 1리턴, timeout 동안 Lock획득 못한 경우 0 리턴, 에러 발생 시 NULL 리턴
  • IS_FREE_LOCK(str)
    문자열 str을 사용할 수 있는 상태인지 체크
  • IS_USED_LOCK(str)
    문자열 str이 사용되고 있는 지 체크
  • RELEASE_LOCK(str)
    str에 걸려있는 Lock을 해제

단, 주의할 점은 User Level Lock은 Client Base가 아닌 Server Base로 동작한다는 점입니다. 당연한 이야기이겠지만, 다수의 클라이언트에서 User Level Lock을 사용하게 되면, 클라이언트가 아닌 서버 측에서 경합이 발생한다는 점입니다. ^^

문자열에 Lock을 걸 수 있는 이 기능을 활용한다면, 앞서 말씀드린 파티셔닝 제약 혹은 물리적인 제약 사항을 극복(?)할 수 있는 솔루션이 될 수 있습니다.

Partitioning Limitation?

테이블 파티셔닝이 반드시 필요한 상황에서 일정 기준으로 유니크 보장도 하고 싶은 경우가 있습니다. 예를 들어 1개월 간 세션 키를 발급하는 경우, 그 기간 동안에는 절대 세션 키가 중복되어서는 안됩니다. 그렇다고 모든 유저에서 발급되는 세션키를 데이터 정리 없이 매번 서버에 적재할 수도 없는 노릇이죠.

이러한 요구 사항 속에서 User Level Lock을 활용하여 제약을 극복해 봅시다.

세션 키를 발급하는 순서는 다음과 같습니다.

user level lock

테이블 스키마는 아래와 같이 간단하게 정의한다고 가정했을 때,

create table t_sessions(
  user_id int not null,
  s_key varchar(32) not null,
  create_at datetime not null,
  primary key(user_id, create_at),
  key ix_skey(s_key)
) engine=innodb 
partition by range columns(create_at)(
  partition p_201310 values less than ('2013-11-01'),
  partition p_201311 values less than ('2013-12-01'),
  partition p_201312 values less than ('2014-01-01')
)

각 단계 별로 SQL을 간단하게 작성해본다면 다음과 같습니다. 위에서 주목할 사항은 s_key에는 유니크 속성이 없음에도 s_key를 중복 체크를 할 수 있다는 점입니다.

##  1단계
select get_lock('session key', 1);

## 2단계
select 1 from t_sessions
where s_key = 'session key';

## 3단계 (30일 동안 중복 세션 키가 없는 경우)
insert into t_sessions values ('myid','session key',now());

## 4단계
select release_lock('session key');

꽤 많은 코드(?)들을 생략하기는 했지만.. 흐름만 알려드리기 위한 예시라.. 넓은 마음으로 이해해 주세요. ^^;

위 예시를 활용한다면, 파티셔닝 테이블에 Foreign Key 효과도 넣을 수 구현할 수 있겠네요. (이것은 멋진 상상력을 발휘해서 구현해보세요. ㅎ)

Conclusion

User Level Lock은 앞에서의 간단한 파티셔닝 테이블 뿐만 아니라, 전~혀 연관이 없는 테이블 사이의 데이터를 처리하는 데에도 활용할 수 있습니다. 게다가, 동일 서버가 아닌, 다수 서버에서 분산된 데이터를 같이 처리해야하는 경우에도 유용하게 사용할 수 있습니다.

User Level Lock은 사용자가 메쏘드를 통해 특정 문자열에 대하여 Lock을 획득하는 것으로, MySQL 테이블 간 제약 사항을 간단하게 극복할 수 있는 초석이 될 수 있습니다. 적절한 시점에 활용하여, 데이터 신뢰성 향상은 물론 개발 시 스트레스도 줄여보도록 해요. ^^

대용량 테이블에서 카운트 정보를 효과적으로 관리하자.

Posted on | by gywndi | Leave a Comment on 대용량 테이블에서 카운트 정보를 효과적으로 관리하자.

Overview

대부분 서비스에서는 데이터 카운트를 합니다. 커뮤니티에서는 사용자 아티클 수를, 결제 서비스에서는 남은 물폼 수를 관리하기 위해서 사용하죠. 그리고 트랜잭션이 중요한 서비스라면, 데이터 일관성 유지를 위해 카운트 시 매번 데이터를 다시 읽어옵니다.

데이터가 적으면 큰 문제가 되지 않겠지만, 데이터 지속적으로 누적됨에 따라 성능 또한 기하급수적으로 저하됩니다.

그렇다면 이러한 환경에서 어떻게 카운트 퍼포먼스를 향상할 수 있을까요? 오늘 포스팅할 내용은 MySQL뿐만 아니라 행 단위 잠금을 지원하는 환경도 포함합니다.

통계 테이블 사용

빠른 데이터 건 수를 가져오는 방법으로는 별도의 통계 테이블을 관리하는 것입니다. 다음과 같이 통계 테이블을 별도로 구성하여 카운트 정보를 관리합니다.

이렇게 되면, 굳이 덩치가 큰 테이블에서 일일이 카운트를 하지 않고, 통계 테이블에서 “한 건의 데이터”만 읽어오므로, 카운트 성능을 크게 향상할 수 있죠.

Count Stat Table

통계 테이블을 운용하는 방법에는 주기적으로 통계 데이터를 업데이트하거나 동일 트랜잭션으로 묶어서 관리하는 방법이 있습니다.

카운트 정보가 서비스에 큰 영향을 미치지 않는다면, 주기적인 업데이트하여 관리할 수 있습니다.

하지만 결제 혹은 선착순 로직처럼 데이터 일관성이 중요하다면, 반드시 실시간으로 데이터 처리가 이루어져야 하죠. 이 경우 동일 트랜잭션으로 묶어서 관리해야 하며, InnoDB에서는 행 단위 잠금으로 동작하므로 트랜잭션이 몰리더라도 데이터 변경 동시성을 어느정도 보장합니다.

하지만 특정 통계 데이터를 동시다발적으로 변경하는 이슈가 발생한다면? 게다가 단위 트랜잭션 성능이 생각보다 좋지 않다면? 예를 들어 대학 수강 신청에서 특정 인기 강좌에 수강 신청이 갑자기 몰리는 경우를 생각할 수 있습니다.

이 경우 DB는 내부 Lock 메커니즘에 따라 물리적 리소스를 전혀 사용하지 않은 채 대기 상태에만 머물어서 전체 서비스에 치명적인 영향을 미칩니다.

그렇다면 이러한 특정 데이터 잠금을 어떻게 회피할 수 있을까요?

단위 트랜잭션 성능을 높여라!

트랜잭션이 중요한 실시간 환경에서 행단위 잠금을 회피하는 첫 번째 단추는 단위 트랜잭션 내 Commit을 가능한 최대한 빠르게 수행하는 것입니다. (당연한 이야기겠지만..) 예를 들어 다음 트랜잭션 상황에서 “후행처리 SELECT”가 데이터 일관성과 큰 연관이 없다면 트랜잭션 외부로 빼내서 시간을 단축할 수 있겠죠.

Transaction

단위 트랜잭션 평균 속도가 0.2초를 0.02초로 줄일 수 있다면, 경합을 최단 기간에 마무리 할 수 있습니다. ^^

통계 데이터를 여러 행에 분산 관리하라!

트랜잭션을 아무리 짧게 유도해도, 해당 트랜잭션이 처리하는 동안에는 그 어떤 트랜잭션도 데이터 변경이 불가합니다. 여전히 한 가지 행에 “순차적으로 Access해서 변경 처리”를 해야 한다는 점에서, 내부적으로 상당한 리소스 비효율이 발생합니다.

그렇다면 다음 그림과 같이 변경 대상이 되는 행을 여러 개로 만들어서 분산 관리하면 어떨까요?

Parallel Stat Data

물론 통계 정보 분산 관리를 위해 오른쪽 그림처럼 추가 칼럼이 더 필요합니다. 이 경우 통계 데이터는 Group By 구문을 사용하여 다음과 같이 가져올 수 있습니다. (하단 테스트 스키마 참고)

단, 반드시 Transaction Isolation Level을 READ-COMMITTED로 설정하여 SELECT 해야 잠금 현상이 발생하지 않습니다. ^^ (물론 카운트 정보가 트랜잭션에 직접적인 요소만 아니라면, 상관없습니다.)

select sum(j) j from test where i = 2

여기서 한가지 중요한 것은  Group By 구문을 사용하는 만큼 무조건 많은 행에 분산하는 것을 올바른 방법이 아닙니다. 동시성을 높이기 위해 SELECT 성능을 약간은 희생하므로 적절한 Trade-Off는 스스로 판단하여.. ^^;;

그렇다면 행 단위 잠금을 위와 같이 분산 관리하였을 때 얼마나 데이터 처리 효율이 좋아지는 지 간단한 테스트로 확인해보겠습니다.

성능 테스트

하나의 행에 몰리는 트래픽을 여러 개의 행으로 나누어서 처리했을 때 처리 변화량을 비교하겠습니다. 위와 같은 환경에서 20개의 세션에서 통계 데이터를 단일 행, 여러 행 경우로 나누어서 테스트 수행합니다.

테스트 환경 구성

## 테이블 생성
CREATE TABLE test (
  i int(11) NOT NULL,
  i2 int(11) NOT NULL,
  j int(11) NOT NULL,
  PRIMARY KEY (i,i2)
) ENGINE=InnoDB;

## 테스트 데이터 생성
INSERT INTO test VALUES
 (1, 1, 0), (1, 2, 0), (1, 3, 0), (1, 4, 0), (1, 5, 0), 
 (1, 6, 0), (1, 7, 0), (1, 8, 0), (1, 9, 0), (1, 10, 0);

업데이트 되는 행을 다음과 같이 제한하여, 분산 환경을 구성합니다. (명시적으로 보이기 위해서 MySQL rand() 함수를 사용했지만, 실제 트래픽 어플에서 랜덤 값을 구현하세요. 버그가 있는듯..^^)

단, Binary Log를 Statement로 기록하는 경우에는 아래처럼 CRC32()를 사용하지 마세요. 특히 리플리케이션 환경이라면, 마스터 슬레이브 간 데이터가 달라집니다.

## 단일 행
update test set j = j + 1
where i = 1 and i2 = 1;

## 5개 행
update test set j = j + 1
where i = 1 and i2 = cast(rand()*5 as unsigned) + 1

## 10개 행
update test set j = j + 1
where i = 1 and i2 = cast(rand()*10 as unsigned) + 1

성능 테스트 결과

트랜잭션 단위 수행 속도가 0.2초인 경우입니다.

Result_0.2

여러 행에 데이터를 분산 업데이트 유도할수록 거의 비례하게 DB본연의 처리 속도가 나옵니다. 그렇다면 불필요한 SELECT를 제거하여 단위 트랜잭션 속도를 0.02초로 줄였다면 어떨까요?

Result_0.02

당연한 이야기이겠지만, 앞선 결과 대비 약 10배 이상의 퍼포먼스를 보이죠.^^ Lock 발생이 줄어드는 만큼 DB리소스를 많이 사용하지만, 그만큼 DBMS효율성이 증대하는 것을 의미하죠.

Conclusion

데이터 누적에 따른 카운트 성능을 별도의 통계 데이터를 관리하여 성능 향상을 유도할 수 있습니다. 하지만, 특정 통계 데이터 동시 변경 요청 발생 시 Lock이 발생할 수 있습니다. 이것은 행단위 잠금과는 별개의 Lock 메커니즘에 따른 결과이죠.

이러한 Lock 현상을 다음 두 가지 방법을 최대한 회피할 수 있습니다.

  1. 단위 트랜잭션 속도 증대
  2. 통계 데이터를 여러 행에 분산 관리

그렇다고 데이터 처리 동시성을 높이기 위해 약간의 SELECT 성능을 희생하는 만큼 무조건 다수의 행에 분산 관리하는 것은 좋지 않습니다.

트랜잭션이 중요한 서비스에서 적용해볼만한 머리 속에만 있던 간단한 팁을 공유합니다.

감사합니다.

MySQL Table Lock에 관한 이해

Posted on | by gywndi | 2 Comments on MySQL Table Lock에 관한 이해

Overview

Table Lock 스토리지 엔진 사용 시 반드시 알아야할 사항을 정리 드리겠습니다.

근래에는 물론 InnoDB가 아~주 많이 사용되고 있겠지만, 여전히 서비스에서는 MyISAM이 쓰이고 있습니다. MyISAM은 MySQL의 대표적인 스토리지 엔진이면서 내부적으로는 Table Lock으로 동작합니다.

관련 스토리지 엔진에 관한 설명은 MySQL특성을 정리한 반드시 알아야할 MySQL 특징 세 가지 포스팅을  참고하시면, 간단한 비교를 하실 수 있습니다. 자 그럼 Table Lock 스토리지 엔진 사용 시 반드시 알아야할 사항을 정리 드리겠습니다.

Table Lock 이해

MySQL에서 Table Lock은 다음 기준에 의해서 부여됩니다.

Write Lock
아무런 Lock이 없으면, 해당 테이블에 Write Lock을 걸어서 데이터 읽기 또는 변경 작업을 수행하지 못하게 합니다. 만약 Read 혹은 Write Lock이 존재하면 Write Lock 큐에 Lock을 넣어서 해당 Lock이 풀릴 때까지 대기합니다.

Read Lock
아무런 Write Lock이 없으면, Read Lock을 걸어서, 데이터 변경 작업을 수행하지 못하도록 합니다. 만약 Write Lock이 있으면 Read Lock 큐에 Lock을 넣고 데이터 변경 작업이 종료될 때까지 대기합니다.

기본적으로 Write Lock이 Read Lock보다 우선 순위가 높지만,
다음과 같이 LOW_PRIORITY 로 변경 가능합니다.
Example)
mysql> INSERT INTO LOW_PRIORITY table_name…
mysql> DELETE LOW_PRIORITY FROM table_name…
mysql> UPDATE LOW_PRIORITY table_name SET…

참고) Internal Locking Methods

위를 다시 간단하게 정리하자면, Write Lock 상태에서는 다른 세션이 해당 테이블 접근이 불가한 상태이고, Read Lock 상태는 다른 세션이 데이터를 Read까지만 가능하다고 볼 수 있습니다.

그러나! 만약에 Write 또는 Read 수행이 오래 걸리는 경우는 어떨까요? Read Lock은 다른 Read 세션에 영향을 미치지 않을 것으로 보이지만, 때로는 Dead Lock을 유발하는 요소가 될 수도 있습니다.

Example

다음과 같은 경우를 예를 들어보겠습니다. 테이블은 MyISAM 엔진입니다.

Session 1

## 수행 시간이 오래 걸리는 조회 쿼리 발생
mysql> SELECT * FROM tab01 WHERE  sleep(1000);

이 경우 다른 세션에서도 tab01 테이블에서 얼마든지 데이터 조회가 가능합니다.

Session 2

## Read Lock 상태 테이블에 데이터 변경
mysql> UPDATE tab01 SET c1 = '' WHERE i = 4;

Update 쿼리는 Session1의 Select 쿼리가 종료될 때까지 대기합니다.

Session 3

## Read Lock 상태이고, Write Lock이 대기 상태에서 Select 수행
mysql> SELECT * FROM tab01 LIMIT 10;

Session 2 의 Write Lock에 의해 Read 불가한 상태로 빠집니다.

프로세스 현황을 확인해보면 아래와 같습니다.
Table Lock Process List

정상적이라면 Select 수행되는 동안 다른 세션에서도 Select가 수행되어야 하는데, 쿼리 우선 순위에 의해서 Select 세션이 Lock 상태로 빠진 것을 확인할 수 있습니다.

Conclusion

MySQL Replication 사용 시 Slave 서버에서 Dead Lock은 위와 같은 상황에서 얼마든지 발생할 수 있기 때문에, 반드시 알고 있어야 합니다. (통계성 SQL이 실행되는 테이블에 Update 발생 시 다른 세션에서는 해당 테이블 데이터 조회 불가)

MyISAM 스토리지 엔진이 트랜잭션이 많은 경우 부적합한 가장 큰 이유입니다.^^