MySQL 클라우드서비스인 MDS 에서 사용 가능한 HeatWave 적용 방법 테스트를 공유하려고 한다.
HeatWave 는 별도의 ETL 작업 없이 OLTP 에서 사용중인 데이터를 HeatWave 메모리에 실시간으로 반영하여 OLTP와 OLAP 을 동시에 사용할 수 있게 해주는 서비스이다.
이 포스팅은 HeatWave 적용하는 방법을 테스트 해봤다.
오라클 클라우드 계정에 MySQL 을 생성하고
HeatWave 사용을 위해 Node 두개를 Enable 시켰다.
- 샘플로 사용한 테이블 생성
CREATE TABLE ORDERS ( O_ORDERKEY BIGINT NOT NULL,O_CUSTKEY INTEGER NOT NULL,O_ORDERSTATUS CHAR(1) NOT NULL,O_TOTALPRICE DECIMAL(15,2) NOT NULL,O_ORDERDATE DATE NOT NULL,O_ORDERPRIORITY CHAR(15) NOT NULL,O_CLERK CHAR(15) NOT NULL,O_SHIPPRIORITY INTEGER NOT NULL,O_COMMENT VARCHAR(79) NOT NULL,PRIMARY KEY (O_ORDERKEY));CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL,L_PARTKEY INTEGER NOT NULL,L_SUPPKEY INTEGER NOT NULL,L_LINENUMBER INTEGER NOT NULL,L_QUANTITY DECIMAL(15,2) NOT NULL,L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,L_DISCOUNT DECIMAL(15,2) NOT NULL,L_TAX DECIMAL(15,2) NOT NULL,L_RETURNFLAG CHAR(1) NOT NULL,L_LINESTATUS CHAR(1) NOT NULL,L_SHIPDATE DATE NOT NULL,L_COMMITDATE DATE NOT NULL,L_RECEIPTDATE DATE NOT NULL,L_SHIPINSTRUCT CHAR(25) NOT NULL,L_SHIPMODE CHAR(10) NOT NULL,L_COMMENT VARCHAR(44) NOT NULL,PRIMARY KEY (L_ORDERKEY, L_LINENUMBER))PARTITION BY KEY()PARTITIONS 128;
- MySQL Shell을 이용하여 업로드
MySQL>SQL> \jsMySQL>JS>util.importTable("/home/opc/tpc/dbgen/orders.tbl", {schema:"normal", table:"orders", fieldsTerminatedBy:"|", bytesPerChunk:"100M", threads:16})util.importTable("/home/opc/tpc/dbgen/lineitem.tbl", {schema:"normal", table:"lineitem", fieldsTerminatedBy:"|", bytesPerChunk:"100M", threads:16})
- 테스트할 테이블에 comment encoding 설정을 하고
- 각 Node 에 분배할 Key지정(defualt : PK)
- 해당 테이블 HeatWave 에 지정
-- optimal encodings--컬럼 성격에 따라서 encoding을 지정해준다--Variable-length encoding (VARLEN) : Default. distinct value가 높은컬럼(코멘트)--Dictionary encoding (SORTED) : distinct value가 작은 컬럼(코드,나라이름)alter table ORDERS change O_ORDERSTATUS O_ORDERSTATUS CHAR(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table ORDERS change O_ORDERPRIORITY O_ORDERPRIORITY CHAR(15) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table ORDERS change O_CLERK O_CLERK CHAR(15) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table ORDERS change O_COMMENT O_COMMENT VARCHAR(79) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table LINEITEM change L_RETURNFLAG L_RETURNFLAG CHAR(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table LINEITEM change L_LINESTATUS L_LINESTATUS CHAR(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table LINEITEM change L_SHIPINSTRUCT L_SHIPINSTRUCT CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table LINEITEM change L_COMMENT L_COMMENT VARCHAR(44) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table LINEITEM change L_SHIPMODE L_SHIPMODE CHAR(10) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';alter table ORDERS change O_COMMENT O_COMMENT VARCHAR(79) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN';-- optimal data placement--defualt 는 pk 이나 주로 사용되는 조인key, group by 기준에 따라 설정 가능alter table LINEITEM change L_ORDERKEY L_ORDERKEY BIGINT NOT NULL COMMENT 'RAPID_COLUMN=DATA_PLACEMENT_KEY=1';-- mark tables for RAPIDalter table LINEITEM secondary_engine RAPID;alter table ORDERS secondary_engine RAPID;-- Set Parallel reads threads to 32set innodb_parallel_read_threads=32;ALTER TABLE LINEITEM SECONDARY_LOAD;ALTER TABLE ORDERS SECONDARY_LOAD;
- 20기가 정도 되는 Lineitem 테이블을 HeatWave Node에 올리는데 2분 11초 정도 걸렸다.
- Order 테이블은 3기가 정도로 12초 소요되었다.
MySQL 10.0.0.146:3306 ssl tpch SQL > set innodb_parallel_read_threads=32;Query OK, 0 rows affected (0.0005 sec)MySQL 10.0.0.146:3306 ssl tpch SQL > ALTER TABLE LINEITEM SECONDARY_LOAD; --약 15기가Query OK, 0 rows affected (2 min 11.8548 sec)MySQL 10.0.0.146:3306 ssl tpch SQL > ALTER TABLE ORDERS SECONDARY_LOAD;Query OK, 0 rows affected (12.6465 sec)
- Test 할 SQL 을 HeatWave 사용 / 미사용 실행
- 일반 MySQL InnoDB에서 11.13 분
- MySQL HeatWave 에서 0.1 초
--HeatWave 미사용시SET SESSION use_secondary_engine=OFF;mysql> SELECTO_ORDERPRIORITY, COUNT(*) AS ORDER_COUNTFROM ORDERSWHERE O_ORDERDATE >= DATE '1994-03-01'-> O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT-> FROM ORDERS-> WHERE O_ORDERDATE >= DATE '1994-03-01'-> AND O_ORDERDATE < DATE '1994-03-01' + INTERVAL '3' MONTH-> AND EXISTS( SELECT *-> FROM LINEITEM-> WHERE L_ORDERKEY = O_ORDERKEY-> AND L_COMMITDATE < L_RECEIPTDATE)-> GROUP BY O_ORDERPRIORITY-> ORDER BY O_ORDERPRIORITY;+-----------------+-------------+| O_ORDERPRIORITY | ORDER_COUNT |+-----------------+-------------+| 1-URGENT | 210875 || 2-HIGH | 209521 || 3-MEDIUM | 209832 || 4-NOT SPECIFIED | 210284 || 5-LOW | 211114 |+-----------------+-------------+5 rows in set (11 min 13.53 sec)--HeatWave 사용시SET SESSION use_secondary_engine=ON;mysql> SELECTO_ORDERPRIORITY, COUNT(*) AS ORDER_COUNTFROM ORDERSWHERE O_ORDERDATE >= DATE '1994-03-01'-> O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT-> FROM ORDERS-> WHERE O_ORDERDATE >= DATE '1994-03-01'-> AND O_ORDERDATE < DATE '1994-03-01' + INTERVAL '3' MONTH-> AND EXISTS( SELECT *-> FROM LINEITEM-> WHERE L_ORDERKEY = O_ORDERKEY-> AND L_COMMITDATE < L_RECEIPTDATE)-> GROUP BY O_ORDERPRIORITY-> ORDER BY O_ORDERPRIORITY;+-----------------+-------------+| O_ORDERPRIORITY | ORDER_COUNT |+-----------------+-------------+| 1-URGENT | 210875 || 2-HIGH | 209521 || 3-MEDIUM | 209832 || 4-NOT SPECIFIED | 210284 || 5-LOW | 211114 |+-----------------+-------------+5 rows in set (0.10 sec)
mysql> explain SELECT /*+ set_var(use_secondary_engine=forced) */ O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM ORDERS WHERE O_ORDERDATE >= DATE '1994-03-01' AND O_ORDERDATE < DATE '1994-03-01' + INTERVAL '3' MONTH AND EXISTS( SELECT *FROM LINEITEM WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE) GROUP BY O_ORDERPRIORITY ORDERBY O_ORDERPRIORITY\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: ORDERSpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 29616076filtered: 11.11Extra: Using where; Using temporary; Using filesort; Using secondary engine RAPID*************************** 2. row ***************************id: 1select_type: SIMPLEtable: LINEITEMpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 119026612filtered: 3.33Extra: Using where; FirstMatch(ORDERS); Using join buffer (hash join); Using secondary engine RAPID2 rows in set, 2 warnings (0.00 sec)
- Extra 에 Using secondary engine RAPID 표시 확인.
* 참고
#오라클클라우드 #MySQL #HeatWave #MDS #OCI
댓글
댓글 쓰기