## 模块 4：实现冷热数据分层存储 在本模块中，你将体验 TiDB 的 SQL 中的 Placement Rules 功能，该功能允许你使用 SQL 语句指定数据在 TiDB 中的存储位置。你将练习使用 Placement 规则，通过表分区将冷数据分离到 `lower-cost` 存储（或低性能节点），将热数据分离到 `higher-cost` 存储（或高性能节点），以提高成本效率。 ![40%](https://lab-static.pingcap.com/quick-demo/PlacementRuleColdHot_aa_001_20230422.png) ### 步骤 1. 扩容 TiKV 集群 + 扩容 TiKV 集群，添加三个具有低性能存储的额外 TiKV 节点。在本实验中，我们只是在每个运行 TiKV 节点的 EC2 上额外放置一个 TiKV 实例。我们假设三个新的 TiKV 节点具有低性能存储，而三个现有的 TiKV 节点具有高性能存储。 ```bash tiup cluster scale-out tidb-test solution-topology-placement-rule.yaml --yes --user ec2-user -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ``` ```output ... Scaled cluster `tidb-test` out successfully ``` ### 步骤 2. 检查 TiDB 集群的状态 ```bash tiup cluster display tidb-test ``` ```output ... Cluster type: tidb Cluster name: tidb-test Cluster version: v7.5.1 Deploy user: tidb SSH type: builtin Dashboard URL: http://10.90.2.59:2379/dashboard Grafana URL: http://10.90.4.204:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.204:9093 alertmanager 10.90.4.204 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.204:3000 grafana 10.90.4.204 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.1.22:2379 pd 10.90.1.22 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.2.59:2379 pd 10.90.2.59 2379/2380 linux/x86_64 Up|L|UI /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.126:2379 pd 10.90.3.126 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.204:9090 prometheus 10.90.4.204 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.123:4000 tidb 10.90.1.123 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.2.193:4000 tidb 10.90.2.193 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.100:9000 tiflash 10.90.4.100 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.1.45:20160 tikv 10.90.1.45 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.45:20161 tikv 10.90.1.45 20161/20181 linux/x86_64 Up /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 10.90.2.251:20160 tikv 10.90.2.251 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.251:20161 tikv 10.90.2.251 20161/20181 linux/x86_64 Up /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 10.90.3.130:20160 tikv 10.90.3.130 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.130:20161 tikv 10.90.3.130 20161/20181 linux/x86_64 Up /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 Total nodes: 15 ``` ### 步骤 3. 创建 placement policies + 创建 placement policies，将热数据指定到具有高性能存储的 TiKV 节点，将冷数据指定到具有低性能存储的 TiKV 节点。 + 以 root 身份连接到 TiDB server。 ```bash ./connect-db1.sh ``` ```output tidb:db1> ``` + 查看现有的 placement rules 的标签，注意 Key 为 `storage-performance`的标签，目前可以忽略 `engine`, `zone` 和 `region`。 ```sql SHOW PLACEMENT LABELS; ``` ```output tidb:db1> SHOW PLACEMENT LABELS; +---------------------+-----------------------+ | Key | Values | +---------------------+-----------------------+ | engine | ["tiflash"] | | region | ["Seattle"] | | storage-performance | ["high", "low"] | | zone | ["az1", "az2", "az3"] | +---------------------+-----------------------+ 4 rows in set (0.00 sec) ``` + 检查可用的标签及其对应的 TiKV 存储。请注意，`store_id` 列表示 TiKV 存储 ID， `label` 列表示每个 TiKV 存储的标签键值对。 ```sql SELECT store_id, address, store_state_name, label FROM information_schema.tikv_store_status; ``` ```output tidb:db1> SELECT store_id, address, store_state_name, label -> FROM information_schema.tikv_store_status; +----------+-------------------+------------------+--------------------------------------------------------------------------------------------------------------------------+ | store_id | address | store_state_name | label | +----------+-------------------+------------------+--------------------------------------------------------------------------------------------------------------------------+ | 1 | 10.90.3.130:20160 | Up | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "high"}, {"key": "zone", "value": "az3"}]| | 2 | 10.90.2.251:20160 | Up | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "high"}, {"key": "zone", "value": "az2"}]| | 3 | 10.90.1.45:20160 | Up | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "high"}, {"key": "zone", "value": "az1"}]| | 98 | 10.90.4.100:3930 | Up | [{"key": "engine", "value": "tiflash"}] | | 186 | 10.90.2.251:20161 | Up | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az2"}] | | 187 | 10.90.1.45:20161 | Up | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az1"}] | | 188 | 10.90.3.130:20161 | Up | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | +----------+-------------------+------------------+--------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.01 sec) ``` + 创建热数据的 placement policy 将其映射到键值对： `(storage-performance, high)`: ```sql DROP PLACEMENT POLICY IF EXISTS hot; ``` ```output tidb:db1> DROP PLACEMENT POLICY IF EXISTS hot; Query OK, 0 rows affected, 1 warning (0.00 sec) ``` ```sql CREATE PLACEMENT POLICY hot CONSTRAINTS="[+storage-performance=high]"; ``` ```output tidb:db1> CREATE PLACEMENT POLICY hot -> CONSTRAINTS="[+storage-performance=high]"; Query OK, 0 rows affected (0.07 sec) ``` + 创建冷数据的 placement policy，将其映射到键值对：`(storage-performance, low)`: ```sql DROP PLACEMENT POLICY IF EXISTS cold; ``` ```output tidb:db1> DROP PLACEMENT POLICY IF EXISTS cold; Query OK, 0 rows affected, 1 warning (0.01 sec) ``` ```sql CREATE PLACEMENT POLICY cold CONSTRAINTS="[+storage-performance=low]"; ``` ```output tidb:db1> CREATE PLACEMENT POLICY cold -> CONSTRAINTS="[+storage-performance=low]"; Query OK, 0 rows affected (0.07 sec) ``` + 查看已创建的 placement policies `hot` 和 `cold` 的约束定义。 ```sql SELECT policy_name, constraints FROM information_schema.placement_policies; ``` ```output tidb:db1> SELECT policy_name, constraints FROM information_schema.placement_policies; +-------------+-----------------------------+ | policy_name | constraints | +-------------+-----------------------------+ | hot | [+storage-performance=high] | | cold | [+storage-performance=low] | +-------------+-----------------------------+ 2 rows in set (0.00 sec) ``` ### 步骤 4. 创建两个表 + 使用上一步中的 placement policies 创建两个 range 分区表，一个有冷热分区，另一个只有冷分区，以最大限度地提高存储成本效率。 + 使用 `hot` 和 `cold` placement policies 创建分区表 `test.orders`。请注意，`order_date` b在 2010 年之前以及 2010 年到 2015 年（不包括 2015 年）之间标记为 `cold`, 而 `order_date` 在 2015 年之后标记为 `hot`，映射到 `MAXVALUE` 分区。partition: ```sql DROP TABLE IF EXISTS test.orders; ``` ```output tidb:db1> DROP TABLE IF EXISTS test.orders; Query OK, 0 rows affected, 1 warning (0.00 sec) ``` ```sql CREATE TABLE test.orders (order_id INT, item VARCHAR(50), order_date DATE, PRIMARY KEY (order_date, order_id)) PARTITION BY RANGE( YEAR(order_date) ) ( PARTITION p10 VALUES LESS THAN (2010) PLACEMENT POLICY=cold, PARTITION p15 VALUES LESS THAN (2015) PLACEMENT POLICY=cold, PARTITION pm VALUES LESS THAN MAXVALUE PLACEMENT POLICY=hot ); ``` ```output tidb:db1> CREATE TABLE test.orders (order_id INT, item VARCHAR(50), order_date DATE, PRIMARY KEY (order_date, order_id)) -> PARTITION BY RANGE( YEAR(order_date) ) ( -> PARTITION p10 VALUES LESS THAN (2010) PLACEMENT POLICY=cold, -> PARTITION p15 VALUES LESS THAN (2015) PLACEMENT POLICY=cold, -> PARTITION pm VALUES LESS THAN MAXVALUE PLACEMENT POLICY=hot -> ); Query OK, 0 rows affected (0.34 sec) ``` + 创建分区表 `test.orders_log`，仅对其所有分区使用 `cold` placement policy。 ```sql DROP TABLE IF EXISTS test.orders_log; ``` ```output tidb:db1> DROP TABLE IF EXISTS test.orders_log; Query OK, 0 rows affected, 1 warning (0.00 sec) ``` ```sql CREATE TABLE test.orders_log (order_id INT, item VARCHAR(50), order_date DATE, PRIMARY KEY (order_date, order_id)) PARTITION BY RANGE( YEAR(order_date) ) ( PARTITION p10 VALUES LESS THAN (2010) PLACEMENT POLICY=cold, PARTITION p15 VALUES LESS THAN (2015) PLACEMENT POLICY=cold, PARTITION pm VALUES LESS THAN MAXVALUE PLACEMENT POLICY=cold ); ``` ```output tidb:db1> CREATE TABLE test.orders_log (order_id INT, item VARCHAR(50), order_date DATE, PRIMARY KEY (order_date, order_id)) -> PARTITION BY RANGE( YEAR(order_date) ) ( -> PARTITION p10 VALUES LESS THAN (2010) PLACEMENT POLICY=cold, -> PARTITION p15 VALUES LESS THAN (2015) PLACEMENT POLICY=cold, -> PARTITION pm VALUES LESS THAN MAXVALUE PLACEMENT POLICY=cold -> ); Query OK, 0 rows affected (0.38 sec) ``` ### 步骤 5. 验证 placement rules 已生效 + 展示关于冷热表 `test.orders` 的 TiKV region信息. 注意具有最高 `START_KEY` 的 `REGION_ID`，它是 `MAXVALUE` 分区。 在示例输出中，`REGION_ID` 是 264 (请注意，在你的环境中，region id 和 start keys 可能不同）。 ```sql SHOW TABLE test.orders REGIONS\G ``` ```output tidb:db1> SHOW TABLE test.orders REGIONS\G *************************** 1. row *************************** REGION_ID: 256 START_KEY: t_83_ END_KEY: t_84_ LEADER_ID: 259 LEADER_STORE_ID: 188 PEERS: 259, 269, 271 SCATTERING: 0 WRITTEN_BYTES: 136 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: CONSTRAINTS="[+storage-performance=low]" SCHEDULING_STATE: SCHEDULED *************************** 2. row *************************** REGION_ID: 260 START_KEY: t_84_ END_KEY: t_85_ LEADER_ID: 263 LEADER_STORE_ID: 188 PEERS: 263, 270, 272 SCATTERING: 0 WRITTEN_BYTES: 136 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: CONSTRAINTS="[+storage-performance=low]" SCHEDULING_STATE: SCHEDULED *************************** 3. row *************************** REGION_ID: 264 START_KEY: t_85_ END_KEY: t_86_ LEADER_ID: 266 LEADER_STORE_ID: 3 PEERS: 265, 266, 268 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: CONSTRAINTS="[+storage-performance=high]" SCHEDULING_STATE: SCHEDULED 3 rows in set (0.02 sec) ``` + 查看 `test.orders` 表的 region 信息与 TiKV 存储的映射是否与 placement policy 一致。根据 placement rules, `REGION_ID` 为 264 的 region 应该应映射到 `storage-performance:high` 的 TiKV 节点，而另外两个 region 256 和 260 应该应映射到 `storage-performance:low`的 TiKV 节点。 ```sql SELECT kvrs.region_id, kvss.store_id, kvrp.is_leader, kvss.label, kvss.start_ts FROM information_schema.tikv_region_status kvrs JOIN information_schema.tikv_region_peers kvrp ON kvrs.region_id = kvrp.region_id JOIN information_schema.tikv_store_status kvss ON kvrp.store_id = kvss.store_id JOIN information_schema.tables t ON t.table_name = kvrs.table_name WHERE t.table_schema='test' AND t.table_name='orders' AND kvrs.is_index = 0 AND kvrp.is_leader = 1; ``` ```output tidb:db1> SELECT kvrs.region_id, kvss.store_id, kvrp.is_leader, kvss.label, kvss.start_ts -> FROM information_schema.tikv_region_status kvrs -> JOIN information_schema.tikv_region_peers kvrp -> ON kvrs.region_id = kvrp.region_id -> JOIN information_schema.tikv_store_status kvss -> ON kvrp.store_id = kvss.store_id -> JOIN information_schema.tables t -> ON t.table_name = kvrs.table_name -> WHERE t.table_schema='test' -> AND t.table_name='orders' -> AND kvrs.is_index = 0 -> AND kvrp.is_leader = 1; +-----------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------+---------------------+ | region_id | store_id | is_leader | label | start_ts | +-----------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------+---------------------+ | 264 | 3 | 1 | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "high"}, {"key": "zone", "value": "az1"}]| 2023-06-08 06:47:58 | | 256 | 188 | 1 | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | 2023-06-08 06:49:39 | | 260 | 188 | 1 | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | 2023-06-08 06:49:39 | +-----------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------+---------------------+ 3 rows in set (0.01 sec) ``` + 展示仅包含 `COLD` 的表 `test.orders_log`的 TiKV region 信息。注意所有 region 的 `REGION_ID`，在示例输出中，它们是 277、281 和 285（请注意，在你的环境中， region id 和 start keys 可能不同）: ```sql SHOW TABLE test.orders_log REGIONS\G ``` ```output tidb:db1> SHOW TABLE test.orders_log REGIONS\G *************************** 1. row *************************** REGION_ID: 277 START_KEY: t_88_ END_KEY: t_89_ LEADER_ID: 280 LEADER_STORE_ID: 188 PEERS: 280, 289, 292 SCATTERING: 0 WRITTEN_BYTES: 136 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: CONSTRAINTS="[+storage-performance=low]" SCHEDULING_STATE: SCHEDULED *************************** 2. row *************************** REGION_ID: 281 START_KEY: t_89_ END_KEY: t_90_ LEADER_ID: 284 LEADER_STORE_ID: 188 PEERS: 284, 291, 294 SCATTERING: 0 WRITTEN_BYTES: 136 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: CONSTRAINTS="[+storage-performance=low]" SCHEDULING_STATE: SCHEDULED *************************** 3. row *************************** REGION_ID: 285 START_KEY: t_90_ END_KEY: t_91_ LEADER_ID: 288 LEADER_STORE_ID: 188 PEERS: 288, 290, 293 SCATTERING: 0 WRITTEN_BYTES: 136 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: CONSTRAINTS="[+storage-performance=low]" SCHEDULING_STATE: SCHEDULED 3 rows in set (0.02 sec) ``` + 检查 `test.orders_log` 表的 region 与 TiKV 存储的映射是否于 placement policy一致。 根据 placement rules，所有 region 都应映射到 `storage-performance:low`的 TiKV 节点。 ```sql SELECT kvrs.region_id, kvss.store_id, kvrp.is_leader, kvss.label, kvss.start_ts FROM information_schema.tikv_region_status kvrs JOIN information_schema.tikv_region_peers kvrp ON kvrs.region_id = kvrp.region_id JOIN information_schema.tikv_store_status kvss ON kvrp.store_id = kvss.store_id JOIN information_schema.tables t ON t.table_name = kvrs.table_name WHERE t.table_schema='test' AND t.table_name='orders_log' AND kvrs.is_index = 0 AND kvrp.is_leader = 1; ``` ```output tidb:db1> SELECT kvrs.region_id, kvss.store_id, kvrp.is_leader, kvss.label, kvss.start_ts -> FROM information_schema.tikv_region_status kvrs -> JOIN information_schema.tikv_region_peers kvrp -> ON kvrs.region_id = kvrp.region_id -> JOIN information_schema.tikv_store_status kvss -> ON kvrp.store_id = kvss.store_id -> JOIN information_schema.tables t -> ON t.table_name = kvrs.table_name -> WHERE t.table_schema='test' -> AND t.table_name='orders_log' -> AND kvrs.is_index = 0 -> AND kvrp.is_leader = 1; +-----------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+ | region_id | store_id | is_leader | label | start_ts | +-----------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+ | 281 | 188 | 1 | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}]| 2023-06-08 06:49:39 | | 285 | 188 | 1 | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}]| 2023-06-08 06:49:39 | | 277 | 188 | 1 | [{"key": "region", "value": "Seattle"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}]| 2023-06-08 06:49:39 | +-----------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+ 3 rows in set (0.01 sec) ``` + 运行查询跟踪以查看 `test.orders`表的分区裁剪。由于你查询的是 2010 年至 2015 年之间的数据，预计会在冷存储上进行查询扫描。从输出中可以观察到，扫描的是 region ID 260 的region，它位于标记为 `storage-performance:low` 的 TiKV 节点上: ```bash TRACE SELECT item, order_date FROM test.orders WHERE order_date BETWEEN STR_TO_DATE('01/01/2013', '%m/%d/%Y') AND STR_TO_DATE('12/31/2013', '%m/%d/%Y'); ``` ```output tidb:db1> TRACE -> SELECT item, order_date FROM test.orders -> WHERE order_date BETWEEN STR_TO_DATE('01/01/2013', '%m/%d/%Y') AND STR_TO_DATE('12/31/2013', '%m/%d/%Y'); +----------------------------------------------------------------------+-----------------+------------+ | operation | startTS | duration | +----------------------------------------------------------------------+-----------------+------------+ | trace | 07:21:19.269181 | 3.05361ms | | ├─session.ExecuteStmt | 07:21:19.269186 | 993.479µs | | │ ├─executor.Compile | 07:21:19.269203 | 325.317µs | | │ └─session.runStmt | 07:21:19.269551 | 604.178µs | | │ └─TableReaderExecutor.Open | 07:21:19.270058 | 64.011µs | | │ └─distsql.Select | 07:21:19.270078 | 31.097µs | | │ └─regionRequest.SendReqCtx | 07:21:19.270210 | 1.92593ms | | │ └─rpcClient.SendRequest, region ID: 260, type: Cop | 07:21:19.270231 | 1.894279ms | | │ └─tikv.RPC | 07:21:19.270232 | 638.566µs | | │ ├─tikv.Wait | 07:21:19.270232 | 36.129µs | | │ │ └─tikv.GetSnapshot | 07:21:19.270232 | 36.129µs | | │ └─tikv.Process | 07:21:19.270268 | 9.578µs | | │ └─tikv.RocksDBBlockRead | 07:21:19.270268 | 9.578µs | | └─*executor.TableReaderExecutor.Next | 07:21:19.270188 | 2.011021ms | +----------------------------------------------------------------------+-----------------+------------+ 14 rows in set (0.00 sec) ``` + 再次运行查询跟踪以检查 `test.orders` 表的分区裁剪。由于你查询的是 2015 年之后的数据，预计会在热存储上进行查询扫描。从输出中可以观察到，扫描的是 region ID 264 的 region ，它位于标记为 `storage-performance:high` 的 TiKV 节点上。 ```sql TRACE SELECT item, order_date FROM test.orders WHERE order_date BETWEEN STR_TO_DATE('01/01/2018', '%m/%d/%Y') AND STR_TO_DATE('12/31/2020', '%m/%d/%Y'); ``` ```output tidb:db1> TRACE -> SELECT item, order_date FROM test.orders -> WHERE order_date BETWEEN STR_TO_DATE('01/01/2018', '%m/%d/%Y') AND STR_TO_DATE('12/31/2020', '%m/%d/%Y'); +----------------------------------------------------------------------+-----------------+------------+ | operation | startTS | duration | +----------------------------------------------------------------------+-----------------+------------+ | trace | 07:22:01.845194 | 3.520554ms | | ├─session.ExecuteStmt | 07:22:01.845198 | 1.770717ms | | │ ├─executor.Compile | 07:22:01.845213 | 316.007µs | | │ └─session.runStmt | 07:22:01.845548 | 1.403662ms | | │ └─TableReaderExecutor.Open | 07:22:01.846018 | 896.463µs | | │ └─distsql.Select | 07:22:01.846035 | 864.376µs | | │ ├─loadRegion | 07:22:01.846045 | 813.222µs | | │ └─regionRequest.SendReqCtx | 07:22:01.847019 | 1.598742ms | | │ └─rpcClient.SendRequest, region ID: 264, type: Cop | 07:22:01.847036 | 1.569261ms | | │ └─tikv.RPC | 07:22:01.847037 | 1.176324ms | | │ └─tikv.Wait | 07:22:01.847037 | 1ms | | │ └─tikv.GetSnapshot | 07:22:01.847037 | 995.958µs | | └─*executor.TableReaderExecutor.Next | 07:22:01.846982 | 1.689167ms | +----------------------------------------------------------------------+-----------------+------------+ 13 rows in set (0.01 sec) ``` + 如果你感兴趣，可以对 `test.orders_log`表尝试类似的查询，该表的数据只会存储在冷 TiKV 节点上。 ### 步骤 6. 删除表和 placement policies ```sql DROP TABLE IF EXISTS test.orders; DROP TABLE IF EXISTS test.orders_log; DROP PLACEMENT POLICY IF EXISTS hot; DROP PLACEMENT POLICY IF EXISTS cold; ``` ```output Query OK, 0 rows affected (0.52 sec) Query OK, 0 rows affected (0.52 sec) Query OK, 0 rows affected (0.53 sec) Query OK, 0 rows affected (0.52 sec) ``` ### 步骤 7. 退出 TiDB 连接，并对 TiKV 节点进行缩容 ```sql EXIT ``` ```output tidb:db1> EXIT Bye ``` ```bash tiup cluster scale-in tidb-test --node ${HOST_KV1_PRIVATE_IP}:20161 --yes ``` ```output ... Scaled cluster `tidb-test` in successfully ``` ```bash tiup cluster scale-in tidb-test --node ${HOST_KV2_PRIVATE_IP}:20161 --yes ``` ```output ... Scaled cluster `tidb-test` in successfully ``` ```bash tiup cluster scale-in tidb-test --node ${HOST_KV3_PRIVATE_IP}:20161 --yes ``` ```output ... Scaled cluster `tidb-test` in successfully ``` + 被删除的 `tikv`会在几分钟或几小时后变为 `ombstone` 状态之后你可以使用 prune c命令将其清除。 如果你不想等待，可以在缩容命令中使用 `--force` 选项。不过，你可能会在监控服务和仪表盘中看到异常情况。 ```bash tiup cluster display tidb-test ``` ```output ... Cluster type: tidb Cluster name: tidb-test Cluster version: v7.5.0 Deploy user: tidb SSH type: builtin Dashboard URL: http://10.90.2.59:2379/dashboard Grafana URL: http://10.90.4.204:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.14:9093 alertmanager 10.90.4.14 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.14:3000 grafana 10.90.4.14 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.1.254:2379 pd 10.90.1.254 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.2.60:2379 pd 10.90.2.60 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.58:2379 pd 10.90.3.58 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.14:9090 prometheus 10.90.4.14 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.2.231:4000 tidb 10.90.2.231 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.3.141:4000 tidb 10.90.3.141 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.207:9000 tiflash 10.90.4.207 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.1.71:20160 tikv 10.90.1.71 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.71:20161 tikv 10.90.1.71 20161/20181 linux/x86_64 Pending Offline /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 10.90.2.109:20160 tikv 10.90.2.109 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.109:20161 tikv 10.90.2.109 20161/20181 linux/x86_64 Pending Offline /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 10.90.3.221:20160 tikv 10.90.3.221 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.221:20161 tikv 10.90.3.221 20161/20181 linux/x86_64 Pending Offline /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 Total nodes: 15 ``` ### 步骤 8. 清理已缩容的 TiKV + 当 TiKV 的状态变为 `Tombstone` 后，你可以清理它，这可能需要几分钟或几小时。 ```bash tiup cluster display tidb-test ``` ```output tiup is checking updates for component cluster ... Starting component `cluster`: /home/ec2-user/.tiup/components/cluster/v1.13.1/tiup-cluster display tidb-test Cluster type: tidb Cluster name: tidb-test Cluster version: v7.5.1 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://18.141.56.57:2379/dashboard Grafana URL: http://10.90.4.14:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.14:9093 alertmanager 10.90.4.14 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.14:3000 grafana 10.90.4.14 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.1.254:2379 pd 10.90.1.254 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.2.60:2379 pd 10.90.2.60 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.58:2379 pd 10.90.3.58 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.14:9090 prometheus 10.90.4.14 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.2.231:4000 tidb 10.90.2.231 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.3.141:4000 tidb 10.90.3.141 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.207:9000 tiflash 10.90.4.207 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.1.71:20160 tikv 10.90.1.71 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.71:20161 tikv 10.90.1.71 20161/20181 linux/x86_64 Tombstone /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 10.90.2.109:20160 tikv 10.90.2.109 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.109:20161 tikv 10.90.2.109 20161/20181 linux/x86_64 Tombstone /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 10.90.3.221:20160 tikv 10.90.3.221 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.221:20161 tikv 10.90.3.221 20161/20181 linux/x86_64 Tombstone /tidb-data/tikv-20161 /tidb-deploy/tikv-20161 Total nodes: 15 There are some nodes can be pruned: Nodes: [10.90.3.221:20161 10.90.1.71:20161 10.90.2.109:20161] You can destroy them with the command: `tiup cluster prune tidb-test` ``` ```bash tiup cluster prune tidb-test --yes ``` ```output ... Destroy success ```