Distributed Database TiDB Tuning & Practical Guide

数据库

Why Choose TiDB in 2026?

TiDB, as a next-generation HTAP (Hybrid Transactional and Analytical Processing) distributed database, has become the go-to solution for enterprise database upgrades in 2026. It perfectly solves the single-machine bottleneck and sharding complexity of traditional MySQL.

TiDB Core Advantages

Dimension TiDB MySQL Sharding CockroachDB Amazon Aurora
Scaling Transparent horizontal App-level sharding Horizontal Vertical + read scaling
MySQL compat Very high (95%+) Native MySQL Medium High
HTAP Native support Extra components needed Not supported Limited
Ops complexity Medium (TiUP) High Medium Low (managed)
Strong consistency Raft protocol Native Raft protocol Limited
Open source Fully open source - Open source Closed source

Key TiDB Milestones in 2026

  1. TiDB 8.x stable: 40%+ performance improvement, supports larger data volumes
  2. TiFlash MPP enhancements: Significantly improved real-time analytics, more window functions
  3. Serverless TiDB: Cloud-native pay-as-you-go model, lower barrier to entry
  4. Resource Control: CPU/IO isolation for multi-tenant scenarios
  5. Global Sort: Dramatically reduces overhead for large-scale data imports

TiDB Architecture Overview

TiDB adopts a compute-storage separation architecture with four core components:

Component Responsibilities

┌─────────────────────────────────────────────────────┐
│                    Client (MySQL Protocol)           │
└──────────────┬──────────────────────────────────────┘
               │
┌──────────────▼──────────────────────────────────────┐
│              TiDB Server (SQL Layer)                 │
│  ┌─────────┐ ┌──────────┐ ┌───────────────┐        │
│  │ Parser  │ │ Optimizer│ │ Executor      │        │
│  └─────────┘ └──────────┘ └───────────────┘        │
└──────────────┬──────────────────────────────────────┘
               │
┌──────────────▼──────────────────────────────────────┐
│           PD (Placement Driver)                      │
│  ┌──────────┐ ┌──────────┐ ┌──────────────┐        │
│  │ Scheduler│ │ Meta     │ │ TSO Clock    │        │
│  └──────────┘ └──────────┘ └──────────────┘        │
└──────────────┬──────────────────────────────────────┘
               │
┌──────────────▼──────────────────────────────────────┐
│    TiKV (Row Store)          TiFlash (Column Store)  │
│  ┌─────────┐               ┌──────────────┐        │
│  │ Raft    │               │ DeltaTree    │        │
│  │ RocksDB │               │ ClickHouse   │        │
│  └─────────┘               └──────────────┘        │
└─────────────────────────────────────────────────────┘
  • TiDB Server: Stateless SQL compute layer, handles SQL parsing, optimization, and execution
  • PD (Placement Driver): Cluster brain, manages scheduling, metadata, and global timestamps
  • TiKV: Row storage engine based on RocksDB + Raft, handles transactional reads/writes
  • TiFlash: Column storage engine based on ClickHouse technology, handles real-time analytics

Data Flow Mechanism

  1. Write path: Client → TiDB → PD (get TSO) → TiKV (Raft write) → TiFlash (async sync)
  2. Read path (OLTP): Client → TiDB → TiKV (point/range scan)
  3. Read path (OLAP): Client → TiDB → TiFlash (MPP parallel compute)

💡 Use the SQL Formatter tool to beautify TiDB SQL queries for easier execution plan analysis.

Deploying TiDB with TiUP

TiUP is TiDB's official cluster management tool, supporting one-click deployment, scaling, and upgrades.

Environment Setup

# Install TiUP
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
source ~/.bash_profile

# Verify installation
tiup --version

Minimal Test Cluster Deployment

# topo.yaml - Minimal topology configuration
global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

monitored:
  node_exporter_port: 9100
  blackbox_exporter_port: 9115

server_configs:
  tidb:
    log.level: "info"
    performance.max-procs: 4
  tikv:
    readpool.coprocessor.max-concurrency: 8
    raftstore.apply-pool-size: 3
    raftstore.store-pool-size: 3
  pd:
    schedule.max-merge-region-size: 20

pd_servers:
  - host: 10.0.1.1

tidb_servers:
  - host: 10.0.1.1

tikv_servers:
  - host: 10.0.1.1

tiflash_servers:
  - host: 10.0.1.1

monitoring_servers:
  - host: 10.0.1.1

grafana_servers:
  - host: 10.0.1.1

# Deploy cluster
tiup cluster deploy tidb-prod v8.5.1 topo.yaml

# Start cluster
tiup cluster start tidb-prod

# Check cluster status
tiup cluster display tidb-prod

# Connect to TiDB
mysql -h 10.0.1.1 -P 4000 -u root

Production Deployment Essentials

  1. Deploy 3 PD nodes: Ensure scheduler high availability
  2. Deploy at least 3 TiKV nodes: Raft majority write
  3. Deploy 2+ TiDB nodes: Frontend load balancing
  4. Deploy TiFlash independently: Avoid I/O contention with TiKV
  5. SSD disks: TiKV data directory must use NVMe SSD
  6. Network: Inter-node latency < 0.5ms, bandwidth ≥ 10Gbps

MySQL Compatibility Checklist

TiDB is highly compatible with MySQL 5.7 / 8.0 protocol, but some differences require attention:

Compatible Features

Feature Support Notes
SELECT / INSERT / UPDATE / DELETE ✅ Fully supported
JOIN (all types) ✅ Fully supported
Subqueries ✅ Fully supported
Window functions ✅ Fully supported TiDB 6.0+
CTE (Common Table Expressions) ✅ Fully supported TiDB 6.0+
Prepared Statement ✅ Fully supported
JSON type ✅ Fully supported
UTF8 / UTF8MB4 ✅ Fully supported

Incompatible Features

Feature Status Workaround
Stored procedures ❌ Not supported Use application logic
Triggers ❌ Not supported Use application events
Foreign keys ⚠️ Syntax only, not enforced Ensure consistency in app
XA syntax ⚠️ Partial support Use TiDB internal 2PC
Auto-increment continuity ⚠️ Not guaranteed Use AUTO_ID_CACHE or sequences
SAVEPOINT ✅ Supported TiDB 6.2+
SELECT ... FOR UPDATE ⚠️ Pessimistic lock Must explicitly enable

Compatibility Verification Script

-- Check TiDB version and compatibility
SELECT tidb_version();

-- Check current transaction mode
SELECT @@tidb_txn_mode;

-- Check auto-increment behavior
SHOW VARIABLES LIKE 'auto_increment%';

-- Check charset support
SHOW CHARACTER SET WHERE Charset LIKE '%utf%';

💡 Use the JSON Formatter tool to validate TiDB JSON data structures.

SQL Optimization & Execution Plan Analysis

EXPLAIN and EXPLAIN ANALYZE

TiDB provides rich execution plan analysis tools:

-- View execution plan
EXPLAIN SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

-- View actual execution statistics (with time, row counts)
EXPLAIN ANALYZE SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

Key Execution Plan Operators

Operator Meaning Optimization Direction
TableFullScan Full table scan Add appropriate index
IndexRangeScan Index range scan Already good, check table lookup cost
IndexLookUp Index + table lookup Consider covering index
IndexReader Index read (no lookup) Covering index, optimal
HashJoin Hash join Check Build-side data size
MergeJoin Merge join Ensure both sides are sorted
IndexJoin Index nested join Good for small table driving large
StreamAgg Stream aggregation Already optimal
HashAgg Hash aggregation Check grouping cardinality
TopN TopN sort Already optimal
Sort Full sort Check if index order can be used

SQL Tuning Practical Examples

-- Example 1: Avoid full table scan
-- Problem: Low selectivity on status causes full scan
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- Fix: Add composite index
ALTER TABLE orders ADD INDEX idx_status_date (status, order_date);

-- Example 2: Covering index to eliminate table lookup
-- Problem: IndexLookUp has high lookup cost
EXPLAIN SELECT customer_id, order_date FROM orders WHERE status = 'shipped';
-- Fix: Covering index
ALTER TABLE orders ADD INDEX idx_cover (status, customer_id, order_date);

-- Example 3: Optimize JOIN order
-- Problem: HashJoin Build side too large
EXPLAIN ANALYZE SELECT *
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.category = 'electronics';
-- Fix: Add driving table index + update statistics
ANALYZE TABLE products;
ANALYZE TABLE order_items;

SQL Plan Management (SPM)

-- Create SQL Binding to pin optimal plan
CREATE SESSION BINDING FOR
  SELECT * FROM orders WHERE status = 'shipped'
USING
  SELECT * FROM orders USE INDEX (idx_status_date) WHERE status = 'shipped';

-- View existing Bindings
SHOW SESSION BINDINGS;

-- Drop Binding
DROP SESSION BINDING FOR
  SELECT * FROM orders WHERE status = 'shipped';

Distributed Index Strategies

Index design in distributed databases differs significantly from single-machine MySQL, requiring consideration of cross-Region cost and hotspot risk.

Index Design Principles

  1. Reduce Region scans: Index prefix should be dispersed to avoid concentration in few Regions
  2. Control index count: Each index adds write overhead and storage
  3. Prefer composite indexes: Reduce table lookups, one composite index replaces multiple single-column indexes
  4. Avoid overly wide indexes: Total index column width should be < 200 bytes
  5. Consider index placement: TiDB supports placing indexes on different storage engines

Index Placement Strategy (Placement Rules)

-- Place cold data index on HDD storage
ALTER TABLE orders ADD INDEX idx_create_time (create_time)
  PLACEMENT POLICY 'cold_storage';

-- Create placement policy
CREATE PLACEMENT POLICY cold_storage LEADER_CONSTRAINTS '[+disk=hdd]' FOLLOWER_CONSTRAINTS '[+disk=hdd]';

Invisible Indexes

-- Create invisible index (no query impact, write maintenance only)
ALTER TABLE orders ADD INDEX idx_test (customer_id) INVISIBLE;

-- Verify index is invisible
SHOW INDEXES FROM orders;

-- Set visible after confirming no performance impact
ALTER TABLE orders ALTER INDEX idx_test VISIBLE;

Hotspot Diagnosis & Resolution

Hotspots are the most common issue in distributed databases, causing uneven load on specific nodes.

Hotspot Identification

-- View hotspot Region distribution
SELECT STORE_ID, COUNT(*) AS region_count
FROM INFORMATION_SCHEMA.TIKV_REGION_STATUS
GROUP BY STORE_ID
ORDER BY region_count DESC;

-- View write hotspots
SELECT TABLE_NAME, INDEX_NAME, WRITTEN_BYTES, READ_BYTES
FROM INFORMATION_SCHEMA.TIKV_REGION_STATUS
WHERE WRITTEN_BYTES > 1048576
ORDER BY WRITTEN_BYTES DESC
LIMIT 20;

Common Hotspot Scenarios & Solutions

Scenario 1: Auto-Increment ID Hotspot

-- Problem: AUTO_INCREMENT causes writes to concentrate in a single Region
CREATE TABLE logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  content TEXT,
  created_at TIMESTAMP
);

-- Solution 1: Use SHARD_ROW_ID_BITS to scatter
CREATE TABLE logs (
  id BIGINT AUTO_INCREMENT,
  content TEXT,
  created_at TIMESTAMP,
  PRIMARY KEY (id)
) SHARD_ROW_ID_BITS = 4;

-- Solution 2: Use timestamp as primary key prefix
CREATE TABLE logs (
  created_at TIMESTAMP,
  id BIGINT AUTO_INCREMENT,
  content TEXT,
  PRIMARY KEY (created_at, id)
) SHARD_ROW_ID_BITS = 4;

Scenario 2: Index Hotspot

-- Problem: Time index writes concentrate on tail Region
ALTER TABLE orders ADD INDEX idx_created (created_at);

-- Solution: Use SHARD_ROW_ID_BITS + PRE_SPLIT_REGIONS
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(64),
  created_at TIMESTAMP,
  amount DECIMAL(10,2)
) SHARD_ROW_ID_BITS = 4
  PRE_SPLIT_REGIONS = 4;

Scenario 3: Small Table Full Hotspot

-- Problem: Small table data in single Region, high-concurrency read hotspot
-- Solution: Use Follower Read to distribute read pressure
SET @@tidb_replica_read = 'leader-and-follower';

-- Or enable at session level
SET SESSION tidb_replica_read = 'follower';

PD Scheduling Optimization

# View current scheduling config
tiup ctl:v8.5.1 pd -u http://10.0.1.1:2379 config show all

# Adjust hotspot scheduling parameters
tiup ctl:v8.5.1 pd -u http://10.0.1.1:2379 config set hot-region-schedule-limit 8
tiup ctl:v8.5.1 pd -u http://10.0.1.1:2379 config set balance-hot-region-schedule-limit 4

# Enable Region Merge to reduce empty Regions
tiup ctl:v8.5.1 pd -u http://10.0.0.1:2379 config set enable-merge-region true

TiFlash Real-Time Analytics in Practice

TiFlash is TiDB's columnar storage engine, enabling real-time OLAP analytics without ETL.

Enable TiFlash Sync

-- Enable TiFlash sync for entire database
ALTER DATABASE orders_db SET TIFLASH REPLICA 2;

-- Enable TiFlash sync for single table (2 replicas)
ALTER TABLE orders SET TIFLASH REPLICA 2;

-- Check sync progress
SELECT * FROM INFORMATION_SCHEMA.TIFLASH_REPLICA_STATUS;

-- Sync specific columns to TiFlash (reduce sync overhead)
ALTER TABLE orders SET TIFLASH REPLICA 1
  INCLUDE (order_id, customer_id, amount, order_date);

MPP Mode Queries

-- Force TiFlash MPP engine
SET @@tidb_isolation_read_engines = 'tiflash';
SET @@tidb_enforce_mpp = 1;

-- Real-time sales analytics
SELECT
  DATE(order_date) AS dt,
  p.category,
  COUNT(*) AS order_count,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-01'
GROUP BY DATE(order_date), p.category
ORDER BY dt DESC, revenue DESC;

-- Restore default engine selection
SET @@tidb_isolation_read_engines = 'tidb,tikv,tiflash';
SET @@tidb_enforce_mpp = 0;

HTAP Smart Routing

-- TiDB auto-routes: small queries to TiKV, large queries to TiFlash
-- No manual configuration needed, optimizer decides automatically

-- Check which engine a query actually uses
EXPLAIN ANALYZE SELECT COUNT(*), SUM(amount)
FROM orders WHERE order_date >= '2026-01-01';
-- Observe whether operator contains tiflash information

Backup & Disaster Recovery (BR)

BR (Backup & Restore) is TiDB's native distributed backup and restore tool.

Full Backup & Restore

# Full backup to S3
tiup br:v8.5.1 backup full \
  --pd "10.0.1.1:2379" \
  --storage "s3://tidb-backup/full-20260611" \
  --s3.region "us-east-1" \
  --send-credentials-to-tikv true \
  --concurrency 4

# Full restore
tiup br:v8.5.1 restore full \
  --pd "10.0.1.1:2379" \
  --storage "s3://tidb-backup/full-20260611" \
  --s3.region "us-east-1"

Incremental Backup

# Incremental backup (based on last backup TS)
tiup br:v8.5.1 backup full \
  --pd "10.0.1.1:2379" \
  --storage "s3://tidb-backup/incr-20260611" \
  --last-backup-ts 450335849738604544

Database/Table-Level Backup

# Database-level backup
tiup br:v8.5.1 backup db \
  --db "orders_db" \
  --pd "10.0.1.1:2379" \
  --storage "s3://tidb-backup/orders-db-20260611"

# Table-level backup
tiup br:v8.5.1 backup table \
  --db "orders_db" \
  --table "orders" \
  --pd "10.0.1.1:2379" \
  --storage "s3://tidb-backup/orders-table-20260611"

Point-in-Time Recovery (PITR)

# Restore to a specific point in time
tiup br:v8.5.1 restore full \
  --pd "10.0.1.1:2379" \
  --storage "s3://tidb-backup/full-20260611" \
  --restored-ts "2026-06-11T14:30:00+08:00"

💡 Use the Hash Encryption tool to verify backup data integrity checksums.

Grafana Monitoring System

TiDB clusters come with a complete Prometheus + Grafana monitoring system.

Core Monitoring Dashboards

Dashboard Key Metrics Alert Threshold Suggestion
TiDB Overview QPS, connections, slow queries Slow query > 1s
TiKV Details CPU, IO, Region count CPU > 80%
PD Details Scheduling tasks, Region distribution Schedule delay > 5min
TiFlash Details Sync delay, MPP task count Sync delay > 10s
BR Backup progress, duration Backup failure
Node Exporter Disk, memory, network Disk usage > 85%

Key Metrics Details

# TiDB key metrics
tidb_server_query_duration:
  description: "Query latency P99"
  alert: "> 500ms for 5 minutes"

tidb_server_slow_query:
  description: "Slow query count"
  alert: "> 10/min"

tikv_grpc_message_duration:
  description: "TiKV gRPC request latency"
  alert: "P99 > 200ms"

tikv_engine_write_bytes:
  description: "Write throughput"
  monitoring: "Watch for sudden drops"

pd_scheduler_balance_region:
  description: "Region scheduling speed"
  alert: "Zero for over 10 minutes"

tiflash_sync_apply_duration:
  description: "TiFlash data sync delay"
  alert: "P99 > 10s"

Custom Alert Rules

# Prometheus alert rule example
groups:
  - name: tidb_alerts
    rules:
      - alert: TiDBHighQPS
        expr: sum(rate(tidb_server_handle_query_duration_seconds_count[1m])) > 50000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "TiDB QPS too high"
          description: "Current QPS exceeds 50000 for 5 minutes"

      - alert: TiKVHighWriteLag
        expr: histogram_quantile(0.99, rate(tikv_grpc_message_duration_seconds_bucket{type="write"}[5m])) > 0.5
        for: 3m
        labels:
          severity: critical
        annotations:
          summary: "TiKV write latency too high"
          description: "P99 write latency exceeds 500ms"

Common Errors & Troubleshooting

Error 1: Region Read/Write Timeout

Error: region is unavailable, wait for a while and retry

Troubleshooting Steps:

# 1. Check Region status
tiup ctl:v8.5.1 pd -u http://10.0.1.1:2379 region check miss-peer

# 2. Check TiKV health
tiup ctl:v8.5.1 pd -u http://10.0.1.1:2379 store

# 3. View slow logs
tiup cluster audit tidb-prod --limit 50

Error 2: Write Conflict

Error: Write conflict, txn start_ts conflicts with another txn

Solution:

-- Adjust transaction retry parameters
SET @@tidb_retry_limit = 20;
SET @@tidb_txn_retry_interval = 100; -- milliseconds

-- Check conflict rate
SELECT * FROM INFORMATION_SCHEMA.TIDB_TRX
  WHERE state = 'LockRcolliding' LIMIT 10;

Error 3: OOM (Out of Memory)

Error: Out Of Memory Quota!

Solution:

-- Set query memory limit
SET @@tidb_mem_quota_query = 1073741824; -- 1GB

-- Enable resource control
CREATE RESOURCE GROUP rg_report
  RU_PER_SEC = 500
  PRIORITY = LOW;

-- Bind user to resource group
ALTER USER report_user RESOURCE GROUP rg_report;

Error 4: TiFlash Sync Delay

-- Check sync progress
SELECT TABLE_ID, REPLICA_COUNT, PROGRESS, AVAILABLE
FROM INFORMATION_SCHEMA.TIFLASH_REPLICA_STATUS;

-- Check TiFlash node status
SELECT * FROM INFORMATION_SCHEMA.TIFLASH_TABLES
  WHERE REPLICA_AVAILABLE = 0;

Migrating from MySQL to TiDB

Migration Solution Comparison

Solution Use Case Downtime Complexity
DM full+incremental Large-scale migration Near-zero Medium
Dumpling + TiDB Lightning One-time migration Hours Low
mysqldump + source Small data volume Hours Low 
# dm-task.yaml - DM migration task config
name: mysql-to-tidb
task-mode: all
target-database:
  host: "10.0.2.1"
  port: 4000
  user: "root"
  password: ""

mysql-instances:
  - source-id: "mysql-replica-01"
    block-allow-list: "bw-rule-1"

block-allow-list:
  bw-rule-1:
    do-dbs: ["orders_db", "users_db"]
    ignore-tables:
      - db-name: "orders_db"
        tbl-name: "log_*"

# Start DM task
dmctl start-task dm-task.yaml

# Check task status
dmctl query-status mysql-to-tidb

# Verify data consistency
dmctl check-task dm-task.yaml

Pre-Migration Compatibility Check

-- Check stored procedure usage
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.routines
WHERE ROUTINE_SCHEMA NOT IN ('mysql', 'sys');

-- Check foreign key constraints
SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

-- Check triggers
SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE
FROM information_schema.triggers;

-- Check auto-increment columns
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, AUTO_INCREMENT
FROM information_schema.columns
WHERE EXTRA LIKE '%auto_increment%';

Performance Benchmarks

TPCC Benchmark

# Test OLTP performance with TPCC
git clone https://github.com/pingcap/go-tpc.git
cd go-tpc

# Prepare data
go-tpc tpcc prepare \
  --host 10.0.1.1 --port 4000 \
  --warehouses 1000 --threads 50

# Run benchmark
go-tpc tpcc run \
  --host 10.0.1.1 --port 4000 \
  --warehouses 1000 --threads 50 \
  --time 600s

# Cleanup
go-tpc tpcc cleanup \
  --host 10.0.1.1 --port 4000 \
  --warehouses 1000

TPC-H Benchmark (HTAP)

# Test OLAP performance with TPC-H
go-tpc tpch prepare \
  --host 10.0.1.1 --port 4000 \
  --scale 100 --threads 10

# Force TiFlash execution
go-tpc tpch run \
  --host 10.0.1.1 --port 4000 \
  --scale 100 --threads 10 \
  --time 300s \
  --engine tiflash

Typical Performance Reference

Scenario Node Config QPS/TPS P99 Latency
OLTP (TPCC) 3×TiDB + 3×TiKV 15,000 TPS < 50ms
OLAP (TPC-H) + 2×TiFlash 22 queries/5min Varies per query
Mixed HTAP Above config TP stable + AP 10x Independent

FAQ

Q1: Is TiDB suitable to replace MySQL sharding?

Yes, this is TiDB's most typical use case. TiDB provides transparent horizontal scaling without application-level sharding logic, while maintaining MySQL protocol compatibility for minimal migration cost. Strongly recommended when a single table exceeds 50 million rows or sharding ops complexity is too high.

Q2: What is TiDB's transaction isolation level?

TiDB defaults to RC (Read Committed) isolation level (v6.0+), and also supports RR (Repeatable Read) semantics. TiDB's RR is actually Snapshot Isolation (SI), which prevents phantom reads but has subtle differences from MySQL's RR in specific scenarios (e.g., current read behavior).

Q3: What is the optimal data scale for TiDB?

  • Single table: Tens of millions to tens of billions of rows; recommend TiDB when exceeding 50 million rows
  • Cluster total data: TB to PB scale
  • Region size: Default 96MB, recommended to keep near this value

Q4: How to choose between TiDB and CockroachDB?

Dimension TiDB CockroachDB
SQL compat MySQL PostgreSQL
HTAP Native support Not supported
Community China/APAC primarily Global
Cloud service TiDB Cloud CockroachDB Cloud

If your stack is MySQL-based and needs HTAP, choose TiDB; if PostgreSQL-based and OLTP only, choose CockroachDB.

Q5: How to handle large transactions in TiDB?

TiDB defaults to 100MB and 5000 rows per transaction. Large transaction solutions:

-- Adjust transaction limits (not recommended to set too high)
SET @@tidb_txn_entry_size_limit = 536870912;  -- 512MB
SET @@tidb_txn_total_size_limit = 1073741824; -- 1GB

-- Recommended: batch commits
-- Split large transactions into small ones in application, 1000-5000 rows per batch

Q6: What is TiDB's License?

TiDB Server, TiKV, and PD use Apache 2.0 fully open source. TiFlash is also open source since v8.0+. TiDB Cloud (Serverless/Dedicated) is a commercial service.

Summary

TiDB has become the benchmark product in the distributed database space in 2026. Its MySQL compatibility, HTAP capability, and cloud-native architecture make it an ideal choice for enterprise database upgrades. Key takeaways:

  1. Architecture: TiDB/TiKV/PD/TiFlash four components, compute-storage separation
  2. Deployment: TiUP one-click management, SSD and network requirements for production
  3. SQL tuning: Use EXPLAIN ANALYZE, master SPM for plan binding
  4. Index design: Consider distributed characteristics, avoid hotspot indexes
  5. Hotspot management: SHARD_ROW_ID_BITS scattering, Follower Read distribution
  6. HTAP: TiFlash columnar + MPP engine for real-time analytics
  7. Disaster recovery: BR supports full/incremental/PITR recovery strategies
  8. Monitoring: Complete Grafana system, key metrics must not be ignored
  9. Migration: DM incremental migration achieves near-zero downtime switchover

Try these browser-local tools — no sign-up required →

#TiDB#分布式数据库#NewSQL#MySQL兼容#教程