## 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. ### 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 --prompt="mysql [\d] > " -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql [(none)] > ``` + 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 --prompt="mysql [\d] > " -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql [(none)] > ``` + 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.