# Configuring TiDB ### Overview + 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. | 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. ### Notes + Completing *Exercises 1: Deploying a TiDB Cluster Using TiUP* is necessary for subsequent exercises to complete successfully. + Many of the steps require that you type commands in a terminal window. You might find it convenient to launch a terminal window and keep it open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt. + In these exercises, we need 10 machines connected in the network. The `Role` and `IP Address` (represented by environment variables) are as follows: | `Node Role` | `IP Address` | | :----------------- | :-------------------------------------------------------- | | `Monitor 1` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Control Machine` | `${HOST_CM_PRIVATE_IP}, ${HOST_CM_PUBLIC_IP}` | | `TiDB server 1` | `${HOST_DB1_PRIVATE_IP}, ${HOST_DB1_PUBLIC_IP}` | | `TiDB server 2` | `${HOST_DB2_PRIVATE_IP}, ${HOST_DB2_PUBLIC_IP}` | | `TiKV server 1` | `${HOST_KV1_PRIVATE_IP}, ${HOST_KV1_PUBLIC_IP}` | | `TiKV server 2` | `${HOST_KV2_PRIVATE_IP}, ${HOST_KV2_PUBLIC_IP}` | | `TiKV server 3` | `${HOST_KV3_PRIVATE_IP}, ${HOST_KV3_PUBLIC_IP}` | | `PD server 1` | `${HOST_PD1_PRIVATE_IP}, ${HOST_PD1_PUBLIC_IP}` | | `PD server 2` | `${HOST_PD2_PRIVATE_IP}, ${HOST_PD2_PUBLIC_IP}` | | `PD server 3` | `${HOST_PD3_PRIVATE_IP}, ${HOST_PD3_PUBLIC_IP}` | | `TiFlash server 1` | `${HOST_TIFLASH1_PRIVATE_IP}, ${HOST_TIFLASH1_PUBLIC_IP}` | | `Alertmanager` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Grafana` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Prometheus` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | ### Assumptions + You are given the user credentials (for example: the private key) of the exercise environment. + You are given access to 10 machines that have been set up. + You have completed the activities in the exercise titled "*Exercise 1: Using TiUP to Deploy a TiDB Cluster" and have a terminal window. + Your environment has TiDB cluster deployed and running. ## Exercise 1: Modifying Global Persistent System Variables ### Overview + [`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. ### Duration + This exercise should take you approximately 5 minutes to complete. ### Tasks + Modify TiDB system variable `tidb_gc_enable`. ### Solution 1. Open a new terminal window (as terminal 1), and connect to the TiDB server 1 by executing the script `connect-db1.sh`. ``` $ ./connect-db1.sh ... tidb:db1> ``` 2. Open a new terminal window (as terminal 2), and connect to the TiDB server 1 by using `connect-db1.sh`. ``` $ ./connect-db1.sh ... 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`. ``` $ ./connect-db2.sh ... tidb:db2> ``` 4. In terminal 1, verify the default value of `tidb_gc_enable`. And, change the value from `ON` to `OFF`. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; SET GLOBAL tidb_gc_enable=OFF; ``` ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | ON | +----------------+-------+ 1 row in set (0.00 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | ON | +----------------+-------+ 1 row in set (0.00 sec) tidb:db1> SET GLOBAL tidb_gc_enable=OFF; -------------- 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. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- 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. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- 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. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- 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. ```sql SET SESSION tidb_gc_enable=ON; ``` ``` tidb:db2> SET SESSION tidb_gc_enable=ON; -------------- 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. ``` $ tiup cluster restart tidb-test --yes ... 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. ``` $ ./connect-db1.sh ... tidb:db1> ``` ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- 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. ```sql SET GLOBAL tidb_gc_enable=ON; ``` ``` tidb:db1> SET GLOBAL tidb_gc_enable=ON; -------------- 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. ## Exercise 2: Modifying Non-Persistent Global System Variables ### Overview + [`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. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks + Modify TiDB system variable `max_connections`. ### Solution 1. In terminal 1, verify the default value of `max_connections`. And, change the value from `0` to `1`, then confirm the change. ```sql SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; SET GLOBAL max_connections=1; SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- SHOW GLOBAL VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:db1> SET GLOBAL max_connections=1; -------------- SET GLOBAL max_connections=1 -------------- Query OK, 0 rows affected (0.00 sec) tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- 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. ```sql SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- 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. ``` $ ./connect-db1.sh 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. ``` $ ./connect-db2.sh ... tidb:db2> ``` 6. In any terminal, exit the connections with TiDB, and restart the TiDB cluster. ``` $ tiup cluster restart tidb-test --yes ... 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. ``` $ ./connect-db2.sh ... tidb:db2> ``` ```sql SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- 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. ## Exercise 3: Modifying Session Only System Variables ### Overview + [`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. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks + Modify TiDB system variable `tidb_allow_remove_auto_inc`. ### Solution 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. ```sql SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; SET SESSION tidb_allow_remove_auto_inc=ON; SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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) tidb:db2> SET SESSION tidb_allow_remove_auto_inc=ON; -------------- SET SESSION tidb_allow_remove_auto_inc=ON -------------- Query OK, 0 rows affected (0.00 sec) tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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. ```sql SET GLOBAL tidb_allow_remove_auto_inc=ON; ``` ``` tidb:db2> SET GLOBAL tidb_allow_remove_auto_inc=ON; -------------- 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. ```sql SET SESSION tidb_allow_remove_auto_inc=OFF; ``` ``` tidb:db2> SET SESSION tidb_allow_remove_auto_inc=OFF; -------------- 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. ## Exercise 4: Modifying Session and Global System Variables ### Overview + [`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. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks + Modify TiDB system variable `auto_increment_offset` at session level. + Modify TiDB system variable `auto_increment_offset` at global level. ### Solution 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. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; SET SESSION auto_increment_offset=99; SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db2> SET SESSION auto_increment_offset=99; -------------- SET SESSION auto_increment_offset=99 -------------- Query OK, 0 rows affected (0.00 sec) tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` In terminal 2. ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` In terminal 3. ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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. ```sql 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. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; SET GLOBAL auto_increment_offset=99; SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db2> SET GLOBAL auto_increment_offset=99; -------------- SET GLOBAL auto_increment_offset=99 -------------- Query OK, 0 rows affected (0.05 sec) tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` In terminal 2. ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` In terminal 3. ``` tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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. ``` $ tiup cluster restart tidb-test --yes ... 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. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` In terminal 1. ``` tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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`. ```sql SET GLOBAL auto_increment_offset=1; ``` ``` tidb:db1> SET GLOBAL auto_increment_offset=1; -------------- 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. ## Exercise 5: Modifying the TiKV Configuration File Parameter ### Overview + In this exercise, you modify the configuration of `TiKV server` using `tiup config` and `tiup reload`. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file. 2. On `Control Machine`, use the `tiup cluster edit-config` command that starts an editor for you to modify `log.level: warn` of `TiKV server`. 3. On `Control Machine`, use the `tiup cluster reload` command to update the configuration. This step will restart all components. 4. 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"`. 5. Close all terminal windows. ### Solution 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`: ``` $ ssh ${HOST_KV1_PRIVATE_IP} vi /tidb-deploy/tikv-20160/conf/tikv.toml # 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: ``` $ tiup cluster edit-config tidb-test 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: ``` $ tiup cluster reload tidb-test 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. ``` $ ssh ${HOST_KV1_PRIVATE_IP} vi -m /tidb-deploy/tikv-20160/conf/tikv.toml # 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" ... ``` ## End of Exercises