TiDB placement_rule in sql使用

1、配置好tikv label

例如：

tikv_servers:
- host: 10.37.129.6
  ssh_port: 22
  port: 20160
  status_port: 20180
  deploy_dir: /tidb-deploy/tikv-20160
  data_dir: /tidb-data/tikv-20160
  log_dir: /tidb-deploy/tikv-20160/log
  config:
    server.labels:
      host: host1
      zone: zone1
  arch: amd64
  os: linux
- host: 10.37.129.6
  ssh_port: 22
  port: 20161
  status_port: 20181
  deploy_dir: /tidb-deploy/tikv-20161
  data_dir: /tidb-data/tikv-20161
  log_dir: /tidb-deploy/tikv-20161/log
  config:
    server.labels:
      host: host1
      zone: zone1
  arch: amd64
  os: linux
- host: 10.37.129.7
  ssh_port: 22
  port: 20160
  status_port: 20180
  deploy_dir: /tidb-deploy/tikv-20160
  data_dir: /tidb-data/tikv-20160
  log_dir: /tidb-deploy/tikv-20160/log
  config:
    server.labels:
      host: host2
      zone: zone2
  arch: amd64
  os: linux
- host: 10.37.129.8
  ssh_port: 22
  port: 20160
  status_port: 20180
  deploy_dir: /tidb-deploy/tikv-20160
  data_dir: /tidb-data/tikv-20160
  log_dir: /tidb-deploy/tikv-20160/log
  config:
    server.labels:
      host: host3
      zone: zone2
  arch: amd64
  os: linux

这里我们有四个tikv实例，其中10.37.129.6:20160 和10.37.129.6:20161 为zone1，10.37.129.7:20160和10.37.129.8:20160为zone2。

2、用sql创建placement rule和业务表

/*查看所有label*/
mysql> SHOW PLACEMENT LABELS;
+--------+-----------------------------+
| Key    | Values                      |
+--------+-----------------------------+
| engine | ["tiflash"]                 |
| host   | ["host1", "host2", "host3"] |
| zone   | ["zone1", "zone2"]          |
+--------+-----------------------------+

/* 配置为3副本，该placement rule表示`leader`只在`zone2`的tikv实例，`follow`在`zone1`和`zone2`各一副本。*/
mysql> create placement policy p1 leader_constraints="[+zone=zone2]" FOLLOWER_CONSTRAINTS="{+zone=zone1: 1,+zone=zone2: 1}";
mysql> create table test.t1 (id int) PLACEMENT POLICY='p1'; 

/* 配置为3副本，该placement rule表示`leader`只在`zone2`的tikv实例，`follow`在`zone1`有两副本。*/
mysql> create placement policy p2 leader_constraints="[+zone=zone2]" FOLLOWER_CONSTRAINTS="{+zone=zone1: 2}";
mysql> create table test.t2 (id int) PLACEMENT POLICY='p2';

3、检查表的peer存储

方式一：

mysql>  show table test.t1 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+-------------------------------------------------------------------------------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS               | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS                                                                    | SCHEDULING_STATE |
+-----------+-----------+---------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+-------------------------------------------------------------------------------------------+------------------+
|     60007 | t_207_    | t_208_  |     60008 |               1 | 60008, 60009, 60010 |          0 |            27 |          0 |                    1 |                0 | LEADER_CONSTRAINTS="[+zone=zone2]" FOLLOWER_CONSTRAINTS="{+zone=zone1: 1,+zone=zone2: 1}" | SCHEDULED        |
+-----------+-----------+---------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+-------------------------------------------------------------------------------------------+------------------+

mysql>  show table test.t2 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+----------------------------------------------------------------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS               | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS                                                     | SCHEDULING_STATE |
+-----------+-----------+---------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+----------------------------------------------------------------------------+------------------+
|     65009 | t_211_    | t_212_  |     65010 |               1 | 65010, 65012, 65013 |          0 |            39 |          0 |                    1 |                0 | LEADER_CONSTRAINTS="[+zone=zone2]" FOLLOWER_CONSTRAINTS="{+zone=zone1: 2}" | SCHEDULED        |
+-----------+-----------+---------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+----------------------------------------------------------------------------+------------------+

可以看到test.t1和test.t2表的peer及placement rule等相关信息

方式二：

mysql> select TRP.REGION_ID,PEER_ID,IS_LEADER,TRP.STORE_ID,DB_NAME,TABLE_NAME,LABEL,ADDRESS from information_schema.TIKV_REGION_PEERS TRP join information_schema.TIKV_REGION_STAT
US TRS on TRP.REGION_ID=TRS.REGION_ID join information_schema.TIKV_STORE_STATUS TSS on TRP.STORE_ID=TSS.STORE_ID where TABLE_NAME='t1' and DB_NAME='test';
+-----------+---------+-----------+----------+---------+------------+------------------------------------------------------------------------+-------------------+
| REGION_ID | PEER_ID | IS_LEADER | STORE_ID | DB_NAME | TABLE_NAME | LABEL                                                                  | ADDRESS           |
+-----------+---------+-----------+----------+---------+------------+------------------------------------------------------------------------+-------------------+
|     60007 |   60008 |         1 |        1 | test    | t1         | [{"key": "host", "value": "host3"}, {"key": "zone", "value": "zone2"}] | 10.37.129.8:20160 |
|     60007 |   60009 |         0 |        2 | test    | t1         | [{"key": "host", "value": "host2"}, {"key": "zone", "value": "zone2"}] | 10.37.129.7:20160 |
|     60007 |   60010 |         0 |        5 | test    | t1         | [{"key": "host", "value": "host1"}, {"key": "zone", "value": "zone1"}] | 10.37.129.6:20161 |
+-----------+---------+-----------+----------+---------+------------+------------------------------------------------------------------------+-------------------+

mysql> select TRP.REGION_ID,PEER_ID,IS_LEADER,TRP.STORE_ID,DB_NAME,TABLE_NAME,LABEL,ADDRESS from information_schema.TIKV_REGION_PEERS TRP join information_schema.TIKV_REGION_STAT
US TRS on TRP.REGION_ID=TRS.REGION_ID join information_schema.TIKV_STORE_STATUS TSS on TRP.STORE_ID=TSS.STORE_ID where TABLE_NAME='t2' and DB_NAME='test';
+-----------+---------+-----------+----------+---------+------------+------------------------------------------------------------------------+-------------------+
| REGION_ID | PEER_ID | IS_LEADER | STORE_ID | DB_NAME | TABLE_NAME | LABEL                                                                  | ADDRESS           |
+-----------+---------+-----------+----------+---------+------------+------------------------------------------------------------------------+-------------------+
|     65009 |   65012 |         0 |        5 | test    | t2         | [{"key": "host", "value": "host1"}, {"key": "zone", "value": "zone1"}] | 10.37.129.6:20161 |
|     65009 |   65013 |         0 |        6 | test    | t2         | [{"key": "host", "value": "host1"}, {"key": "zone", "value": "zone1"}] | 10.37.129.6:20160 |
|     65009 |   65010 |         1 |        1 | test    | t2         | [{"key": "host", "value": "host3"}, {"key": "zone", "value": "zone2"}] | 10.37.129.8:20160 |
+-----------+---------+-----------+----------+---------+------------+------------------------------------------------------------------------+-------------------+

可以看到peer对应的tikv label及ip