1. Virtual Columns
MariaDB 5.2부터 지원되는 Virtual Columns은 가상의 칼럼을 둬서 수식과 조건문을 사용해 데이터의 가공 결과를 저장하는 것을 말한다. 사용 시 PERSISTENT(stored)와 VIRTUAL(generated-only)이라는 두 가지 타입이 존재한다. 디폴트는 PERSISTENT다. PERSISTENT virtual columns은 실제 데이터가 데이터베이스에 저장되는 특성을 갖는다. 반면 VIRTUAL virtual columns은 실제 데이터가 데이터베이스에 저장되지 않고 그때 그때 계산돼 보여주는 역할을 한다. 자, 그럼 다음의 예제를 통해 Virtual Columns을 좀더 쉽게 알아보자.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;"> MariaDB [test]> create table salesuser ( -> id int unsigned not null auto_increment primary key, -> user_id int unsigned not null, -> create_date datetime not null default 0, -> cost int not null default 0, -> sold decimal(15,2) not null default 0, -> day_avg decimal(15,2) not null default 0, -> day_position int not null default 0, -> cost_mod int as (cost mod 10) persistent, -> bonus_amount decimal(15,2) as ( -> case when sold > day_avg and day_position between 1 and 5 -> then 0.2 * (sold - day_avg) #20% bonus -> when sold > day_avg and day_position > 5 -> then 0.05 * (sold - day_avg) #5% bonus -> else 0 -> end) virtual); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into salesuser(user_id, create_date, cost, sold, day_avg, day_position) values (1, curdate(), 105, 300, 150, 3); Query OK, 1 row affected (0.00 sec) MariaDB [test]> select * from salesuser; +----+---------+--+------+--------+---------+--------------+----------+--------------+ | id | user_id |..| cost | sold | day_avg | day_position | cost_mod | bonus_amount | +----+---------+--+------+--------+---------+--------------+----------+--------------+ | 1 | 1 |..| 105 | 300.00 | 150.00 | 3 | 5 | 30.00 | +----+---------+--+------+--------+---------+--------------+----------+--------------+ 1 rows in set (0.00 sec) </xmp>
cost_mod와 bonus_amount 칼럼이 Virtual Columns이라는 것을 눈으로 쉽게 확인할 수 있다. Virtual Columns은 제약 사항도 존재하므로 잘 살펴보고 올바르게 사용하자.
- 하나의 Virtual Column에서 사용된 칼럼은 다른 Virtual Column에서는 사용할 수 없다.
- 상수 표현식은 사용할 수 없다.
- 서브쿼리나 행이 외부 데이터에 의존하는 것은 허용되지 않는다.
- UDF나 Stored Functions은 사용할 수 없다.
- InnoDB, Aria, MyISAM 스토리지 엔진에서만 Virtual Columns을 지원한다.
- primary keys는 지원하지 않는다.
- 인덱스도 PERSISTENT virtual columns에서만 지원된다.
2. Dynamic Columns
MariaDB 5.3부터 지원하고 관계형 DB에서는 지원하지 않는 스키마리스의 형태로, 테이블의 매 행마다 서로 다른 종류의 칼럼 군을 유연하게 저장할 수 있도록 해 준다. 처리방식은 blob 타입에 다양한 칼럼들을 넣는 방식이다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">MariaDB [test]> create table store (id int auto_increment primary key, -> name varchar(40), -> type enum ("shirt", "phone", "computer"), -> price decimal(10,2), -> dynstr mediumblob); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> INSERT INTO store (name, type, price, dynstr) values -> ("Funny shirt", "shirt", 10.0, COLUMN_CREATE(1, "blue", 10, "XL")), -> ("nokia", "phone", 649, COLUMN_CREATE(1, "black", 2, "touchscreen")), -> ("htc Desire hd", "phone", 579, COLUMN_CREATE(1, "black", 3, "Android")), -> ("BM/Lenovo Thinkpad X60s", "computer", 419, COLUMN_CREATE(1, "black", 3, "Linux")); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 # 기본 데이터 조회 MariaDB [test]> SELECT id, name, type, price, length(dynstr) as len, column_list(dynstr) as list FROM store; +----+-------------------------+----------+--------+------+------+ | id | name | type | price | len | list | +----+-------------------------+----------+--------+------+------+ | 1 | Funny shirt | shirt | 10.00 | 17 | 1,10 | | 2 | nokia | phone | 649.00 | 27 | 1,2 | | 3 | htc Desire hd | phone | 579.00 | 23 | 1,3 | | 4 | BM/Lenovo Thinkpad X60s | computer | 419.00 | 21 | 1,3 | +----+-------------------------+----------+--------+------+------+ 4 rows in set (0.02 sec) # 1번 키가 black인 데이터 조회 MariaDB [test]> SELECT name FROM store WHERE COLUMN_GET(dynstr, 1 as char(10)) = "black"; +-------------------------+ | name | +-------------------------+ | nokia | | htc Desire hd | | BM/Lenovo Thinkpad X60s | +-------------------------+ 3 rows in set (0.00 sec) #1번 키가 black인 데이터 값을 read로 수정 MariaDB [test]> UPDATE store set dynstr=COLUMN_ADD(dynstr, 1, "red") WHERE COLUMN_GET(dynstr, 1 as char(10)) = "black"; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 #store 첫번째 키 데이터 조회 MariaDB [test]> SELECT name, COLUMN_GET(dynstr, 1 as char(10)) FROM store WHERE COLUMN_EXISTS(dynstr, 1); +-------------------------+-----------------------------------+ | name | COLUMN_GET(dynstr, 1 as char(10)) | +-------------------------+-----------------------------------+ | Funny shirt | blue | | nokia | red | | htc Desire hd | red | | BM/Lenovo Thinkpad X60s | red | +-------------------------+-----------------------------------+ 4 rows in set (0.00 sec) #count(*) 사용 예 MariaDB [test]> SELECT COLUMN_GET(dynstr, 1 as char(10)) as colour, count(*) FROM store group by COLUMN_GET(dynstr, 1 as char(10)); +--------+----------+ | colour | count(*) | +--------+----------+ | blue | 1 | | red | 3 | +--------+----------+ 2 rows in set (0.00 sec) </xmp>
Dynamic Columns은 다음과 제약 사항을 갖고 있다.
- Columns은 숫자로만 참조가 된다.
- Columns은 0-65535 내의 수를 사용할 수 있다.
- dynamic column object의 최대 크기는 536,870,911 바이트다.
- 인덱스를 사용하려면 virtual column을 이용해야 한다.
3. Table Elimination
Table Elimination는 5.1버전부터 지원하기 시작했으며, 테이블 조인 시 불필요한 테이블의 데이터에 접근하지 않도록 하는 것이 주된 기능이다. 핵심 개념을 <그림 1>을 통해 알아보자.
<그림 1>과 같이 anchor modeling에 의해 Actor는 이름, 생일, 인기도 이라는 Attribute를 가진다. 해당 Attribute들은 데이터베이스에 저장되는 테이블 단위가 된다. 필요한 정보를 호출하기 위해 관련 칼럼들의 조인을 통해, 더 나아가 조인 쿼리의 반복으로 쓰는 번거로움을 피하기 위해 View를 만들어 쿼리를 실행하게 된다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">-- the 'anchor' table which has only synthetic primary key create table ac_anchor(AC_ID int primary key); -- a table for ‘name’ attribute: create table ac_name(AC_ID int, ACNAM_name char(N), primary key(AC_ID)); -- a table for ‘birthdate’ attribute: create table ac_dob(AC_ID int, ACDOB_birthdate date, primary key(AC_ID)); -- a table for ‘rating’ attribute, which is historized: create table ac_rating(AC_ID int, ACRAT_rating int, ACRAT_fromdate date, primary key(AC_ID, ACRAT_fromdate)); -- 조회 actors, with their names and current ratings select ac_anchor.AC_ID, ACNAM_Name, ACDOB_birthdate, ACRAT_rating from ac_anchor left join ac_name on ac_anchor.AC_ID=ac_name.AC_ID left join ac_dob on ac_anchor.AC_ID=ac_dob.AC_ID left join ac_rating on (ac_anchor.AC_ID=ac_rating.AC_ID and ac_rating.ACRAT_fromdate = (select max(sub.ACRAT_fromdate) from ac_rating sub where sub.AC_ID = ac_rating.AC_ID)) -- 뷰테이블 생성 create view actors as select ac_anchor.AC_ID, ACNAM_Name, ACDOB_birthdate, ACRAT_rating from ac_anchor left join ac_name on ac_anchor.AC_ID=ac_name.AC_ID left join ac_dob on ac_anchor.AC_ID=ac_dob.AC_ID left join ac_rating on (ac_anchor.AC_ID=ac_rating.AC_ID and ac_rating.ACRAT_fromdate = (select max(sub.ACRAT_fromdate) from ac_rating sub where sub.AC_ID = ac_rating.AC_ID)) </xmp>
뷰 테이블을 호출하면, 먼저 옵티마이저는 데이터를 머지하는데 이때 불필요한(머지할 데이터가 없는) 테이블은 참조에서 제외해 성능 향상을 보게 한다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">MySQL [test]> explain select ACRAT_rating from actors where ACNAM_name=’Gary Oldman’; +--+――――――-------------+―――--------+――------+―+―――---------―――――--+ |id| select_type | table | type |.| ref | Extra | +―-+――――――------------？+―――-------？+――------+―+―――-----------------―-+ | 1| PRIMARY | ac_anchor | index |.| NULL | Using index | | 1| PRIMARY | ac_name | eq_ref |.| test.ac_anchor.AC_ID | Using where | | 1| PRIMARY | ac_rating | ref |.| test.ac_anchor.AC_ID | | | 3| DEPENDENT SUBQUERY| sub | ref |.| test.ac_rating.AC_ID | Using index | +―-+―――――----―---------+―――---------+――？-----+―+―――-----------――――+ 4 rows in set (0.01 sec) </xmp>
위의 샘플을 통해 쿼리 플랜을 보면 ac_dob 테이블은 참조가 일어나지 않았음을 보여주고 있다. 이렇듯 불필요한 조인을 없애는 것이 Table Elimination의 주요 역할이다.
4. Join Optimizations
4.1. Batched Key Access
Batched Key Access 조인은 5.3버전부터 지원하는 기능으로, 랜덤 액세스를 최대한 줄이려는 목적으로 고안된 알고리즘이다. 조인 대상이 되는 데이터를 미리 예측하고 이를 기반으로 디스크에 저장된 순서대로 데이터를 가져와 디스크 접근 효율을 최대로 올리는 데 목적이 있다.
<그림 2>에서 보듯이 전통적인 Nested Loop Join은 첫 번째 테이블(Country)에서 예측되는 로우를 조회하고, 인덱스 룩업을 통해 선택된 각 행들은 두번 째 테이블에서 참조되는 데이터를 읽어 온다. 만약에 데이터가 대용량이라면 인덱스 LookUp도 많아지게 되고 이는 랜덤 액세스가 많아 진다는 의미도 되기 때문에 성능 문제를 야기한다.
이와 같은 단점을 극복하고자 Batched Key Access가 탄생했다. 첫 번째 테이블(Country)의 레코드들을 Join Buffer에 넣고, Join Buffer 안의 레코드와 매칭될 수 있는 값을 미리 찾아내기 위해 MRR(Multi-Record Read) 인터페이스를 통해 두 번째 테이블(City)의 참조 데이터를 룩업한다. 이때 Multi-Record Read는 Join Buffer 안의 모든 레코드로 구성된 키 값들을 갖고, 두 번째 테이블과 연관된 인덱스 룩업을 수행한다. 두 번째 테이블의 레코드를 빠르게 가져오기 위해 Rowid 순으로 순차 데이터 검색을 한다. 조인 버퍼의 레코드와 ‘미리 가져온’ 두 번째 테이블의 데이터를 비교해 조인 조건이 맞는지를 체크하고 최종적으로 결과값으로 출력하는 것이다. 일반 쿼리 Nest Loop Join과 Batched Key Access의 쿼리 플랜을 보면 다음과 같다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;"> --MySQL에서 Nest Loop Join Plan mysql> select * from City, Country where Country.Continent='Europe' and City.CountryCode=Country.code; +----+---------+--+-------------+-+--------------+-+----------------------+ | id | table |. | key |.| ref |.| Extra | ..-+-----------+--+-------------+-+--------------+------------------------+ | 1 | Country |. | Continent |.| const |.| Using index condition| | 2 | City |. | CountryCode |.| Country.Code |.| | +----+---------+--+-------------+-+--------------+-+------+---------------+ 2 row in set (0.01 sec) -- MariaDB에서 Batched Key Access MariaDB [test]> set optimizer_switch='batched_key_access=on'; MariaDB [test]> select * from City, Country where Country.Continent='Europe' and City.CountryCode=Country.code; +----+---------+--+-------------+-+--------------+-+----------------------+ | id | table |. | key |.| ref |.| Extra | ..-+-----------+--+-------------+-+--------------+------------------------+ | 1 | Country |. | Continent |.| const |.| Using index condition| | 2 | City |. | CountryCode |.| Country.Code |.| Using join buffer | +----+---------+--+-------------+-+--------------+-+------+---------------+ 2 row in set (0.01 sec) </xmp>
4.2. Block hash join
5.3이상부터 지원되는 새로운 조인 알고리즘이다. Block Hash Join에서도 Join Buffer를 사용해 테이블 간의 연관성을 체크하지만, 조인 버퍼를 사용하는 방식에서는 차이가 있다. 즉, Block Hash Join에서는 Join Buffer의 맨 끝 부분에 해시 키를 사용해 해시 테이블을 구축한다.
작동 방식은 다음과 같다. 첫 번째 테이블(Table A)에 조건에 만족하는 레코??으로 해시 매커니즘을 통해 생성된 해시 키가 각 레코드에 매핑되어 Join Buffer에 저장된다. 그리고 두 번째 테이블(Table B)에서는 조인 키(해시함수가 적용된)를 기준으로 조인될 데이터를 찾게 되는 방식이다.
5. Disk access optimization
5.1. Index Condition Pushdown
Index Condition Pushdown은 MariaDB 5.3.3에서 지원되는 기능이다. B-Tree 인덱스를 통해 테이블 데이터에 Access하는 방식에 적용되는 최적화 기능이다. 이 방식은 WHERE 조건절에서 인덱스에 접근하자마자 참조하는 인덱스 필드(Pushed Index Condition라 불리우는)를 체크하는 방식으로 이뤄진다. Pushed Index Condition을 만족하지 않으면 전체 테이블을 읽을 필요가 없다는 원리다.
<그림 5>에서 보듯이 디스크 기반의 스토리지 엔진에서 원하는 조건의 데이터를 돌려받기 위해 MySQL 내부에서는 해당 인덱스에 매칭되는 레코드를 읽어(index lookup), 관련된 실제 테이블 레코드를 가져오게 된다. 이어서 조건이 여러 개 있을 경우에는 유효성 검사를 하는 과정에 스토리지 엔진 상의 데이터를 DB 엔진으로 가져와야 하는 비효율적인 과정이 따른다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">MariaDB [test]> set optimizer_switch='index_condition_pushdown=off'; MariaDB [test]> explain select * from tbl where key_col1 between 10 and 11 and key_col2 like '%foo%'; +----+-------------+-------+-------+---+----------+-----+------+---------------+ | id | select_type | table | type | . | key | .. | rows | Extra | +----+-------------+-------+-------+---+----------+-----+------+---------------+ | 1 | SIMPLE | tbl | range | . | key_col1 | .. | 2 | Using where | +----+-------------+-------+-------+---+----------+-----+------+---------------+ 1 row in set (0.01 sec) </xmp>
위 쿼리를 실행할 경우, key_col1의 인덱스 조건을 타고 스토리지 엔진에서 DB 엔진으로 데이터를 가지고 와서 key_col2라는 조건을 만족하는지 확인하는 유효성 체크를 한다. 이때 key_col2 조건을 만족하지 않는 데이터를 가져올 가능성이 많아 비효율성이 내포돼 있다고 표현했다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">set optimizer_switch='index_condition_pushdown=on'; MariaDB [test]> explain select * from tbl where key_col1 between 10 and 11 and key_col2 like '%foo%'; +----+-------------+-------+-------+----------+--+------------------------------------+ | id | select_type | table | type | key | .| Extra | +----+-------------+-------+-------+----------+--+------------------------------------+ | 1 | SIMPLE | tbl | range | key_col1 | .| Using index condition; Using where | +----+-------------+-------+-------+----------+--+------------------------------------+ 1 row in set (0.01 sec) </xmp>
자, 그럼 Index Condition Pushdown 기능이 추가된 프로세스를 살펴보자. 쿼리의 첫번째 조건인 key_col1의 인덱스에 레퍼런스된 모든 레코드들을 가져오는 것이 아니라, key_col2라는 조건이 Index condition 체크가 푸시돼 앞의 예와 달리 오직 한 건의 데이터만 스토리지에서 DB 엔진으로 가져오게 된다. 이로써 불필요한 데이터를 DB 엔진으로부터 데이터를 로드하지 않기 때문에 전체적인 성능 향상을 가져올 수 있다.
5.2. Multi-Record Read
MySQL이든, NoSQL이든 디스크 상의 데이터를 Random Access하는 방식이라면 성능상 아주 취약 부분이 된다. 원하는 데이터에 접근하기 위해 디스크 헤더가 끊임없이 왔다 갔다 해야 하기에 성능이 저하되기 쉽다. 이러한 단점을 극복하는 차원에서 MariaDB는 Multi-Record Read 방식을 통해 로우 아이디 기준으로 데이터를 정렬, 디스크 접근 시에 시퀀스 액세스를 한다.
기존의 일반적인 MySQL의 데이터 접근 방식은 다음과 같다. 우선 원하는 데이터를 찾기 위해 인덱스를 찾는다. 이어서 해당 인덱스에 레퍼런스된 테이블의 레코드를 찾아 디스크에 액세스하는 형태로 작동한다. <그림 7>의 파란색 번호처럼 실제 디스크에 접근할 때에는 1, 3, 2, 4 순으로 랜덤하게 헤더를 움직여 데이터를 조회한다. 조회되는 데이터 범위와 건수가 클 경우 상당한 성능 저하가 발생한다. 실제 1만 RPM의 디스크는 실제 초당 167번 탐색할 수 있는데, 최악의 경우 초당 167번을 모두 사용하게 된다.
Multi-Range-Read는 <그림 8>과 같이 rowid로 소팅해 시퀀셜하게 디스크에 액세스해 성능을 높이는 것이다. Multi Range Read가 활성화되면 아래의 plan처럼 Rowid-ordered scan이 사용된다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">MariaDB [test]> explain select * from tbl where tbl.key1 between 1000 and 2000; +------------+-------+-------+-----+----+-------------------------------------------+ | select_type|table | type | key | ..| Extra | +------------+-------+-------+------+---+-------------------------------------------+ | SIMPLE | tbl | range | key1 | ..|Using index condition; Rowid-ordered scan | +------------+-------+------------------+-------------------------------------------+ 1 row in set (0.03 sec) </xmp>
이렇듯 시퀀셜 디스크 리드가 빠른 이유는 다음과 같다.
- 디스크 헤드가 앞뒤로 움직임이 적어 검색 속도가 빠르다.
- 다양한 수준의 디스크 블록을 읽을 때, 곧이어 읽을 가능성이 높은 블록을 미리 읽는 Prefetch 기능을 이용할 수 있다.
- 각 디스크 페이지는 정확하게 한 번은 읽혀진다. 이것은 동일한 페이지를 읽기 위해 저장된 디스크 버퍼를 의존하지 않아도 된다는 의미다.
6. Subquery Optimizations
6.1. Semi-Join
보통 MySQL에서 쿼리는 Outer(people)에서 Inner(SubQuery - big_yachts ) 순으로 실행된다. 다음 쿼리를 실행했을 때, <그림 9>처럼 실행될 것이다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">SELECT * FROM people WHERE name IN (SELECT owner FROM big_yachts WHERE cost> 50M) </xmp>
하지만 MariaDB 5.3은 “SET optimizer_switch='semijoin=on'“ 설정을 통해 inner(SubQuery - big_yachts)에서 outer(people)로 실행되도록 지원한다. 실제 처리 흐름은 <그림 10, 11>의 프로세스처럼 흐른다. Semi-Join을 설정할 때는 대개 중복 제거 매커니즘을 같이 선택해 성능 향상을 꾀한다. 중복 제거 매커니즘은 아래와 같이 두가지 방법이 있다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">set optimizer_switch = 'semijoin = on, firstmatch = on, materialization = on, loosescan = on'; SELECT * FROM people WHERE name IN (SELECT owner FROM big_yachts WHERE cost> 50M) </xmp>
첫 번째 방법은 조인 전에 중복 제거 메커니즘인 Materialization, Loose scan을 선택해 처리하는 방법이다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">set optimizer_switch = 'semijoin = on, firstmatch = on, duplicateweedout = on'; SELECT * FROM people WHERE name IN (SELECT owner FROM big_yachts WHERE cost> 50M) </xmp>
두 번째 방법은 조인 후에 중복 제거 메커니즘을 사용하는 것으로, Duplicate Weedout 방식이 있다. Semi-Join에서 사용할 전략에 대해서는 아래에 기술한다
6.2. FirstMatch 전략
FirstMatch 전략은 처음으로 조건에 일치하는 것이 발견되면, 다음 데이터로 넘어가는 방식을 말한다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">select * from Country where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) and Country.continent='Europe' </xmp>
<그림 12>에서 Germany는 두 개의 big cities를 갖고 있다. 따라서 query output이 두 개로 중복돼 나온다. 이는 마지막 도시는 안 거쳐도 된다는 의미가 된다. 따라서 <그림 13>처럼 FirstMatch 전략이 필요하다.
Berlin에서 바로 Germany를 거쳐 France로 넘어가므로 추가 중복작업을 하지 않게 된다.
6.3. LooseScan 전략
LooseScan 전략은 조인 전에 중복을 제거하는 것이다. 특별하게 오더링은 필요 없고, 필요 시 중복 없는 내용으로 사전에 그룹화하는 것을 말한다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">select * from Country where Country.code in (select country_code from Satellite) </xmp>
<그림 14>에서 보듯이 각 위성은 국가별로 그룹화돼 있기 때문에 한 나라의 위성은 다른 나라와 섞이지 않는다. 이로써 위성 기준으로 국가 목록을 중복 없이 얻을 수 있다.
6.4. Materialization 전략
Materialization은 조인 전에 중복을 제거하는 매커니즘이다. SubQuery의 중복을 제거한 결과물을 materialized temporary table이라는 가상의 테이블을 만들어 그 테이블과 조인해 중복을 제거하는 방식이다. <그림 15>처럼 Country 테이블과 materialized temporary table을 조인해 가장 비용이 싼 방법으로 원하는 결과 값을 가져오게 된다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">select * from Country where Country.code IN (select City.Country from City where City.Population > 7*1000*1000) and Country.continent='Europe' </xmp>
<그림 15>는 Materialization 전략을 나타낸 것이다. materialized temporary table(City.country를 채운)의 조인 전략은 두 가지 방법이 있다. 첫 번째는 materialized temporary table에서 Country 테이블로 진행하는 방식으로 Materialization-scan이라고도 부른다. materialized temporary table을 Full Scan해서 Country 테이블과 조인하는 방법이다. 두 번째는 Country 테이블에서 materialized temporary table로 진행하는 방식으로 Country의 Primary key로 materialized temporary table과 조인해 검색하는 방법이다. 이 방법은 Primary Key에서 중복이 없으므로 가장 싼 방법으로 결과물을 가져올 수 있다. 다시 말해 최적의 중복 제거는 Primary Key 사용이라는 점을 알아두자.
6.5. DuplicateWeedout 전략
Duplicate Weedout은 조인 후 중복을 제거하는 매커니즘으로, Primary Key의 속성을 이용해 중복을 제거하는 방식이다.
<xmp style="padding: 10px; border: 1px solid rgb(204, 204, 204); background-color: rgb(255, 255, 204); vertical-align: middle; color: rgb(138, 138, 138); line-height: 14px;">select * from Country where Country.code IN (select City.Country from City where City.Population > 0.33 * Country.Population and City.Population > 1*1000*1000); </xmp>
<그림 16>처럼 일반적인 Inner join에서는 Germany가 3개 출력되는 것처럼 데이터 중복이 많이 발생한다.
그러나 <그림 17>은 Primary Key를 가진 temporary table을 만들고, Primary Key의 제약조건에 의해서 중복이 자동적으로 제거되는 형상이다.
7. Group commit for the binary log
MySQL을 사용하는 대부분의 환경에서는 리플리케이션을 적용해 트래픽을 분산한다. 이는 Slave의 데이터 동기화가 느려지는 원인으로 작용할 수 있다. ‘Group commit for the binary log’는 리플리케이션의 안정성 확보 차원에서도 중요한 기능이다. Group commit은 바이너리 로그와 스토리지 엔진 사이에서 커밋 성능과 리플리케이션 안정성 확보에 도움이 된다. MySQL의 보통 커밋 구조는 <그림 18>과 같다.
클라이언트의 커밋 요청을 받으면 MySQL 서버는 ACID만족 시키기 위해 순차적으로 단일 쓰레드로 커밋 처리를 해 지연이 생길 수 있다. 하지만, MySQL 5.6과 MariaDB에서는 그룹 커밋을 지원한다.
그룹 커밋을 지원하면, 먼저 온 커밋을 처리하는 동안 다음 커밋이 들어왔을 경우 한 번의 디스크 동기 쓰기를 통해 처리를 하고, 동시에 응답을 줄 수 있어서 성능 개선을 가져올 수 있고 동기화 딜레이 요소도 개선을 가져올 수 있다.
실제 Facebook에서 성능을 테스트한 결과지만, MariaDB 5.3이 가장 우수하다는 결과이자, group commit이 성능과 리플리케이션 안정성에 도움을 줄 수 있다는 얘기다. 다음은 MariaDB의 스케일 아웃 관점에서 Cluster 제품과 Replication 제품에 대해 살펴본다.
8. Galera Cluster
MariaDB/Galera는 MariaDB의 Synchronous 방식으로 동작하는 다중 마스터 클러스터다. Galera 라이브러리를 사용해 노드 간 데이터를 복제한다. MariaDB에서 스케일 아웃 관점의 제품이라고 보면 된다. 기술 측면에서 Cluster는 Replication용으로 구현된 Galera library를 사용했다. Galera Cluster 이해를 위해 특징, 장점, 한계점 등을 소개하면 다음과 같다.
8.1. 특징
- 동기 방식의 데이터 복제
- Active-Active 방식의 다중 마스터 구성
- 모든 노드에서 읽기/쓰기 가능
- 클러스터 내 노드 자동 컨트롤
- 특정 노드 장애 시 자동으로 해당 노드 제거
- 자동으로 신규 노드 추가 가능
- 행 단위로 완벽한 병렬적 리플리케이션
- 기존의 MySQL 클라이언트 방식으로 동작
8.2. 장점
- 마스터/슬레이브 간 데이터 동기화 지연 없음
- 트랜잭션 유실이 없음
- 읽기/쓰기 모두 확장이 가능
- 클라이언트에 대기 시간이 길지 않음
8.3. 한계점
- 알파 버전
- 신규 노드 추가 시 모든 데이터 복제가 일어나 부하 발생
- 서버 간 그룹 커뮤니케이션 부하 발생 ？ 쓰기 확장의 한계점 존재
- 모든 노드에 동일한 데이터를 유지해야 하기 때문에 저장 공간 낭비
9. Galera Replication
Galera replication에서의 복제는 커밋 타임에 트랜잭션에 대한 Write Set(데이터베이스에 쓰기 단위, 트랜잭션의 기록하는 모든 논리적인 데이터 집합)을 브로드캐스트해 wsrep API가 노드 간 통신을 통해 처리한다.
9.1. 동기 vs. 비동기 복제
먼저 동기 방식과 비동기 방식의 차이점을 살펴보면 다음과 같다. 동기 방식은 특정 노드에 데이터 변경이 발생했을 때 모든 노드에 데이터 트랜잭션을 보장하는 방식이다. 비동기 방식은 마스터 노드에서 발생한 트랜잭션이 슬레이브 노드에 동시 적용되는 것을 보장하지 않는다. 일정 시간 딜레이가 발생할 수도 있다. 그리고 마스터 노드가 다운됐다면 슬레이브 노드의 데이터는 일부 유실됐을 수도 있다.
비동기 방식과 비교해 동기 방식의 장점은 다음과 같다.
- 노드 장애 시에도 데이터 유실 없이 높은 가용성 달성
- 트랜잭션은 모든 노드에서 병렬적으로 실행
- 클러스트 내 모든 노드 간 데이터 일관성 보장
하지만 동기 복제 방식을 수행하기 위해서는 "2-phase commit"이나, 매우 느리게 동작하는 분산 잠금 기능도 구현돼 있어야 한다. 그렇더라도 확장성과 가용성을 보강하기 위해 복잡한 기능 구현과 낮은 성능 등으로 인해 여전히 비동기 방식의 리플리케이션이 널리 이용되고 있다. MySQL과 PostgreSQL이 비동기 방식으로만 데이터 복제가 이뤄지는 것 또한 그와 같은 이유 때문이다.
9.2. Certification Based Replication Method
성능 저하 없이 Synchronous하게 데이터베이스 리플리케이션을 구현하기 위해 ‘Group Communication and Transaction Ordering techniques’이라는 새로운 방식이 고안됐다. 이것은 많은 연구자들이 제안했던 방식(Database State Machine Approach and Don’t Be Lazy, Be Consistent)으로, 프로토타입을 구현해본 결과 상당한 발전 가능성을 보여줬다. Galera Replication은 높은 가용성과 성능이 필요한 애플리케이션에서 매우 쉽고, 확장 가능한 Synchronous 방식의 리플리케이션을 제공한다. 다음과 같은 특징을 갖고 있다.
- 높은 가용성
- 높은 투명성
- 높은 확장성(애플리케이션에 따라 선형적인 확장까B>
Galera 리플리케이션 기능은 공유 라이브러리처럼 구현돼 있고, wsrep API hook으로 동작하는 시스템이라면 어떠한 트랜잭션과도 연동돼 동작할 수 있는 구조다.
Galera 리플리케이션 라이브러리는 다음의 항목들로 구성됐다.
- wsrep API : DBMS와 replication provider를 책임지는 인터페이스
- wsrep hooks : DBMS에 통합된 wsrep
- Galera provider : wsrep API로 구현돼 있음
- Certification : write sets을 준비하고 인증 수행
- Replication : 리플리케이션 프로토콜을 관리하고 오더링 제공
- GCS framework : group communication systems용 플러그인
4. 결론
MySQL과 상이한 MariaDB의 새로운 기능이나 개선된 기능을 살펴본 결과 MySQL보다는 확연히성능적으로 개선됐다는 느낌을 줬다. 그리고 지속적인 안정화 작업을 한다면 비즈니스에 맞게 적용할 수 있는 날이 올 것으로 전망된다. 또한 MySQL이 오라클에 귀속됨에 따라 오픈소스 제품으로서 그 미래가 불확실하다는 점도 PostgreSQL과 더불어 MariaDB의 존재 이유가 되지 않을까 한다.
