## Module 1: Discussion and Prepare the Environment + In this module, we start with a discussion about the best practice for the application when scaling in the SQL layer. Please read the discussions carefully. + We make some preparation for the lab, including scaling out a TiDB Server (so that we can scale in it later), and importing some data. ### Step 1. Discussion: application development best practice when scaling in the SQL layer > [!TIP] > What we discuss in this module only applies to scaling in the SQL layer. It does NOT apply to scale in TiKV or PD. Scaling in TiKV or PD is transparent to your application. The reasons are two-fold: (1) your application does not talk to TiKV or PD directly, nor does the network load balancer or the TiProxy; (2) The connection between TiDB and PD/TiKV is gRPC. + In general, there are three ways for your application to communicate with TiDB + Via the TiProxy + Via the network load balancer + Directly to one TiDB server instance (NOT recommended in the production environment) + TiProxy is an optional component of TiDB cluster. TiProxy provides connection migration, failover, service discovery, and quick deployment. If you use TiProxy, scaling in the SQL layer is also transparent to your application. You can avoid all the complexity we discuss below. + If your application connects to TiDB via a network load balancer or talks to TiDB directly, the scaling in TiDB may not be completely transparent. You need to follow application development best practice we discuss in this module. This is because the connection between your application (or the network load balancer) and TiDB uses MySQL protocol. In MySQL protocol, the client assumes that there is only a single server, NOT a distributed server fleet. Furthermore, the connection relies on the status of TCP. + In the production environment, people seldom connect the application with the TiDB server instance directly. In this module, we discuss and compare two cases: (a) the application connects to TiDB via the network load balancer; (b) the application connects to TiDB via the TiProxy. + Let's now discuss what a TiDB server node would do when scaling in: + Step 1: Close the TCP port (by default, it is port 4000), and refuse further connections + Step 2: For existing connections, check if there is any transactions executing + Case 2.1: If there are no transactions running, close the connection + Case 2.2: If there are transactions running, wait for a certain period of time for the transaction to finish, and terminate when time out or transaction finishes (whichever is earlier). (The terminology for the waiting time is called [gracefulCloseConnectionsTimeout](https://github.com/pingcap/tidb/blob/bed8f2573a7d6189f1611d913130cfb3150c9100/cmd/tidb-server/main.go#L924-L934C19). It is 15 seconds, and is unconfigurable.) + Let's now discuss the health check process of the network load balancer (NLB). We use the NLB in AWS as an example. There are a few settings and parameters related to the health check process. (More detailed can be found in [Health checks for Network Load Balancer target groups](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/target-group-health-checks.html)) + `HealthCheckTimeoutSeconds`: The amount of time, in seconds, during which no response from a target means a failed health check. The range is 2–120 seconds. It is set to 5 seconds in our case. + `HealthCheckIntervalSeconds`: The approximate amount of time, in seconds, between health checks of an individual target. The range is 5–300 seconds. It is set to 5 seconds in our case. + `UnhealthyThresholdCount`: The number of consecutive failed health checks required before considering a target unhealthy. The range is 2–10. It is set to 2 in our case. + `Unhealthy draining interval`: The amount of time, in seconds, during which the existing connections will be drained, after the target is marked as unhealthy. After this interval, the existing connections are to be disconnected. In our case, it is set to 0. + The amount of time waiting time for a TiDB server node to be marked as unhealthy, in the best case (when scaled in finishes right before a health check is sent), is `(UnhealthyThresholdCount - 1) * HealthCheckIntervalSeconds + HealthCheckTimeoutSeconds`. In our case, it's `(2 - 1) * 5 + 5 = 10` + Here we provide some examples to give you an intuition on the correctness of the calculation mentioned above. For simplicity, in the below figures, we call `HealthCheckIntervalSeconds` as `interval`, `UnhealthyThresholdCount` as `count`, and `HealthCheckTimeoutSeconds` as `timeout`. + Case 1: `interval = 5`, `count = 2`, and `timeout = 2`. The TiDB server scaled in will be marked as `unhealthy` after 7 seconds in the best case. ![demo009-m7-interval-case1_aa_001_20250808.png 85%](https://lab-static.pingcap.com/images/2025/8/8/14ae237101b018cd0389e4808d8e33ebbc499cc3.png) + Case 2: `interval = 5`, `count = 2`, and `timeout = 6`. The TiDB server scaled in will be marked as `unhealthy` after 11 seconds in the best case. ![demo009-m7-interval-case2_aa_001_20250808.png 85%](https://lab-static.pingcap.com/images/2025/8/8/6ac54809141c7d7c25a5ba2180490e96da018790.png) + Case 3: `interval = 5`, `count = 3`, and `timeout = 2`. The TiDB server scaled in will be marked as `unhealthy` after 12 seconds in the best case. ![demo009-m7-interval-case3_aa_001_20250808.png 85%](https://lab-static.pingcap.com/images/2025/8/8/96e212e18bcebfdea83aefa0b817aeaf227ecab2.png) + On the unlucky days, if the scaled in finishes right after a health check is sent, and the health check is returned success in no time, that means you have to wait `UnhealthyThresholdCount * HealthCheckIntervalSeconds + HealthCheckTimeoutSeconds`. Let's take case 1 as an example again. + Case 1: `interval = 5`, `count = 2`, and `timeout = 2`. The TiDB server scaled in will be marked as `unhealthy` after 12 seconds in the worst case. ![demo009-m7-interval-case1_worse_aa_001_20250808.png 85%](https://lab-static.pingcap.com/images/2025/8/8/9c3c48f009559e5b8c149d5716561ca3992acc00.png) + From the above discussion, we should know that in our settings (`interval = 5`, `count = 2`, and `timeout = 5`), the time range for the TiDB node being marked by `unhealthy` by the target group is between 10 seconds and 15 seconds + Based on the procedure of TiDB Server scaling in, there are a few cases where your application needs some extra effort to handle. + (Situation A) In Step 1, it is possible that the network load balancer has not yet marked the TiDB Server to be scaled in as unhealthy, thus the load balancer still establishes new connections to this TiDB Server. It may cause errors like `dial tcp {host}:{port} connect: connection refused`. + (Situation B) In Step 2, Case 2.1, if the closed connection is returned to the connection pool, and is used again later, you may see errors like `begin transaction failed`. + (Situation C) In Step 2, Case 2.1, for connections with multiple transactions (for example, if autocommit is on), it is possible that earlier transactions executes successfully, while later transactions encounter errors like `invalid connection`. + (Situation D) In Step 2, Case 2.2, for transactions running longer than 15 seconds, you may see errors like `Lost connection to MySQL server`. > [!TIP] > We recommend you use connection validation reasonably so that the health status of the connections can be checked periodically. + For those cases mentioned above, simple retry logic may help solve some of them. + Next, we will show you the behavior of the applications when TiDB Server being connected to is scaled in. We will look into three cases: + Application connected to TiDB directly without connection retry + Application connected to TiDB directly with connection retry + Application connected to ToProxy without connection retry ### Step 2. Connect to the TiUP control machine + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. ### Step 3. Check the cluster topology + Currently, you should have 1 TiDB Server, and 3 TiKV Servers. ```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://35.91.126.63: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.3.89:2379 pd 10.90.3.89 2379/2380 linux/x86_64 Up|L /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.74:4000 tidb 10.90.1.74 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.1.103:20160 tikv 10.90.1.103 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.30:20160 tikv 10.90.1.30 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.34:20160 tikv 10.90.1.34 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.204:6000 tiproxy 10.90.4.204 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 9 ``` ### Step 4. Scale out a TiDB Server + Let’s now scale out a TiDB Server, so that later we can scale in a 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 ``` ### Step 5. Check the cluster topology + Make sure you now have two `TiDB`s and one `TiProxy`. ```bash ~/.tiup/bin/tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v8.5.1 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://3.112.81.1:2379/dashboard Grafana URL: http://10.90.4.40:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.40:9093 alertmanager 10.90.4.40 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.40:3000 grafana 10.90.4.40 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.2.141:2379 pd 10.90.2.141 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.40:9090 prometheus 10.90.4.40 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.165:4000 tidb 10.90.1.165 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.1.66:4000 tidb 10.90.1.66 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.3.160:20160 tikv 10.90.3.160 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.193:20160 tikv 10.90.3.193 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.243:20160 tikv 10.90.3.243 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.40:6000 tiproxy 10.90.4.40 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 ``` ### Step 6. Importing data ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```sql USE test CREATE TABLE `users` ( `id` int, `name` varchar(100), `balance` int ); INSERT INTO users VALUES (1, 'Alice', 10); INSERT INTO users VALUES (2, 'Bob', 10); INSERT INTO users VALUES (3, 'Charlie', 10); INSERT INTO users VALUES (4, 'Doug', 10); INSERT INTO users VALUES (5, 'Edison', 10); INSERT INTO users VALUES (6, 'Frank', 10); INSERT INTO users VALUES (7, 'Grace', 10); INSERT INTO users VALUES (8, 'Hannah', 10); INSERT INTO users VALUES (9, 'Iris', 10); INSERT INTO users VALUES (10, 'Jack', 10); ``` ```sql SET GLOBAL autocommit = 0; exit ```