--- title: "Multi-Region TiDB Self-Managed Workshop (WIP)" version: "v2" status: "WIP" description: "In this lab, you will gain hands-on experience deploying TiDB across multiple cloud regions, spanning hundreds or even thousands of kilometers apart, ensuring active-active high availability between two cloud regions (which can also involve different cloud vendors). You will explore how TiDB's ACID transactions and active-active replication function, while also learning best practices for data modeling. Additionally, you’ll dive into coding for TiDB's optimistic and pessimistic transaction lock modes." --- # Multi-Region TiDB Self-Managed Workshop (WIP) ### Overview Please note that the server resources used for this lab are intended for learning purposes and are all micro instances, which generally have slower performance than TiDB production systems. This lab cannot replace a Proof of Concept (PoC). In this lab, you will gain hands-on experience deploying TiDB across multiple cloud regions, spanning hundreds or even thousands of kilometers apart, ensuring active-active high availability between two cloud regions (which can also involve different cloud vendors). You will explore how TiDB's ACID transactions and active-active replication function, while also learning best practices for data modeling. Additionally, you’ll dive into coding for TiDB's optimistic and pessimistic transaction lock modes. The diagram below illustrates the architecture for our hands-on practice. We will set up a multi-region TiDB cluster across AWS's us-east-1 (N.Virginia) and us-east-2 (Ohio) regions, with the adjudicator deployed in the us-west-2 (Oregon). The multi-region TiDB cluster will enable two-way synchronous replication across regions, ensuring continuous database consistency with no replication lag. Note that in this lab, we use public IP addresses to identify our cluster nodes for simplicity. All cluster nodes communicate with each other through internet gateways. In a production scenario, you should first establish cross-region VPC peering and use private IP addresses to deploy the TiDB cluster for security considerations, those topics are out of scope for this workshop and they are common capabilities provided by cloud service providers such as AWS, Azure, and GCP. ### Tasks + Create a multi-region TiDB Self-Managed cluster. + Create schema and load data. + Explore the data model. + Control transaction concurrency by using the pessimistic and optimistic lock modes. + Programming with multi-region TiDB cluster and exploring the best practices for pessimistic and optimistic locks. + Deploy the Bookshop application. + Wrap-Up ### Prerequisites > [!NOTE] > If you plan to use Web SSH in your lab, you can skip this section. Once the lab environment is ready, the Web SSH button will appear. And, click **Next** on the bottom bar to proceed to **Module 1**. + **macOS** users: + Click the private key file [pe-class-key-oregon.pem](/api/labs/demo_001/vm_key?region_name=oregon) to download it. This file is required to log in to the remote Linux for this hands-on lab. + Save the private key file from the download folder to `~/.ssh/` folder. + Use the following commands to set the permission and check whether the file is in place. ```bash chmod 400 ~/.ssh/pe-class-key-oregon.pem ls -lF ~/.ssh/pe-class-key-oregon.pem ``` ```output ... -r--------@ 1 username staff 1674 Jun 21 2023 /Users/username/.ssh/pe-class-key-oregon.pem ``` + **Windows 10** users: + Click the private key file [pe-class-key-oregon.pem](/api/labs/demo_001/vm_key?region_name=oregon) to download it. This file is required to log in to the remote Linux for this hands-on lab. + Save the private key file from the download folder in `%userprofile%\.ssh` folder. + Use the following command to check if the file is in place. Note that you should use CMD, not PowerShell. ```cmd dir %userprofile%\.ssh\pe-class-key-oregon.pem ``` ```output ... 2024/11/14 10:22 1,674 pe-class-key-oregon.pem 1 Files(s) 1,674 bytes ... ``` + **Windows 8** or earlier users: + Click the private key file [pe-class-key-oregon.ppk](/api/labs/demo_001/vm_key?region_name=oregon&key_type=ppk) to download it. This file is required to log in to the remote Linux for this hands-on lab. + Download and install [PuTTY](https://www.putty.org/). ### Duration + This lab should take you approximately 90-120 minutes to complete. > [!TIP] > Once the lab environment is deployed, click **Next** on the bottom bar to begin **Module 1**. ## Practice 1 ### Subject: Deploy a Multi-Region TiDB Cluster TiDB is a distributed SQL database that provides region-level fault tolerance in a multi-region deployment, ensuring 99.999% availability. In this lab, our multi-region TiDB cluster consists of two regional endpoints (each with a TiDB server working as the SQL layer) that support strongly consistent reads and writes, and a third region, known as the adjudicator region, which does not have a TiDB server. The adjudicator region plays a crucial role in maintaining the write quorum and serves as a tie-breaker to determine which region can accept writes in the event of a network partition. Additionally, the adjudicator region hosts the TiKV storage server replicas. In this hands-on practice, our multi-region TiDB deployment includes two regional endpoints (N.Virginia and Ohio), each with a TiDB server (SQL layer) supporting strongly consistent reads and writes. The third region, Oregon, serves as the adjudicator region and does not deploy a TiDB server (SQL layer). ### Solution 1. Open a local terminal (Terminal 1), connect to a remote bastion Linux server us-west-2-monitor1_address in the adjudicator region Oregon to deploy a multi-region TiDB cluster. If you do not possess the credentials to connect, kindly review the Assumptions section under OVERVIEW tab. If prompted, answer `yes`. + macOS users: ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@us-west-2-monitor1_address ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + Windows 10 users: ```command ssh -i %userprofile%\.ssh\pe-class-key-oregon.pem ec2-user@us-west-2-monitor1_address ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + **Windows 8** or earlier users: + Run PuTTY, in `Session` -> `Host Name`, type in `ec2-user@monitor1_address`. + In `Connection` -> `SSH` -> `Auth` -> `Credentials` -> `Private key file for authentication`, browse to find the location of your key file. + Now, you should be able to connect to the remote Linux. + The banner after you logged in: ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` 2. Getting started and review the resources which had been provisioned for you. Take a look at the environment variables shown in this step, as we will be using them throughout this hands-on lab. + Review the IP addresses of the server resources provisioned for you in N.Virginia. ```command cat hosts-env.sh | grep HOST_R1 ``` ```output export HOST_R1APP1_PRIVATE_IP=10.91.1.36 export HOST_R1APP1_PUBLIC_IP=3.92.42.63 export HOST_R1APP1_REGION=us-east-1 export HOST_R1PD1_PRIVATE_IP=10.91.1.30 export HOST_R1PD1_PUBLIC_IP=35.172.232.188 export HOST_R1PD1_REGION=us-east-1 export HOST_R1DB1_PRIVATE_IP=10.91.1.86 export HOST_R1DB1_PUBLIC_IP=44.198.163.20 export HOST_R1DB1_REGION=us-east-1 export HOST_R1KV1_PRIVATE_IP=10.91.1.167 export HOST_R1KV1_PUBLIC_IP=3.239.237.37 export HOST_R1KV1_REGION=us-east-1 ``` + Review the IP addresses of the server resources provisioned for you in Ohio. ```command cat hosts-env.sh | grep HOST_R2 ``` ```output export HOST_R2APP1_PRIVATE_IP=10.92.1.106 export HOST_R2APP1_PUBLIC_IP=3.144.190.79 export HOST_R2APP1_REGION=us-east-2 export HOST_R2PD1_PRIVATE_IP=10.92.1.152 export HOST_R2PD1_PUBLIC_IP=3.142.94.4 export HOST_R2PD1_REGION=us-east-2 export HOST_R2DB1_PRIVATE_IP=10.92.1.37 export HOST_R2DB1_PUBLIC_IP=18.188.218.94 export HOST_R2DB1_REGION=us-east-2 export HOST_R2KV1_PRIVATE_IP=10.92.1.117 export HOST_R2KV1_PUBLIC_IP=18.222.180.78 export HOST_R2KV1_REGION=us-east-2 ``` + Review the IP addresses of the server resources provisioned for you in Oregon, the adjudicator region. ```command cat hosts-env.sh | grep HOST_R3 ``` ```output export HOST_R3MONITOR1_PRIVATE_IP=10.93.1.5 export HOST_R3MONITOR1_PUBLIC_IP=54.187.36.51 export HOST_R3MONITOR1_REGION=us-west-2 export HOST_R3PD1_PRIVATE_IP=10.93.1.246 export HOST_R3PD1_PUBLIC_IP=52.27.101.216 export HOST_R3PD1_REGION=us-west-2 export HOST_R3KV1_PRIVATE_IP=10.93.1.225 export HOST_R3KV1_PUBLIC_IP=35.92.189.136 export HOST_R3KV1_REGION=us-west-2 ``` 3. Use the `ping` command to roughly confirm the distance between these nodes, aiming to show whether they are in the same region or in different regions. + Now, you are on ${HOST_R3MONITOR1_PUBLIC_IP} with IP address us-west-2-monitor1_address in Oregon, so, when you ping following nodes you'll get a predictable latency variances, these nodes are in N.Virginia, Ohio, and Oregon respectively. + From the Oregon monitor node, ping the storage node in N.Virginia, the round-trip time should be around 70 ms, the physical distance is approximately 4,000 kilometers. ```command source ~/hosts-env.sh ping ${HOST_R1KV1_PUBLIC_IP} -c 3 ``` ```output PING 3.239.237.37 (3.239.237.37) 56(84) bytes of data. 64 bytes from 3.239.237.37: icmp_seq=1 ttl=247 time=63.8 ms 64 bytes from 3.239.237.37: icmp_seq=2 ttl=247 time=63.8 ms 64 bytes from 3.239.237.37: icmp_seq=3 ttl=247 time=63.7 ms --- 3.239.237.37 ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2003ms rtt min/avg/max/mdev = 63.789/63.821/63.864/0.031 ms ``` + From the Oregon monitor node, ping the storage node in Ohio, the round-trip time should be around 50 ms, the physical distance is approximately 3,200 kilometers. ```command ping ${HOST_R2KV1_PUBLIC_IP} -c 3 ``` ```output PING 18.222.180.78 (18.222.180.78) 56(84) bytes of data. 64 bytes from 18.222.180.78: icmp_seq=1 ttl=246 time=50.3 ms 64 bytes from 18.222.180.78: icmp_seq=2 ttl=246 time=50.2 ms 64 bytes from 18.222.180.78: icmp_seq=3 ttl=246 time=50.2 ms --- 18.222.180.78 ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2002ms rtt min/avg/max/mdev = 50.277/50.289/50.304/0.011 ms ``` + From the Oregon monitor node, ping the storage node in the same region, the round-trip time should be less than 1 ms, the physical distance can be ignored. ```command ping ${HOST_R3KV1_PUBLIC_IP} -c 3 ``` ```output PING 35.92.189.136 (35.92.189.136) 56(84) bytes of data. 64 bytes from 35.92.189.136: icmp_seq=1 ttl=254 time=0.262 ms 64 bytes from 35.92.189.136: icmp_seq=2 ttl=254 time=0.257 ms 64 bytes from 35.92.189.136: icmp_seq=3 ttl=254 time=0.263 ms --- 35.92.189.136 ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2028ms rtt min/avg/max/mdev = 0.257/0.260/0.263/0.018 ms ``` + Now, you should have an intuitive understanding of the distribution distances of the TiDB cluster we are going to create. 4. Set up a multi-region TiDB cluster spanning three cloud regions. + Review the provisioned cluster topology YAML on the monitor node. Compare the host IP addresses, you'll find that the topology is 1 TiDB server (SQL layer) is in N.Virginia, 1 TiDB server (SQL layer) is in Ohio, 3 TiKV servers (storage layer) are distributed evenly in N.Virginia, Ohio, and Oregon, 3 PD servers (cluster leaders) are also distributed evenly in N.Virginia, Ohio, and Oregon. ```command $ cat topology.yaml ``` ```output global: user: "ec2-user" ssh_port: 22 deploy_dir: "/tidb-deploy" data_dir: "/tidb-data" arch: "amd64" monitored: node_exporter_port: 9100 blackbox_exporter_port: 9115 deploy_dir: "/tidb-deploy/monitored-9100" data_dir: "/tidb-data/monitor_9115" log_dir: "/tidb-deploy/monitor-9100/log" server_configs: pd: replication.location-labels: ["region"] tidb_servers: - host: 44.198.163.20 port: 4000 status_port: 10080 deploy_dir: "/tidb-deploy/tidb-4000" log_dir: "/tidb-deploy/tidb-4000/log" - host: 18.188.218.94 port: 4000 status_port: 10080 deploy_dir: "/tidb-deploy/tidb-4000" log_dir: "/tidb-deploy/tidb-4000/log" tikv_servers: - host: 3.239.237.37 port: 20160 status_port: 20180 deploy_dir: "/tidb-deploy/tikv-20160" data_dir: "/tidb-data/tikv_data" config: server.labels: { region: "virginia" } - host: 18.222.180.78 port: 20160 status_port: 20180 deploy_dir: "/tidb-deploy/tikv-20160" data_dir: "/tidb-data/tikv_data" config: server.labels: { region: "ohio" } - host: 35.92.189.136 port: 20160 status_port: 20180 deploy_dir: "/tidb-deploy/tikv-20160" data_dir: "/tidb-data/tikv_data" config: server.labels: { region: "oregon" } pd_servers: - host: 35.172.232.188 client_port: 2379 peer_port: 2380 deploy_dir: "/tidb-deploy/pd-2379" data_dir: "/tidb-data/pd_data" advertise_client_addr: "http://35.172.232.188:2379" - host: 3.142.94.4 client_port: 2379 peer_port: 2380 deploy_dir: "/tidb-deploy/pd-2379" data_dir: "/tidb-data/pd_data" advertise_client_addr: "http://3.142.94.4:2379" - host: 52.27.101.216 client_port: 2379 peer_port: 2380 deploy_dir: "/tidb-deploy/pd-2379" data_dir: "/tidb-data/pd_data" advertise_client_addr: "http://52.27.101.216:2379" ``` + Use `tiup` to create and deploy a multi-region TiDB cluster named `tidb-demo`. The TiUP utility has already been provisioned for you in this lab. The following `tiup` commands set the TiDB components installation source to local mirror, fix the system settings across all nodes based on the topology YAML, deploy the TiDB cluster across N.Virginia, Ohio, and Oregon, and start the multi-region TiDB cluster. For instructions on downloading TiUP and managing TiDB local mirrors, please refer to the [TiUP documentation](https://docs.pingcap.com/tidb/stable/production-deployment-using-tiup). Please note that we will intentionally initialize the TiDB cluster with a root user that has no password for this hands-on practice. ```command tiup mirror set tidb-community-server-tidb_version-linux-amd64 tiup cluster check topology.yaml --user ec2-user -i /home/ec2-user/.ssh/pe-class-key-oregon.pem --apply tiup cluster deploy tidb-demo tidb_version ./topology.yaml --user ec2-user -i /home/ec2-user/.ssh/pe-class-key-oregon.pem --yes ``` ```output . . . Cluster `tidb-demo` deployed successfully, you can start it with command: `tiup cluster start tidb-demo --init` ``` 5. To start a multi-region TiDB cluster, you can start its components layer by layer, with each layer being horizontally scalable on-demand. The TiDB cluster consists of three main layers. These layers can be started independently and scaled as needed to meet performance and capacity requirements. You can start the decoupled layers with a single command: `tiup cluster start tidb-demo --role tidb --wait-timeout 500`, or you can follow the step-by-step instructions provided later, which will yield the same result. In this lab, we will use the latter approach, as it allows us to provide more detailed explanations. + PD (Placement Driver): The metadata layer responsible for managing the cluster's topology and distributing data. In our lab's topology, the metadata layer is in N.Virginia, Ohio and Oregon. ```command tiup cluster start tidb-demo --role pd ``` ```output ... Starting component pd ... Started cluster `tidb-demo` successfully ``` + TiKV: The storage layer that handles distributed data storage and retrieval. As per our lab's topology, the storage layer is also in N.Virginia, Ohio and Oregon. ```command tiup cluster start tidb-demo --role tikv ``` ```output ... Starting component tikv ... Started cluster `tidb-demo` successfully ``` + TiDB: The SQL layer that processes queries and manages application logic. According to our lab's topology, the SQL layer is in N.Virginia and Ohio only, which means the TiDB cluster's client will perceive the TiDB cluster as operating in both of these regions but not in Oregon. Please note that for cost reasons, we use small instances with basic networking during the lab. The TiDB server may not meet network latency requirements if it's placed on a droplet with noisy neighbors, which could cause this step to fail. If this happens, please retry the following step. While in the production system and TiDB Cloud, this situation can be effectively avoided. [Contact our experts](https://tidb.io/contact-us/) to learn more. In this lab, we are starting a TiDB Self-Manged multi-region cluster which is not a TiDB Cloud Serverless deployment, so the following step may take up to 6 minutes, please be patient. ```command tiup cluster start tidb-demo --role tidb --wait-timeout 420 ``` ```output ... Starting component tidb ... Started cluster `tidb-demo` successfully ``` 6. Get the cluster status. The status of components `pd`, `tidb`, and `tikv` should be `Up`. Note that we didn't start the Grafana and Prometheus components yet. ```command tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v8.1.1 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://52.27.101.216:2379/dashboard ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 3.142.94.4:2379 pd 3.142.94.4 2379/2380 linux/x86_64 Up /tidb-data/pd_data /tidb-deploy/pd-2379 35.172.232.188:2379 pd 35.172.232.188 2379/2380 linux/x86_64 Up /tidb-data/pd_data /tidb-deploy/pd-2379 52.27.101.216:2379 pd 52.27.101.216 2379/2380 linux/x86_64 Up|L|UI /tidb-data/pd_data /tidb-deploy/pd-2379 18.188.218.94:4000 tidb 18.188.218.94 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 44.198.163.20:4000 tidb 44.198.163.20 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 18.222.180.78:20160 tikv 18.222.180.78 20160/20180 linux/x86_64 Up /tidb-data/tikv_data /tidb-deploy/tikv-20160 3.239.237.37:20160 tikv 3.239.237.37 20160/20180 linux/x86_64 Up /tidb-data/tikv_data /tidb-deploy/tikv-20160 35.92.189.136:20160 tikv 35.92.189.136 20160/20180 linux/x86_64 Up /tidb-data/tikv_data /tidb-deploy/tikv-20160 Total nodes: 8 ``` 7. Congratulations! You've just launched a distributed SQL database that spans hundreds and even thousands of kilometers. Keep Terminal 1 open, and click NEXT below to proceed to the next practice and create the schema. ## Practice 2 ### Subject: Create Schema In the second module, we will create a schema with geo-location data placement rules enabled. We'll load data through the Virginia SQL layer, verify the changes, and explore the data model from Ohio. Then, we'll repeat the process in reverse to demonstrate that the multi-region TiDB cluster is in active-active write transactions supported. ### Solution 1. Keep Terminal 1 open and open a new terminal (Terminal 2). In Terminal 2, connect to the application server in the N.Virginia region, us-east-1-app1_address. If prompted, answer `yes`. + macOS users: ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@us-east-1-app1_address ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + Windows 10 users: ```command ssh -i %userprofile%\.ssh\pe-class-key-oregon.pem ec2-user@us-east-1-app1_address ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` 2. Create a `bookshop` schema and load data via N.Virginia SQL layer. + Connect to the SQL layer in N.Virginia as database root user. ```command export MYSQL_PS1="tidb@N.Virginia> " mysql -h ${HOST_R1DB1_PUBLIC_IP} --port 4000 -u root ``` ```output Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2579496964 Server version: 8.0.11-TiDB-v8.1.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. tidb@N.Virginia> ``` + Run the following command in `tidb@N.Virginia> ` to create the database objects and load data for our book shop application. The script creates a schema called bookshop and all of the tables that our application will use. ```command SOURCE bookshop_demo.sql SHOW TABLES; ``` ```output Query OK, 0 rows affected (0.08 sec) . . . +--------------------+ | Tables_in_bookshop | +--------------------+ | _prisma_migrations | | authors | | book_authors | | books | | orders | | ratings | | users | +--------------------+ 7 rows in set (0.00 sec) ``` + The following is an excerpt of the contents of the bookshop_demo.sql script in the previous step, showing the creation of the tables and indexes for our book shop application. You don't need to run these statements now, since they were already executed, but you can see how we've modeled our data for our application. It's worth noting that you'll find that except for AUTO_RANDOM, all the declarations are just like those used in MySQL. We will talk about AUTO_RANDOM later. ```output CREATE TABLE `authors` ( `id` bigint(20) AUTO_RANDOM, `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `gender` tinyint(1) DEFAULT NULL, `birth_year` smallint(6) DEFAULT NULL, `death_year` smallint(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; CREATE TABLE `book_authors` ( `book_id` bigint(20) NOT NULL, `author_id` bigint(20) NOT NULL, PRIMARY KEY (`book_id`, `author_id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; CREATE TABLE `books` ( `id` bigint(20) AUTO_RANDOM, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `type` enum( 'Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports' ) COLLATE utf8mb4_unicode_ci NOT NULL, `published_at` datetime NOT NULL, `stock` int(11) DEFAULT '0', `price` decimal(15, 2) DEFAULT '0.0', PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; CREATE TABLE `orders` ( `id` bigint(20) NOT NULL, `book_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `quality` tinyint(4) NOT NULL, `ordered_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `orders_book_id_idx` (`book_id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; CREATE TABLE `ratings` ( `book_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `score` tinyint(4) NOT NULL, `rated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `uniq_book_user_idx` (`book_id`, `user_id`), PRIMARY KEY (`book_id`, `user_id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; CREATE TABLE `users` ( `id` bigint(20) AUTO_RANDOM, `balance` decimal(15, 2) DEFAULT '0.0', `nickname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `nickname` (`nickname`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; ``` + You can explore the schema and describe the objects in the database with mysql commands, just as you would with any MySQL database. ```command DESC bookshop.books; ``` ```output +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | title | varchar(100) | NO | | NULL | | | type | enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') | NO | | NULL | | | published_at | datetime | NO | | NULL | | | stock | int(11) | YES | | 0 | | | price | decimal(15,2) | YES | | 0.0 | | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ 6 rows in set (0.00 sec) ``` + In the `tidb@N.Virginia> ` prompt, run the following query to see a few books records. ```command SELECT * FROM bookshop.books ORDER BY title LIMIT 3; ``` ```output +----+-------------------------------+------------------------------+---------------------+--------+-------+ | id | title | type | published_at | stock | price | +----+-------------------------------+------------------------------+---------------------+--------+-------+ | 43 | (I've Got a Gal In) Kalamazoo | Sports | 2006-05-22 05:19:49 | 231321 | 85.72 | | 88 | (Let Me Be Your) Teddy Bear | Sports | 2003-04-08 08:37:52 | 140 | 22.14 | | 40 | (You Keep Me) Hangin' On | Humanities & Social Sciences | 2005-02-12 05:29:04 | 163 | 32.72 | +----+-------------------------------+------------------------------+---------------------+--------+-------+ 3 rows in set (0.05 sec) ``` 3. Verify the change from the SQL layer in the Ohio. Note that this time we are connecting to the SQL layer in Ohio. + Keep your Terminal 1 and Terminal 2 open, and open a new local terminal (Terminal 3), in Terminal 3, connect to the application server in Ohio, us-east-2-app1_address. If prompted, answer `yes`. ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@us-east-2-app1_address ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + On the newly opened Terminal 3, connect to the SQL layer in Ohio as database root user. ```command export MYSQL_PS1="tidb@Ohio> " mysql -h ${HOST_R2DB1_PUBLIC_IP} --port 4000 -u root ``` ```output tidb@Ohio> " ``` + Run the following query to see that our data is also available in Ohio. The result you get should be identical to the one in N.Virginia. ```command SELECT * FROM bookshop.books ORDER BY title LIMIT 3; ``` ```output +----+-------------------------------+------------------------------+---------------------+--------+-------+ | id | title | type | published_at | stock | price | +----+-------------------------------+------------------------------+---------------------+--------+-------+ | 43 | (I've Got a Gal In) Kalamazoo | Sports | 2006-05-22 05:19:49 | 231321 | 85.72 | | 88 | (Let Me Be Your) Teddy Bear | Sports | 2003-04-08 08:37:52 | 140 | 22.14 | | 40 | (You Keep Me) Hangin' On | Humanities & Social Sciences | 2005-02-12 05:29:04 | 163 | 32.72 | +----+-------------------------------+------------------------------+---------------------+--------+-------+ 3 rows in set (0.01 sec) ``` 4. Let’s summarize the terminals at hand so far. Now, you have 3 terminals: Terminal 1 is connected to a monitor machine in Oregon, Terminal 2 is connected to the SQL layer in the `tidb@N.Virginia> ` prompt, and Terminal 3 is connected to the SQL layer in Ohio with the `tidb@Ohio> ` prompt. Click NEXT to move on to the next module where we'll explore the data model in more depth. ## Practice 3 ### Subject: Explore the Data Model In this module, we will explore the recommended data model and transactional patterns for TiDB. 1. Primary keys + Notice that all the tables we defined in Practice 2 have Primary Keys. This is of critical importance in TiDB. If you do not specify a Primary Key, TiDB will assign a hidden `_tidb_rowid` value to each row. You cannot change or add a Primary Key to a table post-creation time. When defining a primary key, consider using an integer type. If you use AUTO_INCREMENT, be aware that this can lead to write hotspots due to the monotonically increasing nature of the IDs. To mitigate this, you can use AUTO_RANDOM instead of AUTO_INCREMENT, which helps eliminate row ID continuity and reduces the likelihood of hotspots. Visit the [TiDB Workshop for MySQL Users](https://eng.edu.pingcap.com/plus/external-catalog/courses/231) Exercises 5-2 in TiDB for more information on using AUTO_RANDOM in TiDB. + The conclusion is that TiDB does NOT require your application to change the primary key to a type like UUID that supports equality predicate only. Using string types, INT, and BIGINT as primary keys is fully supported. With AUTO_RANDOM, TiDB ensures the dispersion of primary key values, leveraging the strengths of a distributed database and easing the burden of your schema design. 2. Foreign keys + TiDB supports foreign key constraints. This allows developers and architects the flexibility to either enforce their own order of operations or rely on the database to enforce table dependencies. With TiDB, they can treat it like a traditional RDBMS when designing the reference relationships between entities, delegating the responsibility of checking foreign key references to the database. For maintaining the relation consistency, architects and developers have the freedom to decide wether to use an event-driven architecture (do not use the foreign keys) or rely on TiDB to make their daily work easier (ues the foreign keys). The performance trade-offs caused by using foreign keys are out of scope of this workshop. + As in our demo bookshop schema, we didn't add the foreign keys during the schema creation. Execute the following command the enforce the referencing constraint between `book_authors` and `books` on `book_authors.book_id` and `books.id`. Let's execute it on Terminal 3 with the prompt `tidb@Ohio> `. ```command ALTER TABLE bookshop.book_authors ADD CONSTRAINT book_id_fk FOREIGN KEY (book_id) REFERENCES bookshop.books (id); ``` ```output Query OK, 0 rows affected (4.85 sec) ``` + Try to add record into `book_authors` which violates the foreign key constraint you just created. An error is expected because book with `book_id` 99999999 is not exist. You can execute it via Terminal 2 `tidb@N.Virginia> `. ```command INSERT INTO bookshop.book_authors (book_id, author_id) VALUES (99999999, 1); ``` ```output ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookshop`.`book_authors`, CONSTRAINT `book_id_fk` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`)) ``` + At this point, you have experienced the foreign key constraint capability on a multi-region distributed database. 3. Use of indexes + In TiDB, indexes are created asynchronously, which means index creation option does not block database reads and writes in other sessions. + For example, execute the following SQL command to add an index on `rated_at` column of `ratings` table. The execution will not block online users provide rating scores to our books. Execute it in Terminal 2 `tidb@N.Virginia> ` and do not wait for it to complete. ```command CREATE INDEX rated_at_idx ON bookshop.ratings (rated_at); ``` + Switch to Terminal 3 `tidb@Ohio> ` and execute the following command to check the DDL job queue. The sample output shows the asynchronous DDL job is NOT completed yet, the `QUERY` field shows the DDL which is under execution. Keep in mind that database read and writes from other sessions are not blocked. ```command ADMIN SHOW DDL\G ``` ```output *************************** 1. row *************************** SCHEMA_VER: 87 OWNER_ID: 8c6c9e6e-3b9f-4257-bc85-fefd54b137ef OWNER_ADDRESS: 18.188.218.94:4000 RUNNING_JOBS: ID:141, Type:add index, State:running, SchemaState:delete only, SchemaID:122, TableID:134, RowCount:0, ArgLen:0, start time: 2024-12-14 12:19:10.184 +0000 UTC, Err:, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0 SELF_ID: 8c6c9e6e-3b9f-4257-bc85-fefd54b137ef QUERY: CREATE INDEX rated_at_idx ON bookshop.ratings (rated_at) 1 row in set (0.32 sec) ``` + If the execution output resembles the following, it indicates that the `CREATE INDEX` DDL job has been completed, and the `QUERY` field is empty. ```output *************************** 1. row *************************** SCHEMA_VER: 102 OWNER_ID: 8c6c9e6e-3b9f-4257-bc85-fefd54b137ef OWNER_ADDRESS: 18.188.218.94:4000 RUNNING_JOBS: SELF_ID: 8c6c9e6e-3b9f-4257-bc85-fefd54b137ef QUERY: 1 row in set (0.30 sec) ``` + TiDB supports following types of indexes: + Primary Index: This is the main index that uniquely identifies each row in a table. In TiDB, the primary index is clustered, meaning that the data is stored in the order of the primary key. + Secondary Index: These indexes are created on non-primary key columns to speed up queries that filter by those columns. For example, a secondary index on the title column of a books table can improve the performance of queries that search for books by title. + Clustered Index: A clustered index sorts and stores table data according to specified key columns (primary keys), improving query performance by organizing data for faster retrieval, aka. Index Organized Table. + Composite Index: This type of index is created on multiple columns. It is beneficial when queries involve conditions on multiple columns, allowing for more efficient data retrieval. + Expression Index: Expression indexes allow you to create indexes based on expressions rather than just column values. This can be useful for optimizing queries that involve functions. 4. JOIN, GROUP BY, ORDER BY, Aggregate, and Window functions + TiDB supports the familiar relational operations: sorting, grouping, and aggregation. For example, Let's analyze the top-rated book genres, identify the top 3 books within each genre, and examine their stock levels, in a single query. The following "unfriendly" query is not necessarily the best approach, but it demonstrates that TiDB supports the operations mentioned above. It includes various SQL features, such as JOINs, GROUP BY, ORDER BY, aggregate functions, window functions, and nested subqueries—all within a single query. ```command SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated FROM bookshop.books b JOIN bookshop.ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated ORDER BY avg_score DESC; ``` ```output +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | type | book_count | avg_score | top_3_rated | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | Comics | 64 | 3.5156 | Mrs Robinson,On the Atchison,Be My Baby | | Arts | 86 | 3.4535 | Give Me Everything,Georgia On My Mind,Besame Mucho | | Magazine | 59 | 3.3051 | Wishing Well,Stayin' Alive,Spanish Harlem | | Kids | 48 | 3.1875 | What Goes Around Comes Around,Gonna Make You Sweat (Everybody Dance Now),Hey There | | Life | 175 | 3.1657 | Play That Funky Music,Open Arms,Every Breath You Take | | Sports | 81 | 3.1605 | Waterfalls,The Hustle,My Sweet Lord | | Education & Reference | 65 | 3.1231 | Greatest Love of All,Addicted to Love,ABC | | Science & Technology | 149 | 3.0671 | Build Me Up Buttercup,Dilemma,Daydream Believer | | Humanities & Social Sciences | 78 | 2.9615 | Higher Love,12th Street Rag,Body & Soul | | Novel | 44 | 2.9318 | The Power of Love,Thrift Shop,Summer in the City | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ 10 rows in set (0.24 sec) ``` 5. Explain Plans + Compared with MySQL, TiDB's `EXPLAIN` output format and the details provided differ significantly, with TiDB providing insights into its distributed execution and push-down optimizations. + Push-Down Optimization: TiDB effectively pushes down predicates and aggregations to the storage layer (TiKV), which reduces the data transferred and processed at the TiDB layer. Less data to be transferred, less work for the query processors, better performance! + Distributed Execution: TiDB's distributed architecture allows it to handle larger datasets and complex queries more efficiently than MySQL's single-node execution model. + Example 1: The `root` and `cop[tikv]` in the `task` column indicate that the tasks are executed at the SQL layer (TiDB server instance) and the storage layer (TiKV server instances), respectively. ```command EXPLAIN SELECT title,type,published_at FROM books LIMIT 3; ``` ```output +---------------------------+---------+-----------+---------------+--------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+---------+-----------+---------------+--------------------------------+ | Limit_7 | 3.00 | root | | offset:0, count:3 | | └─TableReader_11 | 3.00 | root | | data:Limit_10 | | └─Limit_10 | 3.00 | cop[tikv] | | offset:0, count:3 | | └─TableFullScan_9 | 3.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------+---------+-----------+---------------+--------------------------------+ 4 rows in set (0.06 sec) ``` + Example 2: The `gt(bookshop.books.stock, 500000)` operator executed by `cop[tikv]` indicates that the "greater than" predicate has been pushed down to the storage layer. ```command EXPLAIN SELECT title,type,published_at FROM books WHERE stock > 500000; ``` ```output +---------------------------+---------+-----------+---------------+------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+---------+-----------+---------------+------------------------------------------------------------------------+ | Projection_4 | 33.33 | root | | bookshop.books.title, bookshop.books.type, bookshop.books.published_at | | └─TableReader_7 | 33.33 | root | | data:Selection_6 | | └─Selection_6 | 33.33 | cop[tikv] | | gt(bookshop.books.stock, 500000) | | └─TableFullScan_5 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------+---------+-----------+---------------+------------------------------------------------------------------------+ 4 rows in set (0.06 sec) ``` + Example 3: The `access object` column displays the tables, indexes, and partitions accessed to fulfill the query. ```command EXPLAIN SELECT * FROM ratings ORDER BY rated_at DESC LIMIT 1; ``` ```output +--------------------------------+---------+-----------+---------------------------------------------+-------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+---------------------------------------------+-------------------------------------+ | IndexLookUp_18 | 1.00 | root | | limit embedded(offset:0, count:1) | | ├─Limit_17(Build) | 1.00 | cop[tikv] | | offset:0, count:1 | | │ └─IndexFullScan_15 | 1.00 | cop[tikv] | table:ratings, index:rated_at_idx(rated_at) | keep order:true, desc, stats:pseudo | | └─TableRowIDScan_16(Probe) | 1.00 | cop[tikv] | table:ratings | keep order:false, stats:pseudo | +--------------------------------+---------+-----------+---------------------------------------------+-------------------------------------+ 4 rows in set (0.08 sec) ``` + Finally, note that the `cop[tikv]` task is parallel in nature. When you see `cop[tikv]`, it typically means the task is executed across multiple TiKV nodes in the storage layer. In the [TiDB SQL Tuning](https://tidb.io/education/) training course, we provide a detailed explanations of the various operations in the execution plan. ## Practice 4 ### Subject: Concurrency Control In any database that supports concurrent access, there's a risk of conflicts, where multiple transactions may attempt to update the same data simultaneously. While a well-designed data model can minimize the chances of such conflicts, they remain a possibility and must be effectively managed. Databases offer mechanisms to handle concurrent data access, with the two primary approaches being pessimistic and optimistic concurrency control. TiDB supports both approaches. ### Solution ## Practice 5 ### Subject: Deploy the Book Shop App ### Solution 1. Deploy the Book Shop App to us-east-1 application server which in turn connects to the us-east-1 SQL layer. + In Terminal 2, exit from the tidb@N.Virginia> prompt to the operating system prompt. ```command EXIT; ``` ```output Bye [ec2-user@ip-10-91-1-171 ~]$ ``` + Run following commands to deploy the Book Shop App. Note that we use `${HOST_R1DB1_PUBLIC_IP}` in `DATABASE_URL` environment variable to tell the application to connect to the us-east-1 SQL layer. ```command curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.7/install.sh | bash; source ~/.bashrc; nvm install --lts; node -e "console.log('Running Node.js ' + process.version)" git clone https://github.com/pingcap/tidb-prisma-vercel-demo.git cd tidb-prisma-vercel-demo corepack enable corepack yarn install yarn export DATABASE_URL=mysql://lib:q1w2e3R4_@${HOST_R1DB1_PUBLIC_IP}:4000/bookshop yarn run prisma:deploy && yarn run setup && yarn run build yarn start ``` 1. Deploy the Book Shop App to us-east-2 application server which in turn connects to the us-east-2 SQL layer. ## Practice 6 ### Subject: Concurrency Control with Pessimistic and Optimistic Lock Modes ### Tips + In this practice, you will use `sysbench` to apply a test the workload, which is a read workload with 18,000 QPS. ### Solution ## Conclusion and End of Lab TiDB Cluster Sizing Tool can help you estimate the number of cluster nodes when creating TiDB cluster for Self-Managed and TiDB Cloud Dedicated. Based on the testing conducted, we've found that the tool's estimation closely approximates the actual requirements. However, the `sysbench` results indicate better performance than the tool's estimation. This discrepancy arises because the lab uses TiDB version tidb_version, while the estimation tool is based on version v6.x. The TiDB Cluster Sizing tool is designed around the schema and workload used in sysbench, meaning its results serve as a preliminary reference for cluster sizing. For optimal system deployment, it's crucial to refine the cluster size based on performance testing with real-world business workloads. Please do NOT give TiDB Cluster Sizing tool to external users or customers. Its use is exclusively for PingCAP professionals. For deeper insights on migrating to TiDB using TiDB Data Migration, we encourage you to explore [PingCAP Education](https://pingcap.com/education/) and enroll in the TiDB Administration instructor-led training course. This comprehensive program will provide you with the knowledge and skills needed to successfully manage and migrate to TiDB.