使用背景
为了减少跨机房流量使用,可以将一些不重要表设置为 3 副本且固定在主机房。
拓扑确定
使用非功能测试环境做测试,先确认当前拓扑:
默认一共 5 副本,当前 3 副本会放在 rack1/2/4,2 副本放在 rack3/5
使用方法
创建 test.pr 表
Create table pr (id int primary key,c1 varchar(200));
查看 test.pr 表的 region 分布
SELECT
trs.DB_NAME ,
trs.TABLE_NAME ,
trp.REGION_ID ,
trp.PEER_ID ,
trp.STORE_ID ,
trp.IS_LEARNER ,
trp.IS_LEADER ,
tss.LABEL
FROM
INFORMATION_SCHEMA.TIKV_REGION_STATUS trs ,
INFORMATION_SCHEMA.TIKV_REGION_PEERS trp ,
INFORMATION_SCHEMA.TIKV_STORE_STATUS tss
WHERE
trs.REGION_ID = trp.REGION_ID
and trp.STORE_ID = tss.STORE_ID
AND LOWER(trs.DB_NAME) = 'test'
and LOWER(trs.TABLE_NAME) = 'pr';
DB_NAME|TABLE_NAME|REGION_ID|PEER_ID |STORE_ID|IS_LEARNER|LABEL |
-------+----------+---------+--------+--------+----------+----------------------------------------------------------------------------------------------------------------------------------------+
test |pr | 3668516|14216698| 4648105| 0|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack4"}, {"key": "host", "value": "host4"}, {"key": "dc", "value": "dc1"}]|
test |pr | 3668516|14217644| 3| 0|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack2"}, {"key": "host", "value": "host2"}, {"key": "dc", "value": "dc1"}]|
test |pr | 3668516|14217004| 3660486| 0|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack1"}, {"key": "host", "value": "host1"}, {"key": "dc", "value": "dc1"}]|
test |pr | 3668516|14235069| 4648108| 0|[{"key": "zone", "value": "zone2"}, {"key": "rack", "value": "rack5"}, {"key": "host", "value": "host5"}, {"key": "dc", "value": "dc2"}]|
test |pr | 3668516|14217728| 13| 0|[{"key": "zone", "value": "zone2"}, {"key": "rack", "value": "rack3"}, {"key": "host", "value": "host3"}, {"key": "dc", "value": "dc2"}]|
可以看到 test.pr 表有 5 副本,region id 为 3668516,5 个 peer id,label 信息可以看到 3 个在 zone1 和 dc1,2个在 dc2 和 zone2。
创建数据存放规则
创建存放规则名为 3policy,规则设置 3 副本,leader 和两个副本都在 dc1(机房label,主机房) 中。
Create placement policy 3policy leader_constraints="[+dc=dc1]" follower_constraints="{+dc=dc1: 2}";
确认规则
SELECT * FROM INFORMATION_SCHEMA.PLACEMENT_POLICIES pp ;
POLICY_ID|CATALOG_NAME|POLICY_NAME|PRIMARY_REGION|REGIONS|CONSTRAINTS|LEADER_CONSTRAINTS|FOLLOWER_CONSTRAINTS|LEARNER_CONSTRAINTS|SCHEDULE|FOLLOWERS|LEARNERS|
---------+------------+-----------+--------------+-------+-----------+------------------+--------------------+-------------------+--------+---------+--------+
1|def |3policy | | | |[+dc=dc1] |{+dc=dc1: 2} | | | 2| 0|
可以看到规则已经创建成功。注意:绑定规则的对象都是异步调度。
查看规则
Show placement;
Target |Placement |Scheduling_State|
--------------+------------------------------------------------------------------+----------------+
POLICY 3policy|LEADER_CONSTRAINTS="[+dc=dc1]" FOLLOWER_CONSTRAINTS="{+dc=dc1: 2}"|NULL |
也可以看下当前标签信息
Key |Values |
------+---------------------------------------------+
dc |["dc1", "dc2"] |
engine|["tiflash"] |
host |["host1", "host2", "host3", "host4", "host5"]|
rack |["rack1", "rack2", "rack3", "rack4", "rack5"]|
zone |["zone1", "zone2"] |
使用规则
为表指定放置规则
alter table test.pr placement policy 3policy;
这样 test.pr 表就会使用3policy 的规则。
查看规则
Show placement;
Target |Placement |Scheduling_State|
--------------+------------------------------------------------------------------+----------------+
POLICY 3policy|LEADER_CONSTRAINTS="[+dc=dc1]" FOLLOWER_CONSTRAINTS="{+dc=dc1: 2}"|NULL |
TABLE test.pr |LEADER_CONSTRAINTS="[+dc=dc1]" FOLLOWER_CONSTRAINTS="{+dc=dc1: 2}"|SCHEDULED |
查看 test.pr 的region 分布
SELECT
trs.DB_NAME ,
trs.TABLE_NAME ,
trp.REGION_ID ,
trp.PEER_ID ,
trp.STORE_ID ,
trp.IS_LEARNER ,
trp.IS_LEADER ,
tss.LABEL
FROM
INFORMATION_SCHEMA.TIKV_REGION_STATUS trs ,
INFORMATION_SCHEMA.TIKV_REGION_PEERS trp ,
INFORMATION_SCHEMA.TIKV_STORE_STATUS tss
WHERE
trs.REGION_ID = trp.REGION_ID
and trp.STORE_ID = tss.STORE_ID
AND LOWER(trs.DB_NAME) = 'test'
and LOWER(trs.TABLE_NAME) = 'pr';
DB_NAME|TABLE_NAME|REGION_ID|PEER_ID |STORE_ID|IS_LEARNER|IS_LEADER|LABEL |
-------+----------+---------+--------+--------+----------+---------+----------------------------------------------------------------------------------------------------------------------------------------+
test |pr | 14241662|14241665| 3| 0| 0|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack2"}, {"key": "host", "value": "host2"}, {"key": "dc", "value": "dc1"}]|
test |pr | 14241662|14241663| 4648105| 0| 1|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack4"}, {"key": "host", "value": "host4"}, {"key": "dc", "value": "dc1"}]|
test |pr | 14241662|14241664| 3660486| 0| 0|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack1"}, {"key": "host", "value": "host1"}, {"key": "dc", "value": "dc1"}]|
可以看到 test.pr 的 region 分布,都存放在 dc1(主机房) 中。
当然,也可以在创建表的时候就直接指定规则:
CREATE TABLE `pr` (
`id` int(11) NOT NULL,
`c1` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PLACEMENT POLICY=`3policy`;
规则取消和删除
取消表规则
alter table test.pr placement policy default;
SELECT
trs.DB_NAME ,
trs.TABLE_NAME ,
trp.REGION_ID ,
trp.PEER_ID ,
trp.STORE_ID ,
trp.IS_LEARNER ,
trp.IS_LEADER ,
tss.LABEL
FROM
INFORMATION_SCHEMA.TIKV_REGION_STATUS trs ,
INFORMATION_SCHEMA.TIKV_REGION_PEERS trp ,
INFORMATION_SCHEMA.TIKV_STORE_STATUS tss
WHERE
trs.REGION_ID = trp.REGION_ID
and trp.STORE_ID = tss.STORE_ID
AND LOWER(trs.DB_NAME) = 'test'
and LOWER(trs.TABLE_NAME) = 'pr';
DB_NAME|TABLE_NAME|REGION_ID|PEER_ID |STORE_ID|IS_LEARNER|IS_LEADER|LABEL |
-------+----------+---------+--------+--------+----------+---------+----------------------------------------------------------------------------------------------------------------------------------------+
test |pr | 14241662|14241664| 3660486| 0| 0|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack1"}, {"key": "host", "value": "host1"}, {"key": "dc", "value": "dc1"}]|
test |pr | 14241662|14241663| 4648105| 0| 1|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack4"}, {"key": "host", "value": "host4"}, {"key": "dc", "value": "dc1"}]|
test |pr | 14241662|14242825| 4648110| 0| 0|[{"key": "zone", "value": "zone2"}, {"key": "rack", "value": "rack5"}, {"key": "host", "value": "host5"}, {"key": "dc", "value": "dc2"}]|
test |pr | 14241662|14241665| 3| 0| 0|[{"key": "zone", "value": "zone1"}, {"key": "rack", "value": "rack2"}, {"key": "host", "value": "host2"}, {"key": "dc", "value": "dc1"}]|
test |pr | 14241662|14242824| 12| 0| 0|[{"key": "zone", "value": "zone2"}, {"key": "rack", "value": "rack3"}, {"key": "host", "value": "host3"}, {"key": "dc", "value": "dc2"}]|
可以看到表数据分布恢复 5 副本。
删除规则
Drop placement policy 3policy;
Show placement;
Target|Placement|Scheduling_State|
------+---------+----------------+
可以看到规则已经没了。
注意:需要删除的规则不能有任何表被绑定。
注意事项
需要注意,以上命令数据库服务需要的权限:
创建规则需要有 create 权限
使用规则 alter 需要权限
如果是 create table 就带上规则,只需要 create 权限即可。