# Implementing Hot and Cold Data Storage Tiering ### Overview + In these exercises, you will gain experience with TiDB's Placement Rules in SQL, a feature that allows you to specify where data is stored in a TiDB using SQL statements. + You practice using the placement rules to separate cold data to `lower-cost` storage (or low performance node) and hot data to `higher-cost` storage (or high performance node) with table partitions to improve cost efficiency. ### Notes + Completing *Exercises 1: Deploying a TiDB Cluster Using TiUP* is necessary for subsequent exercises to complete successfully. + Many of the steps require that you type commands in a terminal window. You might find it convenient to launch a terminal window and keep it open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt. + In these exercises, we need 10 machines connected in the network. The `Role` and `IP Address` (represented by environment variables) are as follows: | `Node Role` | `IP Address` | | :----------------- | :-------------------------------------------------------- | | `Monitor 1` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Control Machine` | `${HOST_CM_PRIVATE_IP}, ${HOST_CM_PUBLIC_IP}` | | `TiDB server 1` | `${HOST_DB1_PRIVATE_IP}, ${HOST_DB1_PUBLIC_IP}` | | `TiDB server 2` | `${HOST_DB2_PRIVATE_IP}, ${HOST_DB2_PUBLIC_IP}` | | `TiKV server 1` | `${HOST_KV1_PRIVATE_IP}, ${HOST_KV1_PUBLIC_IP}` | | `TiKV server 2` | `${HOST_KV2_PRIVATE_IP}, ${HOST_KV2_PUBLIC_IP}` | | `TiKV server 3` | `${HOST_KV3_PRIVATE_IP}, ${HOST_KV3_PUBLIC_IP}` | | `PD server 1` | `${HOST_PD1_PRIVATE_IP}, ${HOST_PD1_PUBLIC_IP}` | | `PD server 2` | `${HOST_PD2_PRIVATE_IP}, ${HOST_PD2_PUBLIC_IP}` | | `PD server 3` | `${HOST_PD3_PRIVATE_IP}, ${HOST_PD3_PUBLIC_IP}` | | `TiFlash server 1` | `${HOST_TIFLASH1_PRIVATE_IP}, ${HOST_TIFLASH1_PUBLIC_IP}` | | `Alertmanager` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Grafana` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Prometheus` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | ### Assumptions + You are given the user credentials (for example: the private key) of the exercise environment. + You are given access to 10 machines that have been set up. + You have completed the activities in the exercise titled "*Exercise 1: Using TiUP to Deploy a TiDB Cluster" and have a terminal window. + Your environment has TiDB cluster deployed and running. ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks 1. Scale out the TiKV cluster to include three extra TiKV nodes with low performance storage. 2. Create placement policies for designating hot data to TiKV nodes with `higher-cost` storage attached, and cold data to TiKV nodes with `lower-cost` storage attached. 3. Create a range-partitioned table, utilizing placement policies in the previous step to maximize storage cost efficiency. 4. Verify that the placement rules have taken effect. 5. Scale in TiKV nodes created in Task 1. ### Solution 1. Scale out the TiKV cluster to include three extra TiKV nodes with low performance storage. In this experiment, we just put an extra TiKV instance on each EC2, where the TiKV nodes run on. We pretend three new TiKV nodes have low performance storage, where three existing TiKV nodes have high performance storage. Details about scaling out TiKV will be discussed in Chapter 6. For the purpose of this experiment, you only need to run the following command. ``` $ tiup cluster scale-out tidb-test solution-topology-placement-rule.yaml --yes ... Scaled cluster `tidb-test` out successfully ``` 2. Check the status of the cluster. There should be six TiKV instances now. ``` $ tiup cluster display tidb-test ... Cluster type: tidb Cluster name: tidb-test Cluster version: v6.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.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. Create placement policies for designating hot data to TiKV nodes with high performance storage, and cold data to TiKV nodes with low performance storage. Connect to TiDB server as root: ``` $ ./connect-db1.sh tidb:db1> ``` Verify the available labels for placement rules. Pay attention to the label with key `storage-performance`, it's safe to ignore `engine`, `zone`, and `region` for now: ```sql SHOW PLACEMENT LABELS; ``` ``` tidb:db1> SHOW PLACEMENT LABELS; +---------------------+-----------------------+ | Key | Values | +---------------------+-----------------------+ | engine | ["tiflash"] | | region | ["oregon"] | | storage-performance | ["high", "low"] | | zone | ["az1", "az2", "az3"] | +---------------------+-----------------------+ 4 rows in set (0.00 sec) ``` Check the available labels and their corresponding TiKV stores. Note that the `store_id` column represents the TiKV store ID, and the `label` column indicates the labeled key-values for each TiKV store: ```sql SELECT store_id, address, store_state_name, label FROM information_schema.tikv_store_status; ``` ``` 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": "oregon"}, {"key": "storage-performance", "value": "high"}, {"key": "zone", "value": "az3"}] | | 2 | 10.90.2.251:20160 | Up | [{"key": "region", "value": "oregon"}, {"key": "storage-performance", "value": "high"}, {"key": "zone", "value": "az2"}] | | 3 | 10.90.1.45:20160 | Up | [{"key": "region", "value": "oregon"}, {"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": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az2"}] | | 187 | 10.90.1.45:20161 | Up | [{"key": "region", "value": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az1"}] | | 188 | 10.90.3.130:20161 | Up | [{"key": "region", "value": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | +----------+-------------------+------------------+--------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.01 sec) ``` Create a placement policy for hot data, mapping it to the label key-value pair: `(storage-performance, high)`: ```sql DROP PLACEMENT POLICY IF EXISTS hot; CREATE PLACEMENT POLICY hot CONSTRAINTS="[+storage-performance=high]"; ``` ``` tidb:db1> DROP PLACEMENT POLICY IF EXISTS hot; Query OK, 0 rows affected, 1 warning (0.00 sec) tidb:db1> CREATE PLACEMENT POLICY hot -> CONSTRAINTS="[+storage-performance=high]"; Query OK, 0 rows affected (0.07 sec) ``` Create a placement policy for cold data, mapping it to the label key-value pair: `(storage-performance, low)`: ```sql DROP PLACEMENT POLICY IF EXISTS cold; CREATE PLACEMENT POLICY cold CONSTRAINTS="[+storage-performance=low]"; ``` ``` tidb:db1> DROP PLACEMENT POLICY IF EXISTS cold; Query OK, 0 rows affected, 1 warning (0.01 sec) tidb:db1> CREATE PLACEMENT POLICY cold -> CONSTRAINTS="[+storage-performance=low]"; Query OK, 0 rows affected (0.07 sec) ``` Verify the constraint definitions of created placement policies, `hot` and `cold`: ```sql SELECT policy_name, constraints FROM information_schema.placement_policies; ``` ``` 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. Create two range-partitioned tables utilizing placement policies in the previous step, one with hot-cold partitions and the other with cold-only partitions, to maximize the storage cost efficiency. Create a partitioned table `test.orders` using `hot` and `cold` placement policies. Note that orders with an `order_date` before 2010 and between 2010 and 2015 (exclusive) are marked as `cold`, while those with an `order_date` after 2015 are marked as `hot` which mapped to the `MAXVALUE` partition: ```sql DROP TABLE IF EXISTS test.orders; 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 ); ``` ``` tidb:db1> DROP TABLE IF EXISTS test.orders; Query OK, 0 rows affected, 1 warning (0.00 sec) 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) ``` Create a partitioned table `test.orders_log` using the `cold` placement policy only for all its partitions: ```sql DROP TABLE IF EXISTS test.orders_log; 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 ); ``` ``` tidb:db1> DROP TABLE IF EXISTS test.orders_log; Query OK, 0 rows affected, 1 warning (0.00 sec) 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. Verify that the placement rules had taken effect. Show TiKV region information about the hot-cold table `test.orders`. Pay attention to the `REGION_ID` with the highest `START_KEY`, it's the `MAXVALUE` partition. In the example output, the `REGION_ID` is 264 (note that you might have different numbering for region id and start keys in your environment): ```sql SHOW TABLE test.orders REGIONS\G ``` ``` 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) ``` Verify the regions and the TiKV stores mapping for table `test.orders` is aligned with the placement policy. According to the placement rules, `REGION_ID` 264's region should be mapped to TiKV node with `storage-performance:high`, whereas the other two regions, 256 and 260 should be mapped to TiKV nodes with `storage-performance:low`: ```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; ``` ``` 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": "oregon"}, {"key": "storage-performance", "value": "high"}, {"key": "zone", "value": "az1"}] | 2023-06-08 06:47:58 | | 256 | 188 | 1 | [{"key": "region", "value": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | 2023-06-08 06:49:39 | | 260 | 188 | 1 | [{"key": "region", "value": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | 2023-06-08 06:49:39 | +-----------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------+---------------------+ 3 rows in set (0.01 sec) ``` Show TiKV region information about the `COLD` only table `test.orders_log`. Pay attention to the `REGION_ID` for all regions, in the example output, they are 277, 281 and 285 (note that you might have different numbering for region id and start keys in your environment): ```sql SHOW TABLE test.orders_log REGIONS\G ``` ``` 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) ``` Verify the regions and the TiKV stores mapping for table `test.orders_log` is aligned with the placement policy. According to the placement rules, all regions should be mapped to TiKV nodes with `storage-performance:low`: ```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; ``` ``` 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": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | 2023-06-08 06:49:39 | | 285 | 188 | 1 | [{"key": "region", "value": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | 2023-06-08 06:49:39 | | 277 | 188 | 1 | [{"key": "region", "value": "oregon"}, {"key": "storage-performance", "value": "low"}, {"key": "zone", "value": "az3"}] | 2023-06-08 06:49:39 | +-----------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+ 3 rows in set (0.01 sec) ``` Run a query trace to verify the partition pruning for table `test.orders`. The query scan on the cold storage is anticipated, as you are querying data between 2010 and 2015. From the output, observe that the region with region ID 260 is scanned, which is on `storage-performance:low` labeled TiKV node: ```sql 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'); ``` ``` 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) ``` Run a query trace to verify the partition pruning for table `test.orders` again. The query scan on the hot storage is anticipated, as you are querying data after 2015. From the output, observe that the region with region ID 264 is scanned, which is on `storage-performance:high` labeled TiKV node: ```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'); ``` ``` 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) ``` If you are interested, you can try similar queries against the table `test.orders_log`, which can only be landed on cold TiKV nodes. 6. Drop the tables and placement policies. ```sql DROP TABLE IF EXISTS test.orders; ``` ```sql tidb:db1> DROP TABLE IF EXISTS test.orders; Query OK, 0 rows affected (0.52 sec) ``` ```sql DROP TABLE IF EXISTS test.orders_log; ``` ```sql tidb:db1> DROP TABLE IF EXISTS test.orders_log; Query OK, 0 rows affected (0.52 sec) ``` ```sql DROP PLACEMENT POLICY IF EXISTS hot; ``` ```sql tidb:db1> DROP PLACEMENT POLICY IF EXISTS hot; Query OK, 0 rows affected (0.53 sec) ``` ```sql DROP PLACEMENT POLICY IF EXISTS cold; ``` ```sql tidb:db1> DROP PLACEMENT POLICY IF EXISTS cold; Query OK, 0 rows affected (0.52 sec) ``` 7. Exit the TiDB connection, and scale in the TiKV nodes. ```sql tidb:db1> EXIT ``` ``` tidb:db1> EXIT Bye ``` ``` $ tiup cluster scale-in tidb-test --node ${HOST_KV1_PRIVATE_IP}:20161 --yes ... Scaled cluster `tidb-test` in successfully $ tiup cluster scale-in tidb-test --node ${HOST_KV2_PRIVATE_IP}:20161 --yes ... Scaled cluster `tidb-test` in successfully $ tiup cluster scale-in tidb-test --node ${HOST_KV3_PRIVATE_IP}:20161 --yes ... Scaled cluster `tidb-test` in successfully ``` The dropped `tikv` will become tombstone in several minutes or hours, after that you can use the prune command to clean it. If you don't want to wait, you may use `--force` in the scale in command. However, you may see abnormalities in monitoring service and dashboard. ``` $ tiup cluster display tidb-test ... Cluster type: tidb Cluster name: tidb-test Cluster version: v6.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 ``` 8. You may clean the TiKV after its status turns into `Tombstone`, which may take several minutes or hours. ``` $ tiup cluster display tidb-test 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: v6.5.0 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` $ tiup cluster prune tidb-test --yes ... Destroy success ``` ## End of Exercises