TiDB 6.0 实战分享丨冷热存储分离解决方案

1. 冷热分离存储，降低存储成本

2. 业务底层物理隔离，实现同一集群不同存储

3. 合并 MySQL 业务，降低运维压力，提升管理效率

mysql>?show?placement?labels?;
+------+-----------------------------------------------------------------+
|?Key??|?Values??????????????????????????????????????????????????????????|
+------+-----------------------------------------------------------------+
|?disk?|?["ssd"]?????????????????????????????????????????????????????????|
|?host?|?["tikv1",?"tikv2",?"tikv3"]?|
|?rack?|?["r1"]??????????????????????????????????????????????????????????|
|?zone?|?["guangzhou"]???????????????????????????????????????????????????|
+------+-----------------------------------------------------------------+
4?rows?in?set?(0.00?sec)

(1) 基础放置策略

#创建放置策略
CREATE?PLACEMENT?POLICY?myplacementpolicy?PRIMARY_REGION="guangzhou"?REGIONS="guangzhou,shenzhen";

#将规则绑定至表或分区表，这样指定了放置规则
CREATE?TABLE?t1?(a?INT)?PLACEMENT?POLICY=myplacementpolicy;
CREATE?TABLE?t2?(a?INT);
ALTER?TABLE?t2?PLACEMENT?POLICY=myplacementpolicy;

#查看放置规则的调度进度，所有绑定规则的对象都是异步调度的。
SHOW?PLACEMENT;

#查看放置策略
SHOW?CREATE?PLACEMENT?POLICY?myplacementpolicy\G
select?*?from?information_schema.placement_policies\G

#修改放置策略，修改后会传播到所有绑定此放置策略的对象
ALTER?PLACEMENT?POLICY?myplacementpolicy?FOLLOWERS=5;

#删除没有绑定任何对象的放置策略
DROP?PLACEMENT?POLICY?myplacementpolicy;

(2) 高级放置策略

#?创建策略，指定数据只存储在ssd
CREATE?PLACEMENT?POLICY?storeonfastssd?CONSTRAINTS="[+disk=ssd]";

#?创建策略，指定数据只存储在hdd
CREATE?PLACEMENT?POLICY?storeonhdd?CONSTRAINTS="[+disk=hdd]";

#?在分区表应用高级放置策略，指定分区存储在hdd或者ssd上，未指定的分区由系统自动调度
CREATE?TABLE?t1?(id?INT,?name?VARCHAR(50),?purchased?DATE)
PARTITION?BY?RANGE(?YEAR(purchased)?)?(
PARTITION?p0?VALUES?LESS?THAN?(2000)?PLACEMENT?POLICY=storeonhdd,
PARTITION?p1?VALUES?LESS?THAN?(2005),
PARTITION?p2?VALUES?LESS?THAN?(2010),
PARTITION?p3?VALUES?LESS?THAN?(2015),
PARTITION?p4?VALUES?LESS?THAN?MAXVALUE?PLACEMENT?POLICY=storeonfastssd
);

功能验证

#?应用该策略的库、表、分区，数据会存储在ssd上
CREATE?PLACEMENT?POLICY?storeonssd?CONSTRAINTS="[+disk=ssd]"?;

#?应用该策略的库、表、分区，数据会存储在hdd上
CREATE?PLACEMENT?POLICY?storeonhdd?CONSTRAINTS="[+disk=hdd]";

#查看集群已有策略
mysql>?show?placement?\G
***************************?1.?row?***************************
Target:?POLICY?storeonhdd
Placement:?CONSTRAINTS="[+disk=hdd]"
Scheduling_State:?NULL
***************************?2.?row?***************************
Target:?POLICY?storeonssd
Placement:?CONSTRAINTS="[+disk=ssd]"
Scheduling_State:?NULL
2?rows?in?set?(0.02?sec)

#?创建数据库tidb_ssd_hdd_test，并设置该库默认落盘策略，设置后新建的表都会默认继承该策略
create?database?tidb_ssd_hdd_test??PLACEMENT?POLICY=storeonssd;

#?查看策略已经应用到指定库上
mysql>?show?placement?\G
***************************?1.?row?***************************
Target:?POLICY?storeonhdd
Placement:?CONSTRAINTS="[+disk=hdd]"
Scheduling_State:?NULL
***************************?2.?row?***************************
Target:?POLICY?storeonssd
Placement:?CONSTRAINTS="[+disk=ssd]"
Scheduling_State:?NULL
***************************?3.?row?***************************
Target:?DATABASE?tidb_ssd_hdd_test
Placement:?CONSTRAINTS="[+disk=ssd]"
Scheduling_State:?SCHEDULED
3?rows?in?set?(0.02?sec)


#?建立分区表，可以看到表建立后默认继承和库一样的落盘策略，关键标识为“/*T![placement]?PLACEMENT?POLICY=`storeonssd`?*/”
CREATE?TABLE?`logoutrole_log?`?(
`doc_id`?varchar(255)?NOT?NULL,
`gameid`?varchar(255)?DEFAULT?NULL?,
--?some?fields
`logdt`?timestamp?DEFAULT?'1970-01-01?08:00:00'?,
`updatetime`?varchar(255)?DEFAULT?NULL?,
UNIQUE?KEY?`doc_id`?(`doc_id`,`logdt`),
--?some?index
KEY?`logdt_gameid`?(`logdt`,`gameid`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_bin?/*T![placement]?PLACEMENT?POLICY=`storeonssd`?*/
PARTITION?BY?RANGE?(?UNIX_TIMESTAMP(`logdt`)?)?(
PARTITION?`p20220416`?VALUES?LESS?THAN?(UNIX_TIMESTAMP('2022-04-17?00:00:00')),
PARTITION?`p20220417`?VALUES?LESS?THAN?(UNIX_TIMESTAMP('2022-04-18?00:00:00')),
PARTITION?`p20220418`?VALUES?LESS?THAN?(UNIX_TIMESTAMP('2022-04-19?00:00:00')),
PARTITION?`p20220419`?VALUES?LESS?THAN?(UNIX_TIMESTAMP('2022-04-20?00:00:00')),
PARTITION?`p20220420`?VALUES?LESS?THAN?(UNIX_TIMESTAMP('2022-04-21?00:00:00')),
PARTITION?`p20220421`?VALUES?LESS?THAN?(UNIX_TIMESTAMP('2022-04-22?00:00:00')),
PARTITION?`p20220422`?VALUES?LESS?THAN?(UNIX_TIMESTAMP('2022-04-23?00:00:00'))
);

mysql>?select?date(logdt)?as?day,count(0)?from?logoutrole_log?group?by?day?order?by?day?;
+------------+----------+
|?day????????|?count(0)?|
+------------+----------+
|?2022-04-16?|??1109819?|
+------------+----------+
1?row?in?set?(1.09?sec)

mysql>?alter?table?tidb_ssd_hdd_test.logoutrole_log?partition?p20220416?placement?policy?storeonhdd;
Query?OK,?0?rows?affected?(0.52?sec)

静态集群冷热存储分离（无外部访问）

alter?table?tidb_ssd_hdd_test.logoutrole_log?partition?p20220417?placement?policy?storeonhdd;

热集群冷热存储分离（外部持续访问）

#应用放置策略将2022-04-18数据从ssd归档到hdd
alter?table?tidb_ssd_hdd_test.logoutrole_log?partition?p20220418?placement?policy?storeonhdd;

归档数据补写

CREATE?PLACEMENT?POLICY?'shared_nodes'?CONSTRAINTS?=?"[+region=shared_nodes]";
CREATE?PLACEMENT?POLICY?'business_c'?CONSTRAINTS?=?"[+region=business_c]";
CREATE?PLACEMENT?POLICY?'business_d'?CONSTRAINTS?=?"[+region=business_d]";
ALTER?DATABASE?a?POLICY=shared_nodes;
ALTER?DATABASE?b?POLICY=shared_nodes;
ALTER?DATABASE?c?POLICY=business_c;
ALTER?DATABASE?d?POLICY=business_d;