Mastering TiDB Performance: Layered and Scenario‑Based Optimization Strategies

Why Choose Performance Optimization as the Theme?

Before TUG's corporate tour to 58.com, as the North China leader of TUG I selected TiDB performance optimization as the main direction because many cases on the Asktug site and offline exchanges involve painful post‑mortems of TiDB incidents, where problems are diagnosed via official docs, forum discussions, or PingCAP experts, and then solved with optimization plans.

This corporate tour invites technical experts from 58.com, Zhihu, Li Auto, and PingCAP to share TiDB performance optimization practices.

My Understanding of Optimization

Optimization is an essential topic for any database and can be divided into two broad categories: layer‑based optimization and scenario‑based optimization.

Layer‑Based Optimization

It is called layer‑based because optimization progresses through successive layers: hardware, software, and SQL.

Hardware CPU: selecting the right CPU and tuning parameters for maximum performance. Network card: choosing 10 GbE vs. 1 GbE and adjusting network‑related settings. Disk: configuring SAS, SATA SSD, or PCIe‑SSD, and tuning I/O scheduler, huge pages, etc. Memory: setting TiDB memory usage and disabling shared memory when appropriate.

Software Operating System: Linux kernel tuning and OS version considerations. TiDB Cluster Parameters: tidb: adjust TiDB server parameters. pd: tune PD scheduling parameters. tikv: configure compression, region size, and other TiKV settings. Dynamic adjustments via SHOW VARIABLES or editing config files.

SQL Optimization Foundational design: proper table schema and column choices. Index usage: leveraging indexes remains a timeless optimization technique. SQL rewriting: different query formulations (e.g., subquery rewrites) affect performance. SQL binding: applying bindings when reaching solution stages. Statistics: TiDB relies on statistics for index selection; inaccurate stats can lead to poor plans.

Scenario‑Based Optimization

Different companies have distinct workloads, so optimization points vary. Common scenarios include read‑hotspot tuning, write‑hotspot tuning, and specific‑case adjustments such as DDL acceleration, KV load balancing, OLAP query handling, ETL bottlenecks, and large‑query throttling.

Read Optimization Read hotspots: frequent issue on Asktug. SQL execution plan stability. Tiflash push‑down: functions not pushed down may degrade performance. High SQL latency caused by various factors. TiDB server OOM: large queries consuming excessive memory.

Write Optimization Write hotspots: also common on Asktug. Pessimistic vs. optimistic transactions. Primary key choices: auto‑increment vs. auto‑random. No primary key: sharding row bits considerations. Distributed transactions: 2PC or asynchronous commit (1PC).

Specific‑Scenario Tuning Slow DDL execution: accelerating index addition. KV load imbalance: understanding PD scheduling parameters. OLAP SQL issues: JOIN + GROUP BY, MPP concepts. ETL scenarios: batch read/write performance. Large query limits: killing queries or setting statement quotas.

Key Points

The purpose of this outline is to surface the many possible questions and directions related to “optimization.” Different people have different understandings, so open discussion is encouraged. Please feel free to join the TUG‑58 corporate tour and share your insights.