## 模块 3：使用 TiProxy 应对非预期停机的应用最佳实践 + 意外总会发生：承载 TiDB Server 的硬件可能会突然故障，甚至可能遇到云可用区的突发中断。在这些情况下，连接仍可能被打断。 + TiDB 以高可用（HA）为目标进行设计。只要你的应用遵循本模块讨论的最佳实践，便能从容应对这类事件。 + 本模块以 TiDB Server 的非预期故障为例进行说明。 ### 步骤 1. 扩容一台 TiDB Server + 在 [Terminal 2](ssh://monitor1-ssh#2) 中扩容一台 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 ``` + 查看集群拓扑，此时应为 2 台 TiDB Server、3 台 TiKV Server、1 台 TiProxy。 ```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 ``` ### 步骤 2. 运行负载 + 在 [Terminal 1](ssh://monitor1-ssh#1) 中运行负载。 + 此时可见各线程分布在不同 TiDB Server 上，其 IP 分别为 `${LAB:HOST_DB1_PRIVATE_IP}` 与 `${LAB:HOST_DB2_PRIVATE_IP}`。 + 代码与模块 1 中的 `demo_tiproxy_no_retry.py` 相同，详细解释可参见模块 1。 ```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 ... ``` ### 步骤 3. 在 [Terminal 2](ssh://monitor1-ssh#2) 中终止 TiDB Server 进程 + 本步骤使用 `kill -9 ` 来模拟 TiDB Server 的突发故障。 + 在 [Terminal 2](ssh://monitor1-ssh#2) 登录到 TiDB Server 2。 + 运行下面的命令（如有提示请输入 `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 使用 systemd 启动守护进程。若进程异常退出，约 15 秒后会被自动拉起。 > 用于重启 TiDB Server 的脚本是 `/tidb-deploy/tidb-4000/scripts/` 下的 `run_tidb.sh`。下面步骤中我们先重命名该脚本，避免 TiDB 在 15 秒内被自动拉起。 + 重命名该重启脚本： ```bash sudo mv /tidb-deploy/tidb-4000/scripts/run_tidb.sh /tidb-deploy/tidb-4000/scripts/run_tidb2.sh ``` + 根据进程ID强制终止 TiDB Server。以下命令不会有任何输出。 ```bash kill -9 `pidof tidb-server` ``` ### 步骤 4. 在 [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 ... ``` + 你会看到两条 `Error during query execution: Lost connection to MySQL server during query` 错误，这是查询被中断的迹象。 + 使用 `ctrl` + `c` 停止负载。 ```output ^C Received interrupt signal. Stopping all threads... ``` > [!TIP] > 有时候，杀掉一台 TiDB Server 并未中断任何查询。此类少见情况，请先按步骤 5 恢复 TiDB 集群，再重复执行步骤 4。你也可以增大线程数（当前为 4）以提高事务被打断的概率。 ### 步骤 5. 重启被终止的 TiDB Server + 在 [Terminal 2](ssh://monitor1-ssh#2) 中将重启脚本名改回： ```bash sudo mv /tidb-deploy/tidb-4000/scripts/run_tidb2.sh /tidb-deploy/tidb-4000/scripts/run_tidb.sh sleep 15 ``` + 在 [Terminal 1](ssh://monitor1-ssh#1) 检查集群拓扑，应为 2 台 TiDB Server、3 台 TiKV Server、1 台 TiProxy。 ```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 ``` ### 步骤 6. 阅读 Python 代码 `demo_tiproxy_retry.py` ```bash cat demo_tiproxy_retry.py ``` + `demo_tiproxy_retry.py` 与 `demo_tiproxy_no_retry.py` 高度相似，区别在于前者实现了如下重试逻辑： + 当连接丢失时，最多重试 5 次，每次间隔 5 秒。 + 当某台 TiDB Server 意外宕机时，该脚本等效地给予 TiProxy 至少 25 秒（5 秒 × 5 次）去识别异常实例，并将连接路由至健康实例。 ```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): ... ``` + 下面讨论 TiProxy 判定某台 TiDB 不健康所需的时间： + 相关参数如下（不可配置）。组合起来意味着健康检查间隔为 3 秒；若在 2 秒内未收到响应，则认为本次检查失败并立刻开始重试；最多重试 3 次，两次重试间隔 1 秒。 + `healthCheckInterval = 3 seconds` + `healthCheckMaxRetries = 3` + `healthCheckRetryInterval = 1 second` + `healthCheckTimeout = 2 seconds` + 为便于说明，下图中对参数名做了简化： + `healthCheckInterval`：`Interval` + `healthCheckMaxRetries`：`Retry` + `healthCheckRetryInterval`：`RetryInterval` + `healthCheckTimeout`：`Timeout` + 若在一次健康检查开始前恰好发生故障（“幸运”情形），TiProxy 大约需要 10 秒标记该 TiDB 为不健康。 ![demo018_tidb_health_judgement_lucky_aa_001_20250911.png 85%](https://lab-static.pingcap.com/images/2025/9/11/a3e648047cd786ce44131a20e3290e0ffa7eef8d.png) + 若在一次成功的健康检查刚结束后立刻发生故障（“不幸运”情形），TiProxy 大约需要 13 秒标记其为不健康。 ![demo018_tidb_health_judgement_unlucky_aa_001_20250911.png 85%](https://lab-static.pingcap.com/images/2025/9/11/09fa151f39fc666a9ebbdd1c67fc019255b1ee09.png) + 由于 `demo_tiproxy_retry.py` 的重试机制覆盖达 25 秒，连接可及时被路由至健康的 TiDB Server。 ### 步骤 7. 运行带重试逻辑的代码 ```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 ... ``` ### 步骤 8. 再次终止 TiDB Server 进程 + 在 [Terminal 2](ssh://monitor1-ssh#2) 中重命名重启脚本： ```bash sudo mv /tidb-deploy/tidb-4000/scripts/run_tidb.sh /tidb-deploy/tidb-4000/scripts/run_tidb2.sh ``` + 根据进程ID强制终止 TiDB Server。以下命令不会有任何输出。 ```bash kill -9 `pidof tidb-server` ``` ### 步骤 9. 在 [Terminal 1](ssh://monitor1-ssh#1) 中观察输出 + 与步骤 4 的输出对比可见：当连接丢失时，代码会对查询进行重试，从而降低客户端收到错误的概率，应用可继续运行。 + 过一段时间后，所有线程都会被路由到健康的 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 ... ``` ### 小结 + 当某台 TiDB Server 非预期下线时，应用到数据库的连接仍可能被打断，但 TiDB 数据库本身保持可用。通过在应用中加入简单的重试逻辑，可以显著提升业务的持续可用性。