{"id":30285,"date":"2025-11-06T08:29:50","date_gmt":"2025-11-06T16:29:50","guid":{"rendered":"https:\/\/www.pingcap.com\/?p=30285"},"modified":"2025-11-12T10:38:14","modified_gmt":"2025-11-12T18:38:14","slug":"scaling-tidb-locally-online-ddl-tutorial","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/scaling-tidb-locally-online-ddl-tutorial\/","title":{"rendered":"How to Scale TiDB Locally with Online DDL"},"content":{"rendered":"

Data-intensive applications outgrow single-node MySQL long before product-market fit is \u201cdone.\u201d Hot partitions, schema change windows, and manual sharding slow teams down. But \ud2f0DB<\/a> solves this with a MySQL \ud638\ud658<\/a>, distributed SQL architecture<\/a> that scales storage and compute independently and keeps applications online during change.<\/p>\n\n\n\n

In this quick tutorial, we’ll spin up TiDB locally with TiUP Playground<\/a>, establish a baseline, and then scale live, all without restarting or taking downtime. Along the way, we’ll observe region rebalancing, verify replication settings, and run DDL safely under load. By the end, you\u2019ll have a working mental model for how TiDB\u2019s elasticity translates from a laptop to production.<\/p>\n\n\n\n

<\/span>Prerequisites<\/strong><\/span><\/h2>\n\n\n\n

First, install TiUP. <\/strong>If you don\u2019t already have TiUP, install it from tiup.io<\/a>:<\/p>\n\n\n\n

curl --proto '=https' --tlsv1.2 -sSf https:\/\/tiup-mirrors.pingcap.com\/install.sh | sh<\/code><\/pre>\n\n\n\n
Then set the mirror and update to the latest version:<\/p>\n\n\n\n
tiup mirror set https:\/\/tiup-mirrors.pingcap.com\ntiup update --self && tiup update --all<\/code><\/pre>\n\n\n\n
<\/span>Step 1: Start Small<\/strong><\/span><\/h2>\n\n\n\n
Let\u2019s start with the tiniest possible cluster: 1\u00d7 TiDB, 1\u00d7 TiKV, 1\u00d7 PD<\/strong>.<\/p>\n\n\n\n
tiup playground --db 1 --pd 1 --kv 1 --tag democluster<\/code><\/pre>\n\n\n\n
Keep this terminal open; TiDB usually runs on port 4000.<\/p>\n\n\n\n
Load a Small Dataset<\/h3>\n\n\n\n
Now, in another terminal:<\/p>\n\n\n\n
tiup bench tpcc --host 127.0.0.1 --port 4000 -D test --dropdata --warehouses 5 prepare<\/code><\/pre>\n\n\n\n
Run a Baseline Workload<\/h3>\n\n\n\n
tiup bench tpcc --host 127.0.0.1 --port 4000 -D test --warehouses 5 --time 120s --threads 32 run<\/code><\/pre>\n\n\n\n
You can also explore DDL in real time. Let’s now login to the MySQL Client:<\/p>\n\n\n\n
mysql -h 127.0.0.1 -P 4000 -u root \n\nUse<\/strong> test<\/strong>;\nCREATE<\/strong> INDEX<\/strong> idx_qty ON<\/strong> stock (s_w_id);\nSHOW<\/strong> INDEXES<\/strong> FROM<\/strong> stock;\nALTER<\/strong> TABLE<\/strong> stock DROP<\/strong> INDEX<\/strong> idx_qty;\nADMIN SHOW<\/strong> DDL<\/strong> JOBS 3;\nSHOW<\/strong> INDEXES<\/strong> FROM<\/strong> stock;<\/code><\/pre>\n\n\n\n
This gives you a baseline TPS and latency profile on a single TiKV<\/a> node.<\/p>\n\n\n\n
Now that we\u2019ve got a baseline, let\u2019s see how TiDB scales storage live.<\/p>\n\n\n\n
<\/span>Step 2: Scale Storage<\/strong><\/span><\/h2>\n\n\n\n
Now for the fun part: let\u2019s add 2 more TiKV nodes (1 \u2192 3)<\/strong> while the workload is running<\/em>.<\/p>\n\n\n\n
tiup playground scale-out --kv 2 --tag democluster\ntiup playground display --tag democluster<\/code><\/pre>\n\n\n\n
By default, TiUP Playground may start with replication=1 (no fault tolerance). Setting it to 3 simulates a real production-like cluster with 3 copies per region.<\/p>\n\n\n\n
Next, check the current replication factor in the MySQL Client:<\/p>\n\n\n\n
SELECT type, instance, `key`, value\nFROM information_schema.cluster_config\nWHERE type = 'pd' AND `key` LIKE '%max-replicas%';<\/code><\/pre>\n\n\n\n
Now set it back to 3 so the Placement Driver (PD), TiDB’s metadata orchestrator, will maintain three replicas per region:<\/p>\n\n\n\n
SET<\/strong> CONFIG pd replication.max-replicas = 3;<\/code><\/pre>\n\n\n\n
Let’s then verify:<\/p>\n\n\n\n
SELECT<\/strong> type<\/strong>, instance<\/strong>, `key`, value<\/strong>\n  -> FROM<\/strong> information_schema.cluster_config\n  -> WHERE<\/strong> type<\/strong> = 'pd' AND<\/strong> `key` LIKE<\/strong> '%max-replicas%';<\/code><\/pre>\n\n\n\n
In SQL, watch the cluster expand:<\/p>\n\n\n\n
-- New stores appear in minutes\nSELECT<\/strong> store_id, address, store_state_name\nFROM<\/strong> information_schema.tikv_store_status;\n\n-- Region distribution across stores\nSELECT<\/strong> store_id,\n      COUNT<\/strong>(*) AS<\/strong> peer_regions,\n      SUM<\/strong>(CASE<\/strong> WHEN<\/strong> is_leader = 1 THEN<\/strong> 1 ELSE<\/strong> 0 END<\/strong>) AS<\/strong> leader_regions\nFROM<\/strong> information_schema.tikv_region_peers\nGROUP<\/strong> BY<\/strong> store_id;<\/code><\/pre>\n\n\n\n
You\u2019ll now see PD rebalance regions and leaders across all three TiKV nodes without downtime.<\/p>\n\n\n\n
Storage isn\u2019t the only thing we can scale \u2014 TiDB\u2019s SQL layer is stateless, so let\u2019s add more compute too.<\/p>\n\n\n\n
<\/span>Step 3: Scale Compute<\/strong><\/span><\/h2>\n\n\n\n
TiDB servers are stateless SQL frontends, so scaling compute is instant.<\/p>\n\n\n\n
Add a 2nd TiDB node:<\/p>\n\n\n\n
tiup playground scale-out --db 1 --tag democluster\ntiup playground display --tag democluster<\/code><\/pre>\n\n\n\n
Find their ports (in the SQL Shell):<\/p>\n\n\n\n
SELECT<\/strong> instance<\/strong> AS<\/strong> sql_addr, status_address\nFROM<\/strong> information_schema.cluster_info\nWHERE<\/strong> type<\/strong>=\"tidb\";<\/code><\/pre>\n\n\n\n
Now drive traffic to both:<\/p>\n\n\n\n
Terminal A \u2192 TiDB #1 (e.g., port 4000)<\/strong><\/p>\n\n\n\n
tiup bench tpcc --host 127.0.0.1 --port 4000 -D test --warehouses 5 --time 120s --threads 32 run<\/code><\/pre>\n\n\n\n
Terminal B \u2192 TiDB #2 (If the SQL port for the second TiDB server was 50861)<\/strong><\/p>\n\n\n\n
tiup bench tpcc --host 127.0.0.1 --port 50861 -D test --warehouses 5 --time 120s --threads 32 run<\/code><\/pre>\n\n\n\n
With a load balancer, traffic spreads across TiDB servers seamlessly.<\/p>\n\n\n\n
So far we\u2019ve scaled OLTP. Now let\u2019s bring analytics into the mix with TiFlash.<\/p>\n\n\n\n
<\/span>Step 4: Mixed Workload Processing with TiFlash<\/strong><\/span><\/h2>\n\n\n\n
TiFlash<\/a> is TiDB\u2019s columnar engine for analytical workloads. Let\u2019s add one node:<\/p>\n\n\n\n
tiup playground scale-out --tiflash 1 --tag democlustertiup playground display --tag democluster<\/code><\/pre>\n\n\n\n
Next, let’s replicate a table:<\/p>\n\n\n\n
USE<\/strong> test<\/strong>;\nALTER<\/strong> TABLE<\/strong> stock SET<\/strong> TIFLASH REPLICA 1;\n\n-- Check replication progress\nSELECT<\/strong> * FROM<\/strong> information_schema.tiflash_replica\nWHERE<\/strong> TABLE_SCHEMA='test' AND<\/strong> TABLE_NAME='stock';<\/code><\/pre>\n\n\n\n
Let’s then force an analytical query:<\/p>\n\n\n\n
EXPLAIN<\/strong> ANALYZE<\/strong>\nSELECT<\/strong> s_i_id, SUM<\/strong>(s_quantity) AS<\/strong> qty\nFROM<\/strong> stock\nGROUP<\/strong> BY<\/strong> s_i_id\nORDER<\/strong> BY<\/strong> qty DESC<\/strong>\nLIMIT<\/strong> 10;<\/code><\/pre>\n\n\n\n
You’ll see that OLTP continues on TiKV, while analytics run on TiFlash, isolating heavy scans from transactions.<\/p>\n\n\n\n
<\/span>Step 5: Observability<\/strong><\/span><\/h2>\n\n\n\n
Let’s work through a few queries to show live behavior:<\/p>\n\n\n\n
-- Connections per TiDB server\nSELECT<\/strong> instance<\/strong>, COUNT<\/strong>(*) AS<\/strong> connections\nFROM<\/strong> information_schema.cluster_processlist\nGROUP<\/strong> BY<\/strong> instance<\/strong>;\n\n-- Regions\/Leaders per TiKV store\nSELECT<\/strong> store_id,\n      COUNT<\/strong>(*) AS<\/strong> peer_regions,\n      SUM<\/strong>(CASE<\/strong> WHEN<\/strong> is_leader = 1 THEN<\/strong> 1 ELSE<\/strong> 0 END<\/strong>) AS<\/strong> leader_regions\nFROM<\/strong> information_schema.tikv_region_peers\nGROUP<\/strong> BY<\/strong> store_id;<\/code><\/pre>\n\n\n\n
As this workload runs, you can watch PD rebalance regions across TiKV stores. TiFlash nodes will typically show only a handful of peer regions, and since they act as Raft learners, they will not hold leader roles.<\/p>\n\n\n\n
<\/span>Step 6: Cleanup<\/strong><\/span><\/h2>\n\n\n\n
When finished, simply close the TiUP Playground terminal, and it will stop the cluster. To wipe leftover data, you can use either:<\/p>\n\n\n\n
tiup clean --all<\/code><\/pre>\n\n\n\n
Or manually remove the demo data:<\/p>\n\n\n\n
rm -rf ~\/.tiup\/data\/*democluster*<\/code><\/pre>\n\n\n\n
<\/span>Conclusion<\/strong>: Scaling TiDB Locally<\/span><\/h2>\n\n\n\n
We started small and scaled TiDB live: added TiKV to expand storage and fault tolerance, added TiDB servers for stateless SQL scale-out, and enabled TiFlash to isolate analytics from OLTP, all while online DDL kept changes safe under load. The net effect is the core promise of TiDB in miniature: elastic capacity, operational simplicity, and zero-downtime evolution. <\/p>\n\n\n\n
Here’s what you can try next:<\/p>\n\n\n\n