# 配置 TiDB 集群 ### 概览 + In TiDB, there are TiDB system variables and TiDB configuration file parameters to control the TiDB cluster behaviors. In these exercises, you gain some experience on modifying TiDB system variables and TiDB configuration file parameters. + There are four TiDB server system variable categories classified based on their effective scope. ```output | Category | Change By | Effective Scope | | :-------------------- | :------------ | :------------------------------------------------------ | | Session Only | `SET SESSION` | Current Session | | Global Persistent | `SET GLOBAL` | All TiDB Server Instances and All Sessions | | Global Non-Persistent | `SET GLOBAL` | Current TiDB Server Instance and its Connected Sessions | | Session and Global | `SET SESSION` | Current Session | | Session and Global | `SET GLOBAL` | All TiDB Server Instances and New Sessions | ``` + In the last exercise, you also use placement rules to place hot and cold data on high and low performance storages respectively. ### 注意事项 + 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. ### 前提条件 + You have downloaded and secured the TiUP Control Machine private key: `chmod 400 [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)` + The `ssh-agent` is running on your laptop. 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). + You have added identity to authentication agent: `ssh-add [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)` ## 练习 1 ### Modifying Global Persistent System Variables ### 概览 + [`tidb_gc_enable`](https://docs.pingcap.com/tidb/stable/system-variables#tidb_gc_enable-new-in-v50) is one of the global persistent TiDB system variables, modify it and observe the result. ### 时长 + This exercise should take you approximately 5 minutes to complete. ### 任务 + Modify TiDB system variable `tidb_gc_enable`. ### 步骤 1. Open a new terminal window (as terminal 1), and connect to the TiDB server 1 by executing the script `connect-db1.sh`. ```command $ ./connect-db1.sh ``` ```output ... tidb:db1> ``` 2. Open a new terminal window (as terminal 2), and connect to the TiDB server 1 by using `connect-db1.sh`. ```command $ ./connect-db1.sh ``` ```output ... tidb:db1> ``` 3. Open a new terminal window again (as terminal 3), and connect to a different TiDB server 2 by using `connect-db2.sh`. ```command $ ./connect-db2.sh ``` ```output ... tidb:db2> ``` 4. In terminal 1, verify the default value of `tidb_gc_enable`. And, change the value from `ON` to `OFF`. ```command SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | ON | +----------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | ON | +----------------+-------+ 1 row in set (0.00 sec) ``` ```command SET GLOBAL tidb_gc_enable=OFF; ``` ```output tidb:db1> SET GLOBAL tidb_gc_enable=OFF; Query OK, 0 rows affected (0.06 sec) ``` 5. Verify the change on terminal 1. The variable value had been updated at both session and global levels. ```command SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) ``` 6. Verify the change on terminal 2. The variable value had been updated at both session and global levels. ```command SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) ``` 7. Verify the change on terminal 3. The variable value had been updated at both session and global levels. ```command SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) ``` 8. In any terminal, attempt to change the value of `tidb_gc_enable` on session scope. Observe the failed result. For example, on terminal 3. ```command SET SESSION tidb_gc_enable=ON; ``` ```output tidb:db2> SET SESSION tidb_gc_enable=ON; ERROR 1229 (HY000): Variable 'tidb_gc_enable' is a GLOBAL variable and should be set with SET GLOBAL ``` 9. In any terminal, exit the connections with TiDB, and restart the TiDB cluster. ```command $ tiup cluster restart tidb-test --yes ``` ```output ... Restarted cluster `tidb-test` successfully ``` 10. Verify that in terminal 1, terminal2, and terminal 3, the value of `tidb_gc_enable` is `OFF`. We use terminal 1 as an example. ```command $ ./connect-db1.sh ``` ```output ... tidb:db1> ``` ```command SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) ``` 11. Change `tidb_gc_enable` back to `ON` in any terminal. ```command SET GLOBAL tidb_gc_enable=ON; ``` ```output tidb:db1> SET GLOBAL tidb_gc_enable=ON; Query OK, 0 rows affected (0.07 sec) ``` 12. Keep all three terminals and database sessions open. ### Summary The global persistent TiDB system variable can only be configured on a global level, and its effects will be immediately applied across all TiDB servers and sessions in the cluster. ## 练习 2 ### Modifying Non-Persistent Global System Variables ### 概览 + [`max_connections`](https://docs.pingcap.com/tidb/stable/system-variables#max_connections) is one of the non-persistent global system variables, modify it and observe the result. ### 时长 + This exercise should take you approximately 10 minutes to complete. ### 任务 + Modify TiDB system variable `max_connections`. ### 步骤 1. In terminal 1, verify the default value of `max_connections`. And, change the value from `0` to `1`, then confirm the change. ```command SHOW SESSION VARIABLES LIKE 'max_connections'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) ``` ```command SET GLOBAL max_connections=1; ``` ```output tidb:db1> SET GLOBAL max_connections=1; Query OK, 0 rows affected (0.00 sec) ``` ```command SHOW SESSION VARIABLES LIKE 'max_connections'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.01 sec) ``` 2. Verify the change on terminal 2, the value is `1`, consistent with the observation from terminal 1. ```command SHOW SESSION VARIABLES LIKE 'max_connections'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.00 sec) ``` 3. Verity the value on terminal 3. Confirm that the value has not changed, it is still `0`. ```command SHOW SESSION VARIABLES LIKE 'max_connections'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.01 sec) ``` 4. Exit the database session on terminal 1 and attempt to log back in by running `connect-db1.sh` again. However, this time the login attempt will fail due to the `max_connections` setting on the TiDB server instance 1. ```command $ ./connect-db1.sh ``` ```output ERROR 1040 (HY000): Too many connections ``` 5. In terminal 1, try to log in by executing `connect-db2.sh`. The login attempt should be successful, as the TiDB server instance 2, has its `max_connections` system variable set to `0`, which implies that any number of connections can be established. ```command $ ./connect-db2.sh ``` ```output ... tidb:db2> ``` 6. In any terminal, exit the connections with TiDB, and restart the TiDB cluster. ```command $ tiup cluster restart tidb-test --yes ``` ```output ... Restarted cluster `tidb-test` successfully ``` 7. Verify that in terminal 1, terminal2, and terminal 3, the value of `max_connections` is 0. We use terminal 1 as an example. ```command $ ./connect-db2.sh ``` ```output ... tidb:db2> ``` ```command SHOW SESSION VARIABLES LIKE 'max_connections'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.01 sec) ``` 8. Keep all three terminals and database sessions open. ### Summary The non-persistent global TiDB system variable can only be configured on a global level, and its effects will be applied to the TiDB instance that you issued the `SET GLOBAL` statement. ## 练习 3 ### Modifying Session Only System Variables ### 概览 + [`tidb_allow_remove_auto_inc`](https://docs.pingcap.com/tidb/stable/system-variables#tidb_allow_remove_auto_inc) is one of the session system variables, modify it and observe the result. ### 时长 + This exercise should take you approximately 10 minutes to complete. ### 任务 + Modify TiDB system variable `tidb_allow_remove_auto_inc`. ### 步骤 1. Verify that you have two terminals (terminal 1 and terminal 3) connected to TiDB server 2, and one terminal (terminal 2) connected to TiDB server 1. 2. Check and modify the system variable `tidb_allow_remove_auto_inc` from `OFF` to `ON` in terminal 1, and check it again. ```command SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | tidb_allow_remove_auto_inc | OFF | +----------------------------+-------+ 1 row in set (0.00 sec) ``` ```command SET SESSION tidb_allow_remove_auto_inc=ON; ``` ```output tidb:db2> SET SESSION tidb_allow_remove_auto_inc=ON; Query OK, 0 rows affected (0.00 sec) ``` ```command SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | tidb_allow_remove_auto_inc | ON | +----------------------------+-------+ 1 row in set (0.00 sec) ``` 3. Check the value of `tidb_allow_remove_auto_inc` from another session in terminal 2. Confirm that it is still `OFF`. ```command SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | tidb_allow_remove_auto_inc | OFF | +----------------------------+-------+ 1 row in set (0.01 sec) ``` 4. Check the value of `tidb_allow_remove_auto_inc` from another session in terminal 3. Confirm that it is also still `OFF`. ```command SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | tidb_allow_remove_auto_inc | OFF | +----------------------------+-------+ 1 row in set (0.00 sec) ``` 5. In any terminal, attempt to change the value of `tidb_allow_remove_auto_inc` on global scope. Observe the failed result. For example, on terminal 3. ```command SET GLOBAL tidb_allow_remove_auto_inc=ON; ``` ```output tidb:db2> SET GLOBAL tidb_allow_remove_auto_inc=ON; ERROR 1228 (HY000): Variable 'tidb_allow_remove_auto_inc' is a SESSION variable and can't be used with SET GLOBAL ``` 6. Change `tidb_allow_remove_auto_inc` back to `OFF` on terminal 1. ```command SET SESSION tidb_allow_remove_auto_inc=OFF; ``` ```output tidb:db2> SET SESSION tidb_allow_remove_auto_inc=OFF; Query OK, 0 rows affected (0.00 sec) ``` 7. Keep all three terminals and database sessions open. ### Summary The session-only TiDB system variable can only be configured on a session level, and its effects will be applied to the current session that you issued the `SET SESSION` statement. ## 练习 4 ### Modifying Session and Global System Variables ### 概览 + [`auto_increment_offset`](https://docs.pingcap.com/tidb/stable/system-variables#auto_increment_offset) is one of the session and global system variables, modify it at the session level and the global level, then observe the results. ### 时长 + This exercise should take you approximately 10 minutes to complete. ### 任务 + Modify TiDB system variable `auto_increment_offset` at session level. + Modify TiDB system variable `auto_increment_offset` at global level. ### 步骤 1. Verify that you have three terminals with database connections. Two terminals (terminal 1 and terminal 3) connected to TiDB server 2, and one terminal (terminal 2) connected to TiDB server 1. 2. Check and modify the system variable `auto_increment_offset` from `1` to `99` at the session level in terminal 1. Confirm that the variable had been updated at the session level only. ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SET SESSION auto_increment_offset=99; ``` ```output tidb:db2> SET SESSION auto_increment_offset=99; Query OK, 0 rows affected (0.00 sec) ``` ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` 3. Check the variable value in terminal 2 and terminal 3 from both session and global levels. Both should remain unchanged, which is `1`. In terminal 2. ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` In terminal 3. ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` 4. Change `auto_increment_offset` back to `1` on terminal 1. ```command SET SESSION auto_increment_offset=1; ``` 5. Change `auto_increment_offset` from `1` to `99` at the global level in terminal 1. Confirm that the variable had been updated at the global level only. ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SET GLOBAL auto_increment_offset=99; ``` ```output tidb:db2> SET GLOBAL auto_increment_offset=99; Query OK, 0 rows affected (0.05 sec) ``` ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` 6. Check the variable value in terminal 2 and terminal 3 from both session and global levels. Confirm that only the variable is updated at the global level only. In terminal 2. ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` In terminal 3. ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` 7. In any terminal, exit the connections with TiDB, and restart the TiDB cluster. ```command $ tiup cluster restart tidb-test --yes ``` ```output ... Restarted cluster `tidb-test` successfully ``` 8. Connect to terminal 1, terminal 2, and terminal 3, and check the value of `auto_increment_offset` from both session and global levels. Confirm that only the variable is updated at both the session and the global level. We use terminal 1 as an example. In terminal 1. ```command SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` ```command SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ```output tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` 9. In any terminal, change `auto_increment_offset` back to `1`. ```command SET GLOBAL auto_increment_offset=1; ``` ```output tidb:db1> SET GLOBAL auto_increment_offset=1; Query OK, 0 rows affected (0.07 sec) ``` ### Summary If you change the session and global variable using `SET SESSION`, the effective scope will be the current session only. If you change the session and global variables using `SET GLOBAL`, the effective scope will be all TiDB server instances and new sessions. ## 练习 5 ### Modifying the TiKV Configuration File Parameter ### 概览 + In this exercise, you modify the configuration of `TiKV server` using `tiup config` and `tiup reload`. ### 时长 + This exercise should take you approximately 15 minutes to complete. ### 任务 + On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file. + On `Control Machine`, use the `tiup cluster edit-config` command that starts an editor for you to modify `log.level: warn` of `TiKV server`. + On `Control Machine`, use the `tiup cluster reload` command to update the configuration. This step will restart all components. + On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file again, and verify that the `log.level = "warn"`. + Close all terminal windows. ### 步骤 1. On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file. Type `:q!` to quit the editor. Enter `yes` if prompted by `ssh`: ```command $ ssh ${HOST_KV1_PRIVATE_IP} vi /tidb-deploy/tikv-20160/conf/tikv.toml ``` ```output # WARNING: This file is auto-generated. Do not edit! All your modification will be overwritten! # You can use 'tiup cluster edit-config' and 'tiup cluster reload' to update the configuration # All configuration items you want to change can be added to: # server_configs: # tikv: # aa.b1.c3: value # aa.b2.c4: value ``` 2. On `Control Machine`, use the `tiup cluster edit-config` command that starts an editor. Enter the following command at the terminal prompt to change the `log.level` to `warn` for `TiKV server`. Press the `ESC` and type `:wq` to quit to the terminal: ```command $ tiup cluster edit-config tidb-test ``` ```output server_configs: tidb: {} tikv: log.level: warn pd: {} tidb_dashboard: {} tiflash: {} tiflash-learner: {} pump: {} drainer: {} cdc: {} kvcdc: {} grafana: {} ``` 3. On `Control Machine`, use the `tiup cluster reload` command to update the configuration. This step will restart all `TiKV server`. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup cluster reload tidb-test ``` ```output tiup is checking updates for component cluster ... Starting component `cluster`: /root/.tiup/components/cluster/v1.11.0/tiup-cluster reload tidb-test Will reload the cluster tidb-test with restart policy is true, nodes: , roles: . Do you want to continue? [y/N]:(default=N) y + [ Serial ] - SSHKeySet: privateKey=/root/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa, publicKey=/root/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa.pub + [Parallel] - UserSSH: user=tidb, host=172.31.0.154 + [Parallel] - UserSSH: user=tidb, host=172.31.0.50 ... Reloaded cluster `tidb-test` successfully ``` 4. On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file again, and verify that `log.level = "warn"`. Type `:q!` to quit the editor. ```command $ ssh ${HOST_KV1_PRIVATE_IP} vi -m /tidb-deploy/tikv-20160/conf/tikv.toml ``` ```output # WARNING: This file is auto-generated. Do not edit! All your modification will be overwritten! # You can use 'tiup cluster edit-config' and 'tiup cluster reload' to update the configuration # All configuration items you want to change can be added to: # server_configs: # tikv: # aa.b1.c3: value # aa.b2.c4: value [log] level = "warn" ... ``` ## 练习 6 ### Implementing Hot and Cold Data Storage Tiering ### 概览 + 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. ![50](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/PlacementRuleColdHot_aa_001_20230422.png) ### 时长 + This exercise should take you approximately 20 minutes to complete. ### 任务 + 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. ### 步骤 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 ``` ```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 | ["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: ```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": "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)`: ```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": "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): ```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": "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: ```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. 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 ``` 8. 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 ``` ## 实验结束