0
0
1
0
专栏/.../

Placement Rules in SQL 使用案例

 WalterWj  发表于  2023-08-10
原创新版本/特性解读6.x 实践

使用背景

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

0
0
1
0

版权声明：本文为 TiDB 社区用户原创文章，遵循 CC BY-NC-SA 4.0 版权协议，转载请附上原文出处链接和本声明。

评论
暂无评论
使用背景拓扑确定使用方法创建 test.pr 表查看 test.pr 表的 region 分布创建数据存放规则确认规则使用规则规则取消和删除取消表规则删除规则注意事项