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

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

  1. Drop Temporary Table
  2. Create Temporary Table
  3. 1부터 10,000,000 범위 숫자 20개 무작위 추출
  4. 다음과 같이 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. 1부터 10,000,000 범위 숫자 20개 무작위 추출
  2. 다음과 같이 IN 구문을 사용하여 Select
SELECT a.i, a.c1, a.c2
FROM dbatest a
WHERE a.i in (1,2,3,4,..,17,18,19,20)
Performance Test Select Result
Performance Test Select Result

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

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

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

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

Performance Test 1000 Rows Select Result
Performance Test 1000 Rows Select Result

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

Performance Test Wait

Benchmark Result : Update

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

Temporary Table

  1. Drop Temporary Table
  2. Create Temporary Table
  3. 1부터 10,000,000 범위 숫자 20개 무작위 추출
  4. 다음과 같이 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. 1부터 10,000,000 범위 숫자 20개 무작위 추출
  2. PreparedStatement를 사용하여 다음과 같은 쿼리 20번 수행
UPDATE dbatest SET c1 = c1 +10, c2 = c2 + 10000 WHERE i = ?
Performance Test Update Result
Performance Test Update Result

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 구문을 발생하지 않고 테이블을 재사용하는 방안이 조금은 더 합리적일 것 같습니다.

디스크 병목 현상에 따른 DB 성능 리포트

Overview

어느 시스템에서도 병목 현상은 어딘가에 있습니다. DBMS 또한 CPU, Memory, Disk로 구성된 하나의 시스템이기 때문에 당연히 특정 구역에서 병목현상이 발생할 수 있죠. 오늘은 Disk에서 발생하는 병목에 관해서 말씀드리겠습니다.

Memory Processing

디스크 배열(RAID)에 따른 DB 성능 비교 에서, 메모리가 충분하면 아래와 같다는 그래프를 보여드렸습니다.

InnoDB Buffer Pool : 12G
InnoDB Buffer Pool : 12G

어떤 경우든 메모리에만 연산이 가능하다면, CPU자원을 거의 활용 가능하다고 할 수 있죠. 그러나 만약 디스크 I/O가 발생하는 순간부터 CPU는 전혀 연산하지 않는 현상이 발생합니다. 바로 디스크 I/O Wait 으로 인한 시스템 병목 현상 발생입니다.

Benchmark

대용량 환경에서 MySQL 성능을 분석하기 위해 2억 데이터 Insert 테스트를 수행하였습니다. (하단 결과에서는 1.3억 데이터까지만 추출하여 그래프를 만들었습니다.)

하단은 테스트를 위해 사용한 테이블 스키마입니다. (간단한 테스트입니다.) 30개 Thread로 동시 Insert 작업을 수행하였고, 건 수에 따라 QPS및 시스템 현황을 살펴보았습니다.

##테이블 정의
CREATE TABLE `test` (
`NO` int(11) NOT NULL AUTO_INCREMENT,
`EMAIL` varchar(128) DEFAULT NULL,
`NAME` varchar(32) NOT NULL,
PRIMARY KEY (`NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY ()PARTITIONS 3;

##테이블 포멧 변경
TRUNCATE TABLE TEST;
ALTER TABLE TEST ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Benchmark Result

버퍼풀 4G 환경 하의 일반 테이블과 압축 테이블 성능 비교 및 버퍼풀 8G 환경 하에서의 성능 비교입니다.
압축 테이블의 경우 생각보다 많은 CPU 작업이 필요하게 되는데요, 메모리에 모든 데이터가 존재한다면 이 자체가 병목입니다.
하지만 데이터가 급증하여 DISK I/O이슈가 발생하면 반대로 CPU 유휴 자원을 사용하여 병목을 줄일 수 있습니다.

디스크 압축에 따른 QPS 비교
디스크 압축에 따른 QPS 비교
디스크 압축에 따른 DISK I/O 비교
디스크 압축에 따른 DISK I/O 비교

7000만 건 데이터(데이터파일 약 5G) Insert까지는 일반 테이블이 압도적인 성능을 보여줍니다.  7000만 건 이상 데이터 Insert에서는 1/4로 압축한 테이블 성능이 꾸준히 유지됩니다. 9000만 건 데이터 Insert부터 1/4 압축 테이블도 성능 저하가 발생합니다.

가장 괄목할만한 사항은 DISK I/O가 급증하는 시점부터는 CPU자원은 거의 사용하지 않는다는 점입니다.

Conclusion

디스크 병목을 풀기 위해서 더욱 빠른 디스크로 고도화하여 풀 수 있는 방법이 있습니다. 다른 방법은 어차피 사용하지 못할 CPU자원이라면, CPU자원을 일부 소모하더라도 압축 테이블로 저장하여 디스크 병목을 해결할 수 있습니다.

앞선 테스트 결과가 반드시 정답은 아닙니다. 무조건 압축 테이블만이 대안은 아닙니다. 하지만 서비스 특성에 맞게 테스트 시나리오를 작성하고 병목을 사전에 최대한 줄이는 활동이 필요합니다.

무조건 고 사양의 서버로 구성하기 보다는 조금만 데이터를 다른 시각으로 설계한다면, 동일 자원에서 최적의 퍼포먼스를 발휘할 수 있을 것입니다.

동일한 내용은 제가 “파란 개발자 블로그”에 작성한 디스크 병목 현상에 따른 DB 성능 리포트 에서도 확인할 수 있습니다.^^

디스크 배열(RAID)에 따른 DB 성능 비교

Overview

MySQL DBMS 하드웨어 구성 시 어떠한 정책으로 움직이는 것이 가장 효율적일지, 메모리/디스크 설정을 변경하여 테스트를 진행하였습니다. 디스크는 RAID 레벨을 변경하였고, innodb_buffer_pool을 조정함으로써 메모리 환경을 구성하였습니다. 서비스 특성에 따라 하드웨어 구성을 달리함으로써, 장비를 더욱더 효율적으로 사용할 수 있을 것으로 기대됩니다.^^

디스크배열(RAID)란?

RAID란 Redundant Array of Inexpensive Disks의 약자로 디스크를 여러장 묶어서, 데이터 중복성 및 성능 향상을 유도할 수 있는 기법입니다. RAID 기법은 참으로 많이 있으나, 일반적으로 실무에서는 RAID0, RAID1, RAID5, RAID10또는 RAID01을 많이 사용합니다.

더욱 상세한 내용은 하단 블로그를 참조하시면 되겠습니다.
http://blog.naver.com/jevida?Redirect=Log&logNo=140118943472

Benchmark 

테스트 환경은 다음과 같습니다.

  • CPU
    – Intel(R) Xeon(R) CPU E5630  @ 2.53GHz Quad * 2EA
  • Memory(InnoDB Buffer Pool)
    – 12G : 데이터 100%가 메모리 안에 존재 (DISK I/O 없음)
    – 8G : 데이터 80% 가 메모리 안에 존재 (DISK I/O 일부 발생)
    – 4G : 데이터 40% 미만 메모리 안에 존재 (DISK I/O 다량 발생)
  • DISK RAID
    – RAID1 : 데이터/로그 디스크 공유
    – RAID1(2) : 데이터/로그 디스크 분리
    – RAID5 : 데이터/로그 디스크 공유
    – RAID10 : 데이터/로그 디스크 공유
  • 데이터 사이즈
    – 50,000,000건 (11G)
  • 동시 접속 수
    – 1, 5, 10, 15, 20, 30, 100
  • 트랜잭션 구성
    – READ-ONLY : 14 Queries (–oltp-read-only –oltp-skip-trx)
    – READ/WRITE : 19 Queries (–oltp-test-mode=complex)

Benchmark Result

  1. Innodb_Buffer_Pool Size : 12G
    테스트 결과 모두 비슷한 성능을 보여주나 100쓰레드에서는 raid1(2) 시 일부 성능이 저하되었습니다. (iblog에서 데이터 저장 공간에 적용 시 물리적 장치 분리에 의한 성능 저하 요인으로 파악되네요.)

    InnoDB Buffer Pool : 12G
    InnoDB Buffer Pool : 12G
  2. Innodb_Buffer_Pool Size : 8G
    READ/WRITE에서는 디스크 배열에 따라 상당한 성능 차이를 보이는데, Buffer-Pool안의 데이터가 디스크로 Flush 되면서 발생하는 데서 기인한 듯 하고, RAID10이 가장 안정적인 성능을 보여줍니다.

    InnoDB Buffer Pool : 8G
    InnoDB Buffer Pool : 8G
  3. Innodb_Buffer_Pool Size : 4G
    가장 재미있는 결과입니다. 디스크 의존도가 높기 때문에 당연히 Raid-10이 가장 우수합니다. 그 다음으로는 Raid-5, Raid-1 순으로 성능을 보입니다.

    InnoDB Buffer Pool : 4G
    InnoDB Buffer Pool : 4G

Conclusion

위 결과를 바탕으로 다음과 같이 결론짓고 싶습니다.

  • 예산이 허용한다면, 다수의 디스크를 사용한 Raid-10으로 구성
  • 빈번하게 사용되는 데이터가 메모리에 80% 이상 존재한다면 Raid-1도 괜찮은 성능을 보임 (특히 Read 시)
  • 데이터 변경 작업이 많은 DB인 경우 반드시 Raid5또는 Raid10으로 구성
  • 성능 확장에는 메모리 투자가 제일 유리

메모리에 데이터가 모두 존재할 수 있다면, CPU성능에 따라 TPS 결과가 나옵니다. 4000이상 TPS가 나오는 경우 CPU idle이 거의 0% 까지 떨어지기 때문에, 여기서부터는 CPU투자에 따라 결과가 좌우되겠죠.

동일한 내용은 제가 “파란 개발자 블로그”에 작성한 DISK Raid 구성에 따른 DB 성능 비교 분석 에서도 확인할 수 있습니다.^^

감사합니다.