## Module 6: Scale In The SQL Layer with TiProxy

Scale in the SQL layer while the `sysbench` workload is running.

### Step 1. Confirm that TiProxy is in Action

- Double check the cluster status, verify TiProxy component is running and listening on port 6000.

  ```bash
  ~/.tiup/bin/tiup cluster display tidb-demo | grep -i tiproxy
  ```

  ```output
  10.90.4.55:6000    tiproxy    10.90.4.55   6000/3080    linux/x86_64  Up    -    /tidb-deploy/tiproxy-6000
  ```

- Double check the sysbench configuration file, verify that the workload will be applied against TiProxy (port 6000).

  ```bash
  cat ./sysbench_config | grep -A1 mysql-host
  ```

  ```output
  mysql-host=10.90.4.55
  mysql-port=6000
  ```

### Step 2. Run test workload

+ Switch to [Terminal 2](ssh://monitor1-ssh#2) and run the `sysbench` workload again. While the workload is running, proceed to the next step, do not wait for the workload to complete.

   ```bash
   sysbench \
    --config-file=sysbench_config oltp_read_only \
    --tables=6 \
    --table-size=1000000 run
   ```

   ```output
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Running the test with following options:
   Number of threads: 14
   Report intermediate results every 5 second(s)
   Initializing random number generator from current time

   Initializing worker threads...

   Threads started!
   ...
   ```

### Step 3. Remove one TiDB server from the cluster

+ On [Terminal 1](ssh://monitor1-ssh#1), scale in the SQL layer by removing one random TiDB server.

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

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

### Step 4. Check TiDB cluster status

+ Verify there is one `tidb` server in the TiDB cluster now, you scaled the SQL layer size from 2 to 1.

   ```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://54.185.182.184:2379/dashboard
   Grafana URL:        http://10.90.4.216:3000
   ID                 Role          Host         Ports        OS/Arch       Status  Data Dir                      Deploy Dir
   --                 ----          ----         -----        -------       ------  --------                      ----------
   10.90.4.216:9093   alertmanager  10.90.4.216  9093/9094    linux/x86_64  Up      /tidb-data/alertmanager-9093  /tidb-deploy/alertmanager-9093
   10.90.4.216:3000   grafana       10.90.4.216  3000         linux/x86_64  Up      -                             /tidb-deploy/grafana-3000
   10.90.3.191:2379   pd            10.90.3.191  2379/2380    linux/x86_64  Up|L    /tidb-data/pd-2379            /tidb-deploy/pd-2379
   10.90.4.216:9090   prometheus    10.90.4.216  9090/12020   linux/x86_64  Up      /tidb-data/prometheus-9090    /tidb-deploy/prometheus-9090
   10.90.1.16:4000    tidb          10.90.1.16   4000/10080   linux/x86_64  Up      -                             /tidb-deploy/tidb-4000
   10.90.3.112:20160  tikv          10.90.3.112  20160/20180  linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
   10.90.3.213:20160  tikv          10.90.3.213  20160/20180  linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
   10.90.3.40:20160   tikv          10.90.3.40   20160/20180  linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
   10.90.4.216:6000   tiproxy       10.90.4.216  6000/3080    linux/x86_64  Up      -                             /tidb-deploy/tiproxy-6000
   Total nodes: 10
   ```

### Step 5. Verify that the workload is not affected by the SQL layer scale-in

- Switch to [Terminal 2](ssh://monitor1-ssh#2), observe and confirm that the scaling in operation did not interrupt the workload.

### Summary

Thanks to TiProxy's session management capability, it performs online connection migration to other active TiDB server instances before a TiDB server instance is scaled in or shut down. As a result, all connections in the workload remain uninterrupted.