Performance Tuning

Performance tuning in TiDB involves query optimization, cluster configuration, and using advanced features.

1. Query Optimization

Use EXPLAIN

-- Show the execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Detailed analysis
EXPLAIN ANALYZE
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.total > 500000;

Optimize Indexes

-- Make sure the index is used
CREATE INDEX idx_email ON users (email);

-- Use a hint if needed
SELECT /*+ USE_INDEX(users, idx_email) */ * FROM users WHERE email = '[email protected]';

Avoid SELECT *

-- ❌ Slow
SELECT * FROM large_table;

-- ✅ Fast
SELECT id, name, email FROM large_table;

2. Cluster Configuration

TiDB Server

[tidb]
# Size of the cached binlog
binlog-commit-cache-size = 65536

# Number of goroutines for DDL
ddl-worker-count = 16

TiKV

[tikv]
# Block cache size
storage.block-cache.capacity = "8GB"

# Number of concurrent readers
readpool.storage.high-concurrency = 8
readpool.coprocessor.high-concurrency = 8

3. Feature Optimization

TiFlash for OLAP

-- Enable a TiFlash replica
ALTER TABLE orders SET TIFLASH REPLICA 1;

-- Force the query to TiFlash
SELECT /*+ read_from_storage(tiflash[orders]) */
  category, SUM(amount)
FROM orders
GROUP BY category;

MPP Mode

-- Enable MPP mode
SET tidb_enforce_mpp = ON;

-- Query using MPP
SELECT /*+ MPP() */ category, SUM(amount)
FROM orders
GROUP BY category;

Async Commit

-- Enable async commit (default ON)
SET GLOBAL tidb_enable_async_commit = ON;

4. Schema Design

Use AUTO_RANDOM

-- Anti-hotspot primary key
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    amount DECIMAL(12, 2)
);

Partitioning

-- Partitioning for queries filtered by date
CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_RANDOM,
    log_date DATE,
    message TEXT
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

5. Monitoring Performance

• TiDB Dashboard — SQL statement analysis
• Grafana — real-time metrics
• Slow Query Log — identify slow queries
• Continuous Profiling — CPU/memory analysis

6. Best Practices

In the next chapter, we will cover the TiDB cheatsheet.