메모리 기반 RDBMS, MemSQL을 알아봅시다

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

MemSQL이란?

MemSQL은 디스크 병목을 최소화하기 위해 만들어진 메모리 기반의 관계형 DBMS입니다. 메모리 기반으로 데이터 처리가 이루어지기 때문에, 엄청난 속도로 Read/Write이 가능하며, 기존의 NoSQL 또는 캐시로만 가능했던 퍼포먼스 향상이 있습니다. 실제로 디스크 기반 DBMS 대비 약 30배 이상의 성능 향상이 있다고 하니, 놀라울 따름입니다.

최근 들어 메모리 가격이 하루가 다르게 저렴해지고 있기 때문에 메모리 사이즈를 최대한 늘려서 가격 대비 성능 비를 최대로 이끌어 내는 DB입니다.

MemSQL 특징

1) 강력한 SQL기반의 통신

SQL 기반으로 통신하며, MySQL API를 따릅니다. 그렇기 때문에 기존 MySQL을 사용하는 서비스 경우 로직 변경이 불필요하다고 합니다. 하다못해 라이브러리 또한 기존 MySQL에서 사용하던 그대로 재사용해도 상관 없습니다.

memsql protocol

SQL기반이기 때문에 복잡한 데이터 처리에 유연하며, DB 레이어가 구분되므로 어플리케이션에 영향을 의존적이지 않습니다.

사실 NoSQL이 강력한 성능을 발휘하기는 하지만, DB Schema와 같은 기반 요소가 어플리케이션에 녹아있고 사용이 어렵습니다. 그리고 데이터 처리 시 개발자 개인 역량에 따라 전체적인 성능에 엄청난 영향을 주기도 하죠.

2) 내구성이 좋은 구조

MemSQL은 서버가 예기치 않게 종료가 된다고 하더라도 데이터 유실이 거의 없습니다. 서버 장애가 발생한다고 하더라도 Snapshot으로 특정 시점의 풀 백업 데이터와 WAL(Write Ahead to Log)로 쌓인 데이터 변경 이력을 조합하여 “장애 시점 이전”으로 데이터 버전을 되돌릴 수 있습니다.

memsql data recovery

물론 데이터 영속성이 불필요한 프로세스 경우에는 “durability = off” 옵션으로 비활성할 수 있습니다. 하지만, 이 경우 서버 재시작 후 메모리에 있는 모든 데이터는 소멸되므로 사용 시 주의를 해야겠죠. ^^;;

3) 쉬운 설치 및 사용

MemSQL은 설치가 정말로 쉬우며, 관련 Configure 파일도 단순합니다. 그리고 MySQL 진영에서 개발을 했었다면, 쿼리 사용에도 큰 무리가 없습니다.

4) Transaction 기능

MemSQL은 트랜잭션을 단일 쿼리 레벨에서 Read Committed 레벨로 데이터를 처리합니다. 사용자 레벨로는 Transaction을 제공하지 않기 때문에 복잡한 Transaction이 요구되는 부분에서는 분명 한계가 있습니다.

만약 being, commit, rollback과 같은 구문을 어플리케이션에서 사용한다고 하더라도, Warning 메세지와 함께 DB 내부에서 무시됩니다. 즉, 데이터 변경 질의가 완료되는 바로 그 순간 다른 세션에서도 변경된 데이터를 바로 읽을 수 있습니다.

Repeatable Read(Snapshot Read) Isolation Level은 내부적으로 Database Snapshot을 생성하기 위해 사용되는데, snapshot-trigger-size 임계치에 도달하여 새로운 Snapshot을 생성할 때 사용합니다.

5) 동시성과 퍼포먼스를 위한 인덱스 기능

Hash Index

Hash 인덱스는 1:1로 매칭되는 경우 최상의 성능을 발휘하며, Hash 인덱스를 사용하기 위해서는 인덱스 필드에 Unique 옵션이 있어야 가능합니다. 그리고 Hash 인덱스 생성 시 BUCKET_COUNT 옵션을 별도로 줄 수 있고, 512에서 2^30까지 설정할 수 있는데, 데이터 사이즈에 따라 적절하게 파라메터 값을 지정해야 합니다. 파라메터 값이 너무 작으면, Hash 효율이 크게 저하되고, 반대로 너무 크면 Hash Bucket을 위해 메모리가 불필요하게 많이 사용되기 때문이죠.

예를 들어 파라메터 값이 너무 작으면 빨간 색 그림처럼 한 개의 Hash 타겟에 여러 개의 데이터가 포함되어 있으므로, 원하는 타겟 데이터를 선별하는 부분에서 로드가 발생합니다. 그렇다고 파라메터를 너무 크게 잡으면 파란 그림처럼 메모리 공간만 잡고 아무 것도 포인팅하고 있지 않는 결과가 발생하죠.

memsql hash index

MemSQL측에서는 누적될 수 있는 데이터 최대치 Row 수의 50%로 설정하는 것이 바람직하다고 합니다. 즉, 하나의 Hash키에서는 평균 2개까지 중복되는 것이 성능 및 공간 효율적으로 최적이라는 것을 의미하는 것이죠.

Skip List

B-Tree는 일반적으로 RDBMS에서 제공하는 인덱스 구조이며, MySQL에서도 관련 기능을 제공합니다.

둘의 차이점은 MySQL은 디스크 기반으로 B-Tree를 수행하나, MemSQL은 메모리 기반의 Skip List 구조로 데이터를 찾아냅니다. 데이터 접근 속도가 더욱 빠른 것이죠.

memsql skip list

하단과 같이 인덱스를 ASC 옵션으로 생성된 경우, “Order By r ASC”로 Skip List로 빠르게 데이터에 접근할 수 있습니다. 그러나 “Order By r DESC” 경우에는 Skip List를 사용하지 않기 때문에 상대적으로 속도가 느리다고는 합니다.

물론 DESC 옵션으로 추가적인 인덱스를 생성하면 동일하게 Skip List의 강점을 이용할 수도 있겠지만, 인덱스 또한 공간을 잡아먹는 데이터이기 때문에 사용 상 주의가 필요합니다.

memsql> CREATE TABLE tbl (
     ->   name VARCHAR(20) PRIMARY KEY,
     ->   r INT,
     ->   INDEX i USING BTREE (r ASC)
     -> );
memsql> EXPLAIN SELECT name FROM tbl ORDER BY r ASC;
+-------------+------+------+-------+
| select_type | type | key  | Extra |
+-------------+------+------+-------+
| SIMPLE      | ALL  | i1   |       |
+-------------+------+------+-------+
memsql> EXPLAIN SELECT name FROM tbl ORDER BY r DESC;
+-------------+------+------+-----------------------+
| select_type | type | key  | Extra                 |
+-------------+------+------+-----------------------+
| SIMPLE      | ALL  | i1   | memsql: requires sort |
+-------------+------+------+-----------------------+

6) SQL을 선행 컴파일하여 처리

SQL 실행 순서는 Oracle과 거의 흡사합니다.

Parse → Code Generate → Plan Cache → Execute 단계를 거칩니다. Plan Cache에 적재된 SQL 실행 속도는 빠르지만, 처음 실행되는 SQL은 상당히 느립니다.

처리 단계를 조목조목 설명하겠습니다.

Parse
쿼리가 유입되면 SQL에 포함된 정수 혹은 문자열 파라메터를 특정 문자로 변환하는 과정입니다.
예를 들어 SELECT * FROM users WHERE id = ‘gywndi’;라는 쿼리는 SELECT * FROM users WHERE id = @; 와 같이 변환됩니다.
물론 이 SQL Parsing 단계는 DB에 오버헤드가 거의 없습니다.
Code Generate
Parsing 단계에서 생성된 SQL을 DB가 쉽게 처리할 수 있는 C++ 기반으로 변환하는 단계입니다.
DB에서는 원본 SQL이 아닌 데이터를 조회하기에 최적화된 코드에 따라서 데이터를 질의합니다.
결과는 바로 다음 단계인 Plan Cache 영역에 저장됩니다.
Plan Cache
전 단계에서 컴파일된 실행 계획을 Plan Cache 메모리 영역에 저장하며, 이후 동일한 타입의 SQL 유입 시 SQL 컴파일 단계 없이 Plan Cache에 있는 정보로 바로 처리합니다.
Execute
데이터 추출은 오직 선행 컴파일된 Plan정보로만 수행됩니다.
이제 SQL을 더이상 순차적으로 읽으며 분석하지 않아도 되기 때문에 마치 NoSQL Solution처럼 혹은 그 이상의 속도를 낼 수 있는 것입니다.

memsql query execute

처음 SQL 질의 요청이 들어오면 Parse → Code Generate → Plan Cache → Execute 단계를 거쳐서 데이터를 뽑아냅니다.

기존에 SQL을 실행하여 이미 Plan Cache에 관련 실행 계획이 저장된 경우에는 Parse → Plan Cache → Execute 단계로 수행을 합니다. 실행 계획 생성 없이 Plan Cache에 저장된 정보로 바로 데이터를 조회할 수 있습니다.

7) Concepts

메모리 기반의 RDBMS인 MemSQL은 두 가지 컨셉으로 만들어졌습니다. Lock이 없는 Hash인덱스와 Skip List를 활용한 B-Tree인덱스를 사용함으로써 일반 CPU에서도 강력한 데이터 처리가 가능합니다. 그리고 MVCC를 제공하기 때문에 엄청난 Writing이 발생함에도 절대 Reading 시 장애가 발생하지 않습니다.

두 가지 특징에 초점을 맞추어 개발을 하였고, 기존 디스크 기반 데이터 처리 속도 대비 30배 이상 빠르다고 합니다.

MemSQL 테스트 후기

Sysbench 툴을 사용하여 벤치마킹을 시도하려 하였으나, DB 특성이 다르다보니 테스트를 할 수 없었습니다. 대신 다른 몇 가지 부분만 짚어서 단일 쿼리 테스트로 진행을 하였고, 테스트 기간 동안 데이터 현황을 한번 살펴보았습니다.

MemSQL Requirement가 일단은 CentOS 6.0 이상이었고, 해당 OS는 제 개인 블로그 서버에만 설치되어 있었기 때문에 어쩔 수 없이 저사양 PC에서 테스트하였습니다. ^^;;

테스트 환경

CPU : E-350 (1.6GHz)
Memory : 8G
Disk : 3.5′ SATAII 5400 rpm Single
Data : 100만 건

Sysbench 툴로 100만 건 데이터를 생성하고, 해당 데이터로 간단한 질의 몇개와 기본적인 차이점을 위주로 살펴보았습니다. MemSQL은 물론 메모리 안에 모두 데이터가 존재하며, MySQL 또한 데이터 사이즈가 크지 않기 때문에 메모리에 전체 데이터가 들어있다고 봐도 무관합니다.

memsql performance result

테스트 결과 100만 건 일괄 업데이트에서는 MySQL이 더 빠릅니다. InnoDB Buffer Pool도 결국에는 메모리에 존재하기 때문이 아닐까 합니다. 그리고 업데이트 도중 다른 세션에서는 현재 변경되고 있는 데이터 현황을 확인할 수 없는 것에서 쿼리 단위로 트랜잭션이 보장되는 것을 추측할 수 있었습니다.

MemSQL에서 SQL이 처음 실행되는 순간, 즉 컴파일이 필요한 시점에는 상당히 느립니다. 그러나 동일 타입의 쿼리가 다시 호출되는 경우 MySQL대비 10배 이상 빠른 결과를 보였습니다.

MemSQL에는 Plan Cache가 있는데 이것에 관한 현황은 다음과 같이 조회할 수 있습니다.

memsql> SHOW PLANCACHE\G
************** 1. row **************
     Database: sysbench
    QueryText: select * from sbtest where id = @
      Commits: 113
    Rollbacks: 0
     RowCount: 2606
ExecutionTime: 5
LogBufferTime: NULL
 LogFlushTime: NULL
  RowLockTime: NULL
************** 2. row **************
     Database: sysbench
    QueryText: select k, count(*) from sbtest group by k
      Commits: 7
    Rollbacks: 0
     RowCount: 53
ExecutionTime: 3611
LogBufferTime: NULL
 LogFlushTime: NULL
  RowLockTime: NULL
************** 3. row **************
     Database: sysbench
    QueryText: INSERT INTO sbtest(k, c, pad) VALUES (?,?,?)
      Commits: 100
    Rollbacks: 0
     RowCount: 1000000
ExecutionTime: 19503
LogBufferTime: 0
 LogFlushTime: 0
  RowLockTime: 0

각 정보는 누적된 결과이며, Plan Cache에 들어있으면, 그 이후로는 SQL 실행 시간이 엄청하게 빨라집니다. (1번 쿼리는 평균 0.00005초 미만입니다.)

그러나! 질의를 만드는 도중 몇 가지 제약 사항을 알게 되었는데.. 기존에서 큰 무리가 없던 쿼리 사용이 불가하다는 것입니다.

간단하게 두 개 정도 쿼리 예를 들겠습니다.

memsql> update sbtest
     -> set k = cast(rand()*100000 as unsigned);
ERROR 1707 (HY000): This query contains constructs not currently supported by MemSQL. The query cannot be executed.

memsql> select count(*)
     -> from (
     ->     select id from sbtest
     ->     where id between 1 and 100
     -> ) t;
ERROR 1707 (HY000): This query contains constructs not currently supported by MemSQL. The query cannot be executed.

Update쿼리에는 함수가 아닌 상수 값이 지정되어야 하며, 위와 같은 타입의 서브쿼리는 지원하지 않습니다. 즉 MySQL에서 MemSQL로 데이터를 이관한다고 하더라도 상당 부분 SQL 변경을 해야할 것 같네요^^;;

위 두 가지 외에도 더욱 많은 제약 사항들이 있을 것 같네요.

Conclusion

트랜잭션을 지원하는 메모리 기반 DBMS이기 때문에 많은 기대를 하였으나, 서비스에 직접적인 투입을 하기에는 아직까지는 한계가 있어 보입니다.풍부한 기능을 가진 MySQL과 테스트에서는 기능적인 요소 뿐만 아니라 편의성 부분에서도 부족합니다.

MySQL 프로토콜을 사용기 때문에 접근성은 타 NoSQL보다는 수훨하나, 기존 MySQL에서 데이터 이관 시 쿼리 또한 상당 부분 변경이 필요합니다. 그리고 Replicaion 기능과 Sharding에 관한 내용을 MemSQL에서는 소개를 하였으나, 활용할 수 있는 메뉴얼을 현재까지 찾을 수 없었습니다. (현재 개발되고 있는 버전이 아닐까 생각이 드네요.)

하지만 메모리 기반이고, NoSQL보다는 접근성이 뛰어나며 단순 Select 테스트에서도 MySQL대비 10배 이상 빠른 성능을 보인 것으로 보아 지속적으로 눈여겨 볼 필요가 있는 제품일 것 같네요. ^^;

DB 목적을 정확하게 정의하고 데이터 그룹 메타 정보 보관 혹은 해시 성격의 데이터를 관리하는 시스템에서라면.. (SQL 종류가 다양하지 않은 단순 질의) 꽤나 좋은 성능을 보이지 않을까요?

물론 서비스에 투입하기 이전에 DB 안정성 및 데이터 견고성에 관한 보장이 선행되어야겠죠.^^

참고자료 : http://developers.memsql.com/

Maria 1탄 – MySQL의 쌍둥이 형제 MariaDB를 소개합니다.

Posted on 2012-06-18 | by gywndi | 7 Comments

MariaDB란?

MySQL이 Sun Microsystems로 넘어가면서 당시 MySQL AB 출신들이 따로 나와서 MySQL을 기반으로 한 다른 오픈 소스 기반의 DBMS를 배포했다고 합니다. 바로 MariaDB가 그것이며 MySQL과 유전 정보를 그대로 고수한 “진짜” 오픈 소스 기반의 DBMS입니다.

현재 Monty Program AB와 MariaDB Community에서 개발하고 있으며, MySQL과 기본적으로 구조 및 사용 방법 등 모두 동일합니다. (동일 소스에서 개발되고 있으니 당연한 말입니다.)

Monty Program AB에 따르면 많은 기능들이 MariaDB에서 먼저 구현을 하고 그 후 MySQL에도 반영이 된다고 하는데, 마치 CentOS와 Redhat 리눅스 관계 같다는 생각이 듭니다.^^

GPL v2 라이선스에 따르기 때문에, Oracle의 횡포로부터 상당히 자유롭습니다. 사실 Oracle에서 MySQL 관련하여 현재는 오픈 소스 정책을 고수하고 있지만, 언제 갑자기 그들의 정책을 폐쇄적으로 바꿀 지 모르기 때문에 상당히 호기심이 가는 제품입니다.

MariaDB vs MySQL – 설치 방법

설치 방법은 MySQL과 동일합니다.

물론 컴파일 시에는 사용하고자하는 스토리지 엔진 기능 on/off를 위해 추가/변경이 될 수 있겠지만, RPM방식 혹은 TAR 압축 해제하여 DB를 설치해도 잘 구동됩니다.

MySQL 설치 방법은 “리눅스에 MySQL 설치하기(CentOS 5.6)” 블로그 포스팅을 참고하세요. (심볼릭 링크 변경 부분만 다릅니다.)

MariaDB vs MySQL – 스토리지 엔진

“MariaDB 5.3″에서 기본적으로 제공하는 스토리지 엔진은 다음과 같습니다.

+------------+---------+--------------+------+------------+
| Engine     | Support | Transactions | XA   | Savepoints |
+------------+---------+--------------+------+------------+
| MEMORY     | YES     | NO           | NO   | NO         |
| MRG_MYISAM | YES     | NO           | NO   | NO         |
| FEDERATED  | YES     | YES          | NO   | YES        |
| BLACKHOLE  | YES     | NO           | NO   | NO         |
| CSV        | YES     | NO           | NO   | NO         |
| Aria       | YES     | NO           | NO   | NO         |
| ARCHIVE    | YES     | NO           | NO   | NO         |
| MyISAM     | YES     | NO           | NO   | NO         |
| InnoDB     | DEFAULT | YES          | YES  | YES        |
| PBXT       | YES     | YES          | YES  | NO         |
+------------+---------+--------------+------+------------+

기본 제공되는 스토리지 엔진 중 다음 3개를 눈여겨볼 필요가 있습니다.

FEDERATED (트랜잭션 제공)
원격 DB 서버 테이블에 네트워크로 접근하는 스토리지 엔진으로 기존
원격 DB에서 로컬 DB로 결과 값만 전달한다는 점에서 MySQL에 기본으로 장착된 FEDERATED와 가장 큰 차이점이 있음
MariaDB에서는 FEDERATEDX라는 새로운 네이밍을 사용
Aria
차세대에 MyISAM 스토리지 엔진을 대체하기 위해 개발
MyISAM에서 파생되었으며, Crash-Safe를 목표로 진행 중, 부분적으로 Transaction을 제공
PBXT(트랜잭션 제공)
Transaction Log 에 선 기록 없이 바로 DB에 기록
Maria5.5부터는 더이상 유지보수를 제공하지 않으므로 기본 스토리지 엔진에서 제외

위 기본 스토리지 엔진 외에 Plugin으로 제공되는 스토리지 엔진을 추가로 설치할 수 있습니다.

OQGRAPH
Graph 기능을 제공하는 스토리지 엔진.
(Maria5.5에는 기본으로 Plugin이 들어있지 않음)
```
MariaDB> INSTALL PLUGIN oqgraph SONAME 'ha_oqgraph.so';
```
SphinxSE
Full-Text Searching이 필요할 때 사용할 수 있는 스토리지 엔진.
단, SphinxSE은 어디까지나 Sphinx의 일부분이며, 스토리지 엔진 사용을 위해서는 Sphinx 데몬을 별도로 설치 필요.
```
MariaDB> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
```

참고로 MySQL 5.5에서 기본적으로 제공하는 스토리지 엔진 리스트입니다

+------------+---------+--------------+------+------------+
| Engine     | Support | Transactions | XA   | Savepoints |
+------------+---------+--------------+------+------------+
| FEDERATED  | NO      | NULL         | NULL | NULL       |
| MRG_MYISAM | YES     | NO           | NO   | NO         |
| MEMORY     | YES     | NO           | NO   | NO         |
| BLACKHOLE  | YES     | NO           | NO   | NO         |
| MyISAM     | YES     | NO           | NO   | NO         |
| CSV        | YES     | NO           | NO   | NO         |
| ARCHIVE    | YES     | NO           | NO   | NO         |
| InnoDB     | DEFAULT | YES          | YES  | YES        |
+------------+---------+--------------+------+------------+

MariaDB vs MySQL – SQL Join

MariaDB 5.3으로 넘어오면서 조인 퍼포먼스가 향상되었는데, 그 중 괄목할 만한 사항은 Semi-join 서브쿼리 성능 향상에 관련된 내용입니다.

예를 들어서 다음과 같은 SQL이 유입되었다고 가정해 보자면..

select * from Country 
where
  Continent='Europe' and
  Country.Code in (select City.country 
                   from City 
                   where City.Population>1*1000*1000);

MySQL 5.5 인 경우 위 쿼리는 아래와 같이 Country -> City 테이블 순으로 쿼리가 실행되며, Continent 조건이 없는 경우 Full-Table Scan이 발생합니다.

Semi Join Sub Query(1)

그러나 MariaDB5.3에서는 반대로 City -> Country 서브쿼리 부분이 먼저 풀리고 결과적으로 외부 테이블과 조인 연산하는 방식으로 데이터를 처리합니다. 즉, Continent 조건이 없어도 Full-Table Scan이 발생하지 않는 것이죠.

Semi Join Sub Query(2)

조건절에 IN 을 써서 간단하게 작성할 수 있는 SQL 경우(설혹 조건이 10건 미만일 지라도)에도 어쩔 수 없이 Inner Join으로 풀어야 하는 경우가 많았기 때문에, 정말로 반가운 내용입니다.

무엇보다 Optimizer Switch를 확인해보면 Optimizer의 선택의 폭이 MySQL 5.5 대비 상당히 다양한 것을 볼 수 있습니다.^^

MariaDB Optimizer Switch

MariaDB> SELECT @@optimizer_switch\G
@@optimizer_switch: index_merge=on
                    ,index_merge_union=on
                    ,index_merge_sort_union=on
                    ,index_merge_intersection=on
                    ,index_merge_sort_intersection=off
                    ,index_condition_pushdown=on
                    ,derived_merge=on
                    ,derived_with_keys=on
                    ,firstmatch=on
                    ,loosescan=on
                    ,materialization=on
                    ,in_to_exists=on
                    ,semijoin=on
                    ,partial_match_rowid_merge=on
                    ,partial_match_table_scan=on
                    ,subquery_cache=on
                    ,mrr=off
                    ,mrr_cost_based=off
                    ,mrr_sort_keys=off
                    ,outer_join_with_cache=on
                    ,semijoin_with_cache=on
                    ,join_cache_incremental=on
                    ,join_cache_hashed=on
                    ,join_cache_bka=on
                    ,optimize_join_buffer_size=off
                    ,table_elimination=on

MySQL 5.5 Optimizer Switch

mysql> SELECT @@optimizer_switch\G
@@optimizer_switch: index_merge=on,
                    index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on

Conclusion

위에서 나열한 특징 외에도 상당한 차이점이 있으나 내용이 방대하여 세세하게 살펴보지는 못했고, 기존 MySQL 대비하여 성능 및 사용 편의성이 얼마나 좋은지를 다양한 벤치마크 활동을 통해서 알아봐야 합니다.

그렇지만, 현재 Oracle이 MySQL을 인수한 상태이고, 언제든지 내부적인 정책을 변경할 수 있는만큼 지속적으로 검토할 가치가 있는 DBMS임에는 틀림없습니다.

꾸준히 MariaDB를 분석하여 포스팅하겠습니다.^^

<참고 자료>

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