Monthly Archives: October 2012

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

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 성능을 희생하는 만큼 무조건 다수의 행에 분산 관리하는 것은 좋지 않습니다.

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

감사합니다.

CentOS 6.x에서 MySQL 운영 시 반드시 확인해봐야 할 파라메터!

Overview

MySQL 내부에서는 최대 허용 가능한 Connection을 설정할 수 있습니다. 하지만 OS 파라메터의 제약으로 때로는 임계치만큼 Connection을 늘릴 수 없는 경우가 발생하기도 합니다. 게다가, 만약 OS가 업그레이드되면서 관련 Default Value 가 변경되었다면? 이유없는 장애가 발생할 수도 있는 것이죠.

오늘은 OS 파라메터 중 CentOS 버전 별 nproc 값에 의한 Max Connection 제한에 대해 포스팅하겠습니다.

Environment

1) CentOS 5.8

CentOS 5.x버전의 nproc(Max User Processes) 기본 값은 다음과 같습니다.

$ ulimit -a | grep processes
max user processes              (-u) 4095

2) CentOS 6.3

이에 반해 CentOS 6.x버전부터는 /etc/security/limit.conf에 nproc에 특별한 설정을 하지 않는 한 1,024를 기본값으로 가집니다.

$ ulimit -a | grep processes
max user processes              (-u) 1024

이 설정은 CentOS 6.x버전부터 사용자 로그인 시 하단 파일에서 1,024 값을 기본값으로 세팅하며, 시스템 리소스를 제한하고자 새로 추가된 설정입니다.

$ cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.
*          soft    nproc     1024

Connection Test

MySQL DB 재시작 직후 pstree  명령으로 확인을 하면 16개의 데몬이 떠있는 것으로 확인됩니다. 물론 top 혹은 ps 명령어로 확인 시에는 단일 프로세스로 확인됩니다.

$ pstree | grep mysql
     |-mysqld_safe---mysqld---16*[{mysqld}]

이 상태에서 단순 Connection만 늘리는 프로그램을 돌려봅니다. 임계치(2000개)만큼만 Connection을 생성하는 간단한 JAVA 프로그램을 로직이며, Connection Open 이후 별다른 Close 작업을 하지는 않습니다.

아래 로직을 CentOS5.8, CentOS6.3에서 nproc 기본 값과 일부 변경 이후 DB를 재시작하여 테스트를 진행합니다.

import java.sql.*;
import java.util.Random;
public class Test {
        public static void main(String[] argv) throws ClassNotFoundException, SQLException{
            // Set Connection Limit
            int connLimit = 2000;

            Connection[] conn = new Connection[connLimit];
            Class.forName("com.mysql.jdbc.Driver");

            // Get Connection
            for(int i = 0; i < connLimit; i++){
                System.out.println(i);
                conn[i] = DriverManager.getConnection("jdbc:mysql://10.0.0.101:3306/dbatest","dbatest","");
            }

            // To Keep Java Program - no exit
            Statement stmt = conn[0].createStatement();
            for(int i = 0;;i++){
                stmt.execute("select sleep(5)");
                System.out.println(i+"th!!");
            }
        }
}

Test Result – CentOS 5.8

1) Default (nproc = 4095)

2,000개 신규 Connection생성에는 문제가 없었습니다.

mysql> select count(*)
    -> from information_schema.processlist;
+----------+
| count(*) |
+----------+
|     2001 |
+----------+
1 row in set (0.01 sec)

2) nproc 변경 (nproc = 200)

그러나 다음과 같이 nproc 값을 200으로 설정 후 동일한 테스트를 진행합니다.

## change max user processes limit
$ ulimit -u 200

## DB restart
$ /etc/init.d/mysqld restart

신규 Connection을 183개 생성 후 Java 콘솔에서 다음 에러와 함께 프로그램이 종료됩니다.

Exception in thread "main" java.sql.SQLException: null,  message from server: "Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug"

위 상태에서 Linux 콘솔에서 신규 Connection 생성 시에도 다음과 같은 에러가 발생합니다. 하단의 경우 TCP/IP가 아닌 Socket을 통한 접근입니다.

$ /usr/local/mysql/bin/mysql -uroot
ERROR 1135 (HY000): Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

pstree로 확인한 mysqld 프로세스 개수입니다.

pstree | grep mysql
     |-mysqld_safe---mysqld---199*[{mysqld}]

Test Result – CentOS 6.3

1) Default (nproc = 1024)

앞선 테스트와 동일하게 Java 프로그램을 실행하였을 때 2,000개의 신규 세션을 맺을것으로 기대되나, 1007개 세션 생성 이후 다음과 같은 에러가 발생합니다.

Exception in thread "main" java.sql.SQLException: null,  message from server: "Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug"

2) nproc 변경 (nproc = 4095)

nproc를 4095개로 설정 및 DB재시작 후 앞선 테스트를 동일하게 수행합니다.

## change max user processes limit
$ ulimit -u 4095

## DB restart
$ /etc/init.d/mysqld restart

결과적으로 2000개의 세션을 생성하는데 문제가 전혀 없습니다.

mysql> select count(*) 
    -> from information_schema.processlist;
+----------+
| count(*) |
+----------+
|     2001 |
+----------+
1 row in set (0.01 sec)

Solution

가장 간단한 해결 방안은 limit.conf 파일에 nproc 값을 넣는 방법입니다.

$ vi /etc/security/limits.conf
## 하단 라인 추가
*          -    nproc     4095

MySQL 재시작 후 위 파라메터가 제대로 적용이 됐는 지 확인합니다. Max Processes값이 여전히 1024라면 콘솔에 다시 접속하여 MySQL을 재시작합니다.

$ cat /proc/<mysql_pid>/limits
Limit                     Soft Limit           Hard Limit
Max cpu time              unlimited            unlimited 
Max file size             unlimited            unlimited 
Max data size             unlimited            unlimited 
Max stack size            10485760             unlimited 
Max core file size        0                    unlimited 
Max resident set          unlimited            unlimited 
Max processes             4095                 4095      
Max open files            50000                50000     
Max locked memory         32768                32768     
Max address space         unlimited            unlimited 
Max file locks            unlimited            unlimited 
Max pending signals       4095                 4095      
Max msgqueue size         819200               819200    
Max nice priority         0                    0         
Max realtime priority     0                    0

Max processes 값이 4095로 상향 조정된 것을 확인할 수 있습니다.

어디에 관련 값을 명시하는 것이 좋을 지는 시스템에 맞게 설정하시면 되겠어요. ^^ 특히나 여러 서버가 동시에 올라오는 공유 서버라면, nproc 파라메터 변경 하나로 전체 프로세스에 영향을 미치며 서버 리소스를 크게 잡을 수도 있기 때문이죠. ㅎ

Conclusion

Active Session이 1,000개인 상태라면 분명 DB에 상당한 부하가 발생합니다. 예전 벤치마킹에서 Active Session이 50개 이상부터는 QPS가 더이상 증가하지도 않는 결과도 나왔습니다.

하지만 실 사용환경에서는 Active Session이 50개 미만이나, Connection 수는 1,000개 이상 존재하는 경우는 다분합니다. 단순히 Connection만 맺고 특별한 SQL을 실행하지 않으므로 Sleep 상태로 머물러 있는 상태이죠.

이 경우 DB내부 Connection 제한이 아닌 OS Process Limit 개수 영향으로 예기치 않는 문제가 발생할 수 있습니다. 분명 MySQL의 세션은 쓰레드임에도 불구하고, OS에서는 Process로 인식하는 현상은 참으로 놀라운 일이네요. ^^;; 왜그럴지..ㅎㅎ

특히, CentOS 5.x버전에서 CentOS 6.x버전으로 OS를 업그레이드하였다면, 관련 서버 설정을 점검하여 잠재적인 장애 이슈를 사전에 제거할 필요가 있습니다.

MySQL InnoDB에서 데이터 1건 변경 시에도 테이블 잠금 현상이 발생할 수 있다!!

Overview

MySQL 트랜잭션 Isolation Level로 인한 장애 사전 예방 법” 포스팅에서 관련 주제를 다룬 적이 있습니다. InnoDB에서 Create Table .. As Select .. 과 같이 사용하는 경우 테이블 잠금이 발생할 수 있는 상황과 회피할 수 있는 팁이었죠.

테이블 Full-Scan 구문이 실행 시 발생할 수 있는 문제에 관한 것입니다. 하지만 때로는 변경 대상이 1 건이라도 쿼리 타입에 따라 테이블 잠금 같은 현항이 발생할 수도 있습니다.

이번 포스팅에서는 이에 관해 정리해보도록 하겠습니다. ^^

Symptoms

다음과 같이 “NOT IN” 과 같이 부정적인 의미의 조건 구문을 사용 시 테이블 잠금 현상이 발생할 수 있습니다. NOT IN 결과 값이 1건일지로도요..

update test set col1 = 0
where col1 not in (0, 1);

이 경우 Read Committed 로 트랜잭션 Isolation Level을 설정해도 인덱스 칼럼 유무에 따라 테이블 잠금 현상이 발생하기도 하죠. 물론 Repeatable Read보다는 경우의 수가 크지는 않습니다.

현재 트랜잭션 Isolation Level은 다음과 같이 확인 할 수 있습니다.

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

Prepare Test

1) 테스트 환경 구성

## 테이블 생성
create table test(
  i int unsigned not null auto_increment,
  j int unsigned not null,
  k int unsigned not null,
  l int unsigned not null,
  primary key(i)
) engine = innodb;

## 타겟 칼럼과 관계 없는 인덱스 생성
alter table test add key(l);

타겟 칼럼에 인덱스를 추가하는 경우 다음과 같이 수행합니다.

## 타겟 칼럼에 인덱스 생성
alter table test add key(j);

2) 데이터 생성

빠른 데이터 생성을 위해 아래와 같이 Insert Into .. Select.. 구문을 사용합니다.

## 데이터 1 건 생성
insert into test (j, k, l)
values
(
    crc32(rand()) mod 2,
    crc32(rand()) mod 2,
    crc32(rand()) mod 2
);

## 하단 쿼리 17건 실행
insert into test (j, k, l)
select
    crc32(rand()) mod 2,
    crc32(rand()) mod 2,
    crc32(rand()) mod 2
from test;

3) 타겟 데이터 설정

타겟 데이터와 무관한 데이터를 구분하기 위해 i 값을 10000 이상인 데이터에 한에서 랜덤하게 업데이트 수행합니다.

## 타겟 칼럼1
update test set j = 99
where i > 10000
order by rand()
limit 1000;

## 비 타겟 칼럼1 - 인덱스 없음
update test set k = 99
where i > 10000
order by rand()
limit 1000;

## 비 타겟 칼럼2 - 인덱스 있음
update test set l = 99
where i > 10000
order by rand()
limit 1000;

Test Scenario

테스트는 Transaction Isolation Level(Repeatable Read, Read Committed), 인덱스 칼럼 여부, 데이터 칼럼 접근 방식(PK, Index, Full-Scan) 그리고 쿼리 타입(“in”, “not in”)을 구분하여 각각 테스트하였습니다.

원활한 테스트를 위해 모든 세션에 자동 커밋 DISABLE 설정합니다.

SET SESSION AUTOCOMMIT = 0;

Transaction Isolation 레벨은 테스트 상황에 맞게 다음과 같이 설정합니다.

## REPEATABLE READ 인 경우 테스트 환경
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

## READ COMMITTED 인 경우 테스트 환경
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

세션1

변경 대상 데이터는 i 값이 10,000보다 큰 행입니다. 하단 결과 테이블에 있는 쿼리를 질의문으로 수행한 상태에서 세션2 쿼리를 실행합니다.

세션2

i 값이 10,000 이하인 데이터 타겟 데이터과 관계없는 데이터를 업데이트 수행합니다.

update test set j = 1 where i = 1;

Test Result

테스트 결과를 캡쳐해서 알아보기는 힘들지만, 다양한 케이스를 테스트하여 정리합니다. ^^

1) “NOT IN” 테스트

  • Repeatable Read : Not In  사용 시 모든 경우 Lock 발생
  • Read Committed : 인덱스(PK 포함)를 사용하여 접근 시 Lock 발생

not in result

2) “IN” 테스트

  • Repeatable Read : 풀스캔 시 Lock 발생, 인덱스(PK포함) 접근 시 Lock 발생 안함
  • Read Committed : 풀스캔, 인덱스 스캔 모두 Lock 발생 안함

in test result

Solutions

1) Transaction Isolation Level 변경 & IN 구문 사용

Transaction Isolation Level을 Read Committed로 변경하고, IN 구문을 사용하도록 합니다. Not In 사용이 반드시 필요하다면 Limit 구문을 통해 한번에 많은 데이터 변경 작업이 일어나지 않도록 유도합니다.

2) Select 후 결과 값으로 데이터 접근

NOT IN 구문이 필요하다면, Select 구문으로 따로 구분하고 그 결과 값으로 데이터를 다시 업데이트합니다.  MySQL의 Repeatable Read에서는 스냅샷 형태로 Select 데이터 일관성을 보장하며, 데이터 업데이트와 무관한 Select 결과 값에는 다른 세션에서 데이터 변경 작업이 가능합니다.

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
                   "select i from test where j not in (0, 1)"
               );
int i = 0;
String inStr = "";
while(rs.next()){
    inStr += ((i++) > 0 ? "," : "") + rs.getInt(1);
}
rs.close();
stmt.executeUpdate(
    "update i set j = 0 where i in (" + inStr + ")"
);

Conclusion

복잡한 위의 결과를 간단하게 정리하면 다음과 같습니다.

  1. 트랜잭션 Isolation Level이 Read Committed더라도 NOT IN 과 같은 부정적인 조건 질의 시 인덱스 칼럼으로 데이터에 접근하는 상황에서는 Lock이 발생합니다.
  2. 트랜잭션 Isolation Level이 Read Committed인 상태에서 IN 구문과 같은 긍정적인 조건 질의 시에는 연관없는 데이터에는 Lock을 걸지 않습니다.
  3. 트랜잭션 Isolation Level이 Repeatable Read인 경우 NOT IN 사용 시 무조건 테이블 Lock 현상이 발생합니다.
  4. 트랜잭션 Isolation Level이 Repeatable Read인 상태에서 IN 구문을 사용하는 경우에는  인덱스 칼럼으로 접근 시에는 Lock 현상이 발생하지 않습니다.

가장 안정적으로 Lock을 피하기 위해서는 트랜잭션 Isolation Level을 Read Committed로 설정하고, 긍적적인 조건만 주는 경우입니다.

물론 인덱스가 있는 상황이라면, 부정적인 조건 사용 시에도 데이터 접근이 빠르므로, 변경 대상 데이터가 많거나 트랜잭션이 오래 유지되지 않는 한 큰 문제는 없습니다. ^^

단, Read Committed로 변경 시 Binary Log 포멧이 자동으로 ROW 형태로 변경되기 때문에, Binary Log 파일이 급격하게 늘어날 수 있는 경우가 발생할 수 있으니, 내부 검토가 반드시 필요합니다.

사용할수록 나날이 새로운 사실이 발견되는 MySQL이네요.

감사합니다.