MySQL에서 Temporary Table을 활용한 데이터 질의..그 효과는?

Posted on 2012-06-15 | by gywndi | 3 Comments

Overview

오늘은 Temporary Table에 관해 포스팅을 하겠습니다. Select및 Update 등을 이따금씩 Temporary Table을 활용하여 수행하는 경우가 있습니다. 동시에 많은 데이터를 일괄 변경하는 것에서는 분명 강점이 있을 것이라 판단되는데, 어떤 상황에서 적절하게 사용하는 것이 좋을까요? 관련 성능 벤치마크 결과를 공개하겠습니다.

Environment

테이블에는 약 1000만 건 데이터가 존재하며, Primary Key외에는 추가 인덱스는 생성하지 않았습니다. 서로 동등하게 빠른 데이터 접근이 가능하다는 가정 하에 PK외 인덱스에서 발생할 수 있는 성능 저하 요소를 배제하기 위해서 입니다.^^

## DDL for dbatest
CREATE TABLE dbatest (
  i int(11) NOT NULL AUTO_INCREMENT,
  c1 int(11) NOT NULL,
  c2 int(11) NOT NULL,
  c3 varchar(255) DEFAULT NULL,
  PRIMARY KEY (i),
) ENGINE=InnoDB;

## Table Infomation
+-------------+----------+-------+-------+------------+
| TABLE_NAME  | ROWS     | DATA  | IDX   | TOTAL_SIZE |
+-------------+----------+-------+-------+------------+
| dba.dbatest | 10000105 | 1283M | 0.00M | 1283.00M   |
+-------------+----------+-------+-------+------------+

성능 테스트 시 Temporary Table은 아래와 패턴(tmp_dbatest_세션번호)으로 DB 세션 단위로 정의하여 성능을 측정하였습니다. 물론 Temporary Table이 필요한 부분에서만 사용 되겠죠.^^

Memory Storage 엔진은 테이블 Lock으로 동작하지만, 자신의 Temporary Table은 자신의 세션에서만 사용하기 때문에 동시에 여러 세션에서 읽히게 되는 그런 경우는 없다고 봐도 무관합니다.

## DDL for Temporary Table
CREATE TEMPORARY TABLE tmp_dbatest_12(
  i int not null,
  primary key(i)
) engine = memory;

대상 테이블에는 앞에서 언급한 것과 같이 약 1,000만 건 데이터가 들어있으며, Primary Key는 1부터 순차적으으로 정의도어 있습니다.

트래픽은 Java Thread를 여러 개 발생하여 마치 실 서비스 상태와 유사한 환경을 조성하였으며, 5개 세션(쓰레드)부터 단계적으로 200개까지 세션 수를 늘려서 초당 트랜잭션 수(TPS)를 측정하였습니다.

디스크 지연으로 인한 영향을 최소화하기 위해서 메모리는 충분히 할당하였고, 데이터 생성 후에는 DB Restart를 배제함으로써 모든 데이터를 메모리에 있다고 가정하였습니다. (테스트 시 리소스 현황을 확인하면서 Disk I/O로 인한 Bottleneck이 없음을 어느정도 확신하습니다.)

테스트는 기본적으로 하나의 트랜잭션에서 20건의 데이터 Select 또는 Update를 얼마나 효율적으로 질의할 수 있느냐에 초점을 두었습니다.

자! 이제 성능 테스트 결과를 공개합니다.

Benchmark Result : Select

Temporary Table 사용 유무를 나누어서 테스트를 진행하였습니다.

Temporary Table

Drop Temporary Table
Create Temporary Table
1부터 10,000,000 범위 숫자 20개 무작위 추출
다음과 같이 Temporary Table과 Join하여 데이터 Select

SELECT a.i, a.c1, a.c2
FROM dbatest a
INNER JOIN tmp_dbatest_12 b ON a.i = b.i

None Temporary Table

1부터 10,000,000 범위 숫자 20개 무작위 추출
다음과 같이 IN 구문을 사용하여 Select

SELECT a.i, a.c1, a.c2
FROM dbatest a
WHERE a.i in (1,2,3,4,..,17,18,19,20)

동시 접속 수가 많아질수록 Temporary Table 없이 사용하는 것이 성능이 월등히 좋았습니다.

위 케이스에서는 상당히 예상 가능한 결과로, Create/Drop/Insert/Select 등 네 가지 질의가 동일 트랜잭션에서 발생하기 때문 요인으로 볼 수 있겠죠. 하지만 만약 추후 기 저장된 데이터를 재활용한다면 상당히 다른 결과를 보여줄 수 있겠죠? (예를 들어 통계 중간 단계 혹은 자주 읽히는 데이터 임시 저장이라든가..)

IN 구문 성능이 1,000건 Select에서는 어느정도 영향이 있지 않을까라는 생각이 들어서 1,000 건 동시 데이터 질의 트래픽을 발생하여 측정하였습니다.

테스트 결과는 하단과 같으며, 여전히 Temporary Table 없이 사용하는 것이 성능이 더 좋았습니다. 앞선 결과와 차이가 크지 않은 것은 Select 쿼리 자체의 부하가 늘어났기 때문으로 파악할 수 있습니다.

Performance Test 1000 Rows Select Result

그리고 테스트 도중 “Query End” 상태로 약 20초 대기상태에 빠지는 경우가 발생하였습니다. 아마도 내부 메모리 경합으로 인한 문제가 아닐까 추측해봅니다.

Performance Test Wait

Benchmark Result : Update

앞선 테스트 방식과 동일하게 Update에서도 Temporary Table 사용 유무에 따라 구분하였고, 동시에 20 Row의 데이터 변경하는 것에 초점을 맞추었습니다.

Temporary Table

Drop Temporary Table
Create Temporary Table
1부터 10,000,000 범위 숫자 20개 무작위 추출
다음과 같이 Temporary Table과 Join하여 데이터 Update

UPDATE dbatest a
INNER JOIN tmp_dbatest_12 b ON a.i = b.i
SET a.c1 = a.c1 +10, a.c2 = a.c2 + 10000

None Temporary Table

1부터 10,000,000 범위 숫자 20개 무작위 추출
PreparedStatement를 사용하여 다음과 같은 쿼리 20번 수행

UPDATE dbatest SET c1 = c1 +10, c2 = c2 + 10000 WHERE i = ?

Update에서는 상당한 효과를 보입니다. Temporary Table없이 단순 건 단위로 Update 수행하는 것보다 확실히 효율이 좋습니다. 데이터 처리 시 Permission Check -> Open Table -> Process -> Close Table 등과 같이 일련의 작업이 필요한데 이러한 것을 한방 쿼리로 퉁(?) 친 결과가 아닐까 생각해 봅니다. 하지만 앞서서 발생했던 처리 지연 원인이 확실하지 않은 시점에서 Update 시 무조건 좋다고 볼 수는 없겠네요.

OLTP 환경에서 안정성 검토가 이루어져야 할 것이고, 10건 이상 동시 데이터 변경 작업에서는 성능 향상 효과를 상당히 얻을 수 있겠습니다.^^

Conclusion

위 결과를 정리하자면 다음과 같습니다.

Select 시에 Temporary Table을 사용하는 것은 성능 상으로는 전혀 도움이 되지 않습니다. 빈도있는 테이블 생성/삭제, 데이터 Insert및 Join Select 등 불필요한 단계 때문이죠. 하지만 중간 결과를 저장하거나, 추후 빈도있는 재사용을 위한 목적이라면 큰 효과를 볼 수 있을 것 같습니다.

그리고 위 Update 그래프 결과를 참고할 때 10건 이상 동시 데이터 업데이트 처리 시에는 분명 효율성이 상승하는 효과는 분명히 있습니다. 서비스 로직에 따라서 동시에 수많은 데이터를 업데이트 처리하는 경우에는 큰 효과를 걷을 수 있겠습니다.

하지만, 앞서서 발생했던 처리 지연을 염두해야 하며, 가능한한 Drop 및 Create 구문을 발생하지 않고 테이블을 재사용하는 방안이 조금은 더 합리적일 것 같습니다.

MySQL 트랜잭션 Isolation Level로 인한 장애 사전 예방 법

Posted on 2012-05-16 | by gywndi | 5 Comments

Overview

MySQL에서 전체 데이터를 Scan 하는 쿼리를 질의하여 서비스에 큰 영향이 발생할 수 있습니다.

InnoDB 스토리지 엔진의 기본 Isolation Level이 REPEATABLE-READ이기 때문에 발생하는 현상인데, 이것은 세션 변수 일부를 변경하여 문제를 사전에 해결할 수 있습니다.

얼마 전 이와 비슷한 장애가 발생하여 원인 분석 및 해결 방안을 포스팅합니다.

Symptoms

Transaction Isolation Level이 REPEATABLE-READ(MySQL Default) 상태에서 Insert into Select 혹은 Create Table As Select 로 전체 테이블 참조 쿼리 실행 시 참조 테이블에 데이터 변경 작업이 “대기” 상태에 빠지는 현상이 있습니다.

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

Insert Into Select

세션1

mysql> insert into activity_test_stat2
    -> select
    ->     act_type,
    ->     to_uid,
    ->     act_time,
    ->     to_user_name,
    ->     before_user_name,
    ->     count(*) cnt
    -> from activity_test
    -> group by act_type, to_uid, act_time,
    ->     to_user_name, before_user_name;

세션2 – 테이블에 데이터 변경

mysql> update activity_test set ACT_TYPE = 105 limit 10;

세션3 – update SQL는 “Updating” 상태

mysql> show processlist\G
************************* 1. row *************************
     Id: 255867
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 1
  State: Updating
   Info: update activity_test set ACT_TYPE = 105 limit 10
************************* 2. row *************************
     Id: 255962
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 2
  State: Copying to tmp table
   Info: insert into activity_test_stat2 select act_type,

Delete 작업 시 Update와 같이 대기 현상 또는 Dead Lock 오류 발생합니다.

mysql> delete from activity_test limit 10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Create Table As Select

세션1

mysql> create table activity_test_stat as
    -> select
    ->     act_type,
    ->     to_uid,
    ->     act_time,
    ->     to_user_name,
    ->     before_user_name,
    ->     count(*) cnt
    -> from activity_test
    -> group by act_type, to_uid, act_time,
    ->     to_user_name, before_user_name;

세션2 – 테이블에 데이터 변경

mysql> update activity_test set ACT_TYPE = 105 limit 10;

세션3 – update SQL는 “Updating” 상태

mysql> show processlist\G
************************* 1. row *************************
     Id: 255867
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 2
  State: Updating
   Info: update activity_test set ACT_TYPE = 105 limit 10
************************* 2. row *************************
     Id: 255962
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 4
  State: Copying to tmp table
   Info: create table activity_test_stat as select act_type,

Delete 작업 시 Update와 같이 대기 현상 또는 Dead Lock 오류 발생합니다.

mysql> delete from activity_test limit 10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Cause

MySQL InnoDB 스토리지 엔진의 기본 Isolation Level이 REPEATABLE-READ로 설정되어 있기 때문에 발생합니다.

REPEATABLE-READ에서는 현재 Select 버전을 보장하기 위해 Snapshot을 이용하는데, 이 경우 해당 데이터에 관해서 암묵적으로 Lock과 비슷한 효과가 나타납니다.

즉, Select 작업이 종료될 때까지 해당 데이터 변경 작업이 불가합니다.

Transaction Isolation Level

READ UNCOMMITTED
다른 트랜잭션이 Commit 전 상태를 볼 수 있음
Binary Log가 자동으로 Row Based로 기록됨 (Statement설정 불가, Mixed 설정 시 자동 변환)
READ-COMMITTED
Commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 특정 타 트랜잭션이 Commit을 수행하면 해당 데이터를 Read할 수 있음
Binary Log가 자동으로 Row Based로 기록됨 (Statement설정 불가, Mixed 설정 시 자동 변환)
REPEATABLE READ
MySQL InnoDB 스토리지 엔진의 Default Isolation Level
Select 시 현재 데이터 버전의 Snapshot을 만들고, 그 Snapshot으로부터 데이터를 조회
동일 트랜잭션 내에서 데이터 일관성을 보장하고 데이터를 다시 읽기 위해서는 트랜잭션을 다시 시작해야 함
SERIALIZABLE
가장 높은 Isolation Level로 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸림
다른 트랜잭션에서는 해당 영역에 관한 데이터 변경 뿐만 아니라 입력도 불가

Isolation Level에 관한 자세한 정보는 하단 MySQL 매뉴얼을 참조하세요.
http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

Solution

Insert into Select 경우 Isolation Level을 READ-COMMITED나 READ-UNCOMMITED로 변경하여 해결할 수 있습니다.
다음과 같이 세션 설정을 변경 후 Create Table As Select, Insert into Select를 수행하면 문제가 없습니다.

mysql> set tx_isolation = 'READ-COMMITTED';

설정 파일에 영구적으로 transaction isolation 변경 적용하고자 한다면 다음과 같이 설정 후 DB를 재시작 합니다.

$ vi /etc/my.cnf
## [mysqld] 설정에 추가
transaction-isolation           = READ-COMMITTED

비슷한 설정으로 다음과 같이 Isolation을 변경할 수 있습니다.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

모든 것은 Isolation Level이 REPEATABLE READ 이상인 경우 발생하오니, 주의하여 사용하시기 바랍니다.

(설정 자체가 READ COMMITTED에서는 발생하지 않음)

※ 주의사항 ※
커넥션 풀을 사용하는 경우 변경된 세션 값은 해당 커넥션이 재 시작되기 전까지 유지되므로, 반드시 사용 후 원래 설정 값으로 돌려놓아야 합니다.

Conclusion

조금은 어려운 주제일 수 있습니다. 그러나 유독 MySQL에만 국한되는 내용이 아닌, 트랜잭션을 지원하는 DB 사용 시 반드시 알아야할 사항이라고 생각됩니다.

그러나 MySQL 에만 국한되는 내용이 아닐 뿐더러, 대용량 분석 시스템을 구상 중이라면 반드시 알아야할 사항이라고 생각합니다.^^

MySQL 성능 죽이는 잘못된 쿼리 습관

Posted on 2012-05-11 | by gywndi | 4 Comments

Overview

안정적인 서비스 유지를 위해서는 쿼리 작성이 상당히 중요합니다. 잘못된 쿼리 하나가 전체적인 퍼포먼스를 크게 저해하기도 하고 최악의 경우 장애 상황까지 치닫기 때문이죠

단일 코어에서 Nested Loop Join으로 데이터를 처리하는 MySQL 특성 상 쿼리 구문에 큰 영향을 받습니다. (반드시 알아야할 MySQL 특징 세 가지 참고)

그래서 오늘은 쿼리 작성 시 기피해야 하는 사항 세 가지정도 골라봅니다.

Case 1

SELECT @RNUM:=@RNUM+1 AS RNUM, ROW.*
FROM (SELECT @RNUM:=0) R,
(
    SELECT
        M.MASTER_NO,
        M.TITLE,
        MI.PATH,
        M.REGDATE,
        CM.TYPE
    FROM MAIN AS M
    LEFT OUTER JOIN TAB01 AS MI
        ON M.MASTER_NO = MI.MASTER_NO
    INNER JOIN TAB02      AS CM
        ON M.MASTER_NO = CM.MASTER_NO
    WHERE M.DEL_YN = 'N'
    ORDER BY M.MASTER_NO DESC
) ROW
LIMIT 10000, 10

SQL Plan Case1-1

오라클 쿼리에 익숙하신 분들이 흔히 하는 실수입니다.

오라클 rownum 효과를 내기 위해 (SELECT @RNUM:=0) 로 번호를 붙이다 보니 결과적으로 필요없는 데이터를 스캔합니다. Nest Loop Join으로 데이터를 처리하기 때문에 퍼포먼스가 상당히 떨어집니다.

Row 번호는 어플리케이션 서버에서 생성하고, 다음과 같이 쿼리를 작성하는 것이 좋습니다.

SELECT
    M.MASTER_NO,
    M.TITLE,
    MI.PATH,
    M.REGDATE,
    CM.TYPE
FROM MAIN AS M
LEFT OUTER JOIN TAB01 AS MI ON M.MASTER_NO = MI.MASTER_NO
INNER JOIN TAB02      AS CM ON M.MASTER_NO = CM.MASTER_NO
WHERE M.DEL_YN = 'N'
ORDER BY M.MASTER_NO DESC
LIMIT 10000, 10

SQL Plan Case1-2

변환 전/후 쿼리 프로파일링을 해보면 다음과 같습니다. 변환 후에 필요없는 데이터 스캔에 소요되던 Sending Data가 사라지고, 단순하게 처리됩니다.

SQL Profile Case1

Case 2

Where 조건 Left Value에 함수 적용하여 결과적으로 Full Scan이 발생하는 경우입니다. 서비스 구현 단계에서는 쉽고 직관적으로 보일지는 몰라도, DB 내부 데이터 처리에서 엄청난 자원을 소모합니다.

이런 습관은 DBMS 상관없이 기피해야 합니다.

SELECT *
FROM VIEW_MASTER_LOG_GROUP TAB01
WHERE DATE_FORMAT(ST_LAST_DATE, '%Y-%m-%d') LIKE DATE_FORMAT(NOW(), '%Y-%m-%d');

SQL Plan Case2-1

Where 조건 날짜 검색 로직을 살펴보면 결과적으로 오늘 0시 이후 데이터를 가져오는 구문입니다. 그렇다면 다음과 같이 변환해 봅시다.

인덱스를 타게 Left Value에서 불필요한 Function을 제거하고, Between으로 0시 이후 데이터를 가져옵니다.

SELECT *
FROM VIEW_MASTER_LOG_GROUP TAB01
WHERE ST_LAST_DATE BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d') AND NOW();

SQL Plan Case2-2

Full Scan이 아닌 Range Scan이며 정상적으로 인덱스를 탑니다.

Case 3

데이터 추가 조회를 위한 Outer Join 사용 시 주의할 점입니다. 바로 위 1차 변환된 쿼리를 기준으로 말씀 드리겠습니다.

하단 쿼리는 Outer Join이 조건 검색에 영향을 미치지 않고 추가 정보 조회만을 위한 역할로 사용될 때 입니다.

SELECT
    M.MASTER_NO,
    M.TITLE,
    MI.PATH,
    M.REGDATE,
    CM.TYPE
FROM MAIN AS M
INNER JOIN TAB01 AS CM
    ON CM.MASTER_NO = M.MASTER_NO
LEFT OUTER JOIN TAB02 AS MI
    ON M.MASTER_NO = MI.MASTER_NO
WHERE M.DEL_YN = 'N'
ORDER BY M.MASTER_NO DESC
LIMIT 10000, 10;

SQL Plan Case3-1

데이터를 10,000번째 위치부터 10 건을 가져온다면 결과적으로 불필요한 10000번 Outer Join이 발생합니다. 쿼리 성능이 상당이 안좋습니다.

물론 데이터가 적을 경우에는 큰 문제가 없지만, 데이터가 누적됨에 따라 서버에 큰 영향을 미칠 수 있습니다. 아래와 같이 수정을 해보죠.

SELECT
    A.MASTER_NO,
    A.TITLE,
    MI.PATH,
    A.REGDATE,
    A.TYPE
FROM(
    SELECT
        M.MASTER_NO,
        M.TITLE,
        M.REGDATE,
        CM.TYPE
    FROM MAIN AS M
    INNER JOIN TAB01 AS CM
        ON CM.MASTER_NO = M.MASTER_NO
    ORDER BY M.MASTER_NO DESC
    LIMIT 10000, 10
) A
LEFT OUTER JOIN TAB02 AS MI
    ON A.MASTER_NO = MI.MASTER_NO;

SQL Plan Case3-2

SQL Plan 정보는 더 안좋은 것처럼 보이지만, SQL을 프로파일링 해보면 다음과 같이 좋은 성능을 확인할 수 있습니다.

SQL Profile Case3

변환 후 프로파일은 더욱 길어지기는 했지만, Outer Join을 위한 Sending Data 시간만큼 단축되었습니다.

Conclusion

3가지 간단한 사례이기는 하지만, SQL 튜닝 시 확인을 해보면 종종 걸리는 문제들입니다. 쿼리 특성에 따라 성능이 좌우되는 만큼 SQL도 서비스 로직을 정확히 파악하여 작성한다면 서버 자원을 효율적으로 배분할 수 있겠죠.

잊지 마세요. MySQL에서는 단일 코어에서 Nested Loop Join 방식으로 데이터를 처리한다는 사실을..

재미있는 사례로 다음에 인사 드리겠습니다. ^^