## Module 2: Application Best Practice When TiDB Scaling in with network load balancer

In this module, we discuss the application best practice when scaling in the TiDB Server when you use a network load balancer.

The figure below shows the topology of the TiDB cluster at this moment. The TiDB Server with the red dotted line will be scaled in later.

![demo016-m7-tapo-nlb_aa_002_20250821.png 85%](https://lab-static.pingcap.com/images/2025/9/5/3fac225ddd21a2a170c5a725da8590ffb3a465d3.png)

### Step 1. Register TiDB servers to the network load balancer

  ```bash
  source ./hosts-env.sh
  source ./cloud-env.sh

  LB_ARN=`aws elbv2 describe-load-balancers --names ${LAB:USER_UNIQUE_TAG} --query "LoadBalancers[0].LoadBalancerArn" --output text --region ${LAB:REGION_CODE}`

  DEMO_TG_ARN=`aws elbv2 describe-target-groups --load-balancer-arn ${LB_ARN} --query "TargetGroups[0].TargetGroupArn" --output text --region ${LAB:REGION_CODE}`

  aws elbv2 register-targets --target-group-arn ${DEMO_TG_ARN} --targets "Id=${HOST_DB1_PRIVATE_IP},Port=4000" "Id=${HOST_DB2_PRIVATE_IP},Port=4000" --region ${LAB:REGION_CODE}

  sleep 60;
  echo TiDB instances registered to NLB target group ${DEMO_TG_ARN}
  ```

  ```output
  TiDB instances registered to NLB target group arn:aws:elasticloadbalancing:us-west-2:xxxxxxxxxxxx:targetgroup/xxxxxx-TG-Z2TREFSV0YSJ/8efa9967b824f524
  ```

### Step 2. Check the health status of the TiDB servers in the target group

  ```bash
  aws elbv2 describe-target-health --region ${LAB:REGION_CODE} --target-group-arn ${DEMO_TG_ARN} --output table
  ```

  ```output
  -------------------------------------------------
  |             DescribeTargetHealth              |
  +-----------------------------------------------+
  ||          TargetHealthDescriptions           ||
  |+---------------------------------------------+|
  ||               HealthCheckPort               ||
  |+---------------------------------------------+|
  ||  10080                                      ||
  |+---------------------------------------------+|
  |||                  Target                   |||
  ||+-------------------+---------------+-------+||
  ||| AvailabilityZone  |      Id       | Port  |||
  ||+-------------------+---------------+-------+||
  |||  ap-northeast-1a  |  10.90.2.178  |  4000 |||
  ||+-------------------+---------------+-------+||
  |||               TargetHealth                |||
  ||+------------------+------------------------+||
  |||  State           |  healthy               |||
  ||+------------------+------------------------+||
  ||          TargetHealthDescriptions           ||
  |+---------------------------------------------+|
  ||               HealthCheckPort               ||
  |+---------------------------------------------+|
  ||  10080                                      ||
  |+---------------------------------------------+|
  |||                  Target                   |||
  ||+-------------------+--------------+--------+||
  ||| AvailabilityZone  |     Id       | Port   |||
  ||+-------------------+--------------+--------+||
  |||  ap-northeast-1a  |  10.90.2.70  |  4000  |||
  ||+-------------------+--------------+--------+||
  |||               TargetHealth                |||
  ||+------------------+------------------------+||
  |||  State           |  healthy               |||
  ||+------------------+------------------------+|
  ```

### Step 3. Get the DNS name for the load balancer

  ```bash
  LB_DNS=`aws elbv2 describe-load-balancers --region ${LAB:REGION_CODE} --names ${LAB:USER_UNIQUE_TAG} --query 'LoadBalancers[0].DNSName' --output text`
  echo ${LB_DNS}
  ```

  ```output
  g-xxxx-xxxx-xx-xx-xxxxx-xxxx.elb.ap-northeast-1.amazonaws.com
  ```

### Step 4. Run the following code against TiDB servers without reconnect logic

+ In [Terminal 1](ssh://monitor1-ssh#1), read the following code, and run it.
+ In the code below, 4 threads are started. For each thread, there is an infinite loop where it connects to the network load balancer, and update a record. There is a `sleep(4)` command in the code to mimic long running queries.
+ If a thread is routed to the TiDB server, which is scaled in later, it may run into the situation A and B described in Step 1.

  ```bash
  cat demo_no_retry.py
  ```

  ```output
  while True:
      try:
          conn = connect(
              database="test",
              host=lb_dns,
              port=4000,
              user="root",
              password="",
          )
          cursor = conn.cursor(prepared=True)

          random_integer = random.randint(0, 10)
          random_id=random.randint(1, 10)
          query = "UPDATE test.users SET balance = " + str(random_integer) + " WHERE id = " + str(random_id) + ";"

          cursor.execute("BEGIN")
          cursor.execute(query)
          time.sleep(4)

          try:
              cursor.execute("COMMIT")
              print(f"Thread {thread_id}: Committed. user {random_id} balance updated to: {random_integer}")
          except Error as e:
              print(f"Thread {thread_id}: Error during query execution: {e}")
          try:
              cursor.close()
              conn.close()
          except Error as e:
              pass

      except (Error, MySQLInterfaceError) as connect_err:
          print(f"Thread {thread_id}: Connection or execution error: {connect_err}")
  ```

  ```bash
  python3 demo_no_retry.py ${LB_DNS}
  ```

  ```output
  ...
  Thread 3: Committed. user 3 balance updated to: 3
  Thread 4: Committed. user 9 balance updated to: 4
  ...
  ```

### Step 5. Run the code against TiDB servers with retry logic

+ Open [Terminal 2](ssh://monitor1-ssh#2), read the following code, and run it.
+ This time, we have additional re-try logic in case connection drops during TiDB scaling in.
+ The retry interval is set to `5 seconds`.

  ```bash
  cat demo_retry.py
  ```

  ```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):
  ...
  ```

  ```bash
  LB_DNS=`aws elbv2 describe-load-balancers --region ${LAB:REGION_CODE} --names ${LAB:USER_UNIQUE_TAG} --query 'LoadBalancers[0].DNSName' --output text`
  echo ${LB_DNS}
  ```

  ```output
  g-xxxx-xxxx-xx-xx-xxxxx-xxxx.elb.ap-northeast-1.amazonaws.com
  ```

  ```bash
  python3 demo_retry.py ${LB_DNS}
  ```

  ```output
  ...
  Thread 2: Committed. user 4 balance updated to: 2
  Thread 1: Committed. user 1 balance updated to: 7
  ...
  ```

### Step 6. Scale in a TiDB Server

+ Open [Terminal 3](ssh://monitor1-ssh#3), run the following command

  ```bash
  ~/.tiup/bin/tiup cluster scale-in tidb-demo --node ${HOST_DB2_PRIVATE_IP}:4000 -y
  ```

  ```output
  ...
  Scaled cluster `tidb-demo` in successfully
  ```

### Step 7. Observe [Terminal 1](ssh://monitor1-ssh#1)

+ In [Terminal 1](ssh://monitor1-ssh#1), you may see the one or more of the following error messages.

  ```output
  Thread X: Connection or execution error: Lost connection to MySQL server during query
  ```

  ```output
  Thread X: Connection or execution error: 2003 (HY000): Can't connect to MySQL server on 'g-xxx-xxx-xxx-xxx-77fd842711201a37.elb.ap-northeast-1.amazonaws.com:4000' (111)
  ```

### Step 8. Observe [Terminal 2](ssh://monitor1-ssh#2)

+ In [Terminal 2](ssh://monitor1-ssh#2), you may see the following messages. Our application retries up to 5 times. It waits for 5 seconds between each retry. As we have already discussed in Module 1, in the worst case, the network load balancer will take 15 seconds to realized that one TiDB Server is unhealthy, and then route your queries to the healthy TiDB Server. Our application provides sufficient time window for NLB to detect the unhealthiness of the scaled in TiDB Server. Thus, application zero downtime is achieved.

  ```output
  Attempt 1 failed: Lost connection to MySQL server during query. Retrying...
  ```

  ```output
  Attempt 1 failed: 2003 (HY000): Can't connect to MySQL server on 'g-pingcap-com-ch-migrate-6540307-d8eaff67a1fb8f9e.elb.ap-northeast-1.amazonaws.com:4000' (111). Retrying...
  ```

+ The results in [Terminal 2](ssh://monitor1-ssh#2) show that `demo_connection_reconnect.py` has handle the TiDB Server scaling in, with a simple retry logic. Thus, this application can exploit TiDB's zero downtime feature.

### Step 9. Stop the workload

+ In [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2), use `ctrl` + `c` to stop the workload.

### Step 10. Deregister the TiDB nodes from the target group

+ In [Terminal 1](ssh://monitor1-ssh#1), run the following commands. Note, the following command MUST be executed in **Terminal 1**.

  ```bash
  TIDB_TARGETS=$(aws elbv2 describe-target-health --region ${LAB:REGION_CODE} --target-group-arn ${DEMO_TG_ARN} --query 'TargetHealthDescriptions[?Target.Port==`4000`].Target' --output json)
  ```

  ```bash
  if [ "$TIDB_TARGETS" != "[]" ]; then
    aws elbv2 deregister-targets --region ${LAB:REGION_CODE} --target-group-arn ${DEMO_TG_ARN} --targets "$TIDB_TARGETS"
    echo "Deregistered TiDB servers"
  else
    echo "No TiDB servers found to deregister"
  fi
  ```

  ```output
  Deregistered TiDB servers
  ```

### Step 11. Check the result of the deregistration

+ The following command checks the result of the deregistration. You may see one of the following output.

  ```bash
  aws elbv2 describe-target-health --region ${LAB:REGION_CODE} --target-group-arn ${DEMO_TG_ARN} --output table
  ```

  ```output
  ----------------------
  |DescribeTargetHealth|
  +--------------------+
  ```

  ```output
  -------------------------------------------------------------
  |                   DescribeTargetHealth                    |
  +-----------------------------------------------------------+
  ||                TargetHealthDescriptions                 ||
  |+--------------------------------------+------------------+|
  ||  HealthCheckPort                     |  10080           ||
  |+--------------------------------------+------------------+|
  |||                        Target                         |||
  ||+---------------------------+---------------------------+||
  |||  AvailabilityZone         |  ap-northeast-1a          |||
  |||  Id                       |  10.90.2.226              |||
  |||  Port                     |  4000                     |||
  ||+---------------------------+---------------------------+||
  |||                     TargetHealth                      |||
  ||+--------------+----------------------------------------+||
  |||  Description |  Target deregistration is in progress  |||
  |||  Reason      |  Target.DeregistrationInProgress       |||
  |||  State       |  draining                              |||
  ||+--------------+----------------------------------------+||
  ||                TargetHealthDescriptions                 ||
  |+--------------------------------------+------------------+|
  ||  HealthCheckPort                     |  10080           ||
  |+--------------------------------------+------------------+|
  |||                        Target                         |||
  ||+---------------------------+---------------------------+||
  |||  AvailabilityZone         |  ap-northeast-1a          |||
  |||  Id                       |  10.90.2.172              |||
  |||  Port                     |  4000                     |||
  ||+---------------------------+---------------------------+||
  |||                     TargetHealth                      |||
  ||+--------------+----------------------------------------+||
  |||  Description |  Target deregistration is in progress  |||
  |||  Reason      |  Target.DeregistrationInProgress       |||
  |||  State       |  draining                              |||
  ||+--------------+----------------------------------------+||
  ```

### Conclusion

+ In this module, we discuss the application best practice when scaling in the TiDB Server when you use a network load balancer.
+ We show that, when provided enough retry time window, the application can achieve zero downtime with a network load balancer, when scaling in the TiDB Server.