How TiDB v7 Upgrade Boosted Performance and Solved Real‑World Issues

1. Introduction

Since early 2020, the DeWu DBA team has been operating a self‑built TiDB cluster. Over five years, the cluster evolved through versions v4.0.9, v4.0.11, v5.1.1, v5.3.0, and finally stabilized at v5.3.3. Rapid business growth and increasing data volumes exposed stability and performance challenges, prompting a migration to TiDB v7.5.x.

2. TiDB Architecture

TiDB is a distributed relational database fully compatible with the MySQL protocol and ecosystem. Its advantages include:

Distributed architecture with seamless horizontal scaling and no need for sharding keys.

HTAP support, allowing simultaneous high‑concurrency transactions and real‑time analytical queries.

Complete MySQL compatibility, enabling drop‑in replacement in most scenarios.

Built‑in self‑healing high availability.

ACID transactions with RR and RC isolation levels.

The deployment uses SLB for load balancing, a single‑instance per node design, SSD storage for TiKV, and tailored server specifications for different components.

3. New Features in TiDB v7

Version 7 introduces stronger scalability, faster performance, and better resource utilization. SQL improvements enhance compatibility, flexibility, and ease of use. Network I/O is optimized with batch processing and additional push‑down operators. The Region scheduling algorithm has been refined, boosting stability and performance.

4. Upgrade Journey

Current Pain Points

Cluster version too low (latest production is v5.3.3, while v8.5.3 is the kernel latest; v7.5.x is the most stable for users).

TiCDC component instability in versions before v6.5.0.

Backup windows exceeding 8 hours, causing >30% load spikes.

Occasional cluster jitter and bugs in low‑version clusters.

Upgrade Approach

Both in‑place and migration upgrades are possible, but the team chose migration upgrade for its short business impact, greyscale rollback capability, and independence from version gaps.

Migration Upgrade Steps

Cluster research and sizing.

Prepare environment and validate the new cluster.

Perform traffic migration.

Destroy the old cluster after validation.

5. Issues Encountered

v7.5.x SQL tends to full‑table scans

Even with proper indexes on a table of 21.5 billion rows, the optimizer prefers full scans. After collecting statistics, the plan remained unchanged.

-- Query SQL
SELECT *
FROM fin_xxx_xxx
WHERE xxx_head_id = 1111111111111111
  AND xxx_type = 'XX0002'
  AND xxx_user_id = 11111111
  AND xxx_pay_way = 'XXX00000'
  AND is_del IN ('N', 'Y')
LIMIT 1;

Solutions:

Bind the SQL using SPM.

Set tidb_opt_prefer_range_scan = ON to prefer range scans.

v7.5.x Aggregation queries produce inaccurate plans

Aggregations on large tables no longer hit effective indexes, leading to dramatically longer execution times compared to v4.x/v5.x.

-- Query SQL
SELECT statistics_date, COUNT(1)
FROM merchant_assessment_xxx
WHERE create_time BETWEEN '2025-08-20 00:00:00' AND '2025-09-09 00:00:00'
GROUP BY statistics_date
ORDER BY statistics_date;

Solution: Set tidb_opt_objective = determinate to use stable statistics for plan generation.

6. Benefits of Upgrade

Performance improvements include:

~40% overall performance boost; complex SQL latency reduced.

Average RT improved from 16.9 ms to 9.36 ms (≈44.6% faster).

Backup time reduced by >50%.

TiCDC synchronization speed increased by tens of times.

High‑compression storage reduces data footprint to ~55% of MySQL’s three‑replica size.

New features such as TTL automatic data expiration further optimize storage and performance.

7. Reasons to Choose TiDB

TiDB excels in scenarios where MySQL falls short, including non‑sharded queries, heavy analytical workloads, high disk usage, and data skew. Its horizontal scalability and distributed processing address these challenges effectively.

8. Summary

The migration to TiDB v7.5.x delivered significant performance and stability gains, streamlined operations with better monitoring and diagnostics, and positioned the platform for future enhancements such as AI‑enabled features in v8.5.x. Ongoing research will continue to integrate the latest capabilities into production.