## Module 3: Application Best Practice During Unexpected Downtime with TiProxy + Accidents will happen. It is possible that the hardware running TiDB server goes down unexpectedly, or you may even face an unexpected cloud availability zone outage. In these cases, your connection will still be broken. + TiDB is designed to maintain high availability (HA). As long as your application follows the best practice discussed in this module, you should be able to cope with those events easily. + In this module, we use unexpected TiDB server failure as an example. ### Step 1. Scale out one TiDB server + In [Terminal 2](ssh://monitor1-ssh#2), and scale out one TiDB server. ```bash ~/.tiup/bin/tiup cluster scale-out tidb-demo scale-out-tidb-2.yaml \ --user ec2-user \ -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ -y ``` ```output ... Scaled cluster `tidb-demo` in successfully ``` + Check the cluster topology. you should have 2 TiDB servers, 3 TiKV servers, and 1 TiProxy server. ```bash ~/.tiup/bin/tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v${LAB:TIDB_VERSION} Deploy user: ec2-user SSH type: builtin Dashboard URL: http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard Grafana URL: http://${LAB:HOST_MONITOR1_PRIVATE_IP}:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- ${LAB:HOST_MONITOR1_PRIVATE_IP}:9093 alertmanager ${LAB:HOST_MONITOR1_PRIVATE_IP} 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 ${LAB:HOST_MONITOR1_PRIVATE_IP}:3000 grafana ${LAB:HOST_MONITOR1_PRIVATE_IP} 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 ${LAB:HOST_PD1_PRIVATE_IP}:2379 pd ${LAB:HOST_PD1_PRIVATE_IP} 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 ${LAB:HOST_MONITOR1_PRIVATE_IP}:9090 prometheus ${LAB:HOST_MONITOR1_PRIVATE_IP} 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 ${LAB:HOST_DB1_PRIVATE_IP}:4000 tidb ${LAB:HOST_DB1_PRIVATE_IP} 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 ${LAB:HOST_DB2_PRIVATE_IP}:4000 tidb ${LAB:HOST_DB2_PRIVATE_IP} 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 ${LAB:HOST_KV1_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV1_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_KV2_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV2_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_KV3_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV3_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_MONITOR1_PRIVATE_IP}:6000 tiproxy ${LAB:HOST_MONITOR1_PRIVATE_IP} 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 10 ``` ### Step 2. Run the workload + In [Terminal 1](ssh://monitor1-ssh#1), run the workload. + At this stage, you can see that the threads work on the different TiDB servers. The IP addresses of the TiDB servers are `${LAB:HOST_DB1_PRIVATE_IP}` and `${LAB:HOST_DB2_PRIVATE_IP}`. + The code is the same as the code `demo_tiproxy_no_retry.py` in Module 1. You can refer to Module 1 for detailed explanations. ```bash python3 demo_tiproxy_no_retry.py ``` ```output ... Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 3 balance updated to: 2 Thread 4: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 1 balance updated to: 6 Thread 3: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 9 balance updated to: 8 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 10 balance updated to: 5 Thread 2: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 1 balance updated to: 3 ... ``` ### Step 3. Kill the TiDB server in [Terminal 2](ssh://monitor1-ssh#2) + In this step, we will use `kill -9 ` to simulate an unexpected TiDB server failure. + In [Terminal 2](ssh://monitor1-ssh#2), log in to TiDB server 2. + Run the following command, if prompt, type `yes`. ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ${HOST_DB2_PRIVATE_IP} -y ``` ```output Last login: Thu Sep 4 23:12:01 2025 from ip-10-90-4-66.ap-northeast-1.compute.internal 50 package(s) needed for security, out of 63 available Run "sudo yum update" to apply all updates. ``` > [!TIP] > TiUP uses systemd to start a daemon process. If the process terminates unexpectedly, it will be pulled up after 15 seconds. > The script used to restart the TiDB server is `run_tidb.sh` located under `/tidb-deploy/tidb-4000/scripts/`. In the following steps, we simply rename the script, so that TiDB will not be restarted in 15 seconds. + Rename the restarting script. ```bash sudo mv /tidb-deploy/tidb-4000/scripts/run_tidb.sh /tidb-deploy/tidb-4000/scripts/run_tidb2.sh ``` + Kill a TiDB server based on the process id. The following command should have no output. ```bash kill -9 `pidof tidb-server` ``` ### Step 4. Observe the output in [Terminal 1](ssh://monitor1-ssh#1) ```output ... Thread 1: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 4 balance updated to: 1 Thread 3: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 10 balance updated to: 10 Thread 4: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 9 balance updated to: 1 Thread 2: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 3 balance updated to: 4 Thread 3: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. Error during query execution: Lost connection to MySQL server during query Thread 1: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. Error during query execution: Lost connection to MySQL server during query Thread 4: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 7 balance updated to: 1 Thread 2: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 10 balance updated to: 2 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 1 balance updated to: 8 ... ``` + You can see two errors `Error during query execution: Lost connection to MySQL server during query`. This is a sign where the query is disrupted. + Stop the workload by using `ctrl` + `c`. ```output ^C Received interrupt signal. Stopping all threads... ``` > [!TIP] > It is possible that today is your lucky day - killing a TiDB server does not interrupt any query. In such rare cases, follow Step 5 to restore the TiDB cluster, and perform Step 4 again. You may also consider increase the number of threads (current value is 4), to boost the chance of a transaction being interrupted. ### Step 5. Restart the killed TiDB Server + In [Terminal 2](ssh://monitor1-ssh#2), rename the restarting script to the origin name ```bash sudo mv /tidb-deploy/tidb-4000/scripts/run_tidb2.sh /tidb-deploy/tidb-4000/scripts/run_tidb.sh sleep 15 ``` + In [Terminal 1](ssh://monitor1-ssh#1), check the cluster topology. you should have 2 TiDB servers, 3 TiKV servers, and 1 TiProxy server. ```bash ~/.tiup/bin/tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v${LAB:TIDB_VERSION} Deploy user: ec2-user SSH type: builtin Dashboard URL: http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard Grafana URL: http://${LAB:HOST_MONITOR1_PRIVATE_IP}:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- ${LAB:HOST_MONITOR1_PRIVATE_IP}:9093 alertmanager ${LAB:HOST_MONITOR1_PRIVATE_IP} 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 ${LAB:HOST_MONITOR1_PRIVATE_IP}:3000 grafana ${LAB:HOST_MONITOR1_PRIVATE_IP} 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 ${LAB:HOST_PD1_PRIVATE_IP}:2379 pd ${LAB:HOST_PD1_PRIVATE_IP} 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 ${LAB:HOST_MONITOR1_PRIVATE_IP}:9090 prometheus ${LAB:HOST_MONITOR1_PRIVATE_IP} 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 ${LAB:HOST_DB1_PRIVATE_IP}:4000 tidb ${LAB:HOST_DB1_PRIVATE_IP} 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 ${LAB:HOST_DB2_PRIVATE_IP}:4000 tidb ${LAB:HOST_DB2_PRIVATE_IP} 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 ${LAB:HOST_KV1_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV1_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_KV2_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV2_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_KV3_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV3_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_MONITOR1_PRIVATE_IP}:6000 tiproxy ${LAB:HOST_MONITOR1_PRIVATE_IP} 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 10 ``` ### Step 6. Read the Python code `demo_tiproxy_retry.py` ```bash cat demo_tiproxy_retry.py ``` + The code `demo_tiproxy_retry.py` and `demo_tiproxy_no_retry.py` is highly similar. Except that `demo_tiproxy_retry.py` implements retry logic as following. + In the case of connection lost, the code will retry up to 5 times. It waits 5 seconds between each retry. + When a TiDB server goes down unexpectedly, the Python code `demo_tiproxy_retry.py` effectively gives the TiProxy at least 25 seconds (5 seconds * 5 times) to identify the corrupted TiDB server, and route the connects to the healthy TiDB servers. ```output ... def retry(max_attempts=5, delay=5): def decorator(func): @wraps(func) def wrapper(*args, **kwargs): for attempt in range(max_attempts): try: return func(*args, **kwargs) except (Error, MySQLInterfaceError) as e: if attempt == max_attempts - 1: # Last attempt print(f"All {max_attempts} attempts failed. Last error: {e}") raise print(f"Attempt {attempt + 1} failed: {e}. Retrying...") time.sleep(delay) return None return wrapper return decorator @retry(max_attempts=5, delay=5) def execute_database_operation(thread_id): ... ``` + Let's now discuss, how long it takes before a TiProxy identify a TiDB as unhealthy. + Here are a few related parameters. Note that they are not configurable. When combined, those parameter means that the health check interval is 3 seconds. If no response received within 2 seconds, TiProxy will regard this health check as failure, and then start the retry process immediately. TiProxy will retry for at most 3 times, and there is 1 second interval between 2 retries. + `healthCheckInterval = 3 seconds` + `healthCheckMaxRetries = 3` + `healthCheckRetryInterval = 1 second` + `healthCheckTimeout = 2 seconds` + We simplifies the parameter names in the following figures. + `healthCheckInterval` : `Interval` + `healthCheckMaxRetries` : `Retry` + `healthCheckRetryInterval` : `RetryInterval` + `healthCheckTimeout = 2 seconds` : `Timeout` + In a **lucky** case, where the TiDB server goes down right **before** a health check. It takes the TiProxy approximately 10 seconds to mark a TiDB server as unhealthy. ![demo018_tidb_health_judgement_lucky_aa_001_20250911.png 85%](https://lab-static.pingcap.com/images/2025/9/11/a3e648047cd786ce44131a20e3290e0ffa7eef8d.png) + In an **unlucky** case, where the TiDB server goes down right **after** a successful health check. It takes the TiProxy approximately 13 seconds to mark a TiDB server as unhealthy. ![demo018_tidb_health_judgement_unlucky_aa_001_20250911.png 85%](https://lab-static.pingcap.com/images/2025/9/11/09fa151f39fc666a9ebbdd1c67fc019255b1ee09.png) + Since the retry mechanism in `demo_tiproxy_retry.py` covers as much as 25 seconds, the connection will be routed to the healthy TiDB server in time. ### Step 7. Run the code with retry logic ```bash python3 demo_tiproxy_retry.py ``` ```output ... Thread 4: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 5 balance updated to: 6 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 8 balance updated to: 2 Thread 2: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 2 balance updated to: 5 ... ``` ### Step 8. Kill the TiDB server again + In [Terminal 2](ssh://monitor1-ssh#2), rename the restarting script. ```bash sudo mv /tidb-deploy/tidb-4000/scripts/run_tidb.sh /tidb-deploy/tidb-4000/scripts/run_tidb2.sh ``` + Kill a TiDB server based on the process id. The following command should have no output. ```bash kill -9 `pidof tidb-server` ``` ### Step 9. Observe the output in [Terminal 1](ssh://monitor1-ssh#1) + Compared to the output in Step 4, the following output shows that the code retry its query when the connection is lost. So that your clients is less likely to receive an error message. You application can keep going. + After some time, all threads are routed to the healthy TiDB server. ```output ... Thread 2: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. Error during query execution: Lost connection to MySQL server during query Attempt 1 failed: Lost connection to MySQL server during query. Retrying... Thread 4: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. Error during query execution: Lost connection to MySQL server during query Attempt 1 failed: Lost connection to MySQL server during query. Retrying... Thread 2: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 10 balance updated to: 2 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 1 balance updated to: 8 ... ``` ### Conclusion + If the TiDB server is offline unexpectedly, the connection between your application and the TiDB will still be broken. However, the TiDB cluster survives. Simple retry logic in your application can keep you application alive.