Placement Rules in SQL 使用案例

作者： WalterWj

使用背景

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