# Lab 6: Operating TiDB Cluster ### Overview In these exercises, you scale the TiKV cluster using TiUP and change the name of TiDB cluster. The diagram below illustrates the deployment topology of the TiDB cluster. ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TiDBClusterDeploymentTopologyExercise_aa_007_20220914.png) ### Notes + Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this demo. + Commands and SQL statements presented on this page are clickable for easy copy and paste. + The required IP addresses for specific commands will be substituted with the actual ones automatically once you have set up the lab environment after Step 1. + Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide. + The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt. ### Assumptions + You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): `chmod 400 [~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)` + Windows users please refer to this [guide](https://github.com/pingcap/tidb-course-201-lab/blob/master/README_303_v65_en_class_setup_guide_for_student.md) for private key preparation. ## Exercise 1 ### Subject: Scaling out a TiKV Cluster ### Overview In this exercise, you add a TiDB node to the `monitor1` node. You will perform all tasks on Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks + Read the yaml file `solution-scale-out-tikv.yaml`, which is used to scale out TiKV. + Scale out the TiKV. ### Solution 1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`. ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address ``` ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` 2. Read `solution-scale-out-tikv.yaml` file. The IP addresses shown below will be different from yours. ```command $ cat solution-scale-out-tikv.yaml ``` ```output tikv_servers: - host: 10.90.4.93 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: region: seattle zone: az4 storage-performance: high ``` 3. Run the `tiup cluster scale-out` command to scale out TiKV cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster scale-out tidb-test --user ec2-user -i ~/.ssh/pe-class-key-oregon.pem solution-scale-out-tikv.yaml ``` ```output ... Do you want to continue? [y/N]: (default=N) y ... Scaled cluster `tidb-test` out successfully ``` 4. Check the status of the deployed TiDB cluster and the new TiKV node (IP address: `HOST_MONITOR1_PRIVATE_IP`). There are 4 TiKV nodes now. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster display tidb-test ``` ```output tiup is checking updates for component cluster ... Starting component `cluster`: /home/ec2-user/.tiup/components/cluster/v1.12.1/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.1.122:2379/dashboard Grafana URL: http://10.90.4.214:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.214:9093 alertmanager 10.90.4.214 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.214:3000 grafana 10.90.4.214 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.1.122:2379 pd 10.90.1.122 2379/2380 linux/x86_64 Up|L|UI /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.2.243:2379 pd 10.90.2.243 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.25:2379 pd 10.90.3.25 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.214:9090 prometheus 10.90.4.214 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.133:4000 tidb 10.90.1.133 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.2.92:4000 tidb 10.90.2.92 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.103:9000 tiflash 10.90.4.103 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.1.98:20160 tikv 10.90.1.98 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.235:20160 tikv 10.90.2.235 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.148:20160 tikv 10.90.3.148 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.214:20160 tikv 10.90.4.214 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 Total nodes: 13 ``` ## Exercise 2 ### Subject: Scaling in a TiKV Cluster ### Overview In this exercise, you remove a TiDB node from the `monitor1` node. You will perform all tasks on the Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks + Scale in the TiKV cluster. + Delete the stopped TiKV node. ### Solution 1. Run the `tiup cluster scale-in` command to scale in TiKV cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster scale-in tidb-test --node ${HOST_MONITOR1_PRIVATE_IP}:20160 ``` ```output ... Do you want to continue? [y/N]:(default=N) y The component `[tikv]` will become tombstone, maybe exists in several minutes or hours, after that you can use the prune command to clean it Do you want to continue? [y/N]:(default=N) y ... Scaled cluster `tidb-test` in successfully ``` 2. Check the status of the deployed TiDB cluster. Observe that the status of the TiKV node (IP address: `HOST_MONITOR1_PRIVATE_IP`) has changed to `Tombstone`. ```command $ tiup cluster display tidb-test ``` ```output tiup is checking updates for component cluster ... ... ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- ... 10.90.4.214:20160 tikv 10.90.4.214 20160/20180 linux/x86_64 Tombstone /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 Total nodes: 13 There are some nodes can be pruned: Nodes: [10.90.4.214:20160] You can destroy them with the command: `tiup cluster prune tidb-test` ``` 3. Execute the `tiup cluster prune tidb-test` to clean up node data. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster prune tidb-test ``` ```output ... Do you confirm this action? [y/N]:(default=N) y ... Destroy success ``` 4. Check the status of the deployed TiDB cluster, confirm that the TiKV node (IP address: `HOST_MONITOR1_PRIVATE_IP`) no longer exists. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster display tidb-test ``` ```output tiup is checking updates for component cluster ... Starting component `cluster`: /home/ec2-user/.tiup/components/cluster/v1.12.1/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.1.122:2379/dashboard Grafana URL: http://10.90.4.214:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.214:9093 alertmanager 10.90.4.214 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.214:3000 grafana 10.90.4.214 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.1.122:2379 pd 10.90.1.122 2379/2380 linux/x86_64 Up|L|UI /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.2.243:2379 pd 10.90.2.243 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.25:2379 pd 10.90.3.25 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.214:9090 prometheus 10.90.4.214 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.133:4000 tidb 10.90.1.133 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.2.92:4000 tidb 10.90.2.92 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.103:9000 tiflash 10.90.4.103 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.1.98:20160 tikv 10.90.1.98 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.235:20160 tikv 10.90.2.235 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.148:20160 tikv 10.90.3.148 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 Total nodes: 12 ``` ## Exercise 3 ### Subject: Renaming Cluster ### Overview In this exercise, you rename the TiDB cluster. ### Duration + This exercise should take you approximately 10 minutes to complete. You will perform all tasks on the Control Machine (monitor1_address). ### Tasks + Change the name of TiDB cluster to `tidb-prod`. + Check the status of the deployed TiDB cluster and view the name of TiDB cluster. + Change the name of TiDB cluster back to `tidb-test`. + Check the status of the deployed TiDB cluster and view the name of TiDB cluster. + Close all open terminal windows. ### Solution 1. Change the name of TiDB cluster to `tidb-prod`. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster rename tidb-test tidb-prod ``` ```output ... Do you confirm this action? [y/N]:(default=N) y ... Do you want to continue? [y/N]:(default=N) y ... Reloaded cluster `tidb-prod` successfully ``` 2. Check the status of the deployed TiDB cluster and view the name of TiDB cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster display tidb-prod ``` ```output tiup is checking updates for component cluster ... Starting component `cluster`: /home/ec2-user/.tiup/components/cluster/v1.12.1/tiup-cluster display tidb-prod Cluster type: tidb Cluster name: tidb-prod ... ``` 3. Change the name of TiDB cluster back to `tidb-test`. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster rename tidb-prod tidb-test ``` ```output ... Do you confirm this action? [y/N]:(default=N) y ... Do you want to continue? [y/N]:(default=N) y ... Reloaded cluster `tidb-test` successfully ``` 4. Check the status of the deployed TiDB cluster and view the name of TiDB cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster display tidb-test ``` ```output tiup is checking updates for component cluster ... Starting component `cluster`: /home/ec2-user/.tiup/components/cluster/v1.12.1/tiup-cluster display tidb-prod Cluster type: tidb Cluster name: tidb-test ... ``` 5. Close all open terminal windows. ## Exercise 4 ### Subject: Implementing Hot and Cold Data Storage Tiering ### Overview In this exercise, 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. ![40](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/PlacementRuleColdHot_aa_001_20230422.png) ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks + Scale out the TiKV cluster to include three extra TiKV nodes with low performance storage. + 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. + Create a range-partitioned table, utilizing placement policies in the previous step to maximize storage cost efficiency. + Verify that the placement rules have taken effect. + 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. ```command $ tiup cluster scale-out tidb-test solution-topology-placement-rule.yaml --yes --user ec2-user -i ~/.ssh/pe-class-key-oregon.pem ``` ```output ... Scaled cluster `tidb-test` out successfully ``` 2. Check the status of the cluster. There should be six TiKV instances now. ```command $ tiup cluster display tidb-test ``` ```output ... 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: ```command $ ./connect-db1.sh ``` ```output 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: ```command 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) ``` + 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: ```command 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) ``` + Create a placement policy for hot data, mapping it to the label key-value pair: `(storage-performance, high)`: ```command 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) ``` ```command 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) ``` + Create a placement policy for cold data, mapping it to the label key-value pair: `(storage-performance, low)`: ```command 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) ``` ```command 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) ``` + Verify the constraint definitions of created placement policies, `hot` and `cold`: ```command 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. 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: ```command 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) ``` ```command 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) ``` Create a partitioned table `test.orders_log` using the `cold` placement policy only for all its partitions: ```command 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) ``` ```command 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. 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): ```command 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) ``` 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`: ```command 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) ``` 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): ```command 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) ``` 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`: ```command 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) ``` 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: ```command 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) ``` 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: ```command 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) ``` 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. ```command DROP TABLE IF EXISTS test.orders; ``` ```output tidb:db1> DROP TABLE IF EXISTS test.orders; Query OK, 0 rows affected (0.52 sec) ``` ```command DROP TABLE IF EXISTS test.orders_log; ``` ```output tidb:db1> DROP TABLE IF EXISTS test.orders_log; Query OK, 0 rows affected (0.52 sec) ``` ```command DROP PLACEMENT POLICY IF EXISTS hot; ``` ```output tidb:db1> DROP PLACEMENT POLICY IF EXISTS hot; Query OK, 0 rows affected (0.53 sec) ``` ```command DROP PLACEMENT POLICY IF EXISTS cold; ``` ```output tidb:db1> DROP PLACEMENT POLICY IF EXISTS cold; Query OK, 0 rows affected (0.52 sec) ``` 7. (Optional) Exit the TiDB connection, and scale in the TiKV nodes. ```command EXIT ``` ```output tidb:db1> EXIT Bye ``` ```command $ tiup cluster scale-in tidb-test --node ${HOST_KV1_PRIVATE_IP}:20161 --yes ``` ```output ... Scaled cluster `tidb-test` in successfully ``` ```command $ tiup cluster scale-in tidb-test --node ${HOST_KV2_PRIVATE_IP}:20161 --yes ``` ```output ... Scaled cluster `tidb-test` in successfully ``` ```command $ tiup cluster scale-in tidb-test --node ${HOST_KV3_PRIVATE_IP}:20161 --yes ``` ```output ... 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. ```command $ tiup cluster display tidb-test ``` ```output ... 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 Total nodes: 15 ``` 8. (Optional) You may clean the TiKV after its status turns into `Tombstone`, which may take several minutes or hours. ```command $ 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: 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` ``` ```command $ tiup cluster prune tidb-test --yes ``` ```output ... Destroy success ``` ## End of Exercises