MySQL Heatwave를 살펴보았습니다

Posted on 2022-05-23 | by gywndi

Overview

안녕하세요. 너무 오랜만에 글을 올려봅니다. 올해도 벌써 반이 훌쩍지나버렸네요.

MySQL을 쓰시는 분들, 아니 RDBMS를 써오시던 분들의 가장 가려운 부분은 개인적으로 통계 쿼리 수행 속도라고 봅니다. 특히나 데이터 사이즈가 하루가 다르게 폭발적으로 증가해가는 상황에서 너무나도 반가운 소식이라고 봅니다. HTAP(Hybrid transactional/analytical processing) 구현이라 하는데..

오늘 이 포스팅에서는 서비스 활용 관점으로 Heatwave를 이야기해보도록 하겠습니다.

MySQL Heatwave는?

Oracle Cloud에서 제공하는 OLAP 분산 클러스터로, MySQL의 InnoDB데이터를 자동으로 Heatwave 클러스터로 동기화하여, 제공하는 스토리지 엔진 플러그인입니다.

Content is described in the surrounding text.

그림을 보면. 결국 HeatWave는 HeatWave Cluster와 HeatWave storage engine 두 가지로 분류해볼 수 있겠네요. 마치. MySQL ndb cluster와 같이. 🙂

HeatWave storage engine
- 스토리지 엔진(SECONDARY 엔진)으로, MySQL서버와 HeatWave Cluster간 데이터의 통신 역할
- 사용자의 쿼리가 실행되는 이것을 Heatwave쪽으로 Query pushdown 하여 쿼리를 실행
HeatWave Cluster
- 데이터를 저장 및 프로세싱하는 분산 노드의 집합.
- Oracle cloud 내부에 구성된 HeatWave Storage 레이어에 데이터에 저장.
- InnoDB 데이터와 다른 저장소에 저장이 됨.

MySQL을 통해 들어온 쿼리를 HeatWave Storage 엔진을 통해 HeatWave Cluster에 Query pushdown하여 전달하여 분산 컴퓨팅을 한 후 쿼리 결과를 사용자에게 다시 제공을 해주는?? 기본적으로 모든 데이터 변경은 InnoDB Only라는 측면에서는 다른듯 하네요. 구조상으로는 ndb cluster와 유사한것 같은데.. 트랜잭션 상으로는 미묘하게 다른 느낌이 오네요.

MySQL InnoDB 스토리지 엔진과 직접적으로 연계하여 데이터를 처리해볼 수 있는 분석 용도의 분산 Column-Oriented Database 이라고 봐도 무관해보이네요.

Using HeatWave

거창하게 별도의 테이블을 만들어서 진행한다기 보다는.. 아래와 같이 ALTER 구문을 통해서 설정을 해볼 수 있다 하네요.

## 64KB 넘는 컬럼 제외
mysql> ALTER TABLE orders MODIFY description BLOB NOT SECONDARY;

## SECONDARY_ENGINE 엔진 지정
mysql> ALTER TABLE orders SECONDARY_ENGINE = RAPID;

## 데이터 복사(InnoDB->Heatwave)
mysql> ALTER TABLE orders SECONDARY_LOAD;

이 일련의 과정을 거치면, 이후부터는 서비스에서 사용하는 InnoDB변경 내역이 백그라운드에서 자연스럽게 Heatwave 데이터노드로 동기화 될 것이고, 이 내용을 기반으로 바로 OLAP쿼리를 수행해볼 수 있습니다. 만약 Heatwave에서 지원하는 쿼리만 사용한다고 했을 시에.. 사용자는 분석을 위해 별도의 데이터소스를 통하지 않고도, MySQL 데이터 소스 하나만으로도 원하는 결과를 깔끔(?)하게 도출해볼 수 있겠습니다.

## secondary 사용할래~ (Heatwave 사용할래)
SET SESSION use_secondary_engine=ON;

## secondary 사용할래~ (InnoDB에서 데이터 처리할래)
SET SESSION use_secondary_engine=OFF;

HeatWave에서 데이터를 제거하고 싶으면, 아래와 같이 SECONDARY_UNLOAD를 실행하면 됩니다.

깔끔하게 테이블도 그냥 InnoDB로만 변경하고자 한다면, 아래와 같이 SECONDARY_ENGINE을 NULL로 지정해야 합니다. 만약 Truncate 혹은 DDL작업이 필요하다면. 반드시 SECONDARY_ENGINE을 NULL로 변경해야 작업 수행할 수 있습니다.

## HeatWave Cluster에서 데이터 제거
mysql> ALTER TABLE orders SECONDARY_UNLOAD;

## SECONDARY_ENGINE 사용 비활성화
mysql> ALTER TABLE orders SECONDARY_ENGINE = NULL;

Workload Optimization

Heatwave에서는 데이터를 저장하는 방식(Encoding)과 인덱싱(Placement Key)을 하는 두가지 방법으로 워크로드에 맞는 설정을 해볼 수 있습니다. 기본적으로 모든 옵션은 각 칼럼들의 COMMENT내용을 활용하고 있으며. 대소문자를 구분하기에, 반드시 모두 대문자로 명시를 해야한다고 하네요. ex) COMMENT 'RAPID_COLUMN=ENCODING=VARLEN'

1. Encoding

Variable-length Encoding, Dictionary encoding 두가지 타입을 제공하며. 각각 다른 특성을 가집니다.

1.1. Variable-length Encoding

기본 인코딩 타입으로, NULL을 허용하며, 문자열 컬럼 저장에 효율적이라고 합니다.

MySQL에서 제공해주는 캐릭터셋을 지원하며, 무엇보다 데이터 처리 시 MySQL의 메모리가 크게 필요치 않기 때문에 자원 활용이 좋다고 하네요. Group by, Join, Limit, Order by 등을 지원합니다.

ALTER TABLE orders 
  MODIFY `O_COMMENT` VARCHAR(79) COLLATE utf8mb4_bin NOT NULL 
  COMMENT 'RAPID_COLUMN=ENCODING=VARLEN';

1.2. Dictionary encoding (SORTED)

데이터 가짓수(Distinct)가 적은 스트링 저장에 효율적이라 합니다.

예를 들면.. 코드와 같은? 메모리 사용을 MySQL 엔진 쪽을 활용하기 때문에. 관련 리소스 소모가 있을 수 있다고 하고.. 특성만 보면. B-map 인덱싱과 왠지 유사한 느낌적인 느낌도.. ^^

Join, Like 등을 비롯한 문자열 연관 오퍼레이션에 제약이 있다고 합니다. 마찬가지로, 컬럼 레벨로 아래와 같이 코멘트에 인코딩 옵션을 넣어서 설정해볼 수 있겠습니다.

ALTER TABLE orders 
  MODIFY `O_COMMENT` VARCHAR(79) COLLATE utf8mb4_bin NOT NULL 
  COMMENT 'RAPID_COLUMN=ENCODING=SORTED';

2. Data Placement Keys

기본적으로 PK 기반으로 데이터가 분산되어 관리되지만.. 성능 또는 기타 목적을 위해 별도로 생성하는 키라고 되어있고. 마치 여러개의 Clustering Key를 설정하는 듯한 개념으로 보이네요. Variable-length 인코딩만 지원합니다.테이블당 1~16까지 지정할 수 있으며. 하나의 숫자는 하나의 컬럼에만 할당 가능합니다. (복합 인덱싱 불가!)

ALTER TABLE orders 
  MODIFY date DATE COMMENT 'RAPID_COLUMN=DATA_PLACEMENT_KEY=1',
  MODIFY price FLOAT COMMENT 'RAPID_COLUMN=DATA_PLACEMENT_KEY=2';

지금까지 나름 메뉴얼 기반으로 HeatWave에 대해서 퀵하게 훑어보았습니다. 그러나, 역시 직접 해보는 것이 특성을 알아볼 수 있는 제대로된 접근이기에. 개인적으로 의문나는 부분 위주로 몇가지 테스트해보았습니다.

Setup HeatWave

앞에서 이야기를 한 것처럼, HeatWave는 Oracle Cloud에서만 사용할 수 있습니다. 테스트를 위해, 일단 가입을 하고 무료 테스트 코인을 받아서 아래와 같이 HeatWave 용도의 데이터베이스를 생성해봅니다.

생성을 완료했을지라도, 정작 해당 스토리지 엔진이 존재하지 않는데. 아직 HeatWave 클러스터를 생성하지 않았기 때문이죠.

 mysql> show engines;
+--------------------+---------+--------------+------+------------+
| Engine             | Support | Transactions | XA   | Savepoints |
+--------------------+---------+--------------+------+------------+
| FEDERATED          | NO      | NULL         | NULL | NULL       |
| MEMORY             | YES     | NO           | NO   | NO         |
| InnoDB             | DEFAULT | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | NO           | NO   | NO         |
| MyISAM             | YES     | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | NO           | NO   | NO         |
| CSV                | YES     | NO           | NO   | NO         |
| ARCHIVE            | YES     | NO           | NO   | NO         |
+--------------------+---------+--------------+------+------------+

MySQL 인스턴스를 누르고, Heatwave 클러스터 구성을 하면. (위 위치에 메뉴가 있음) 클러스터 생성으로 변경이 되면서. 한참을 기다리다보면. HeatWave State: Active 상태로 변경됩니다.

MySQL에 접속을 해서 엔진을 확인해보면, 아래와 같이 RAPID 엔진이 정상적으로 올라와 있네요.

 mysql> show engines;
+--------------------+---------+--------------+------+------------+
| Engine             | Support | Transactions | XA   | Savepoints |
+--------------------+---------+--------------+------+------------+
| FEDERATED          | NO      | NULL         | NULL | NULL       |
| MEMORY             | YES     | NO           | NO   | NO         |
| InnoDB             | DEFAULT | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | NO           | NO   | NO         |
| RAPID              | YES     | NO           | NO   | NO         | <<==  올라옴
| MyISAM             | YES     | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | NO           | NO   | NO         |
| CSV                | YES     | NO           | NO   | NO         |
| ARCHIVE            | YES     | NO           | NO   | NO         |
+--------------------+---------+--------------+------+------------+

Loading Test Data

Oracle 메뉴얼에 Airport 샘플 데이터를 공개해놓았기 때문에. 이것을 적극 활용해봐야겠죠? 큰 테이블 만들기도 참 귀찮았는대.. 아. 일단. 이 전에. MySQL에 붙기 위한.. Compution instance를 하나 생성부터 해야겠군요. 이 부분은 스킵!

MySQL클라이언트를 설치하고, 추가로 데이터 로딩에서 mysql shell을 쓰기에 같이 설치!

[opc@instance-20220516-1013 ~]$ sudo yum install mysql
[opc@instance-20220516-1013 ~]$ sudo yum install mysql-shell

정상적으로 MySQL에 쿼리를 실행할 수 있는 환경이 구성되었다면, 이제 테스트 데이터를 받아서, mysql shell로 데이터를 로딩해보도록 하겠습니다.

[opc@instance-20220516-1013 ~]$ wget https://downloads.mysql.com/docs/airport-db.tar.gz
[opc@instance-20220516-1013 ~]$ tar xzvf airport-db.tar.gz
[opc@instance-20220516-1013 ~]$ cd airport-db
[opc@instance-20220516-1013 airport-db]$ mysqlsh chan@10.0.0.143
Please provide the password for 'chan@10.0.0.143':   

MySQL 10.0.0.143:3306 ssl JS > util.loadDump("airport-db", {threads: 16, deferTableIndexes: "all", ignoreVersion: true})

데이터 로딩이 마무리되었고.. HeatWave를 테스트해볼 환경이 모두 준비 되었습니다.

mysql> show tables from airportdb;
+---------------------+
| Tables_in_airportdb |
+---------------------+
| airline             |
| airplane            |
| airplane_type       |
| airport             |
| airport_geo         |
| airport_reachable   |
| booking             |
| employee            |
| flight              |
| flight_log          |
| flightschedule      |
| passenger           |
| passengerdetails    |
| weatherdata         |
+---------------------+

Heatwave VS InnoDB

문서에 나온대로, 실제 성능을 체감해보도록 하겠습니다.

1. Initialize HeatWave

쿼리를 날리기에 앞서. airportdb에 있는 모든 테이블을 Heatwave로 변경을 시켜줘야겠죠? 이 과정이 생각보다 오래 걸리지 않더군요. (일부 테이블은 몇천만건 데이터를 가지고 있음에도..)

mysql> call sys.heatwave_load(json_array('airportdb'), null);
mysql> select name, load_status 
    -> from performance_schema.rpd_tables, 
    ->      performance_schema.rpd_table_id
    -> where rpd_tables.id = rpd_table_id.id;
+-----------------------------+-----------------------+
| NAME                        | LOAD_STATUS           |
+-----------------------------+-----------------------+
| airportdb.flight_log        | AVAIL_RPDGSTABSTATE   |
| airportdb.airport_geo       | AVAIL_RPDGSTABSTATE   |
| airportdb.flight            | AVAIL_RPDGSTABSTATE   |
| airportdb.passengerdetails  | AVAIL_RPDGSTABSTATE   |
| airportdb.passenger         | AVAIL_RPDGSTABSTATE   |
| airportdb.airplane          | AVAIL_RPDGSTABSTATE   |
| airportdb.weatherdata       | LOADING_RPDGSTABSTATE |
| airportdb.flightschedule    | AVAIL_RPDGSTABSTATE   |
| airportdb.booking           | AVAIL_RPDGSTABSTATE   |
| airportdb.employee          | AVAIL_RPDGSTABSTATE   |
| airportdb.airplane_type     | AVAIL_RPDGSTABSTATE   |
| airportdb.airport           | AVAIL_RPDGSTABSTATE   |
| airportdb.airline           | AVAIL_RPDGSTABSTATE   |
| airportdb.airport_reachable | AVAIL_RPDGSTABSTATE   |
+-----------------------------+-----------------------+

mysql> select count(*) from booking;
+----------+
| count(*) |
+----------+
| 54304619 |
+----------+

2. Query HeatWave

실행계획을 보면, 별다른 옵션없이 옵티마이저가 이 쿼리를 RAPID엔진을 사용하는 것으로 Extra부분에서 정보를 확인해볼 수 있겠습니다. 그런데 실제 가격별 카운팅 결과는 0.09초에 마무리가 되는.. 굉장히 좋은 성능을 보여주네요.

mysql> SET SESSION use_secondary_engine=ON;
mysql> explain
    -> SELECT booking.price, count(*)
    ->   FROM booking WHERE booking.price > 500
    ->  GROUP BY booking.price
    ->  ORDER BY booking.price LIMIT 10;
+----+----------+----------+--------------------------------------+
| id | rows     | filtered | Extra                                |
+----+----------+----------+--------------------------------------+
|  1 | 54202876 |    33.33 | Using ..Using secondary engine RAPID |
+----+----------+----------+--------------------------------------+

mysql> SELECT booking.price, count(*)
    ->   FROM booking WHERE booking.price > 500
    ->  GROUP BY booking.price
    ->  ORDER BY booking.price LIMIT 10;
+--------+----------+
| price  | count(*) |
+--------+----------+
| 500.01 |      860 |
| 500.02 |     1207 |
| 500.03 |     1135 |
| 500.04 |     1010 |
| 500.05 |     1016 |
| 500.06 |     1039 |
| 500.07 |     1002 |
| 500.08 |     1095 |
| 500.09 |     1117 |
| 500.10 |     1106 |
+--------+----------+
10 rows in set (0.09 sec)

3. Query InnoDB

InnoDB로만 데이터를 처리했을 시 결과입니다. 실행계획을 보면, 앞에서 명시되었던 RAPID 엔진 사용이 사라졌고. 실제 카운팅 쿼리를 수행해보면. 10초 이상.. 무려 1000배의 시간이 더 걸리는 성능을 보이죠. 일단, 이런 효율면에서는 확연하게 성능차가 나오네요.

mysql> SET SESSION use_secondary_engine=OFF;
mysql> explain
    -> SELECT booking.price, count(*)
    ->   FROM booking WHERE booking.price > 500
    ->  GROUP BY booking.price
    ->  ORDER BY booking.price LIMIT 10;
+----+----------+----------+--------------------------------------+
| id | rows     | filtered | Extra                                |
+----+----------+----------+--------------------------------------+
|  1 | 54202876 |    33.33 | Using where; Using..; Using filesort |
+----+----------+----------+--------------------------------------+
mysql> SELECT booking.price, count(*)
    ->   FROM booking WHERE booking.price > 500
    ->  GROUP BY booking.price
    ->  ORDER BY booking.price LIMIT 10;
+--------+----------+
| price  | count(*) |
+--------+----------+
| 500.01 |      860 |
| 500.02 |     1207 |
| 500.03 |     1135 |
| 500.04 |     1010 |
| 500.05 |     1016 |
| 500.06 |     1039 |
| 500.07 |     1002 |
| 500.08 |     1095 |
| 500.09 |     1117 |
| 500.10 |     1106 |
+--------+----------+
10 rows in set (10.66 sec)

이것 외에도 다른 다양한 쿼리가 몇몇 샘플로 더 공개되어 있지만, 이것은. 스킵하는 것으로..

Operational Test

아무리 엔진이 좋아도, 운영 측면에서 준비가 안되어있다면, 이것은 빛좋은 개살구일뿐입니다. 만약 서비스에서 사용을 한다면, 좋을만한 포인트가 무엇일지 기준으로 추가로 테스트해보았습니다.

1. Online DDL

아쉽게도… Heatwave로, 아니 SECONDARY_ENGINE=RAPID 으로 정의된 테이블에는 ALTER구문이 동작하지 않습니다. 만약, 테이블 구조 변경이 필요하다면. SECONDARY_ENGINE속성을 없앤 후에 ALTER를 수행해야합니다,

mysql> alter table heatwave_test add c10 varchar(10);
ERROR 3890 (HY000): DDLs on a table with a secondary engine defined are not allowed.

mysql> truncate table heatwave_test;
ERROR 3890 (HY000): DDLs on a table with a secondary engine defined are not allowed.

mysql> alter table partition_test secondary_engine = null;

mysql> truncate table heatwave_test;
Query OK, 0 rows affected (0.01 sec)

테이블 칼럼 추가 삭제 시에는 이런 부담을 안고 가야하기에. DDL이 절대 변하지 않을만한 상황에서 활용을 하는 것이 좋을 듯 하네요.

2. Partitioning

앞선 이야기에서처럼. SECONDARY_ENGINE=RAPID 으로 정의된 테이블에는 ALTER구문이 동작하지 않습니다. 이것은 파티셔닝 추가/삭제에 대해서도 마찬가지입니다.

사실 테이블에서는 디비에 부담없이 오래된 데이터를 정리하는 목적으로 파티셔닝을 많이 활용하기 때문에.. 아쉬운점이 많네요. 특히나. 이 테이블은 아무래도 분석 관련된 쿼리가 많이 활용될 것이라, 데이터도 많아지게 될 것이고. 아주 오래된 데이터를 효율적으로 제거하는 방안도 있어야할텐데..

무엇보다, 파티셔닝 테이블로 로딩을 했을지라도. RAPID 내부적으로는 의미가 없기도 하고요. 사실. Heatwave쪽에 별도의 데이터가 존재하기에. 당연한 결과이기는 합니다만..

mysql> SET SESSION use_secondary_engine=OFF;
+----+---------------------------------+---------+-------------+
| id | partitions                      | rows    | Extra       |
+----+---------------------------------+---------+-------------+
|  1 | p2017,p2018,p2019,p2020,p999999 | 9533500 | Using where |
+----+---------------------------------+---------+-------------+

mysql> SET SESSION use_secondary_engine=ON;
+----+------------+---------+---------------------------------------+
| id | partitions | rows    | Extra                                 |
+----+------------+---------+---------------------------------------+
|  1 | NULL       | 9533500 | Using .. Using secondary engine RAPID |
+----+------------+---------+---------------------------------------+

파티셔닝이 안되는 것은 아니지만, 우선 파티셔닝을 했을지라도 파티셔닝 구조변경을 위해서는 HeatWave재구성을 해야한다는 측면에서는 매리트가 없습니다. 이럴꺼면 그냥 테이블 단위 파티셔닝을 개발 레벨에서 구현을 해서 사용하는 것이 훨씬 유리해보이네요.

그냥 파티셔닝은 비효율적이다라는 정도로 정리!

Performance TEST

사실 Cloud에서 동작을 하는 것이라. 성능적으로 기하 급수적으로 변화시킬만한 파라메터가 없는 것은 사실입니다. 그러나, 적어도 Heatwave 테이블과 InnoDB로만 구성된 테이블간의 성능차이는 확인해봐야겠죠?

mysql> create database bmt;
mysql> use bmt;

mysql> create table tb_innodb_only(
    ->  i int not null primary key auto_increment,
    ->  c1 varchar(100) not null,
    ->  c2 varchar(100) not null,
    ->  c3 varchar(100) not null,
    ->  c4 varchar(100) not null,
    ->  c5 varchar(100) not null,
    ->  c6 varchar(100) not null,
    ->  c7 varchar(100) not null,
    ->  ts timestamp
    -> );

mysql> create table tb_innodb_rapid(
    ->  i int not null primary key auto_increment,
    ->  c1 varchar(100) not null,
    ->  c2 varchar(100) not null,
    ->  c3 varchar(100) not null,
    ->  c4 varchar(100) not null,
    ->  c5 varchar(100) not null,
    ->  c6 varchar(100) not null,
    ->  c7 varchar(100) not null,
    ->  ts timestamp
    -> );
mysql> alter table tb_innodb_rapid secondary_engine=rapid;
mysql> alter table tb_innodb_rapid secondary_load;

1. InnoDB VS HeatWave

InnoDB와 HeatWave 양쪽으로 두 벌의 데이터가 존재하기 때문에. SELECT와 같이 둘중 하나 담당하는 쿼리 테스트는 의미없을 듯 하고. 양쪽 모두 영향을 미치는 데이터 변경으로 테스트를 해보겠습니다. 테스트 트래픽은 쉽게쉽게 mysqlslap 유틸을 활용하여 생성해보도록 하겠습니다.

1.1 InnoDB performance

10건 데이터를 100개 프로세스가 나눠서 넣는 것으로. 총 10번 수행합니다.

mysqlslap                     \
  -u chan -p -h 10.0.0.143    \
  --concurrency=100           \
  --iterations=10             \
  --number-of-queries=100000  \
  --create-schema=bmt         \
  --no-drop                   \
  --query="insert into tb_innodb_only values (null, uuid(), uuid(), uuid(), uuid(), uuid(), uuid(), uuid(),now());"

Benchmark
  Average number of seconds to run all queries: 20.136 seconds
  Minimum number of seconds to run all queries: 18.896 seconds
  Maximum number of seconds to run all queries: 20.851 seconds
  Number of clients running queries: 100
  Average number of queries per client: 1000

유입 쿼리량을 보니.. 대략 초당 6,000 정도 쿼리를 처리하네요.

|Com_insert|5804|
|Com_insert|5430|
|Com_insert|6218|
|Com_insert|5759|
|Com_insert|6173|
|Com_insert|5823|
|Com_insert|5586|
|Com_insert|5460|
|Com_insert|6085|
|Com_insert|5842|
|Com_insert|6312|
|Com_insert|5807|
|Com_insert|6210|
|Com_insert|6036|

1.2 HeatWave Performance

이번에는 동일한 트래픽을 Heatwave로 구성된 테이블에 줘보도록 하겠습니다. 마찬가지로, 10건 데이터를 100개 프로세스가 나눠서 넣습니다.

mysqlslap                     \
  -u chan -p -h 10.0.0.143    \
  --concurrency=100           \
  --iterations=10             \
  --number-of-queries=100000  \
  --create-schema=bmt         \
  --no-drop                   \
  --query="insert into tb_innodb_rapid values (null, uuid(), uuid(), uuid(), uuid(), uuid(), uuid(), uuid(),now());"

Benchmark
  Average number of seconds to run all queries: 20.271 seconds
  Minimum number of seconds to run all queries: 19.184 seconds
  Maximum number of seconds to run all queries: 21.355 seconds
  Number of clients running queries: 100
  Average number of queries per client: 1000

유입 쿼리량을 보니.. 대략 초당 6,000 정도 쿼리를 처리하네요. 차이가 거의 없다고 해야하나.. -_-;

|Com_insert|5925|
|Com_insert|6201|
|Com_insert|5621|
|Com_insert|5923|
|Com_insert|5837|
|Com_insert|5609|
|Com_insert|5926|
|Com_insert|5268|
|Com_insert|5977|
|Com_insert|5630|
|Com_insert|6185|
|Com_insert|5046|
|Com_insert|6335|
|Com_insert|5761|
|Com_insert|6222|
|Com_insert|5926|

2. Count query for paging

생각없이 검색 쿼리를 날려보았는데. 의외의 결과가 나와서 추가로 적어봅니다.

서비스에서 많이 사용하는 쿼리 중 제일 컨트롤이 어려운 부분은 사실 페이징입니다. 데이터가 폭발적으로 증가할수록 언제나 늘 고민을 해야하는 것도. 바로 게시판 타입의 서비스이기도 하죠. 물론 오프셋 기반의 페이징(특정 ID값 이전의 10건씩 가져오는 방식)은 소셜 서비스의 방대한 데이터를 처리하기 위해 많이들 사용하는 추세이기는 합니다만.. 모든 요구사항을 이런식으로 구현하기에도 명백히 한계가 있습니다.

그런데. Heatwave를 태우게 되면. 단순 카운팅 뿐만 아니라. 아래와 같이 별도의 검색조건을 추가로 준다고 할지라도. 큰 무리없이 데이터 카운팅 결과를 가져옵니다. (0.05초 VS 10.84초)

#####################################
# Heatwave
#####################################
mysql> SET SESSION use_secondary_engine=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*)
    ->   FROM booking
    ->  WHERE booking.price > 500
    ->    AND seat like '24%';
+----------+
| count(*) |
+----------+
|     1407 |
+----------+
1 row in set (0.05 sec)

#####################################
# Without heatwave
#####################################
mysql> SET SESSION use_secondary_engine=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*)
    ->   FROM booking
    ->  WHERE booking.price > 500
    ->    AND seat like '24%';
+----------+
| count(*) |
+----------+
|     1407 |
+----------+
1 row in set (10.84 sec)

의외로. 제 개인적으로는 워드프레스와 상당히 궁합이 맞을 수 있겠다는 생각이…?? 새로운 발견!!

Conclusion

지금까지 몇가지를 추가로 살펴본 HeatWave는.. 이렇게 정리를 해보고싶네요.

Oracle Cloud에서만 사용 가능
InnoDB 데이터와 별개의 분산 스토리지에 저장된다. (InnoDB first)
Online DDL은 불가하고. DDL시에도 secondary_engine을 비활성 필요
InnoDB는 파티셔닝 구성해도, HeatWave는 내부적으로 파티셔닝 구성 안되어 있음.
파티셔닝의 관리 역시 순탄치 않음
InnoDB와 성능 차이(INSERT)가 없음.
게시판 카운팅 쿼리에 나름 적합(?)

무엇보다 Oracle cloud에서만 사용할 수 있다는 점이 아쉽기는 하지만.

OnPremise로 가능할지라도. Heatwave 클러스터 구축 및 관리가 필요할 것이니. 이것이 꼭 단점으로만 부각할만한 것은 아니라고 봅니다. 그리고. Oracle cloud에서는 의외로.. MySQL binlog가 활성화되어있고, Replication 계정을 추가할 수 있기에.. 데이터 핸들링 측면에서는 꽤나 좋은 환경이라는 생각도 문득 드네요. ㅎㅎ (DBaaS인데 이 점은 정말 매력적인 포인트입니다. 데이터의 흐름을 가두지 않는 것. 아 근데 나중에 막힐라나?? -_-;;)

이런 측면을 놓고보면, 안정성/성능을 좀더 파악해봐하겠지만, 개인적으로는 서

긴 글 읽으시느라 고생많았습니다. 오랜만에 포스팅을 마치겠습니다.

아마존의 가상 RDBMS인 Amazon RDS의 특성 몇 가지

Posted on 2012-08-10 | by gywndi | Leave a Comment

Overview

지난 해 말 글로벌 서비스를 겨냥하여 Amazon 가상 플랫폼 상에 인증 서비스를 오픈하였고, 올해 초에는 푸딩.투 서비스 또한 런칭하여 서비스 중에 있습니다.

글로벌 서비스를 위한 저장소로는 아마존에서 제공하는 가상 관계형 DBMS인 Amazon RDS를 사용 중입니다.

이번 포스팅에서는 Amazon RDS에 대한 특성 몇 가지를 설명 드리겠습니다.

Virtual Database Instance

Amazon RDS는 Virtual Database Instance입니다.

DBMS는 데이터를 처리하는 미들웨어이고, 미들웨어는 OS 기반 위에서 동작합니다. 일반적인 상황이라면 OS에 접근하여 그에 맞게 DBMS를 설치하고, 관련 파라메터도 정의를 해야만 하지만, 모든 것이 "웹 콘솔" 상에서 간단하게 처리합니다.

웹 콘솔에서 “Launch DB Instance” 버튼을 누르면 하단과 같은 레이어가 나오는데, 사용할 DBMS를 선택하고 DB 인스턴스 정보 몇가지만 입력하여 생성을 하면 10분 안에 즉시 사용 가능합니다.

MySQL, Oracle, SQL Server 등 일반적으로 많이 사용하는 DBMS를 사용할 수 있습니다. (2011년 중반에는 SQL Server는 제공하지 않았습니다. ^^)

가상 DB 인스턴스이기 때문에 직접적으로 OS에 접근하여 조작은 불가합니다. DB 튜닝을 위해 파라메터를 변경하는 경우에도 직접적으로는 불가하며, rds-cli라는 클라이언트 툴을 사용해서 조작해야 합니다.

다음은 Slow Log를 사용할 수 있도록 설정하는 간단한 샘플입니다.

rds-modify-db-parameter-group testDB \
--region us-west-1 \
--parameters "name=slow_query_log, value=on, method=immediate"

이런 사항들이 불편함으로 다가올 수 있겠지만, 모든 OS관련된 사항들을 RDS 클라이언트 API Call을 통해서 이루어지고, 특별히 OS에 대해서 관리할 사항 또한 없기 때문에 상당 부분 DB 운영 이슈가 사라집니다.

Multi-AZ (Availablity Zone)

Amazon RDS에서 High Availablity를 구현하는 대표적인 방법입니다.

가용 Zone에 두 개의 인스턴스(기본 인스턴스/예비 인스턴스)를 띄우고, 기본 인스턴스DB에서 데이터 변경 즉시 동기화합니다. Replication이 비동기적인 방식으로 동작하지만, Multi-AZ은 동기화 방식이라는 점에서 큰 차이가 있습니다.

MySQL Replication의 고질적인 문제인 실시간 데이터 동기화 지연 문제와, 장애 시 빠른 복구가 어려운 문제를 단번에 해결할 수 있는 방안이죠.

하지만 꼭 기억해야할 몇가지 사항이 있습니다.

첫째 Multi-AZ 기능은 High Availablity 구현을 위한 방법입니다.

예비 인스턴스는 단지 기본 인스턴스에서 일어난 이벤트를 적용할 뿐 Read/Write 트래픽을 분산하지 않습니다. Read는 바로 아래에서 설명할 Replication으로 어느정도 분산이 가능하나, Write 은 데이터 Sharding 기법 외에는 방법이 없습니다.

둘째 Multi-AZ은 같은 Region에서만 구성 가능 합니다.

Amazon RDS에는 Region과 Zone 개념이 있습니다.

Region은 “미국 서부 캘리포니아”, “일본”, “싱가폴” 등과 같이 큰 대륙 혹은 지역을 의미합니다. 그리고 대륙 간에는 인터넷 라인으로 연결되어 있습니다.

Region 안에는 여러 개의 Zone이 있습니다. Zone은 Region에 포함된 몇 개의 IDC 센터입니다. 각 Zone은 전용선으로 연결되어 있으며, 지역적으로 수십 혹은 수백 킬로미터 떨어져 있습니다.

Zone 사이에는 전용선으로 데이터 전송 속도 및 신뢰성이 보장되기 때문에, 기본 인스턴스 반영 시 즉각적인 동기화가 가능합니다.

그러나 Region는 상황이 다릅니다. 인터넷 망으로 연결되어 있기 때문에, 데이터 동기화가 즉시 불가한 것이죠.

Data Replication

Amazon RDS에서 Multi-AZ이 HA 구현을 위한 방법이라면, DB Scale-Out을 위한 방안으로는 전통적인 MySQL의 복제 기술, 즉 MySQL Replication을 제공합니다. 슬레이브 DB를 추가하는 방법은 간단합니다. 웹 콘솔에서 “Create Read Replica” 버튼을 누르고 몇가지 정보만 넣으면 됩니다.

인스턴스에서는 최대 5개의 복제 서버를 가질 수 있으며, 데이터는 MySQL Replication과 동일하게 비동기적으로 일관성이 유지됩니다. 즉, 언제든지 마스터/슬레이브 간 데이터 동기화 지연 현상이 발생할 수 있습니다.

제약 사항

Amazon RDS에서는 다음과 같은 몇 가지 제약 사항이 있습니다.

DB 인스턴스는 최대 10 개까지 생성 가능
DB 인스턴스 별 최대 가능한 스토리지는 1TB
각 DB 인스턴스의 복제서버는 최대 5개까지만 생성 가능
Multi-AZ은 동일한 가용 존 내에서만 사용 가능

Conclusion

전체가 아닌 일부 특성만을 적어놓기는 했지만, 간단한 개념 잡기에는 충분하다고 생각합니다. ^^;;

Amazon RDS 사용을 하면 DB 시스템적인 운영 이슈가 최소화되기 때문에 굉장히 편리합니다. 하지만 몇 가지 제약 사항으로 인하여 제대로 사용을 하지 못하면 큰 낭패를 볼 수 있습니다. 또한 간단한 DB 성능 테스트 면에서도 동일한 스펙의 물리 서버 성능의 1/3 정도만 발휘하는 것으로 나타났습니다.

이제 정말로 시스템 성능이 아닌 DBA 자체의 개발 역량이 중요시되는 추세인 것 같네요.

공부를 더욱더 열심히 해야 살아남겠군요. ^^;;

Amazon RDS에서 유실된 데이터 복원하기

Posted on 2012-06-05 | by gywndi | Leave a Comment

Overview

Amazon Relational Database Service(Amazon RDS)는 클라우드에서 관계형 데이터베이스를 쉽게 설치, 운영 및 확장할 수 있는 서비스입니다.

자원을 유연하게 배분할 수 있는 이점이 있는 클라우드이지만, 모든 서비스는 결국에는 사람 손을 거쳐야 하고, 때로는 인재로 인한 데이터 유실 사고가 발생할 수 있습니다.

사용이 편리하게 구현되어 있지만, 사용자에게 제공하는 권한 또한 상당히 제약적(인스턴스 관리자일지라도)입니다.

오늘은 Amazon RDS 상에서 데이터 유실 장애가 발생한 경우 대처할 수 있는 방안에 관하여 포스팅하도록 하겠습니다. (기준은 MySQL이나 타 DBMS도 큰 차이가 없을 것 같네요^^)

복구 순서는 다음과 같습니다.

백업 DB 인스턴스 생성
임시 복구 테이블 생성
데이터 추출 및 복원

백업 DB 인스턴스 생성

Amazon RDS에서는 앞서 언급드린 것과 같이 사용자에게 제공하는 권한이 상당히 제약적입니다. 무엇보다 DB서버에는 OS개념이 없고, 오직 DBMS에 개방된 포트를 통해서만 DB 접속이 가능합니다. 그리고 사용 시 불필요한 권한 또한 대부분 회수 되어 있죠. 결과적으로 로컬 IDC에서 데이터 유실 발생 시 Binlog Position을 활용하여 장애 시점 이전으로 데이터를 돌리는 것이 불가합니다.

하지만 RDS에서는 “Restore To Point In Time” 라는 기능을 제공합니다. 새벽에 생성된 DB 이미지와 내부적으로 Binary Log를 취합하여 실제 사용자가 원하는 시점의 DB 인스턴스를 생성하는 기능이죠.

백문이불여일견!! 한번 보시죠^^

Amazon Restore To Point In Time

Amazon RDS 콘솔 상에서 “Restore To Point In Time“버튼을 클릭합니다. 그러면 하단과 같이 DB 인스턴스 생성을 위한 옵션을 입력하는 레이어가 뜹니다.

Amazon Restore To Point In Time Option

Restore Time을 장애 시점 이전으로 설정합니다. 물론 장애 시점과 가까울수록 데이터 신뢰성을 더욱 커지겠죠. 단, 여기서 시간은 UTC 기준으로 넣으셔야 합니다.

“DB Instance Identifier“에는 생성할 DB 인스턴스 이름이니, 기존 네이밍과 중복이 되지 않도록 지정합니다.

기타 옵션은 큰 의미는 없으나, 비용적인 측면을 고려하여 “DB Instance Class“를 Small 사이즈로 설정합니다. (생성된 DB에서는 단순 Data Export만 수행할 것이기 때문에 좋은 성능은 필요 없습니다.)

옵션을 모두 입력을 하고 “Launch DB Instance“를 클릭하면 아래와 같이 새로운 DB 인스턴스가 생성이 됩니다. ^^

Amazon Launch Backup DB Instance

DB 인스턴스가 생성되는 과정은 기존 운영되고 있는 서비스 DB에는 영향을 주지 않습니다. 새벽에 스냅샷 형태로 풀 백업된 DB 이미지에 Binary Log 변경 사항을 내부적으로 취합하기 때문이죠. ^^

오~랜 시간이 지난 후 확인을 해보면 백업 DB 인스턴스 Status가 Available로.. 즉 새로운 DB 인스턴스 생성이 완료되었습니다 . 참 쉽죠??

Amazon RDS End Point

신규로 생성된 백업 DB 인스턴스를 클릭하면 관련 Description이 위와 같이 나오는데, 여기서 End Point를 보면 RDS에 접근하기 위한 주소가 나옵니다. 클라이언트에서는 End Point를 통해서 DB 접속을 진행하면 됩니다.

임시 복구 테이블 생성

자! 이제 백업 DB 인스턴스를 생성하였으니, 데이터를 복구하는 단계로 넘어가야겠죠? 사전에 복구할 테이블과 동일한 구조의 테이블을 생성을 합니다. 데이터 이관을 Export/Import로 데이터를 이관하기 위함입니다.

임시 복구 테이블 생성

mysql> create table tb_repair_tmp like tb_repair;
Query OK, 0 rows affected (0.42 sec)

인덱스 제거

임시 테이블을 Rename할 목적이 아니라면, 기존 인덱스는 필요하지 않습니다. 과감하게 날려줍니다!! Primary Key는 물론 제외하고 날리셔야겠죠? ^^;;

mysql> drop index idx_tb_repair_indt on tb_repair_tmp;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index idx_tb_repair_closedt on tb_repair_tmp;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0

복원을 위해 데이터를 저장할 임시 테이블까지 모두 생성하였습니다.

데이터 추출 및 복원

mysqldump 유틸리티를 활용하여 데이터를 dump하는 동시에 sed유틸리티로 테이블명만 임시 백업 테이블명으로 변경하여 바로 데이터를 입력합니다.

“MySQL에서 테이블 스키마를 “무중단”으로 변경해보자!!” 편에서 데이터를 이관한 것과 동일한 방식입니다. ^^ 얼마전 RDS 데이터 복원을 하며 대충 만들어놓은 스크립트를 우연찮게 재사용하게 되었네요. ㅎㅎ

명시적인 명령어는 하단과 같습니다.

$ mysqldump -udbuser -pxxxxx \
  --single-transaction \
  --no-create-db \
  --no-create-info \
  --triggers=false \
  --comments=false \
  --add-locks=false \
  --disable-keys=false \
  --host=xx-master-restore.xx.us-east-1.rds.amazonaws.com \
  --port=3306 \
  --databases targetdb \
  --tables tb_repair \
| sed -r 's/^INSERT INTO `tb_repair`/INSERT INTO `tb_repair_tmp`/gi' \
| mysql -udbuser -pxxxxx \
  --host xx-master.xx.us-east-1.rds.amazonaws.com \
  --port 3306 targetdb

mysqldump에 들어가는 host는 백업 DB 인스턴스 End Point이고, mysql에 들어가는 host는 복원할 서버 End Point입니다. 헷갈리면 안됩니다!!

위 작업이 마무리되면 임시 테이블에 장애 시점 이전의 데이터가 들어있는 것을 확인할 수 있습니다.

데이터 보정

장애 시점 이전 데이터를 구성하였으니, 이제는 구성된 데이터를 활용하여 전체적인 데이터 복구 작업을 마무리합니다. 장애를 유발한 SQL에 따라서 복구 시나리오가 다릅니다.

Case 1 : Drop Table

단순하게 테이블 Rename합니다. 물론 Rename을 하기 위해서는 앞서 진행했던 인덱스 제거 작업을 해서는 안되겠죠.

Case 2 : Delete Table (Truncate Table)

테이블에 데이터는 없으나, 지속적으로 누적되고 있는 경우입니다. 이 경우는 누적되는 데이터를 선별해서 데이터를 복원해야 합니다.

하단과 같이 임시 테이블에는 존재하나 원본 테이블에는 없는 데이터를 선별해서 데이터를 복원합니다. DB에 무리가 가지 않도록 10만 건씩 나눠서 데이터를 복사합니다. 여기서 seq는 각 테이블의 Primary Key입니다.

insert into tb_repair
select a.*
from tb_repair_tmp a
left join tb_repair b on a.seq = b.seq
where b.seq is null
limit 100000;

Case 3 : Update Table

다음과 같이 update 시 inner join을 수행하여 데이터를 보정합니다. DB에 무리가 가지 않도록 10만 건씩 나눠서 데이터를 업데이트 합니다. 하단은 Primary Key가 Auto_Increment 옵션이 적용된 경우이고, 일반 스트링인 경우에는 limit을 활용하여 적절하게 자르시기 바랍니다.^^ 실수로 특정 필드를 공백으로 업데이트한 경우입니다.

update tb_repair a
inner join tb_repair_tmp b on a.seq = b.seq
set a.passwd = b.passwd
where a.passwd = ''
and a.seq between 1 and 100000;

Conclusion

Amazon RDS에서 제공하는 “Restore To Point In Time” 기능을 사용하면, DB 장애 처리 시 사용하던 복잡한 Dump 명령 없이 간단하게 데이터를 복원할 수 있습니다.

단, “Restore To Point In Time” 사용 시 Binary Log 포지션을 일일이 확인하며 장애 시점 바로 이전까지는 복원이 불가하다는 것을 반드시 인지하시기 바랍니다.

그리고 데이터 보정 후 반드시 검증도 꼭 하시고, 기타 웹로그가 있는 경우에도 충분히 반영을 하셔야 합니다.

데이터를 복구한 이후에는 임시로 생성한 DB 인스턴스는 반드시 제거하세요. (비용이 나갑니다.)