1/20的成本！PolarDB-X冷热分离存储评测

CREATE TABLE `orders` (
    `o_orderkey` int(11) NOT NULL,
    `o_custkey` int(11) NOT NULL,
    `o_orderstatus` varchar(1) NOT NULL,
    `o_totalprice` decimal(15, 2) NOT NULL,
    `o_orderdate` date NOT NULL,
    `o_orderpriority` varchar(15) NOT NULL,
    `o_clerk` varchar(15) NOT NULL,
    `o_shippriority` int(11) NOT NULL,
    `o_comment` varchar(79) NOT NULL,
    PRIMARY KEY (`o_orderdate`, `o_orderkey`),
    KEY `auto_shard_key_o_orderkey` USING BTREE (`o_orderkey`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1
PARTITION BY KEY(`o_orderkey`)
PARTITIONS 4

create table orders like sizetest.orders engine = 'oss' archive_mode = 'loading';

CREATE TABLE `ngsim` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Vehicle_ID` int(11) NOT NULL,
  `Frame_ID` int(11) NOT NULL,
  `Total_Frames` int(11) NOT NULL,
  `Global_Time` bigint(20) NOT NULL,
  `Local_X` decimal(10, 3) NOT NULL,
  `Local_Y` decimal(10, 3) NOT NULL,
  `Global_X` decimal(15, 3) NOT NULL,
  `Global_Y` decimal(15, 3) NOT NULL,
  `v_length` decimal(10, 3) NOT NULL,
  `v_Width` decimal(10, 3) NOT NULL,
  `v_Class` int(11) NOT NULL,
  `v_Vel` decimal(10, 3) NOT NULL,
  `v_Acc` decimal(10, 3) NOT NULL,
  `Lane_ID` int(11) NOT NULL,
  `O_Zone` char(10) DEFAULT NULL,
  `D_Zone` char(10) DEFAULT NULL,
  `Int_ID` char(10) DEFAULT NULL,
  `Section_ID` char(10) DEFAULT NULL,
  `Direction` char(10) DEFAULT NULL,
  `Movement` char(10) DEFAULT NULL,
  `Preceding` int(11) NOT NULL,
  `Following` int(11) NOT NULL,
  `Space_Headway` decimal(10, 3) NOT NULL,
  `Time_Headway` decimal(10, 3) NOT NULL,
  `Location` char(10) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`ID`)
PARTITIONS 4


/*+TDDL:load_data_auto_fill_auto_increment_column=true*/
load data local infile './dataset/Next_Generation_Simulation__NGSIM__Vehicle_Trajectories_and_Supporting_Data.csv' into table ngsim FIELDS TERMINATED BY ',' IGNORE 1 LINES (Vehicle_ID,Frame_ID,Total_Frames,Global_Time,Local_X,Local_Y,Global_X,Global_Y,v_length,v_Width,v_Class,v_Vel,v_Acc,Lane_ID,O_Zone,D_Zone,Int_ID,Section_ID,Direction,Movement,Preceding,Following,Space_Headway,Time_Headway,Location)

create table ngsim like sizetest.ngsim engine = 'oss' archive_mode = 'loading';

CREATE TABLE `ACCESS_LOG` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `CONTENT` varchar(10000) DEFAULT NULL,
    PRIMARY KEY (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`ID`)
PARTITIONS 4


/*+TDDL:load_data_auto_fill_auto_increment_column=true*/
load data local infile './dataset/access.log' into table ACCESS_LOG FIELDS TERMINATED BY '\n' IGNORE 1 LINES (CONTENT)

create table ACCESS_LOG like sizetest.ACCESS_LOG engine = 'oss' archive_mode = 'loading';

CREATE TABLE `USER_LOG` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `USER_ID` int(11) NOT NULL,
    `ITEM_ID` int(11) NOT NULL,
    `CAT_ID` int(11) NOT NULL,
    `SELLER_ID` int(11) NOT NULL,
    `BRAND_ID` int(11) DEFAULT NULL,
    `TIME_STAMP` char(4) NOT NULL,
    `ACTION_TYPE` char(1) NOT NULL,
    PRIMARY KEY (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`ID`)
PARTITIONS 4


/*+TDDL:load_data_auto_fill_auto_increment_column=true*/
load data local infile './dataset/user_log_format1.csv' into table user_log FIELDS TERMINATED BY ',' IGNORE 1 LINES (user_id,item_id,cat_id,seller_id,brand_id,time_stamp,action_type)

create table USER_LOG like sizetest.USER_LOG engine = 'oss' archive_mode = 'loading';

select logical_table_name, sum(table_rows),sum(extent_size)/1024/1024 as `size(MB)` 
from information_schema.files 
where logical_schema_name = 'sizetest_oss' 
      and remove_ts is null 
      and life_cycle = 1 
        and logical_table_name in ('ACCESS_LOG', 'ngsim', 'orders', 'USER_LOG')
group by logical_table_name;

1. 基于LSM Tree的HBase，通常基于compaction做通用场景的优化，具备普适性，这也是业界NewSQL数据库基于LSM Tree做存储引擎所具备的能力，通用在3~4倍的压缩率。
2. PolarDB-X的热数据，默认采用MySQL InnoDB，整体的空间基本和开源MySQL对齐，PolarDB-X的冷数据，相比于PolarDB-X的热数据场景，平均有3~4倍的压缩，基本对齐LSM Tree的压缩场景，另外，面向字符和数值类型居多的场景最多有10倍的压缩效果。
3. PolarDB-X基于存储介质的选型和优化，PolarDB-X On OSS的冷数据归档，结合高压缩率以及HDD存储成本的优势，成本仅为PolarDB-X热数据的1/20，具体计算公式 = 1 (3~4倍压缩率 * 6倍的OSS存储成本优势)。

1. 极低的冷数据存储成本，仅为在线热数据的1/20成本。

2. 一个数据库实例，一个SQL引擎可以同时访问热数据和冷数据，具备跨数据的join/union/subquery等。在数据库运维上，冷热数据具备一体化的备份恢复能力，支持任意时间点恢复。

3. 数据入湖，冷数据基于开源ORCFile通用格式，可以轻松对接Spark/Flink等开源生态，后续我们会开放对应Connector扩展接入大数据生态。