# SaaS 应用的实用数据库功能 ## 概述 SaaS(软件即服务)应用通常需要高度可扩展性和多租户友好的功能来处理大量客户的不同工作负载。TiDB 作为一个分布式 SQL 数据库，提供了多个实用功能来增强 SaaS 应用的灵活性、性能和运营效率。 在这个动手实验中，你将探索四个围绕 TiDB 功能设计的模块，这些功能在 SaaS 行业中非常有价值。这些任务包括快速创建数千个数据库和表、执行无缝在线表结构变更，以及应用全局执行计划绑定来标准化跨多个表的查询执行。 下图显示了本实验 TiDB 集群的部署拓扑。 ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExerciseDemo304_aa_001_20250523.png) ### 时长 + 完成本实验大约需要 100 分钟。 > [!TIP] > 点击 **开始** 启动实验环境。 > 环境准备就绪后，点击底部工具栏中的 **前进** 进入 **模块 1**。 > 然后，按照实验指南的步骤连接到实践环境并开始你的任务。 ## 模块 1: 快速创建大量数据库和表 SaaS 应用通常为不同租户提供独立的数据库或表。TiDB 的可扩展性和高效的元数据管理使其能够快速创建数千个数据库和表，同时保持性能和稳定性。 在本模块中，我们将尝试创建 1,000 个数据库，每个数据库包含 10 个表，总共 10,000 个表。 在生产环境中，你可以轻松扩展到百万级表。由于今天时间有限，我们将生成限制在 10k 个表。 ### 步骤 1. 连接到 TiUP 控制机节点 + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 登录后显示的横幅： ```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. 查看 TiDB 集群状态 + 获取集群状态。 ```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 ``` ### 步骤 3. 修改2个必要的 TiDB 系统变量 + 以数据库用户 `root` 身份连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql> ``` + 检查表创建加速变量 `tidb_enable_fast_create_table`。从 `v8.5` 开始默认值为 `ON`。对于从 v8.4.0 或更早版本升级的集群，默认值保持为 `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) ``` + 检查 schema 缓存大小变量 `tidb_schema_cache_size`。当单个集群中的表数量超过 100k 时，通过监控 Infoschema v2 Cache Operation 指标并调整缓存大小值(默认 512MB)来保持缓存命中率大于 85%。你可以通过路径 `TiDB` -> `Schema Load` -> `Infoschema v2 Cache Operation` & `Infoschema v2 Cache Size` 在 grafana 中找到这些指标。 ```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; ``` ### 步骤 4. 准备 sysbench 配置 + 获取 sysbench 工具。在本模块中我们将使用 sysbench 并行创建数据库和表。 ```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. ``` + 设置 `sysbench` 配置。我们将在本动手实验中使用 64 个线程。 ```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 ``` ### 步骤 5. 使用 sysbench 创建 10,000 个表 + 创建 1000 个数据库，每个数据库 10 个表，每个表有 100 行种子数据。这用于模拟 SaaS 应用初始化时的场景。它将创建许多数据库和表，每个数据库本质上代表一个租户。这将花费你大约 2 分钟，请记住我们使用的是微型虚拟机实例的来创建的实验 TiDB 集群，这不是生产级环境。 + 在真实的生产环境中，您可以以相同的速度创建数百万个表。 ```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' ... ``` ### 步骤 6. 验证创建的数据库 + 以数据库用户 `root` 身份连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql> ``` + 验证数据库。 ```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) ``` + 验证表并检查 `sbtest1000` 数据库中一个随机表的行数。 ```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) ``` + 当新租户订阅 SaaS 应用时，TiDB 中的元数据创建和种子数据填充非常快。 + 保持 mysql 客户端和 TiDB 的链接，请继续下一个模块。 ## 模块 2: 跨数据库执行计划绑定 在 SaaS 工作负载中，通常在租户之间会发生查询模式变化，导致执行计划不一致。TiDB 的 SQL 执行计划管理(SPM)支持全局执行计划绑定，以确保跨不同数据库的稳定和最优查询性能。 ### 步骤 1. 启用跨数据库执行计划绑定 + 通过将系统变量 [`tidb_opt_enable_fuzzy_binding`](https://docs.pingcap.com/tidb/stable/system-variables/#tidb_opt_enable_fuzzy_binding-new-in-v760) 设置为 `ON`，同时为全局和当前会话启用跨数据库执行计划绑定。 ```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 | +-------------------------------+-------+ ``` ### 步骤 2. 创建跨数据库执行计划绑定 + 在模块 1 中，我们创建了 1000 个数据库，它们都有名为 `sbtest1` 和 `sbtest2` 的相同定义的表。我们将选择数据库 `sbtest1` 和 `sbtest999` 作为执行计划绑定的目标数据库。 ```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) ``` + 对跨多个数据库的某种类型的 SQL 进行执行计划采样，在我们的例子中是 `sbtest1` 和 `sbtest999`。 ```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 | +-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+ ``` + 我们观察到，上述查询在 `sbtest1` 和 `sbtest999` 数据库中的默认执行计划是相同的，使用 `MergeJoin` 进行表连接。 + 现在 DBA 决定在所有数据库中使用 `HashJoin` 方法而不是 `MergeJoin` 来提高上述查询的性能。在 `CREATE GLOBAL BINDING` 语句中，`*.sbtest2` 和 `*.sbtest1` 中的 `*` 代表所有数据库，意味着执行计划绑定将在跨所有 schema 执行此查询时生效。 ```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) ``` + 验证绑定设置。观察到 `Default_db` 为空，表示此执行计划在所有数据库中生效。 ```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) ``` + 验证绑定结果。在来自三个数据库的示例查询中，`sbtest1`、`sbtest666` 和 `sbtest999`，CBO 选择了绑定指定的 `HashJoin`。 ```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 | +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ ``` ### 步骤 3. 删除跨数据库执行计划绑定 + 检查现有的绑定。注意绑定的 SQL digest 供后续使用，它显示在 `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: ``` + 删除绑定 ```sql DROP GLOBAL BINDING FOR SQL DIGEST '4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910'; DROP SESSION BINDING FOR SQL DIGEST '4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910'; ``` ```output Query OK, 1 row affected (0.01 sec) ``` + 验证绑定已被删除。 ```sql SHOW BINDINGS\G ``` ```output Empty set (0.00 sec) ``` + 通过跨数据库执行计划绑定，你可以使用单个绑定定义来提高多个数据库的查询性能，在我们的示例中是针对 1000 个数据库。 + 保持 mysql 客户端连和 TiDB 服务器的链接，继续进行下一个模块。 ## 模块 3: 管理多租户环境的系统资源 本模块旨在指导你如何通过使用资源控制有效管理 TiDB 中的资源组。它确保不同的数据库用户获得受控且必要的资源。这样你就可以将不同用户拥有的工作负载分类为预先指定的优先级。TiDB 资源控制利用资源组属性如 `RU_PER_SEC`、`PRIORITY` 和 `BURSTABLE` 来帮助 DBA 实现系统资源控制，在本实验中我们将只使用 `RU_PER_SEC`。 ### 步骤 1. 数据库多租户设置 + 创建两个数据库用户作为两个租户。`t999` 用于访问数据库 `sbtest999`，`t1000` 用于访问数据库 `sbtest1000`。 + 假设 `t1000` 和 `sbtest1000` 是高级租户配置，而 `t999` 和 `sbtest999` 是免费租户的配置。 ```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.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ``` + 退出远程 SQL 交互。 ```sql EXIT ``` + 点击这里 - [终端 2](ssh://monitor1-ssh#2) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 登录后显示的横幅： ```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/ ``` + 现在，你应该有两个终端连接到远程 Linux。 ### 步骤 2. 从两个连接启动示例工作负载并观察基准 + 在 **终端 1** 和 **终端 2** 中启动示例工作负载。示例工作负载将运行约 1 分钟。 + **复制** 以下命令到 [终端 1](ssh://monitor1-ssh#1) ，它将以用户 `t999` 的身份在数据库 `sbtest999` 上运行示例工作负载。先不要执行，切换到 [终端 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 ``` + **复制** 以下命令到 [终端 2](ssh://monitor1-ssh#2) ，它将以用户 `t1000` 的身份在数据库 `sbtest1000` 上运行示例工作负载。先不要执行。 ```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 ``` + 现在两个终端都准备好执行相同的工作负载，在 [终端 1](ssh://monitor1-ssh#1) 上按 ENTER 键，然后快速切换到 [终端 2](ssh://monitor1-ssh#2) ，再次按 ENTER。 + 工作负载将运行 1 分钟。比较两个终端的 sysbench 输出。查看 `tps` 和 `qps`，它们是相似的，类似于以下示例输出。 + 在 [终端 1](ssh://monitor1-ssh#1) 中，事务约为 `79.95 每秒`，查询约为 `1598.91 每秒`。 ```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 ``` + 在 [终端 2](ssh://monitor1-ssh#2) 中，事务约为 `80.57 每秒`，查询约为 `1611.43 每秒`。 ```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 ``` ### 步骤 3. 为不同租户设置 RU 配额 + 现在有一个有趣的事情。假设你的系统正处于压力之下。在决定横向扩展之前，你想给高级用户(如 t1000)更多的系统资源，给免费层租户(如 t999)更少的资源。 + 在 [终端 1](ssh://monitor1-ssh#1) 中，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql> ``` + 为用户 `t999` 和用户 `t1000` 设置资源组，对[Request Units (RU) per second](https://docs.pingcap.com/tidb/stable/tidb-resource-control-ru-groups/#what-is-request-unit-ru)设置不同的配额，`t999` 获得 `1000`，`t1000` 获得 `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) ``` ### 步骤 4. 再次启动示例工作负载并观察结果 + **复制** 以下命令到 [终端 1](ssh://monitor1-ssh#1) ，它将以用户 `t999` 的身份在数据库 `sbtest999` 上运行示例工作负载。先不要执行，切换到 [终端 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 ``` + **复制** 以下命令到 [终端 2](ssh://monitor1-ssh#2) ，它将以用户 `t1000` 的身份在数据库 `sbtest1000` 上运行示例工作负载。先不要执行。 ```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 ``` + 现在两个终端都准备好执行相同的工作负载，在 [终端 1](ssh://monitor1-ssh#1) 上按 ENTER 键，然后快速切换到 [终端 2](ssh://monitor1-ssh#2) ，再次按 ENTER。 + 比较两个终端的 sysbench 输出。查看 `tps` 和 `qps`，这次它们不会相同。 + 在 [终端 1](ssh://monitor1-ssh#1) 中，免费层租户，事务约为 `20.41 每秒`，查询约为 `408.21 每秒`。 ```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 ``` + 在 [终端 2](ssh://monitor1-ssh#2) 中，高级租户，事务约为 `82.60 每秒`，查询约为 `1652.09 每秒`。 ```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 ``` + 以上是模块3 的内容。请记住，[终端 1](ssh://monitor1-ssh#1) 中的工作负载是故意被减慢的。除了 `RU_PER_SEC`，资源控制还为 DBA 提供了其他可配置的属性，如 `PRIORITY` 和 `BURSTABLE`。有关如何利用所有这些属性的更多信息，请访问 [利用资源控制功能管理数据库混合工作负载](https://labs.pingcap.com/labs/demo_006)。请继续下一个模块。 ## 模块 4: 在线表结构变更 在线表结构变更在 SaaS 环境中尤为重要，因为 SaaS 需要保证不间断运行。在本模块中，您将执行 DDL 操作，向现有表添加新列以支持业务变更，同时在同一表上进行 DML 插入操作。你将观察到 DDL 操作不会被正在进行的 DML 阻塞，并且运行 DML 的 Java 应用程序被设计为能够感知 TiDB 的在线 DDL 功能。它被编程为能够无缝处理表结构变更。 ### 步骤 1. 查看 sysbench 创建的表结构 + 在 [终端 1](ssh://monitor1-ssh#1) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql> ``` + 检查表 `sbtest1000.sbtest9` 的定义。表中有四个列，`id` 是主键。 ```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) ``` ### 步骤 2. 准备在线表结构变更 + 关闭元数据锁，以便 DDL 语句不会被同一表上正在进行的 DML 事务阻塞。 ```sql SET GLOBAL tidb_enable_metadata_lock = OFF; SHOW VARIABLES LIKE "tidb_enable_metadata_lock"; EXIT ``` + 查看提供的 Java 程序，它执行简单的插入操作，并且最重要的是，在遇到 `8028` 错误时，它能够重试事务。简而言之，它与 TiDB 的在线表结构变更行为兼容。错误代码 `8028`，消息为 Information schema is changed，当程序尝试提交事务但检测到与事务开始时不同的 schema 定义时发生。 ```bash cat InsertJob.java | egrep -A1 -B1 8028 ``` ```output } catch (SQLException e) { if (e.getErrorCode() == 8028) { System.out.println("Schema mutation encountered, retry ..."); ``` ### 步骤 3. 启动插入工作负载 + 在 [终端 1](ssh://monitor1-ssh#1) 上，运行以下命令启动插入工作负载，它不会停止。保持它运行。继续下一步。 ```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 ... ``` ### 步骤 4. 执行在线表结构变更 + 根据某些业务需求，你决定向表 `sbtest1000.sbtest9` 添加新列，即 `ed VARCHAR(10) DEFAULT 'N/A'`。 + 切换到 [终端 2](ssh://monitor1-ssh#2) ，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql> ``` + 检查表两次，确认插入工作负载正在工作，行数在增加。 ```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) ``` + 执行 DDL 向 `sbtest1000.sbtest9` 添加新列。 ```sql ALTER TABLE sbtest1000.sbtest9 ADD COLUMN (ed VARCHAR(10) DEFAULT 'N/A'); ``` ```output Query OK, 0 rows affected (0.34 sec) ``` + 切换回 [终端 1](ssh://monitor1-ssh#1) ，观察输出，我们期望看到 `Schema mutation encountered, retry ...` 的输出，并且工作负载没有被中断。 ```output ... Inserting 50 Inserting 51 Schema mutation encountered, retry ... Inserting 52 Inserting 53 Inserting 54 Inserting 55 Inserting 56 ... ``` + 返回 [终端 2](ssh://monitor1-ssh#2) ，确认行数仍在增加。 ```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) ``` + 验证表定义已更改。注意列 `ed varchar(10)` 是新列。 ```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) ``` + 此时，你已经见证了 TiDB 的在线 DDL 功能，它允许表结构变更而无需停止任何操作。转到 [终端 1](ssh://monitor1-ssh#1) ，按 CTRL-C。然后，退出两个终端。 ## 结论 TiDB 直观地支持 SaaS 应用程序。TiDB 受到许多 SaaS 公司的广泛欢迎。 使用 TiDB，可以快速创建数千甚至数百万个数据库和表，而不会影响性能。TiDB 中的元数据创建和种子数据填充非常快；TiDB 的 SQL 计划管理（SPM）可用于确保在多个数据库中始终使用稳定且最优的查询计划；TiDB 的资源控制功能可以隔离资源使用，因此你可以通过始终给予高级客户更高的优先级来更好地支持他们。不再有“吵闹的邻居”；最后，TiDB 还支持在线表结构变更。例如，DDL 操作不会被正在进行的 DML 阻塞。因此，你的 SaaS 业务始终在线。