MySQL 성능 최적화를 위한 몇 가지 팁!!

Overview

트위터에서 우연히 성능 관련 가벼운 아는척(?)을 시작으로 일이 커지고 말았네요. ^^;; 성능 관련된 트윗을 보고 몇 가지 코멘트만 한다는 것이.. ㅎㄷㄷ한 멘션이 되고 말았습니다.

그래서 부족하나마, MySQL 성능 최적화 시 “본능적”으로 이행하는 몇 가지를 정리해보겠습니다.

Global Variable

성능과 연관이 되는 몇 가지 파라메터 변수는 반드시 체크를 하시기 바랍니다. MySQL에서 주로 InnoDB를 사용하는 상태라면 innodb_buffer_pool_size, innodb_log_file_size,  innodb_log_files_in_group, innodb_flush_log_at_trx_commit, innodb_doublewrite, sync_binlog 정도가 성능에 직접적인 영향을 미치는 요소라고 볼 수 있습니다.

  • innodb_buffer_pool_size
    InnoDB에게 할당하는 버퍼 사이즈로 50~60%가 적당하며, 지나치게 많이 할당하면 Swap이 발생할 수 있습니다.
  • innodb_log_file_size
    트랜잭션 로그를 기록하는 파일 사이즈이며, 128MB ~ 256MB가 적당합니다.
  • innodb_log_files_in_group
    트랜잭션 로그 파일 개수로  3개로 설정합니다.
  • innodb_flush_log_at_trx_commit
    서비스 정책에 따라 다르게 설정하겠지만, 저는 일반적으로 2값으로 세팅합니다.
    – 0: 초당 1회씩 트랜잭션 로그 파일(innodb_log_file)에 기록
    – 1: 트랜잭션 커밋 시 로그 파일과 데이터 파일에 기록
    – 2: 트랜잭션 커밋 시 로그 파일에만 기록, 매초 데이터 파일에 기록
  • innodb_doublewrite
    이중으로 쓰기 버퍼를 사용하는지 여부를 설정하는 변수로 활성화 시 innodb_doublewrite 공간에 기록 후 데이터 저장합니다. 저는 활성화합니다.
  • sync_binlog
    트랜잭션 커밋 시 바이너리 로그에 기록할 것인지에 관한 설정이며, 저는 비활성 처리합니다.

참고로 innodb_buffer_pool_size를 32G  메모리 서버에서 24G로 할당한 적이 있는데, SQL트래픽이 많아짐에 따라 Swap이 발생하더군요. 버퍼풀에는 대략 한 시간 정도 Active한 데이터와 인덱스를 담을 수 있는 사이징이라면 적절할 것 같습니다.

sync_binlog는 binlog 파일에 매 트랜잭션마다 기록할 것인지를 설정하는 파라메터인데, BBWC 혹은 FBWC이 없다면 활성화를 권고하지 않습니다. (개인적으로 경험해본 바에 따르면 on/off에 따라서 10~20배 정도 차이가 나기도 하더군요.)

Session Variables

MySQL은 단일 쓰레드에서 Nested Loop 방식으로 데이터를 처리합니다. 물론 5.6 버전부터는 조인 알고리즘이 몇가지 더 추가되기는 하지만, 여전히 미흡하죠. 결국 SQL처리 시 일시적으로 사용하는 Temporary Table이 디스크에 사용되지 않도록 유도하는 것이 제일 중요한 것 같습니다.

먼저 mysqladmin 유틸리티로 현재 Temporary Table 현황을 모니터링 하도록 합니다. 매 초마다 Status 차이를 보여주는 명령어이며, Created_tmp_files 이 꾸준히 많다면 tmp_table_size를 늘려줄 필요가 있습니다. Global Variable 에 설정하는 것보다는 필요 시 Session Variable로 설정하는 것을 권고 드립니다.

mysqladmin -uroot -p extended-status -r -i 1 | grep -E 'Created_tmp|--'

통계성 쿼리 질의 전 아래와 같이 세션 변수 설정(2G로 할당)을 한 후 진행하면 한결 빠르게 쿼리가 처리됩니다.

set session tmp_table_size = 2 * 1024 * 1024 * 1024;
set session max_heap_table_size = 2 * 1024 * 1024 * 1024;

하지만 이것은 어디까지나 디스크 접근을 줄이기 위한 목적이므로, 쿼리 자체를 수정하거나 다른 접근 방법으로 데이터를 처리하는 것이 가장 확실한 방법일 것입니다.

만약 Create Table As Select 혹은 Insert into .. Select 구문을 자주 사용하여 통계 데이터를 입력한다면 Transaction Isolation을 READ-COMMITTED로 변경하시기 바랍니다. 구문 실행 도중 Lock이 발생할 수 있기 때문이죠. ^^

예전에 포스팅한 MySQL 트랜잭션 Isolation Level로 인한 장애 사전 예방 법을 참고하세요.

Schema & SQL

MySQL에서는 서버 변수보다는 Schema와 SQL 특성에 큰 영향을 받는 DBMS 입니다. 일단 서버 설정이 기본적인 사이징정도로만 구성되면, 그 이후로는 Schema와 SQL이 DB특성에 맞게 작성되었는지 여부가 성능에 가장 큰 요소가 됩니다.

MySQL 특징은 예전 포스팅 ““을 참고하시면 되겠습니다.

1) Schema

InnoDB를 주로 사용한다는 가정 하에 말씀 드리겠습니다.

InnoDB 는 Primary Key 순으로 데이터가 저장됩니다. Primary Key가 Rowid처럼 사용되는 것이죠. 만약 Primary Key가 무작위로 입력되는 테이블이라면, 테이블에 데이터가 누적됨에 따라 성능도 비례하게 떨어지게 됩니다. Primary Key는 순차적으로 저장되도록 하며, 만약 구조 상 여의치 않다면 테이블 파티셔닝을 통해서 데이터 파일 사이즈를 최소로 유지하시기 바랍니다.

mysql secondary index

그리고  Secondary Index는 위 그림처럼 Primary Key를 Value 로 가집니다. 모든 Secondary Index 에는 Primary Key를 가지기 때문에 Primary Key의 데이터 타입이 전체 인덱스 사이즈에 큰 영향을 미칩니다.

InnoDB에서는 경우에 따라서, 인덱스가 실 데이터보다 더 큰 경우가 자주 있습니다. 그러한 경우가 있는지를 확인하고, 인덱스 사이즈를 최대한 줄이는 것이 성능상 좋습니다. 간단한 테이블 조회 쿼리입니다.

SELECT
    CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) TABLE_NAME,
    CONCAT(ROUND(TABLE_ROWS/1000000,2),'M') ROWS,
    CONCAT(ROUND(DATA_LENGTH/(1024*1024),2),'M') DATA,
    CONCAT(ROUND(INDEX_LENGTH/(1024*1024),2),'M') IDX,
    CONCAT(ROUND((DATA_LENGTH+INDEX_LENGTH)/(1024*1024),2),'M') TOTAL_SIZE,
    ROUND(INDEX_LENGTH/DATA_LENGTH,2) IDXFRAC,
    ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema', 'performance_schema')
ORDER BY DATA_LENGTH+INDEX_LENGTH DESC;

문자열 인덱스라면 Trigger + CRC32로 사이즈를 줄일 수 있습니다. 트리거에서 Insert혹은 Update가 발생하면 CRC32 함수로 문자열을 Unsigned Int 타입으로 변환하여 특정 칼럼에 저장하고, 해당 칼럼을 인덱스 필드로 사용하는 것입니다.

간단한 트리거 예제입니다. Insert 관련이며, Update 는 비슷하게 정의하시면 되겠죠. ^^

CREATE TRIGGER trg_test_insert
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  SET NEW.str_crc = CRC32(LOWER(TRIM(NEW.str)));
END$$

질의는 다음과 같이 합니다. 대략 1/43억 확률로 중복 데이터가 발생할 수 있으나, str값을 다시 조회 조건으로 주기 때문에 정상적인 데이터만 가져옵니다.

SELECT * FROM test
WHERE str = 'abcdefg'
AND str_crc = CRC32(LOWER(TRIM('abcdefg')));

이같이 쓰는 이유는 문자열 칼럼에 인덱스를 제거하기 위함이니 헷갈리시면 안됩니다!!

트리거 활용을 잘 하면 DB 자원을 많이 잡아먹는 성능 취약 요소를 최소화할 수 있습니다. 예를 들어 특정 통계 데이터가 자주 필요한 경우라면, 매번 Group By 질의를 하지 않고 트리거로 통계 테이블에 데이터를 적용하면서 수행하는 것도 한가지 방안입니다. 그렇다고 무조건 트리거를 맹신해서는 안됩니다. ^^;; 트리거는 최대한 단순하게 필요한 만큼만!!

2) SQL

SQL 관련은 문제 발생 요소가 너무도 다양해서 자세하게 설명하기가 어렵습니다. 먼저 예전 포스팅 ““를 참고하세요.

추가로 몇 가지 설명 더 드리겠습니다. 일단 다음과 같은 쿼리 습관은 좋지 않습니다.

SELECT * FROM userinfo
WHERE id IN (SELECT id FROM userinfo_log 
             WHERE reg_date > '2012-09-09');

서브 쿼리 실행 후 WHERE 조건을 수행하는 것이 아닌, 매번 데이터를 Nested Loop 탐색을 하면서 서브쿼리를 수행하기 때문에 불필요한 부하가 발생합니다.예전 포스트 ” Maria 1탄 – MySQL의 쌍둥이 형제 MariaDB를 소개합니다.“에 관련된 내용이 있으니 한번 읽어보세요. ^^

또한 다음과 같은 쿼리 습관은 최대한 피하시기 바랍니다.

SELECT ..
FROM (
    SELECT .. FROM .. WHERE ..
) a
INNER JOIN (
    SELECT .. FROM .. WHERE ..
) b ON a.col = b.col;

두 개의 서브 쿼리가 Temporary Table로 내부적으로 처리되면서, 두 테이블 간 풀 스캔이 발생합니다. Nested Loop방식으로 발생하는 풀 스캔은 시스템 성능에 엄청난 타격을 주므로, 테이블 구조를 잘 파악해서 인덱스를 잘 활용할 수 있도록 쿼리를 이쁘게(?) 작성하세요. (중간 테이블이 1만건씩이면 두 개 테이블 연산에는 1억 번의 연산이 필요합니다. ㅎㄷㄷ;;)

불필요한 Left조인이 있는지, 혹은 지나치게 서브 쿼리를 사용하는지, Select 조건에 들어간 칼럼이 반드시 필요한 데이터들인지, 커버링 인덱스를 사용할 수 있는 지 등여러 가지가 있겠습니다만, 다 언급하기에는 한계가 있네요. ^^

한가지 기억하실 것은 MySQL은 단일 쓰레드에서 Nested Loop 방식으로 데이터를 처리하므로, DB 가 처리할 데이터를 최소화 유도해야 한다는 것입니다.

Conclusion

계획없이 작성한 포스팅인만큼 여기저기 부족하고 보완해야할 부분이 여기저기 많습니다. 그치만 MySQL DB 성능을 최적화한다면, 살펴봐야할 몇 가지라는 생각이 들어서 간단하게나마 정리하였습니다. 성능에 직접적인 영향을 주는 환경 변수만 정책에 맞게 설정을 한 후에는 테이블 구조와 SQL을 MySQL 특성에 맞게 작성을 한다면 가시적인 효과를 빠르게 볼 수 있을 것 같네요. ^^

감사합니다.

SQL 작성 시 묵시적 형 변환 함정에 빠지지 말자!!

Overview

정말 오랜만에 포스팅합니다. 그동안 개인적인 일이 조금 있어서.. 조금 소홀이 했었네요. ^^

오늘은 묵시적인 형 변한에 대해서 설명을 드릴까 합니다. 서비스 쿼리를 작성하다보면, 이상이 없는 데 성능이 이상하게 안좋은 경우가 있습니다. 명시적으로 쿼리 내용을 볼 수 있다면 괜찮겠지만, 어플리케이션에서 변수를 바인딩하여 SQL을 실행하는 경우는 더욱 찾기가 어렵습니다.

묵시적 형 변환은 단순히 MySQL 뿐만 아니라 Oracle, MS-SQL 등 다른 DBMS에서도 반드시 주의를 해야하는 사항입니다.

묵시적 형 변환이란?

묵시적 형 변환이란 조건절 데이터 타입이 다르면 “우선 순위가 있는 쪽”으로 “형 변환”이 내부적으로 발생하는 것을 말합니다.

예를 들어 정수 값과와 문자열을 비교하는 경우, 숫자가 문자열보다 우선 순위에 있기 때문에 문자열은 정수값으로 자동 형 변환됩니다.

묵시적인 형 변환은 예외를 DBMS 내부적으로 처리하기 위함이므로, 서비스 도중 언제든지 발생할 수 있습니다. 하지만 만약 묵시적 형 변환이 일어나는 곳이 인덱스 칼럼이라면 어떨까요?  인덱스가 있을 지라도 조건 절을 처리하기 위해 모든 데이터를 묵시적으로 형 변환합니다. 이 경우 인덱스는 무의미하며 풀 테이블 스캔이 발생하죠.

자~! 직접 보시죠^^

테스트 환경 구성

1) 테이블 생성

## 테이블 생성
create table test(
  i int unsigned not null auto_increment,
  j int unsigned not null,
  s varchar(64) not null,
  d datetime not null,
  primary key(i)
);

## 인덱스 추가
alter table test add key (j), add key(s), add key(d);

2) 데이터 생성

스키마를 생성하였으니, 이제 데이터를 넣도록 하겠습니다. MySQL의 rand()와 crc32() 함수를 사용하여 정수, 문자열 그리고 날짜 데이터를 생성하여 위 테스트 테이블에 넣습니다.

먼저 test 테이블에 한 건 데이터를 다음과 같이 넣습니다.

insert into test (j, s, d)
values
(
    crc32(rand()), 
    crc32(rand())*12345,
    date_add(now(), interval -crc32(rand())/5 second)
);

그리고 다음과 같이 쿼리를 17번 실행하면 테이블에는 131072 건의 데이터가 저장됩니다. 물론 더 많은 데이터 환경에서 테스트를 하고자 한다면 몇 번 더 실행하면 되겠죠. ^^

insert into test (j, s, d)
select
    crc32(rand()), 
    crc32(rand())*12345,
    date_add(now(), interval -crc32(rand())/5 second)
from test;

이제 테스트 환경은 간단하게 구성되었습니다. 저는 위처럼 테스트 데이터를 간단하게 생성하였지만, 위 방법말고도 얼마든지 테스트 데이터를 생성할 수 있습니다.

빠른 테스트 환경을 구성하는 것이 제일 좋은 것 같아요. ^^ 더욱 안 귀찮은 방법이 있으면 저좀 알려주세요. (사실 저것도 귀찮다능~! ㅋ)

묵시적 형 변환 테스트

그리고 테스트를 할 데이터를 한 건 무작위로 가져와서 대상으로 정합니다.

select * from test order by rand() limit 1;

저는 다음 데이터를 기준으로 테스트를 진행하도록 하겠습니다. 무작위로 데이터를 생성하였기 때문에 위 쿼리 실행 결과과 다음과 같지 않을 수도 있습니다.

+--------+------------+----------------+---------------------+
| i      | j          | s              | d                   |
+--------+------------+----------------+---------------------+
| 122416 | 3835333344 | 17424528721665 | 2000-01-16 13:59:59 |
+--------+------------+----------------+---------------------+

정수 칼럼을 “문자열” 로 검색

위 테이블에서 j 는 정수형입니다. j 칼럼 기준으로 데이터를 검색할 경우 정수형이 아닌 문자열로 조건을 주었을 때 내부적인 처리입니다.

explain 
select * from test
where j = '3835333344';

+----+-------+------+------+---------+------+-------+
| id | table | type | key  | key_len | rows | Extra |
+----+-------+------+------+---------+------+-------+
|  1 | test  | ref  | j    | 4       |    1 |       |
+----+-------+------+------+---------+------+-------+

우선 순위가 정수형이 높기 때문에 문자열 조건 값은 묵시적으로 정수형으로 형 변환 되므로 실행 시 문제가 되지 않습니다.

문자열 칼럼을 “숫자”로 검색

s 칼럼은 문자열입니다. 이번에는 칼럼이 문자열인 경우에 정수 형 데이터 값으로 조회하는 경우를 테스트해보겠습니다.

explain 
select * from test 
where s = 17424528721665;

+----+-------+------+------+---------+--------+
| id | table | type | key  | key_len | rows   |
+----+-------+------+------+---------+--------+
|  1 | test  | ALL  | NULL | NULL    | 131276 |
+----+-------+------+------+---------+--------+

s 필드에 인덱스나 존재하나 묵시적인 형 변환으로 인하여 풀 테이블 스캔이 발생합니다. 위 테이블 데이터가 크지 않기 때문에 수행 속도가 느리지 않으나, 만약 천만건 이상의 데이터 환경에서 테스트하는 경우라면 엄청난 성능 저하가 발생합니다.

만약 파라메터 바인딩을 사용해서 문자열 칼럼에 정수형을 넣는 경우라면, 소스 상에서는 여간 찾기가 힘듭니다. 물론 MySQL에서 show processlist 명령을 수행하면 바인딩된 변수 값까지 나오지만, 경우에 따라서는 상당히 찾아내기 난해한 경우가 있습니다. 다음과 같은 경우를 예를 들 수 있습니다.

String sql = "select * from test where s = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 2406525903);

Conclusion

사실 이것저것 생각하기 싫다면 MySQL에서 무조건 문자열 조건으로 질의해도 큰 문제는 없습니다. 어차피 정수형이 문자열보다 우선 순위에 있기 때문에 문자열은 묵시적 형변환되기 때문이죠. 하지만 추후 다른 DBMS 로 데이터 이관하는 경우에는 문제가 생길 수 있습니다. 예를 들어 Oracle to PostgreSQL로 테스트 이관을 한 적이 있는데 DB 상의 데이터 타입과 바인딩되는 데이터 타입이 맞지 않으면 예외 처리할 수도 있습니다. (참고로 DB가 아닌 클라이언트 쪽에서 발생한 예외입니다.)

게다가 때로는 예기치 않는 결과 값이 발생할 수도 있습니다. 특히나 문자열 사이에 보이지 않는 문자가 포함된 경우에는.. 그러므로 묵시적 형 변환 함정에 빠지지 말고 반드시 조건절에는 칼럼 타입을 맞춰서 질의하는 것을 강력하게 권고 드립니다.

다른 재미있는 사례로 다시 포스팅하겠습니다.

감사합니다. ^^

 

Maria 2탄 – 진화하는 Maria, 함께하는 MySQL!!

Overview

MySQL 오픈 소스 진영은 더이상 단순 데이터 처리에만 강한 DBMS이기를 거부합니다. 이제는 대용량 처리에 적합하도록 탈바꿈 중입니다.

지금까지 MySQL에서는 단일 쓰레드로 Nested Loop 방식으로 쿼리를 처리하였기 때문에, 조인 건 수가 대형화될 수록 성능이 급속도로 악화되었습니다.

MariaDB는 5.3버전부터 DB 엔진과 스토리지 엔진 간의 데이터 전송이 개선되었고, 조인 시 추가적인 블록 기반의 조인 알고리즘을 제공합니다. 물론 MySQL도 5.6버전부터는 관련 기능을 어느정도 지원합니다.

변화하는 MariaDB에 대해 몇 가지 소개하도록 하겠습니다.

Disk access optimization

1) Index Condition Pushdown

MySQL/MariaDB는 구조적으로 DB 엔진과 스토리지 엔진 역할이 명확하게 구분됩니다.

DB 엔진은 데이터를 처리하여 클라이언트에게 전달하고, 스토리지 엔진은 물리적 장치에서 읽어와 DB 엔진에 전달합니다.

이런 구조이기 때문에 다양한 스토리지 엔진을 가질 수 있다는 확장성이 있지만, 그에 따라 내부적인 비효율이 발생하기도 합니다.

다음과 같은 SQL이 호출된다고 가정합니다. tbl 테이블에는 (key_col1, key_col2)로 인덱스가 구성되어 있습니다.

select * from tbl 
where key_col1 between 10 and 11
and key_col2 like '%foo%';

여기서 데이터를 스토리지 엔진에 전달할 때는 key_col1에 해당하는 조건만 전달할 수 있습니다. key_col2는 문자열 패턴 검색이므로 인덱스 사용에서는 무의미하기 때문이죠.

만약 key_col1 의 between 조건 결과가 100만 건이라고 가정하면, 스토리지 엔진으로 부터 대상 데이터 100만 건을 모두 DB 엔진으로 가져와서 key_col2 유효성을 체크합니다. 그렇기에 스토리지 엔진에서 DB엔진으로 데이터를 전송하는 “Sending Data”에서 비효율이 발생하기도 합니다.

MariaDB None Index Condition Pushdown
MariaDB None Index Condition Pushdown

그러나 MariaDB 5.3.3부터 Index Condition Pushdown 기능이 추가되면서, 인덱스 데이터 구조를 활용하여 한번 더 필터링하여 필요한 데이터만 테이블 데이터에서 읽고 DB 엔진에 전달합니다.

MariaDB Index Condition Pushdown
MariaDB Index Condition Pushdown
위 그림에서는 앞선 그림과는 다르게 오직 한 건만 DB 엔진에 전달합니다. 불필요한 데이터를 DB엔진에 전달하지 않기 때문에 퍼포먼스가 크게 향상되겠죠. (간단한 테스트에서는, 1분이 넘던 쿼리가 1초 내로 처리되었습니다.^^)

단, Index Condition Pushdown 기능이 동작하기 위해서는 위와 같이 “조건을 포함하는 형식”으로 인덱스가 구성이 되어 있어야 합니다.

2) Multi-Record Read

디스크는 데이터를 읽어오는 구조 상 Random Access에 성능이 취약합니다. 데이터를 읽어들이기 위해서는 헤더를 끊임없이 움직여야 하기 때문이죠.

MariaDB에서는 효과적으로 데이터를 긁어오기 위해서 Multi-Record Read 기능을 제공합니다. 필요한 데이터를 Rowid를 기준으로 정렬하여 디스크에 데이터를 요청합니다. Rowid로 데이터가 정렬되었기 때문에 디스크는 Sequential하게 데이터를 읽어오죠. 즉 데이터를 읽기 위해 과도하게 헤더가 움직이지 않아도 된다는 것을 의미합니다.

Multi-Record Read를 간단하게 그림으로 표현하겠습니다.

MariaDB Multi-Record Read
MariaDB Multi-Record Read

인덱스 구조로부터 키가 1,2,4,6,7에 해당하는 결과를 가져와서, 이를 다시 Rowid 기준으로 정렬을 합니다.

그리고 Rowid 기준으로 실제 스토리지 엔진에 데이터를 요청하게 되는데, Rowid 순으로 접근하는 경우 디스크에서 Random Access가 최소화됩니다.

위 그림은 MyISAM 기준이며, InnoDB인 경우 Rowid 역할을 하는 Primary Key 순으로 재정렬하여 데이터를 효과적으로 가져오겠죠.^^

Join Buffer

MariaDB 5.3부터는 조인 버퍼를 기존보다 더욱 효율적으로 사용합니다.

가변형 데이터 타입(Varchar) 경우 최대 문자열보다 부족한 부분에 \0 문자로 채우지 않고, Null 필드 경우 조인 버퍼에 적재를 하지 않고 데이터를 처리합니다. 즉 조인 버퍼 사용 효율이 증대하는 것이죠

Inner Join에서만 사용하던 조인 버퍼를 이제는 Outer Join과 Semi Join에서도 사용할 수 있도록 기능이 개선되었습니다.

1) Incremental Join Buffer

조인 버퍼를 더욱 더 효율적으로 사용하기 위한 새로운 접근입니다.

테이블A, 테이블B, 테이블C 등 세 개의 테이블을 조인하는 경우에는 두 개의 조인 버퍼를 내부적으로 사용합니다.

첫번째 조인 버퍼(테이블A과 테이블B 사이의 조인 버퍼)은 테이블A의 레코드 값을 임시로 저장하고 테이블B와 비교하기 위한 용도로 사용됩니다.

두번째 조인버퍼(“테이블A과 테이블B 결과”와 테이블C 사이의 조인 버퍼)는 앞선 결과 값과 테이블 C 조인을 위해 임시로 데이터를 저장하는 용도로 사용됩니다. 기존까지는 “테이블A와 테이블B 결과”를 “Copy”하면서 두번째 조인 버퍼에 적재하였습니다. 여기서 메모리에는 이중으로 데이터가 적재되는 현상이 발생하고, 비효율 현상이 발생하는 것이죠.

MariaDB incremental join buffer
MariaDB incremental join buffer

그러나 Incremental join buffer 방식에서는 데이터를 복사하지 않고, 위 그림과 같이 테이블A와 테이블B 결과가 저장된 임시 공간에 접근할 수 있는 “포인터” 값만 조인 버퍼에 저장합니다.

즉, “불필요한 데이터 Copy를 제거”하면서 메모리 공간을 더욱 효율적으로 활용할 수 있는 것이죠.

2) Join Buffer with Outer-Join/Semi-Join

MariaDB5.3부터는 Inner-Join 뿐만 아니라 Outer-Join과 Semi-Join에서도 조인 버퍼를 활용합니다.

Outer-Join에서는 조인 버퍼 내부에 “매칭 플래그”, 즉 테이블A가 기준 테이블인 경우 관련 데이터와 매칭되는 여부를 체크하는 플래그가 내부적으로 포함됩니다.

기본적으로 매칭 플래그는 OFF 값으로 세팅되어 있고, 테이블B에서 일치하는 데이터를 찾으면 플래그를 ON으로 변경합니다.

조인 버퍼에서 테이블A와 테이블B 간 데이터 매칭 여부 수행 이후 여전히 OFF값을 플래그로 가지는 필드인 경우, 테이블B에 해당하는 칼럼들은 NULL로 채웁니다.

MariDB Join Buffer with Outer-Join/Semi-Join
MariDB Join Buffer with Outer-Join/Semi-Join

Semi-Join(IN 안의 서브쿼리와 같은 조건)에서도 매칭 플래그가 비슷하게 사용됩니다.

다만 매칭 플래그가 On이 되는 시점에서 관련 데이터를 테이블B에서 더이상 탐색하지 않는다는 점에서 차이점이 있습니다.

Block Based Join Algorithm

1) Block Nested Loop Join

블록 기반의 조인 알고리즘을 소개하기에 앞서, Block Nested Loop Join에 대해 설명하도록 하겠습니다.

테이블A와 테이블B이 있는 상태에서 다음 SQL이 호출된다고 가정합니다.

Select a.r1, b.r2
From TABEL_A a
Inner Join TABLE_B On a.r1 = b.r2
MariaDB Block Nested Loop Join
MariaDB Block Nested Loop Join

테이블A로부터 읽어오면서 조인버퍼가 가득 찰 때까지 채웁니다. 여기서는 연두색 사각형이 조인 버퍼를 가득 채우는 데이터라고 보면 되겠습니다.

조인 버퍼가 가득 채워지면, 테이블B를 스캔하면서 조인 버퍼에 있는 데이터와 매칭되는지 하나하나 체크하고, 매칭되면 조인 결과로 내보냅니다.

조인 버퍼 안의 모든 데이터를 비교하는 과정이 끝나면, 조인 버퍼를 비우고 다시 앞선 과정을 수행합니다. 여기서는 노란 색 사각형 부분입니다.

이러한 과정을 테이블A에서 조인 버퍼에 더이상 데이터를 채울 수 없는 시점, 즉 테이블A 조건에 해당하는 데이터를 모두 처리할 때까지 반복 수행합니다. 여기서 테이블B를 스캔하는 횟수는 조인 버퍼에 데이터가 적재되는 횟수와 동일합니다. 그리고 테이블B 데이터를 스캔할 때는 Full table scan, Full index scan, Range index scan 등으로 데이터에 접근합니다.

2) Block Hash Join

Block Hash Join은 MariaDB 5.3부터 제공하는 새로운 조인 알고리즘입니다.

이 알고리즘은 테이블 간 조인을 동등 비교 시에서 사용됩니다.

다른 조인 알고리즘과 마찬가지로, Block Hash Join에서도 조인 버퍼를 사용하여 테이블 간의 연관성을 체크하지만, 조인 버퍼를 사용하는 방식에서는 약간 다릅니다.

MariaDB Block Hash Join
MariaDB Block Hash Join

테이블A에서 데이터를 읽어와 조인 버퍼에 밀어 넣을 때, 테이블A 조건에 해당하는 해시 값을 내부적으로 생성하고 조인 버퍼에 저장 합니다.

그리고 테이블B에서 조건을 해시값을 통하여 직접 데이터 매칭 여부를 결정하고 결과셋을 생성합니다. 즉 Nested Loop 조인 방식에서는 데이터에 순차적으로 접근해야 하는 것과는 커다란 차이가 있습니다.

조인 버퍼에 별도로 해시 값을 추가 저장하기 때문에, 기존 Block Nested Loop 방식보다는 조인 버퍼에 저장되는 데이터 양이 적으나, 테이블A가 작을수록 혹은 조인 버퍼에 저장되는 데이터 가지 수가 작을 수록 상당한 퍼포먼스를 발휘합니다.

3) Batched Key Access Join

기존의 Block Nested Join에서는 대용량 테이블과의 조인에서는 성능이 크게 떨어질 수밖에 없습니다.

테이블 조인 시 랜덤 Access가 발생하기 때문이죠. 그나마 인덱스를 생성하여 차선책으로 해결할 수는 있겠지만, 완벽한 대안은 아닐 것입니다.

Batched Key Access 조인은 랜덤 Access를 최대한 줄이려는 목적으로 고안된 알고리즘으로, 조인 대상이 되는 데이터를 “미리 예측”함과 동시에 디스크에 저장된 순서대로 데이터를 가져와서 “디스크 접근 효율”을 최대로 늘리자는 데 있습니다.

MariaDB Batched Key Access Join

기본적인 Batched Key Access 조인은 다음과 같습니다.

다른 Block Based Join 알고리즘처럼, Batched Key Access 조인도 첫번 째 피연산자의 레코드 값을 조인 버퍼에 채웁니다.

그리고 조인 버퍼가 다시 채워지면 조인 버퍼 안에 있는 레코드와 매칭이 될 수 있는 값을 조인 테이블로부터 “미리” 찾아냅니다.

MariaDB Batched Key Access Join
MariaDB Batched Key Access Join

조인 버퍼 안에 있는 레코드와 매칭이 될 수 있는 값을 미리 찾아내기 위해서 Multi-Record Read 인터페이스를 호출합니다.

Multi-Record Read는 조인 버퍼 안의 모든 레코드로 구성된 키 값들로 테이블B와 연관된 인덱스 룩업을 수행하고, 테이블B의 레코드를 빠르게 가져오기 위해 Rowid 순으로 데이터를 검색 합니다. 자세한 내용은 상단에 설명되어 있습니다. ^^

그리고 조인 버퍼의 레코드와 “미리 가져온” 테이블B의 데이터를 비교하여 조인 조건이 맞는지를 체크하고 최종적으로 결과값으로 출력하는 것이죠.

Conclusion

물론 위에서 소개한 기능은 대부분 상용 DBMS에서 구현되어 있습니다. 그리고 그동안은 MySQL DB 엔진 태생적인 문제로 단순 데이터 처리 혹은 작은 데이터 조각만을 취급하는 소규모 DBMS로 인식되어 왔던 것이죠. 또한 옵티마이저 기능이 여전히 좋지 않기 때문에, 쿼리 작성 시에도 상당한 노력을 기울여야 최상의 퍼포먼스가 나옵니다.

하지만, 점차적으로 기능이 개선됨에 따라 MariaDB혹은 MySQL을 통해서도 얼마든지 어느정도의 대용량 데이터를 처리할 수 있는 모습으로 변모하고 있습니다.

더이상 DB 태생적인 한계점이 사라진다는 점에서 앞으로 MySQL 오픈소스 진영의 다음 행보가 상당히 기대됩니다.

<참고자료>
http://kb.askmonty.org/en/what-is-mariadb-53/
http://kb.askmonty.org/en/index-condition-pushdown/
http://kb.askmonty.org/en/block-based-join-algorithms/#batch-key-access-join
http://kb.askmonty.org/en/multi-range-read-optimization/
http://assets.en.oreilly.com/1/event/2/Batched%20Key%20Access_%20a%20Significant%20Speed-up%20for%20Join%20Queries%20Presentation.ppt