---
title: "Practical Database Features For SaaS Applications"
version: "v2"
status: "GA"
description: "SaaS (Software-as-a-Service) applications often require a highly scalable and multi-tenant friendly features to handle numerous customers with diverse workloads. TiDB, as a distributed SQL database, provides several practical features that enhance the flexibility, performance, and operational efficiency of SaaS applications.\nIn this hands-on lab, you will explore four modules designed around TiDB features highly valued by our SaaS provider users. These tasks include rapidly creating thousands of schemas and tables, performing seamless online schema changes, and applying global execution plan bindings to standardize query execution across multiple schemas."
---
# Practical Database Features For SaaS Applications
## Overview
SaaS (Software-as-a-Service) applications often require highly scalable and multi-tenant friendly features to handle numerous customers with diverse workloads. TiDB, as a distributed SQL database, provides several practical features that enhance the flexibility, performance, and operational efficiency of SaaS applications.
In this hands-on lab, you will explore four modules designed around TiDB features highly valuable in the SaaS industries. These tasks include rapidly creating thousands of schemas and tables, performing seamless online schema changes, and applying global execution plan bindings to standardize query execution across multiple schemas.
The following diagram shows the deployment topology of the TiDB cluster for this lab.
### Duration
+ This lab should take you approximately 100 minutes to complete.
> [!TIP]
> Click **Start** to launch the lab environment.
> Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**.
> And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks.
## Module 1: Rapidly Create A Large Number Of Schemas And Tables
SaaS applications often provision separate schemas or tables for different tenants. TiDB's scalability and efficient metadata management enable the rapid creation of thousands of schemas and tables while maintaining performance and stability.
In this module, we will attempt to create 1,000 schemas, each containing 10 tables, resulting in a total of 10,000 tables.
In your live environment you can scale this up to a million tables without issue. Due to time constraints for today we will limit the generation to 10k tables.
### Step 1. Connect to the TiUP Control Machine node
+ Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab.
+ 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/
```
### Step 2. Review the status of the TiDB cluster
+ Get the cluster status.
```bash
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.188.180.4:2379/dashboard
Grafana URL: http://10.90.4.196:3000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
-- ---- ---- ----- ------- ------ -------- ----------
10.90.4.196:9093 alertmanager 10.90.4.196 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093
10.90.4.196:3000 grafana 10.90.4.196 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000
10.90.2.26:2379 pd 10.90.2.26 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379
10.90.4.196:9090 prometheus 10.90.4.196 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090
10.90.1.149:4000 tidb 10.90.1.149 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000
10.90.3.216:20160 tikv 10.90.3.216 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
10.90.3.225:20160 tikv 10.90.3.225 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
10.90.3.95:20160 tikv 10.90.3.95 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
Total nodes: 8
```
### Step 3. Enable two necessary variables for massive table usage.
+ Connect to TiDB server instance as database user `root`.
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root
```
```output
mysql>
```
+ Check the table creation acceleration variable, `tidb_enable_fast_create_table`. The default value is `ON` from `v8.5` onward. For the clusters upgraded from v8.4.0 or earlier versions, the default value remains `OFF`.
```sql
show variables like 'tidb_enable_fast_create_table';
```
```output
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| tidb_enable_fast_create_table | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
```
+ Check the schema cache size variable, `tidb_schema_cache_size`. When the number of tables in a single cluster exceeds 100k, maintain the cache hit ratio greater than 85% by monitoring the Infoschema v2 Cache Operation metrics and adjusting the value of cache size (512MB by default). You can access granfana to find the metrics by the path `TiDB` -> `Schema Load` -> `Infoschema v2 Cache Operation` & `Infoschema v2 Cache Size`.
```sql
show variables like 'tidb_schema_cache_size';
```
```output
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| tidb_schema_cache_size | 536870912 |
+------------------------+-----------+
1 row in set (0.00 sec)
```
```sql
EXIT;
```
```output
mysql> EXIT;
```
### Step 4. Prepare sysbench configuration
+ Get the sysbench tool. In this module we will use sysbench to create databases and tables in parallel.
```bash
git clone https://github.com/lilinghai/sysbench/
```
```output
Cloning into 'sysbench'...
remote: Enumerating objects: 10789, done.
remote: Counting objects: 100% (271/271), done.
remote: Compressing objects: 100% (172/172), done.
remote: Total 10789 (delta 119), reused 107 (delta 94), pack-reused 10518 (from 4)
Receiving objects: 100% (10789/10789), 5.37 MiB | 29.74 MiB/s, done.
Resolving deltas: 100% (7534/7534), done.
```
+ Set up the `sysbench` configuration. We will use 64 threads in this hands-on lab.
```bash
cat << EOF > sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=root
mysql-password=
mysql-db=test
time=60
threads=64
report-interval=1
EOF
cat sysbench_config
```
```output
mysql-host=10.90.1.125
mysql-port=4000
mysql-user=root
mysql-password=
mysql-db=test
time=60
threads=64
report-interval=1
```
### Step 5. Create 10,000 tables using sysbench
+ Create 1000 databases and 10 tables for each database, each table has 100 seed rows. This is used to simulate the moment when a SaaS application is initialized. It will create many databases and tables, with each schema essentially representing a tenant. It will take you approximately 2 minutes, and please keep in mind that we are using micro instances for our lab TiDB cluster, it is not a production grade environment.
+ In a real-world production environment you can create millions of tables at the same speed.
```bash
cd ~/sysbench/workloads/many_db_tables/
sysbench oltp_read_write preparedb \
--config-file=/home/ec2-user/sysbench_config --db_prefix=sbtest --dbs=1000 --tables=10 --table_size=100
sysbench oltp_read_write preparetable \
--config-file=/home/ec2-user/sysbench_config --db_prefix=sbtest --dbs=1000 --tables=10 --table_size=100
sysbench oltp_read_write preparedata \
--config-file=/home/ec2-user/sysbench_config --db_prefix=sbtest --dbs=1000 --tables=10 --table_size=100
cd ~
```
```output
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
...
Creating database 'sbtest15'...
Creating database 'sbtest3'...
Creating database 'sbtest10'...
Creating database 'sbtest5'...
...
Creating table sbtest2.sbtest2 ...
Creating table sbtest6.sbtest2 ...
Creating table sbtest7.sbtest2 ...
Creating table sbtest5.sbtest2 ...
Creating table sbtest1.sbtest2 ...
Creating table sbtest2.sbtest1 ...
Creating table sbtest8.sbtest1 ...
Creating table sbtest1.sbtest1 ...
Creating table sbtest6.sbtest1 ...
Creating table sbtest7.sbtest1 ...
Creating table sbtest4.sbtest2 ...
...
Inserting 100 records into 'sbtest1.sbtest2'
Inserting 100 records into 'sbtest1.sbtest1'
Inserting 100 records into 'sbtest1.sbtest7'
Inserting 100 records into 'sbtest1.sbtest3'
Inserting 100 records into 'sbtest1.sbtest6'
...
```
### Step 6. Verify the created schemas
+ Connect to TiDB server instance as database user `root`.
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root
```
```output
mysql>
```
+ Verify the schemas.
```sql
SHOW DATABASES;
```
```output
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| mysql |
| sbtest1 |
| sbtest10 |
...
| sbtest987 |
| sbtest988 |
| sbtest989 |
| sbtest99 |
| sbtest990 |
| sbtest991 |
| sbtest992 |
| sbtest993 |
| sbtest994 |
| sbtest995 |
| sbtest996 |
| sbtest997 |
| sbtest998 |
| sbtest999 |
| sys |
| test |
+--------------------+
1006 rows in set (0.00 sec)
```
+ Verify the tables and check the row count of a random table in the `sbtest1000` schema.
```sql
USE sbtest1000;
SHOW TABLES;
SELECT COUNT(1) FROM sbtest10;
```
```output
Database changed
+----------------------+
| Tables_in_sbtest1000 |
+----------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+----------------------+
10 rows in set (0.00 sec)
...
+----------+
| count(1) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
```
+ As the new tenants are subscribing the SaaS application, the metadata creation and seed data population is extreme fast in TiDB.
+ Keep mysql client connected to TiDB, and proceed to the next module.
## Module 2: Cross-Schema Execution Plans Binding
SaaS workloads often exhibit query pattern variations across tenants, leading to inconsistent execution plans. TiDB's SQL Plan Management (SPM) enables global execution plan binding to ensure stable and optimal query performance across different schemas.
### Step 1. Enable the Cross-Schema execution plan binding
+ Enable the Cross-Schema execution plan binding by setting system variable [`tidb_opt_enable_fuzzy_binding`](https://docs.pingcap.com/tidb/stable/system-variables/#tidb_opt_enable_fuzzy_binding-new-in-v760) to `ON`, both for global and for current session.
```sql
SHOW VARIABLES LIKE 'tidb_opt_enable_fuzzy_binding';
```
```output
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| tidb_opt_enable_fuzzy_binding | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
```
```sql
SET GLOBAL tidb_opt_enable_fuzzy_binding=1;
SET tidb_opt_enable_fuzzy_binding=1;
SHOW VARIABLES like 'tidb_opt_enable_fuzzy_binding';
```
```output
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| tidb_opt_enable_fuzzy_binding | ON |
+-------------------------------+-------+
```
### Step 2. Create the Cross-Schema execution plan binding
+ In module 1, we created 1000 schemas and they all have tables named `sbtest1` and `sbtest2` with the same definition. We will pick up schema `sbtest1` and `sbtest999` as the target schemas for execution plan binding.
```sql
SHOW CREATE TABLE sbtest1.sbtest1\G
SHOW CREATE TABLE sbtest999.sbtest1\G
```
```output
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.01 sec)
```
+ Sample the execution plans of a certain type of SQL across multiple schemas, `sbtest1` and `sbtest999` in our case.
```sql
EXPLAIN SELECT COUNT(*) AS num
FROM sbtest1.sbtest1 AS t1,sbtest1.sbtest2 AS t2
WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100;
EXPLAIN SELECT COUNT(*) AS num
FROM sbtest999.sbtest1 AS t1,sbtest999.sbtest2 AS t2
WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100;
```
```output
+-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+
| StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 |
| └─MergeJoin_44 | 3.12 | root | | inner join, left key:sbtest1.sbtest1.id, right key:sbtest1.sbtest2.id |
| ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 |
| │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:true, stats:partial[id:unInitialized] |
| └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 |
| └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:true, stats:partial[id:unInitialized] |
+-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
| StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 |
| └─MergeJoin_44 | 3.12 | root | | inner join, left key:sbtest999.sbtest1.id, right key:sbtest999.sbtest2.id |
| ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 |
| │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:true, stats:pseudo |
| └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 |
| └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:true, stats:pseudo |
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
```
+ Observe that the default execution plans for the above queries in the `sbtest1` and `sbtest999` schemas are the same, with table joins using the `MergeJoin`.
+ Now DBA decide to use `HashJoin` method instead of `MergeJoin` across all schemas to improve performance for above queries. In `CREATE GLOBAL BINDING` statement, the `*` in `*.sbtest2` and `*.sbtest1` represents all schemas, meaning that the execution plan binding will take effect when executing this query across all schemas.
```sql
CREATE GLOBAL BINDING USING
SELECT /*+ HASH_JOIN(t1, t2) */ COUNT(*) AS num FROM *.sbtest1 AS t1, *.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100;
CREATE SESSION BINDING USING
SELECT /*+ HASH_JOIN(t1, t2) */ COUNT(*) AS num FROM *.sbtest1 AS t1, *.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100;
```
```output
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
```
+ Verify the binding setting. Observe that the `Default_db` is empty, indicating that this execution plan takes effect across all schemas.
```sql
SHOW BINDINGS\G
```
```output
*************************** 1. row ***************************
Original_sql: select * from ( ? . `sbtest1` as `t1` ) join `*` . `sbtest2` as `t2` where `t1` . `id` = `t2` . `id` and `t1` . `id` > ? and `t1` . `id` < ?
Bind_sql: SELECT /*+ HASH_JOIN(`t1`, `t2`)*/ * FROM (`*`.`sbtest1` AS `t1`) JOIN `*`.`sbtest2` AS `t2` WHERE `t1`.`id` = `t2`.`id` AND `t1`.`id` > 10 AND `t1`.`id` < 100
Default_db:
Status: enabled
Create_time: 2025-02-24 09:33:23.736
Update_time: 2025-02-24 09:33:23.736
Charset: utf8
Collation: utf8_general_ci
Source: manual
Sql_digest: 107feec0308c4e216b296a9b5ca03638bf400cb17157f3df35d5dd52460a10ef
Plan_digest:
1 row in set (0.00 sec)
```
+ Verify the binding result. In the sample queries from three schemas, `sbtest1`, `sbtest666`, and `sbtest999`, the CBO selects `HashJoin` specified by the bindings.
```sql
EXPLAIN SELECT COUNT(*) AS num
FROM sbtest1.sbtest1 AS t1,sbtest1.sbtest2 AS t2
WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100;
EXPLAIN SELECT COUNT(*) AS num
FROM sbtest666.sbtest1 AS t1,sbtest666.sbtest2 AS t2
WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100;
EXPLAIN SELECT COUNT(*) AS num
FROM sbtest999.sbtest1 AS t1,sbtest999.sbtest2 AS t2
WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100;
```
```output
+-------------------------------+---------+-----------+---------------+----------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+----------------------------------------------------------------+
| StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 |
| └─HashJoin_63 | 3.12 | root | | inner join, equal:[eq(sbtest1.sbtest1.id, sbtest1.sbtest2.id)] |
| ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 |
| │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:false, stats:pseudo |
| └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 |
| └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------+----------------------------------------------------------------+
+-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+
| StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 |
| └─HashJoin_63 | 3.12 | root | | inner join, equal:[eq(sbtest666.sbtest1.id, sbtest666.sbtest2.id)] |
| ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 |
| │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:false, stats:pseudo |
| └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 |
| └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+
+-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+
| StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 |
| └─HashJoin_63 | 3.12 | root | | inner join, equal:[eq(sbtest999.sbtest1.id, sbtest999.sbtest2.id)] |
| ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 |
| │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:false, stats:pseudo |
| └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 |
| └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+
```
### Step 3. Remove the Cross-Schema execution plan binding
+ Check the existing bindings. Pay attention to the binding's SQL digest for later use, it is displayed on the field `Sql_digest`.
```sql
SHOW BINDINGS\G
```
```output
Original_sql: select count ( ? ) as `num` from ( ? . `sbtest1` as `t1` ) join `*` . `sbtest2` as `t2` where `t1` . `id` = `t2` . `id` and `t1` . `id` > ? and `t1` . `id` < ?
Bind_sql: SELECT /*+ HASH_JOIN(`t1`, `t2`)*/ count(1) AS `num` FROM (`*`.`sbtest1` AS `t1`) JOIN `*`.`sbtest2` AS `t2` WHERE `t1`.`id` = `t2`.`id` AND `t1`.`id` > 10 AND `t1`.`id` < 100
Default_db:
Status: enabled
Create_time: 2025-02-24 09:49:15.265
Update_time: 2025-02-24 09:49:15.265
Charset: utf8
Collation: utf8_general_ci
Source: manual
Sql_digest: 4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910
Plan_digest:
```
+ Remove the binding
```sql
DROP GLOBAL BINDING FOR SQL DIGEST '4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910';
DROP SESSION BINDING FOR SQL DIGEST '4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910';
```
```output
Query OK, 1 row affected (0.01 sec)
```
+ Verify that the binding has been deleted
```sql
SHOW BINDINGS\G
```
```output
Empty set (0.00 sec)
```
+ With Cross-Schema execution plan binding, you can improve the query performance for multiple schemas with a single binding definition, in our example, it is for 1000 schemas.
+ Keep the mysql client connected to TiDB server instance and proceed to the next module.
## Module 3: Managing System Resources For A Multi-Tenancy Environment
This module aims to instruct you on effectively managing resource groups within TiDB by using Resource Control. It ensures that different database users receive controlled and necessary resources. So you can classify the workloads owned by different users to pre-specified priorities. TiDB Resource Control utilizes resource group attributes such as `RU_PER_SEC`, `PRIORITY`, and `BURSTABLE` to help DBAs to achieve the system resource controls, in this lab we will use `RU_PER_SEC` only.
### Step 1. Database multi-tenancy setup
+ Create two database users to act as two tenancies at the database level. `t999` for accessing schema `sbtest999`, `t1000` for accessing schema `sbtest1000`.
+ Let's suppose that `t1000` and `sbtest1000` is a premium tenant configuration, while `t999` and `sbtest999` is a configuration for a free tier tenant.
```sql
CREATE USER 't999'@'%' IDENTIFIED BY 'tidb';
CREATE USER 't1000'@'%' IDENTIFIED BY 'tidb';
GRANT ALL PRIVILEGES ON sbtest999.* TO 't999'@'%';
GRANT ALL PRIVILEGES ON sbtest1000.* TO 't1000'@'%';
```
```output
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
```
+ Exit SQL interface.
```sql
EXIT
```
+ Click here - [Terminal 2](ssh://monitor1-ssh#2) to open a new terminal connected to the virtual machine assigned for your hands-on lab.
+ 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/
```
+ For now on, you should have two Terminals connected to the remove Linux.
### Step 2. Start the sample workload from both connections and observe the baseline
+ Start the sample workload in both **Terminal 1** and **Terminal 2**. The sample workload will run about 1 minute.
+ **COPY** the following command to [Terminal 1](ssh://monitor1-ssh#1), it will run a sample workload on schema `sbtest999` as user `t999`. Do NOT execute it, switch to [Terminal 2](ssh://monitor1-ssh#2).
```bash
clear
cat << EOF > t999_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t999
mysql-password=tidb
mysql-db=sbtest999
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t999_sysbench_config --tables=10 --table_size=100
```
+ **COPY** the following command to [Terminal 2](ssh://monitor1-ssh#2), it will run a sample workload on schema `sbtest1000` as user `t1000`. Do NOT execute it.
```bash
clear
cat << EOF > t1000_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t1000
mysql-password=tidb
mysql-db=sbtest1000
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t1000_sysbench_config --tables=10 --table_size=100
```
+ Now that both terminals are ready to execute the same workload, press the ENTER key on [Terminal 1](ssh://monitor1-ssh#1), and quickly switch to [Terminal 2](ssh://monitor1-ssh#2), press ENTER again.
+ The workloads will run for 1 minute. Compare the sysbench output from both terminals. Look at the `tps` and `qps`, they are similar, for example, similar to the following sample output.
+ In [Terminal 1](ssh://monitor1-ssh#1), transactions are around `79.95 per sec`, queries are `1598.91 per sec`.
```output
...
[ 55s ] thds: 1 tps: 83.00 qps: 1655.99 (r/w/o: 1157.99/332.00/166.00) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 74.00 qps: 1486.01 (r/w/o: 1042.01/296.00/148.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 78.00 qps: 1551.99 (r/w/o: 1083.99/312.00/156.00) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 77.00 qps: 1555.00 (r/w/o: 1092.00/309.00/154.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 79.00 qps: 1567.00 (r/w/o: 1094.00/315.00/158.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 78.00 qps: 1557.99 (r/w/o: 1090.00/312.00/156.00) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 67172
write: 19192
other: 9596
total: 95960
transactions: 4798 (79.95 per sec.)
queries: 95960 (1598.91 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0146s
total number of events: 4798
Latency (ms):
min: 8.86
avg: 12.51
max: 28.92
95th percentile: 15.83
sum: 60008.24
Threads fairness:
events (avg/stddev): 4798.0000/0.00
execution time (avg/stddev): 60.0082/0.00
```
+ In [Terminal 2](ssh://monitor1-ssh#2), transactions are around `80.57 per sec`, queries are `1611.43 per sec`.
```output
...
[ 55s ] thds: 1 tps: 81.00 qps: 1609.01 (r/w/o: 1124.01/323.00/162.00) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 76.00 qps: 1534.04 (r/w/o: 1074.03/308.01/152.00) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 83.00 qps: 1660.01 (r/w/o: 1162.01/332.00/166.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 82.00 qps: 1639.99 (r/w/o: 1147.99/328.00/164.00) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 87.00 qps: 1739.99 (r/w/o: 1217.99/348.00/174.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 84.00 qps: 1672.99 (r/w/o: 1172.99/332.00/168.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 67690
write: 19340
other: 9670
total: 96700
transactions: 4835 (80.57 per sec.)
queries: 96700 (1611.43 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0074s
total number of events: 4835
Latency (ms):
min: 8.53
avg: 12.41
max: 35.83
95th percentile: 15.83
sum: 60000.84
Threads fairness:
events (avg/stddev): 4835.0000/0.00
execution time (avg/stddev): 60.0008/0.00
```
### Step 3. Set RU quota for different tenants
+ Here comes an interesting thing. Suppose that your system is under pressure. Before you decide to scale-out, you want to give more system resources to the premium users (such as t1000) and lesser to the free-tier tenants (such as t999).
+ In [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB server instance.
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root
```
```output
mysql>
```
+ Set up resource groups for user `t999` and user `t1000` with a different quota on [Request Units (RU) per second](https://docs.pingcap.com/tidb/stable/tidb-resource-control-ru-groups/#what-is-request-unit-ru), `t999` gets `1000` and `t1000` gets `20000`.
```sql
DROP RESOURCE GROUP IF EXISTS free_tier;
DROP RESOURCE GROUP IF EXISTS premium;
CREATE RESOURCE GROUP free_tier RU_PER_SEC = 1000;
CREATE RESOURCE GROUP premium RU_PER_SEC = 20000;
ALTER USER t999 RESOURCE GROUP free_tier;
ALTER USER t1000 RESOURCE GROUP premium;
SELECT * FROM information_schema.resource_groups;
SELECT Host, user, User_attributes FROM mysql.user WHERE user IN ('t999','t1000');
EXIT
```
```output
+-----------+------------+----------+-----------+-------------+------------+
| NAME | RU_PER_SEC | PRIORITY | BURSTABLE | QUERY_LIMIT | BACKGROUND |
+-----------+------------+----------+-----------+-------------+------------+
| default | UNLIMITED | MEDIUM | YES | NULL | NULL |
| free_tier | 1000 | MEDIUM | NO | NULL | NULL |
| premium | 20000 | MEDIUM | NO | NULL | NULL |
+-----------+------------+----------+-----------+-------------+------------+
3 rows in set (0.00 sec)
+------+-------+---------------------------------+
| Host | user | User_attributes |
+------+-------+---------------------------------+
| % | t999 | {"resource_group": "free_tier"} |
| % | t1000 | {"resource_group": "premium"} |
+------+-------+---------------------------------+
2 rows in set (0.00 sec)
```
### Step 4. Start the sample workloads again and observe the results
+ **COPY** the following command to [Terminal 1](ssh://monitor1-ssh#1), it will run a sample workload on schema `sbtest999` as user `t999`. Do NOT execute it, switch to [Terminal 2](ssh://monitor1-ssh#2).
```bash
clear
cat << EOF > t999_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t999
mysql-password=tidb
mysql-db=sbtest999
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t999_sysbench_config --tables=10 --table_size=100
```
+ **COPY** the following command to [Terminal 2](ssh://monitor1-ssh#2), it will run a sample workload on schema `sbtest1000` as user `t1000`. Do NOT execute it.
```bash
clear
cat << EOF > t1000_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t1000
mysql-password=tidb
mysql-db=sbtest1000
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t1000_sysbench_config --tables=10 --table_size=100
```
+ Now that both terminals are ready to execute the same workload, press the ENTER key on [Terminal 1](ssh://monitor1-ssh#1), and quickly switch to [Terminal 2](ssh://monitor1-ssh#2), press ENTER again.
+ Compare the sysbench output from both terminals. Look at the `tps` and `qps`, this time they will not be the same.
+ In [Terminal 1](ssh://monitor1-ssh#1), the free-tier tenant, the transactions are around `20.41 per sec`, queries are `408.21 per sec`.
```output
...
[ 55s ] thds: 1 tps: 20.00 qps: 403.00 (r/w/o: 282.00/81.00/40.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 20.00 qps: 401.00 (r/w/o: 280.00/81.00/40.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 20.00 qps: 384.00 (r/w/o: 266.00/78.00/40.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 20.00 qps: 414.99 (r/w/o: 294.00/81.00/40.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 19.00 qps: 383.01 (r/w/o: 266.00/79.00/38.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 20.00 qps: 400.00 (r/w/o: 280.00/80.00/40.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 17150
write: 4900
other: 2450
total: 24500
transactions: 1225 (20.41 per sec.)
queries: 24500 (408.21 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0175s
total number of events: 1225
Latency (ms):
min: 11.11
avg: 48.99
max: 58.21
95th percentile: 55.82
sum: 60014.80
Threads fairness:
events (avg/stddev): 1225.0000/0.00
execution time (avg/stddev): 60.0148/0.00
```
+ In [Terminal 2](ssh://monitor1-ssh#2), the premium tenant, transactions are around `82.60 per sec`, queries are `1652.09 per sec`.
```output
...
[ 55s ] thds: 1 tps: 81.00 qps: 1617.00 (r/w/o: 1132.00/323.00/162.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 82.00 qps: 1635.00 (r/w/o: 1143.00/328.00/164.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 82.00 qps: 1645.00 (r/w/o: 1153.00/328.00/164.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 89.00 qps: 1774.00 (r/w/o: 1240.00/356.00/178.00) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 81.00 qps: 1622.00 (r/w/o: 1136.00/324.00/162.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 84.00 qps: 1685.00 (r/w/o: 1181.00/336.00/168.00) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 69398
write: 19828
other: 9914
total: 99140
transactions: 4957 (82.60 per sec.)
queries: 99140 (1652.09 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0076s
total number of events: 4957
Latency (ms):
min: 8.78
avg: 12.10
max: 38.10
95th percentile: 15.27
sum: 60001.22
Threads fairness:
events (avg/stddev): 4957.0000/0.00
execution time (avg/stddev): 60.0012/0.00
```
+ That concludes Module 3. Keep in mind that the workload in [Terminal 1](ssh://monitor1-ssh#1) is intentionally slowed down. In addition to `RU_PER_SEC`, Resource Control offers other configurable attributes for DBAs, such as `PRIORITY` and `BURSTABLE`. For more information about how to leverage all of them please visit [Using Resource Control to Manage Mixed Workloads](https://labs.tidb.io/labs/demo_006). Please proceed to the next module.
## Module 4: Online Schema Change
Online schema changes are particularly critical in SaaS environments, where uninterrupted uptime and continuous availability are vital for ensuring a seamless user experience. In this module, you will execute a DDL operation to add a new column to an existing table to support a business change while simultaneous DML insertions are occurring on the same table. You'll observe that the DDL operation is not blocked by the ongoing DML, and the Java application running the DML is designed to be aware of TiDB's Online DDL capabilities. It has been programmed to expect schema changes and handle them seamlessly.
### Step 1. Review the schema created by sysbench
+ On [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB server instance.
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root
```
```output
mysql>
```
+ Check the definition of table `sbtest1000.sbtest9`. There are four columns defined in the table, `id` is the primary key.
```sql
DELETE FROM sbtest1000.sbtest9;
DESC sbtest1000.sbtest9;
```
```output
+-------+-----------+------+------+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+------+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| k | int | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+-----------+------+------+---------+----------------+
4 rows in set (0.00 sec)
```
### Step 2. Prepare for online schema change
+ Turn off the metadata lock so that the DDL statements will not be blocked by on-going DML transactions on the same tables.
```sql
SET GLOBAL tidb_enable_metadata_lock = OFF;
SHOW VARIABLES LIKE "tidb_enable_metadata_lock";
EXIT
```
+ Review the provided mini Java program, it performs the simple insertion operations endlessly. And most importantly, it expects the error `8028` and is able to retry the transaction, in short, it is compatible with TiDB's online schema change behavior. Error code `8028`, with the message Information schema is changed, occurs when the program attempts to commit the transaction but detects a schema definition different from the one at the beginning of the transaction.
```bash
cat InsertJob.java | egrep -A1 -B1 8028
```
```output
} catch (SQLException e) {
if (e.getErrorCode() == 8028) {
System.out.println("Schema mutation encountered, retry ...");
```
### Step 3. Start the insertion workload
+ On [Terminal 1](ssh://monitor1-ssh#1), run following commands to start the insertion workload, it won't stop. Keep it running. Proceed to the next step.
```bash
rm -f InsertJob.class
javac -cp .:mysql-connector-java-5.1.36-bin.jar InsertJob.java
sleep 1
java -cp .:mysql-connector-java-5.1.36-bin.jar InsertJob ${HOST_DB1_PRIVATE_IP} 4000 t1000
```
```output
Inserting 0
Inserting 1
Inserting 2
Inserting 3
Inserting 4
Inserting 5
Inserting 6
Inserting 7
...
```
### Step 4. Perform the online schema change
+ Based on certain business requirements, you decide to add a new column to table `sbtest1000.sbtest9`, which is `ed VARCHAR(10) DEFAULT 'N/A'`.
+ Switch to [Terminal 2](ssh://monitor1-ssh#2), connect to TiDB server instance.
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root
```
```output
mysql>
```
+ Check the table twice, confirm that the insertion workload is working and the row count is increasing.
```sql
SELECT COUNT(1) FROM sbtest1000.sbtest9;
SELECT SLEEP(2);
SELECT COUNT(1) FROM sbtest1000.sbtest9;
```
```output
+----------+
| COUNT(1) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
+----------+
| SLEEP(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
+----------+
| COUNT(1) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)
```
+ Perform the DDL to add a new column to `sbtest1000.sbtest9`.
```sql
ALTER TABLE sbtest1000.sbtest9 ADD COLUMN (ed VARCHAR(10) DEFAULT 'N/A');
```
```output
Query OK, 0 rows affected (0.34 sec)
```
+ Switch back to [Terminal 1](ssh://monitor1-ssh#1), observe the output, we are expecting an output with `Schema mutation encountered, retry ...`, and the workload is not interrupted.
```output
...
Inserting 50
Inserting 51
Schema mutation encountered, retry ...
Inserting 52
Inserting 53
Inserting 54
Inserting 55
Inserting 56
...
```
+ Go back to [Terminal 2](ssh://monitor1-ssh#2), confirm that the row count is still increasing.
```sql
SELECT COUNT(1) FROM sbtest1000.sbtest9;
SELECT SLEEP(2);
SELECT COUNT(1) FROM sbtest1000.sbtest9;
```
```output
+----------+
| COUNT(1) |
+----------+
| 71 |
+----------+
1 row in set (0.01 sec)
+----------+
| SLEEP(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
+----------+
| COUNT(1) |
+----------+
| 74 |
+----------+
1 row in set (0.00 sec)
```
+ Verify that the table definition was changed. Note that the column `ed varchar(10)` is the new column.
```sql
DESC sbtest1000.sbtest9;
```
```output
+-------+-------------+------+------+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| k | int | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
| ed | varchar(10) | YES | | N/A | |
+-------+-------------+------+------+---------+----------------+
5 rows in set (0.00 sec)
```
+ At this point, you've witnessed TiDB's Online DDL capability, which allows schema changes without the need to halt any operations. Go to [Terminal 1](ssh://monitor1-ssh#1), and press CTRL-C. Then, exit both terminals.
## Conclusion
TiDB intuitively supports SaaS applications. TiDB is widely welcomed by many SaaS companies.
With TiDB, thousands, or even millions, of schemas and tables can be created rapidly, without impacting the performance. The metadata creation and seed data population is extremely fast in TiDB; TiDB's SQL Plan Management (SPM) could be used to guarantee a stable and the optimal query plan is always used across multiple schemas; TiDB's Resource Control feature can isolate resource usages, so that you can better support premium customers by always giving them higher priorities. There will be no more "noisy neighbours"; Finally, TiDB also supports online schema change. For example, the DDL operation is not blocked by the ongoing DML. Therefore, your SaaS business is always online.
```hbsptform
{
"ui": {
"titleLevel": 3
},
"portalId": "4466002",
"formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf",
"region": "na1",
"sfdcCampaignId": "701RC00000VqCODYA3"
}
```