How to Scale TiDB Locally with Online DDL

Data-intensive applications outgrow single-node MySQL long before product-market fit is “done.” Hot partitions, schema change windows, and manual sharding slow teams down. But TiDB solves this with a MySQL-compatible, distributed SQL architecture that scales storage and compute independently and keeps applications online during change.

In this quick tutorial, we’ll spin up TiDB locally with TiUP Playground, 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’ll have a working mental model for how TiDB’s elasticity translates from a laptop to production.

Prerequisites

Before we get started, install TiUP and update to the latest version:

tiup mirror set https://tiup-mirrors.pingcap.com

tiup update --self && tiup update --all

Next, view previous Playground Cluster files:

ls -la ~/.tiup/data

When you’re done experimenting, you can clean up:

rm -rf ~/.tiup/data/*democluster*

Or for all previous cluster files:

rm -rf ~/.tiup/data/*

Step 1: Start Small

Let’s start with the tiniest possible cluster: 1× TiDB, 1× TiKV, 1× PD.

tiup playground --db 1 --pd 1 --kv 1 --tag democluster

Keep this terminal open; TiDB usually runs on port 4000.

Load a Small Dataset

Now, in another terminal:

tiup bench tpcc --host 127.0.0.1 --port 4000 -D test --dropdata --warehouses 5 prepare

Run a Baseline Workload

tiup bench tpcc --host 127.0.0.1 --port 4000 -D test --warehouses 5 --time 120s --threads 32 run

You can also explore DDL in real time. Let’s now login to the SQL Shell:

mysql -h 127.0.0.1 -P 4000 -u root 

Use test;
CREATE INDEX idx_qty ON stock (s_w_id);
SHOW INDEXES FROM stock;
ALTER TABLE stock DROP INDEX idx_qty;
ADMIN SHOW DDL JOBS 3;
SHOW INDEXES FROM stock;

This gives you a baseline TPS and latency profile on a single TiKV node.

Now that we’ve got a baseline, let’s see how TiDB scales storage live.

Step 2: Scale Storage

Now for the fun part: let’s add 2 more TiKV nodes (1 → 3) while the workload is running.

tiup playground scale-out --kv 2 --tag democluster
tiup playground display --tag democluster

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.

Next, check the current replication factor in the SQL Shell:

SELECT type, instance, `key`, value
  -> FROM information_schema.cluster_config
  -> WHERE type = 'pd' AND `key` LIKE '%max-replicas%';

Now set it back to 3 so the Placement Driver (PD), TiDB’s metadata orchestrator, will maintain three replicas per region:

SET CONFIG pd replication.max-replicas = 3;

Let’s then verify:

SELECT type, instance, `key`, value
  -> FROM information_schema.cluster_config
  -> WHERE type = 'pd' AND `key` LIKE '%max-replicas%';

In SQL, watch the cluster expand:

-- New stores appear in minutes
SELECT store_id, address, store_state_name
FROM information_schema.tikv_store_status;

-- Region distribution across stores
SELECT store_id,
      COUNT(*) AS peer_regions,
      SUM(CASE WHEN is_leader = 1 THEN 1 ELSE 0 END) AS leader_regions
FROM information_schema.tikv_region_peers
GROUP BY store_id;

You’ll now see PD rebalance regions and leaders across all three TiKV nodes without downtime.

Storage isn’t the only thing we can scale — TiDB’s SQL layer is stateless, so let’s add more compute too.

Step 3: Scale Compute

TiDB servers are stateless SQL frontends, so scaling compute is instant.

Add a 2nd TiDB node:

tiup playground scale-out --db 1 --tag democluster
tiup playground display --tag democluster

Find their ports (in the SQL Shell):

SELECT instance AS sql_addr, status_address
FROM information_schema.cluster_info
WHERE type="tidb";

Now drive traffic to both:

Terminal A → TiDB #1 (e.g., port 4000)

tiup bench tpcc --host 127.0.0.1 --port 4000 -D test --warehouses 5 --time 120s --threads 32 run

Terminal B → TiDB #2 (If the SQL port for the second TiDB server was 50861)

tiup bench tpcc --host 127.0.0.1 --port 50861 -D test --warehouses 5 --time 120s --threads 32 run

With a load balancer, traffic spreads across TiDB servers seamlessly.

So far we’ve scaled OLTP. Now let’s bring analytics into the mix with TiFlash.

Step 4: Mixed Workload Processing with TiFlash

TiFlash is TiDB’s columnar engine for analytical workloads. Let’s add one node:

tiup playground scale-out --tiflash 1 --tag democlustertiup playground display --tag democluster

Next, let’s replicate a table:

USE test;
ALTER TABLE stock SET TIFLASH REPLICA 1;

-- Check replication progress
SELECT * FROM information_schema.tiflash_replica
WHERE TABLE_SCHEMA='test' AND TABLE_NAME='stock';

Let’s then force an analytical query:

EXPLAIN ANALYZE
SELECT s_i_id, SUM(s_quantity) AS qty
FROM stock
GROUP BY s_i_id
ORDER BY qty DESC
LIMIT 10;

You’ll see that OLTP continues on TiKV, while analytics run on TiFlash, isolating heavy scans from transactions.

Step 5: Observability

Let’s work through a few queries to show live behavior:

-- Connections per TiDB server
SELECT instance, COUNT(*) AS connections
FROM information_schema.cluster_processlist
GROUP BY instance;

-- Regions/Leaders per TiKV store
SELECT store_id,
      COUNT(*) AS peer_regions,
      SUM(CASE WHEN is_leader = 1 THEN 1 ELSE 0 END) AS leader_regions
FROM information_schema.tikv_region_peers
GROUP BY store_id;

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.

Step 6: Cleanup

When finished, simply close the TiUP Playground terminal, and it will stop the cluster. To wipe leftover data:

rm -rf ~/.tiup/data/*democluster*

Conclusion: Scaling TiDB Locally

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.

Here’s what you can try next:

• Repeat the demo with larger datasets and higher thread counts to see leader/region dynamics at scale.
• Try different replication settings and observe impact on failover and p95 latency.
• Explore load balancing across TiDB servers and enable TiFlash replicas on additional hot tables.
• Move from Playground to a managed or production deployment to validate your target SLOs.

If you’d like to play around with more TiDB capabilities in a test environment, check out our free hands-on lab courses.