How TiDB Boosted Real-Time Reporting and HTAP with TiFlash: A Practical Upgrade Journey

Background

Implementing real‑time reporting and multi‑active data center architecture by synchronizing business databases.

TiDB Selection Advantages

Traditional MySQL multi‑source replication is simple but suffers from single‑point bottlenecks; TiDB’s distributed architecture offers superior high availability and performance scalability.

TiDB Architecture

TiDB Server : Stateless SQL layer exposing MySQL protocol, parses and optimizes queries, generates distributed execution plans, and forwards data requests to storage nodes (TiKV or TiFlash). Multiple instances can be load‑balanced.

PD (Placement Driver) Server : Manages cluster metadata, stores real‑time data distribution, provides dashboard, allocates transaction IDs, and schedules data movement. Deployed as an odd number of nodes for high availability.

Storage Nodes

TiKV Server : Distributed transactional key‑value store. Data is stored in Regions (key ranges). Supports Snapshot Isolation and automatic multi‑replica (default three) for high availability.

TiFlash : Columnar storage node that accelerates analytical workloads.

First Experience with TiDB (v2.1)

Deployed a 3‑TiKV + 1‑PD + 1‑TiDB cluster in early 2019, syncing business data in real time. Pain points included crude sync tools, transaction size limits, unsupported DDL operations, and insufficient support for MySQL reserved fields.

Upgrade to TiDB 4.0

After a year of reporting use, the team upgraded to TiDB 4.0, gaining several key improvements:

DM (Data Migration) tool for full‑load and incremental MySQL/MariaDB to TiDB migration, simplifying error handling and reducing operational cost.

TiUP package manager for component management, offline deployment, and streamlined operations.

Support for large transactions (up to 10 GB) and both optimistic and pessimistic transaction models.

New system tables under information_schema exposing topology, configuration, logs, hardware, OS, and slow‑query information.

TiFlash integration via Multi‑Raft Learner protocol, providing strong consistency between TiKV (row store) and TiFlash (column store) and enabling HTAP resource isolation.

Post‑upgrade, synchronization monitoring issues were resolved, and DML‑based filtering allowed TiDB to archive historical data, freeing up production storage.

Using TiFlash for HTAP

To reduce TiKV pressure from heavy reporting queries, TiFlash was introduced as an isolated storage engine, offering:

Simple, compact architecture with flexible activation of TiFlash for any online table.

Low deployment cost—only two servers needed for production.

Identical schema and data between online and analytical tables, allowing direct SQL queries without extra sync overhead.

Optimizer support for custom or automatic execution path selection.

Performance comparison showed TiFlash queries on time‑range conditions were about four times faster than TiKV.

Example query:

select IFNULL(sum(a.realPayOrderTotalAmt),0) as realPOTotalAmt, IFNULL(sum(a.orderUserCnt),0) as orderUserCnt from ( select   sum(case when sm.decimalext2 is null then sm.realpayordertotal else (sm.realcardpay + sm.decimalext2) - sm.realpayfreight end ) as realPOTotalAmt, count(DISTINCT sm.customerid) as orderUserCnt   from order.so_master sm inner join MemberMarketing.mm_send_history sh on sm.customerid=sh.user_id where sh.batch_id = 'dd17045eeff4b4bb419002f86813865' and sh.status=1 and sm.createdTime >=sh.send_time and sm.createdTime >='2021-04-12 08:34:41' and sm.createdTime <= concat(date_format(sh.send_time,'%Y-%m-%d'),' 23:59:59') and sm.orderStatus not in (0,120) and sm.cityFlag='001' union all select sum(case when sm.decimalext2 is null then sm.realpayordertotal else (sm.realcardpay + sm.decimalext2) - sm.realpayfreight end ) as realPOTotalAmt, count(DISTINCT sm.customerid) as orderUserCnt from order.so_master sm inner join MemberMarketing.mm_send_history sh on sm.customerid=sh.user_id where sh.batch_id = 'dd17045eeff4b4bb419002f86813865' and sh.status=1 and sm.createdTime >=sh.send_time and sm.createdTime >='2021-04-12 08:34:41' and sm.createdTime <= concat(date_format(sh.send_time,'%Y-%m-%d'),' 23:59:59') and sm.orderStatus not in (0,120) and sm.cityFlag='001' union all select sum(case when sm.decimalext2 is null then sm.realpayordertotal else (sm.realcardpay + sm.decimalext2) - sm.realpayfreight end ) as realPOTotalAmt, count(DISTINCT sm.customerid) as orderUserCnt  /*+ read_from_storage(tiflash[sm,sh]) */  from order.so_master sm inner join MemberMarketing.mm_send_history sh on sm.customerid=sh.user_id where sh.batch_id = 'dd17045eeff4b4bb419002f86813865' and sh.status=1 and sm.createdTime >=sh.send_time and sm.createdTime >='2021-04-12 08:34:41' and sm.createdTime <= concat(date_format(sh.send_time,'%Y-%m-%d'),' 23:59:59') and sm.orderStatus not in (0,120) and sm.cityFlag='001' ) as a;

TiKV query took 70 seconds, while TiFlash completed in 20 seconds, achieving nearly a 4× speedup.

Pitfalls and Optimization Tips After Using 4.0

DM upstream source switch : Migrating MySQL to Huawei Cloud RDS broke GTID continuity, requiring manual adjustment of binlog and GTID settings and cleaning checkpoint tables in dm_meta to resume sync.

Specifying TiFlash engine : Setting engines = ["tidb", "tiflash"] at the TiDB instance level was ineffective in version 4.0 due to a bug; upgrading to 4.0.2 resolved the issue.

DDL serialization : TiDB serializes DDL execution via a single owner node. While most DDLs finish within 1 second, ADD INDEX on large tables can block other DDLs, so schedule such operations during low‑traffic periods.

Business‑side isolation : Because TiDB Server is stateless, separating reporting workloads from business workloads allows independent configuration tuning and resource control.