# Exercises 2: Enjoying Zero Downtime with TiDB
### Overview
+ During these exercises, you will get hands-on experience with TiDB's zero downtime capabilities.
+ In Exercise 2-1, your business will remain operational after a database storage layer instance failure and scale-in/out.
+ In Exercise 2-2, your business will remain operational in the event of database SQL layer instance failure and scale-in/out.
+ In Exercise 2-3, your business operations are not affected even if there is a failure in the database metadata layer instance.
### Notes
+ Some of the steps require that you type commands in two different terminal windows. You might find it convenient to launch two terminals and keep them 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 the terminal window is OS prompt.
+ The "`tidb>`" or "`mysql>`" in the terminal window is SQL command prompt.
+ These exercises can be run on a single machine, such as laptop.
### Assumptions
+ You are using macOS or Linux.
+ You have cloned the [lab repository](https://github.com/pingcap/tidb-course-201-lab.git).
+ You have completed the Exercises 1.
+ You have JDK installed.
## Exercise 2-1: Storage Layer Instance Failure and Scale-out/in Showcase
### Duration
+ This exercise should take you approximately 10 minutes to complete.
### Tasks
1. Create a table within the `test` schema.
2. Initiate a Java task to insert rows into the created table.
3. Select a TiKV instance at random and forcefully terminate it.
4. Verify that the Java workload remains unaffected.
5. Add a healthy TiKV instance back (scale-out) to the TiDB Playground cluster.
### Solutions
1. Log in as the user `root` and make sure the region max replicas is set to 3.
```
$ cd tidb-course-201-lab/scripts/
$ ./connect-4000.sh
```
```sql
SET CONFIG PD `replication.max-replicas`=3;
SHOW CONFIG WHERE type='pd' AND name='replication.max-replicas';
```
```
tidb:4000> SET CONFIG PD `replication.max-replicas`=3;
Query OK, 0 rows affected (0.47 sec)
tidb:4000> SHOW CONFIG WHERE type='pd' AND name='replication.max-replicas';
+------+----------------+--------------------------+-------+
| Type | Instance | Name | Value |
+------+----------------+--------------------------+-------+
| pd | 127.0.0.1:2382 | replication.max-replicas | 3 |
| pd | 127.0.0.1:2379 | replication.max-replicas | 3 |
| pd | 127.0.0.1:2384 | replication.max-replicas | 3 |
+------+----------------+--------------------------+-------+
3 rows in set (0.02 sec)
```
2. Create a table named `dummy` under the `test` schema while keeping the database session logged in.
```sql
DROP TABLE IF EXISTS test.dummy;
CREATE TABLE test.dummy (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80), event JSON);
```
tidb:4000> DROP TABLE IF EXISTS test.dummy;
Query OK, 0 rows affected (2.05 sec)
tidb:4000> CREATE TABLE test.dummy (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80), event JSON);
Query OK, 0 rows affected (1.05 sec)
```
3. Open a new terminal, execute script `02-demo-jdbc-endless-insert-dummy.sh` to apply the testing workload.
```
$ ./02-demo-jdbc-endless-insert-dummy.sh
Connecting by jdbc:mysql://127.0.0.1:4000/test?useServerPrepStmts=true&cachePrepStmts=true
Connection established.
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 18:07:12 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 18:07:12 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 18:07:13 CST 2023')
...
```
4. Return to the command line client session of MySQL and verify that the number of rows in the `test.dummy` table is increasing.
```sql
SELECT name, MAX(id), COUNT(event) FROM test.dummy GROUP BY name ORDER BY name;
SELECT SLEEP(1);
SELECT name, MAX(id), COUNT(event) FROM test.dummy GROUP BY name ORDER BY name;
```
```
tidb:4000> SELECT COUNT(*) FROM test.dummy;
+----------+
| COUNT(*) |
+----------+
| 406 |
+----------+
1 row in set (0.00 sec)
tidb:4000> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
| 0 |
+----------+
1 row in set (1.00 sec)
tidb:4000> SELECT COUNT(*) FROM test.dummy;
+----------+
| COUNT(*) |
+----------+
| 408 |
+----------+
1 row in set (0.01 sec)
```
5. Open a new terminal again, execute the script `playground-check.sh` to review the TiDB Playground cluster status.
```
$ ./playground-check.sh
tiup is checking updates for component playground ...timeout!
Starting component `playground`: /Users/username/.tiup/components/playground/v1.12.0/tiup-playground display
Pid Role Uptime
--- ---- ------
64302 pd 9m22.565751606s
64303 pd 9m22.48643253s
64304 pd 9m22.402706634s
64305 tikv 9m22.326618405s
64306 tikv 9m22.209588552s
64308 tikv 9m22.115029008s
64309 tidb 9m22.03864131s
64310 tidb 9m21.956338004s
64311 tidb 9m21.88306325s
64351 tiflash 8m20.125332261s
```
6. Randomly selecting a TiKV instance (by PID) to terminate emulates a system failure of a TiKV server. In the example, `64306` is selected.
```
$ kill -9 64306
```
7. Check the cluster status again.
```
$ ./playground-check.sh
tiup is checking updates for component playground ...
Starting component `playground`: /Users/username/.tiup/components/playground/v1.12.0/tiup-playground display
Pid Role Uptime
--- ---- ------
64302 pd 9m58.768547973s
64303 pd 9m58.689229206s
64304 pd 9m58.605502777s
64305 tikv 9m58.529414305s
64306 tikv signal: killed
64308 tikv 9m58.317826029s
64309 tidb 9m58.241432113s
64310 tidb 9m58.159128966s
64311 tidb 9m58.085854188s
64351 tiflash 8m56.32812433s
```
8. Verify that the number of rows in the `test.dummy` table is increasing, the workoad is not affected.
```sql
SELECT COUNT(*) FROM test.dummy;
SELECT SLEEP(1);
SELECT COUNT(*) FROM test.dummy;
```
```
tidb:4000> SELECT COUNT(*) FROM test.dummy;
+----------+
| COUNT(*) |
+----------+
| 483 |
+----------+
1 row in set (0.00 sec)
tidb:4000> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
| 0 |
+----------+
1 row in set (1.00 sec)
tidb:4000> SELECT COUNT(*) FROM test.dummy;
+----------+
| COUNT(*) |
+----------+
| 485 |
+----------+
1 row in set (0.00 sec)
```
9. Although the business has not been interrupted, since the `replication.max-replicas` is 3, the TiDB Playground cluster cannot afford to lose one more TiKV server or it will be data loss, the cluster need to grow back to 3 members by adding a new one.
```
$ ./add-tikv-instance.sh
tiup is checking updates for component playground ...
Starting component `playground`: /Users/username/.tiup/components/playground/v1.12.0/tiup-playground scale-out --kv 1
```
```
$ ./playground-check.sh
tiup is checking updates for component playground ...
Starting component `playground`: /Users/username/.tiup/components/playground/v1.12.0/tiup-playground display
Pid Role Uptime
--- ---- ------
64302 pd 22m28.507529656s
64303 pd 22m28.428212955s
64304 pd 22m28.344488246s
64305 tikv 22m28.268400281s
64306 tikv signal: killed
64308 tikv 22m28.056811962s
64925 tikv 5m48.184605229s
64309 tidb 22m27.98042226s
64310 tidb 22m27.898118883s
64311 tidb 22m27.824847169s
64351 tiflash 21m26.067116781s
```
10. Discard the failed TiKV storage. In the example, it is `PID 64306`.
```
tiup playground scale-in --pid 64306
tiup is checking updates for component playground ...
Starting component `playground`: /Users/username/.tiup/components/playground/v1.12.0/tiup-playground scale-in --pid 64306
tikv will be stop when tombstone
```
11. Stop the Java workload by pressing `Ctrl-C`. If you would like to proceed to the next exercise, keep it running.
```
...
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 19:18:36 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 19:18:37 CST 2023')
^C $
```
12. So far, there has been no impact on the Java workload. We have experienced an "unplanned" TiKV instance failure (a hard scale-in), a "planned" TiKV layer scale-out. If time permits, you may proceed with step 9 to perform a soft scale-in, anticipating the same outcome, meaning your business will continue to operate normally. It is important to note that in a production environment, the Playground should not be utilized. Further understanding of operational practices within a production system can be acquired by enrolling in the "TiDB Administration" instructor-led training course.
## Exercise 2-2: SQL Layer Instance Failure and Scale-out/in Showcase
### Duration
+ This exercise should take you approximately 10 minutes to complete.
### Tasks
1. Select a SQL Layer instance at random and forcefully terminate it.
2. Verify that the Java workload remains unaffected.
3. Add a healthy SQL Layer instance back (scale-out) to the TiDB Playground cluster.
### Solutions
1. Execute script `02-demo-jdbc-endless-insert-dummy.sh to apply the testing workload if you abort it in previous exercise. Note that in the example Java workload, connections are attempted to the ports 4000, 4001, and 4002 in a round-robin manner, corresponding to the TiDB Playground TiDB servers, respectively.
```
$ ./02-demo-jdbc-endless-insert-dummy.sh
```
2. Open a new terminal and execute the script `playground-check.sh` to review the status of the TiDB Playground cluster. Take note of the PID numbers of the TiDB server instances in ascending order, which, in this example, are `67997`, `67998`, and `67999`.
```
$ ./playground-check.sh
tiup is checking updates for component playground ...timeout!
Starting component `playground`: /Users/username/.tiup/components/playground/v1.12.0/tiup-playground display
Pid Role Uptime
--- ---- ------
67991 pd 27m46.748072052s
67992 pd 27m46.67253318s
67993 pd 27m46.606971748s
67994 tikv 27m46.512909437s
67995 tikv 27m46.437197863s
67996 tikv 27m46.36117216s
67997 tidb 27m46.252391449s
67998 tidb 27m46.173933395s
67999 tidb 27m46.094067034s
68034 tiflash 26m43.536789172s
```
3. Execute the following command and associate the respective PID numbers from the previous step with `tidb-0`, `tidb-1`, and `tidb-2` in ascending order. In the example, PID of `tidb-0, port:4000` is `67997`, PID of `tidb-1, port:4001` is `67998` and PID of `tidb-2, port:4002` is `67999`.
```
$ grep -ri 'addr=127.0.0.1:400[012]' ~/.tiup/data/classroom/tidb-*
/Users/username/.tiup/data/classroom/tidb-0/tidb.log:... [addr=127.0.0.1:4000]
/Users/username/.tiup/data/classroom/tidb-1/tidb.log:... [addr=127.0.0.1:4001]
/Users/username/.tiup/data/classroom/tidb-2/tidb.log:... [addr=127.0.0.1:4002]
```
4. Destroy `tidb-0` first.
```
$ kill -9 67997
```
5. While observing the terminal window running the Java workload, it can be noted that the workload switches to the next TiDB server instance (port 4001) after encountering a connection error.
```
...
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:31:50 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:31:51 CST 2023')
Error from TRY A: java.sql.SQLException: Could not retrieve transation read-only status server
Connecting by jdbc:mysql://127.0.0.1:4001/test?useServerPrepStmts=true&cachePrepStmts=true
Connection established.
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:31:52 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:31:52 CST 2023')
...
```
6. Destroy `tidb-1`, the workload should connect to the next TiDB server instance as expected.
```
$ kill -9 67998
```
```
...
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:44:14 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:44:14 CST 2023')
Error from TRY A: java.sql.SQLException: Could not retrieve transation read-only status server
Connecting by jdbc:mysql://127.0.0.1:4002/test?useServerPrepStmts=true&cachePrepStmts=true
Connection established.
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:44:15 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 20:44:16 CST 2023')
...
```
7. Check the cluster status again, two TiDB server instances have gone.
```
$ ./playground-check.sh
...
Pid Role Uptime
--- ---- ------
67997 tidb signal: killed
67998 tidb signal: killed
67999 tidb 43m21.032598438s
...
```
8. Although TiDB server is stateless, you should ensure the capacity is sufficient to handle the workload, try add one TiDB server back to the cluster. Port 4000 will be reused. In the real world, the number of TiDB server instances should be determined based on the SQL workload of the system.
```
$ ./add-tidb-instance.sh
tiup is checking updates for component playground ...
Starting component `playground`: /Users/username/.tiup/components/playground/v1.12.0/tiup-playground scale-out --db 1
To connect new added TiDB: mysql --comments --host 127.0.0.1 --port 4000 -u root -p (no password)
```
Currently, there are two active TiDB servers with SQL layer operational.
```
$ ./playground-check.sh
...
Pid Role Uptime
--- ---- ------
...
67997 tidb signal: killed
67998 tidb signal: killed
67999 tidb 55m45.21135116s
70127 tidb 4m11.548342016s
```
9. Again, there has been no impact on the Java workload because it implements a reconnect logic. We have experienced an "unplanned" SQL layer instance failure (a hard scale-in), a "planned" SQL layer scale-out. If time permits, you may proceed with `tiup playground scale-in --pid ` to perform a soft scale-in, anticipating the same outcome, meaning your business will continue to operate normally. During production, TiDB servers are typically deployed behind a load balancer to avoid the need for clients to be aware of the specific connection details for each TiDB server, as shown in our example. Even better, PingCAP provides [TiProxy](https://github.com/pingcap/TiProxy) which ensures the persistence of the connection, allowing for seamless handling of unplanned outages and planned scale-in of TiDB servers without disconnecting the client. Moreover, both load balancer and TiProxy come pre-configured in the TiDB Cloud Dedicated Tier. You can get more detailed information from PingCAP professionals through the provided [link](https://www.pingcap.com/demo/?utm_source=edu&utm_medium=referral&utm_campaign=201.1.E2).
## Exercise 2-3: Metadata Layer Instance Failure Showcase
### Duration
+ This exercise should take you approximately 10 minutes to complete.
### Tasks
1. Select a Placement Driver instance at random and forcefully terminate it.
2. Verify that the Java workload remains unaffected.
### Solutions
1. Get the PID of Placement Driver instances.
```
$ ./playground-check.sh
...
Pid Role Uptime
--- ---- ------
67991 pd 1h18m17.285282706s
67992 pd 1h18m17.209743953s
67993 pd 1h18m17.144183097s
...
```
2. Destroy one of them, in this case, PID 67991.
```
$ kill -9 67991
```
```
$ ./playground-check.sh
...
Pid Role Uptime
--- ---- ------
67991 pd signal: killed
67992 pd 1h20m8.50647082s
67993 pd 1h20m8.440909269s
...
```
3. Check and confirm that the Java workload remains unaffected.
```sql
SELECT COUNT(*) FROM test.dummy;
SELECT SLEEP(1);
SELECT COUNT(*) FROM test.dummy;
```
```
idb:4000> SELECT COUNT(*) FROM test.dummy;
+----------+
| COUNT(*) |
+----------+
| 7221 |
+----------+
1 row in set (0.00 sec)
tidb:4000> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
| 0 |
+----------+
1 row in set (1.00 sec)
tidb:4000> SELECT COUNT(*) FROM test.dummy;
+----------+
| COUNT(*) |
+----------+
| 7223 |
+----------+
1 row in set (0.01 sec)
```
4. Terminate the Java workload by pressing `Ctrl-C`.
```
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 21:24:44 CST 2023')
INSERT INTO test.dummy (name) VALUES ('Sun Apr 09 21:24:44 CST 2023')
^C
```
## Conclusion
TiDB is purposefully designed to eliminate both planned and unplanned downtimes. It supports online scaling-in/out at every level of the component, along with online rolling updates and online DDL. Its built-in self-healing ability is enabled at the compute, storage, and metadata layers, ensuring business continuity even when faced with software failures, hardware malfunctions, or data center outages. In addition, users can enjoy these benefits on TiDB Cloud with no additional setup. Enroll the free course [TiDB Workshop for MySQL Users](https://www.pingcap.com/education/) and the instructor-led training [TiDB Administration](https://www.pingcap.com/education/) to learn more.
## End of Exercises 2