使用背景

为了减少跨机房流量使用，可以将一些不重要表设置为 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 权限即可。