## Module 1: TiProxy Service Discovery During TiDB Server Scaling Out In this module, we show you the service discovery capability of TiProxy. When a TiDB server performs scaling out, if you use a common load balancer, you need to manually update the TiDB server list. However, TiProxy can automatically adjust the TiDB server list without manual intervention. The following figure shows the topology of the TiDB in our lab. The TiDB server with the red-dotted line is the one to be scaled out. ![demo018_splash_image_aa_003_20250926.png 85%](https://lab-static.pingcap.com/images/2025/9/26/359c3af54e50e857a606f12425b9849e2fcff4ba.png) ### Step 1. Connect to the TiUP control machine + Click here - [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2) to open two new terminals connected to the virtual machine assigned for your hands-on lab. + All remaining steps should be performed in [Terminal 1](ssh://monitor1-ssh#1), unless otherwise specified. ### Step 2. Check the cluster topology + Currently, you should have 1 TiDB server, 3 TiKV servers, and 1 TiProxy server. + The TiProxy is deployed on the virtual machine where monitoring service resides. This virtual machine is also used as the control machine for our lab. This setting is OK for the purpose of this lab. However, in production environment, TiProxy usually uses a dedicated node. + TiProxy uses the port `6000`. ```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_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: 9 ``` + Check the `yaml` file building this TiDB cluster. + In the `server_configs` section, we set `graceful-wait-before-shutdown` to `15` seconds. You will find more explanation about this in Module 2. + At the button part of the `yaml` file shows the configuration of TiProxy. ```bash cat topology.yaml ``` ```output global: user: "ec2-user" ssh_port: 22 deploy_dir: "/tidb-deploy" data_dir: "/tidb-data" arch: "amd64" server_configs: pd: replication.max-replicas: 3 tidb: security.auto-tls: true graceful-wait-before-shutdown: 15 pd_servers: - host: ${LAB:HOST_PD1_PRIVATE_IP} client_port: 2379 peer_port: 2380 advertise_client_addr: "${LAB:HOST_PD1_PUBLIC_IP}:2379" tidb_servers: - host: ${LAB:HOST_DB1_PRIVATE_IP} port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log tikv_servers: - host: ${LAB:HOST_KV1_PRIVATE_IP} port: 20160 status_port: 20180 - host: ${LAB:HOST_KV2_PRIVATE_IP} port: 20160 status_port: 20180 - host: ${LAB:HOST_KV3_PRIVATE_IP} port: 20160 status_port: 20180 monitoring_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} grafana_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} alertmanager_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} tiproxy_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} ``` ### Step 3. Prepare the schema for the workload ```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); SELECT * FROM users; exit ``` ```output ... mysql> SELECT * FROM users; +------+---------+---------+ | id | name | balance | +------+---------+---------+ | 1 | Alice | 10 | | 2 | Bob | 10 | | 3 | Charlie | 10 | | 4 | Doug | 10 | | 5 | Edison | 10 | | 6 | Frank | 10 | | 7 | Grace | 10 | | 8 | Hannah | 10 | | 9 | Iris | 10 | | 10 | Jack | 10 | +------+---------+---------+ 10 rows in set (0.00 sec) ... ``` ### Step 4. Read the Python code ```bash cat demo_tiproxy_no_retry.py ``` + In the following lines, in the main method (at the button of the code), 4 threads are started. ```output num_threads = 4 print(f"Starting {num_threads} database worker threads...") # Create and start threads threads = [] for i in range(num_threads): thread = threading.Thread( target=database_worker, args=(i + 1,), daemon=True ) threads.append(thread) thread.start() time.sleep(0.5) ``` + In the following lines, the Python code connects each thread to the TiProxy on port `6000`, which will later be routed to TiDB servers. + You can include the username and password in the connection just as you are connecting to TiDB server. ```output conn = connect( database="test", host="${LAB:HOST_MONITOR1_PRIVATE_IP}", port=6000, user="root", password="", ) cursor = conn.cursor(prepared=True) ``` + In the following lines, the actual IP address and the port number of the TiDB server, where the thread is connected to, are printed out. So that, we can clearly observe the routing behavior of TiProxy when running the code. ```output cursor.execute("SELECT @@hostname as hostname, @@port as port, CONNECTION_ID() as connection_id") server_info = cursor.fetchone() hostname, port, connection_id = server_info # Extract IP address from hostname (format: ip-10-90-2-124.ap-northeast-1.compute.internal) if hostname.startswith('ip-'): ip_parts = hostname.split('.')[0].split('-')[1:] # Split by '.' then by '-', skip 'ip' ip_address = '.'.join(ip_parts) else: ip_address = hostname ``` + In the following lines, each thread in the Python code randomly update the data in the table. + Each transaction is started explicitly, and last for more than 4 seconds (`time.sleep(4)`). ```output 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}: Connected to TiDB server {ip_address}:{port}. User {random_id} balance updated to: {random_integer}") except Error as e: print(f"Thread {thread_id}: Connected to TiDB server {ip_address}:{port}. Error during query execution: {e}") ... ``` ### Step 5. Run the workload + At this stage, you can see that all threads work on the same TiDB server. The IP address of the TiDB server is `${LAB:HOST_DB1_PRIVATE_IP}`, and the port number is `4000`. ```bash python3 demo_tiproxy_no_retry.py ``` ```output ... Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 6 balance updated to: 4 Thread 2: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 3 balance updated to: 3 Thread 4: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 8 balance updated to: 3 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 4 balance updated to: 5 Thread 3: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 8 balance updated to: 7 ... ``` ### Step 6. Scale out one TiDB server + In [Terminal 2](ssh://monitor1-ssh#2), check the yaml file for scaling out. ```bash cat scale-out-tidb-2.yaml ``` ```output tidb_servers: - host: ${LAB:HOST_DB2_PRIVATE_IP} port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log ``` + 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 ``` + Wait for a few minutes, and go back to [Terminal 1](ssh://monitor1-ssh#1). You should now be able to see that some threads are now connected to the new TiDB server. There is no human intervention during this process. ```output ... Thread 3: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 6 balance updated to: 9 Thread 4: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 1 balance updated to: 9 Thread 2: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 9 balance updated to: 5 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 ... ``` > [!TIP] > It takes a few minutes for the TiProxy to evenly route connections to two TiDB servers. Please wait patiently. + Do NOT stop the workload in [Terminal 1](ssh://monitor1-ssh#1). Let's keep it running. + Please now move on to Module 2. ### Conclusion + In this lab, we show you that TiProxy is able to discover the new TiDB SQL layer service, and then evenly distribute the workload across multiple TiDB servers.