TiDB: The MySQL-Compatible Distributed SQL Database

TIFLASH STORAGE ARCHITECTURE
════════════════════════════════════════════════════════════════════
 
TIDB CLUSTER WITH TIFLASH
─────────────────────────
 
┌─────────────────────────────────────────────────────────────────┐
│                         TiDB Servers                             │
│     (SQL Layer - decides whether to use TiKV or TiFlash)        │
└─────────────────────────────────────────────────────────────────┘
                    │                           │
                    ▼                           ▼
    ┌───────────────────────────┐   ┌───────────────────────────┐
    │      TiKV CLUSTER         │   │     TiFlash CLUSTER       │
    │    (Row Storage)          │   │     (Columnar Storage)    │
    ├───────────────────────────┤   ├───────────────────────────┤
    │                           │   │                           │
    │  Region 1 [L]  Region 2[F]│   │  Region 1 [Learner]      │
    │  Region 3 [F]  Region 4[L]│   │  Region 2 [Learner]      │
    │  ...                      │   │  Region 3 [Learner]      │
    │                           │   │  ...                      │
    │  • Point lookups          │──►│  • Analytical queries     │
    │  • Small transactions     │   │  • Full scans             │
    │  • Raft leaders/followers │   │  • Aggregations           │
    │                           │   │  • Raft learner only      │
    └───────────────────────────┘   └───────────────────────────┘
                                            │
                                    Raft Replication
                                    (async, learner)
                                            │
                                    ┌───────┴───────┐
                                    │               │
                                    ▼               ▼
 
TIFLASH NODE INTERNAL STRUCTURE
───────────────────────────────
 
┌─────────────────────────────────────────────────────────────────┐
│                      TiFlash Node                                │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │                    Delta Layer (Row Format)                 │ │
│  │  Recent writes, stored in row format for fast ingestion    │ │
│  │  ┌─────────────────────────────────────────────────────────┐│ │
│  │  │ INSERT row1: (id=1001, name="Alice", amount=150.00)    ││ │
│  │  │ INSERT row2: (id=1002, name="Bob", amount=200.00)      ││ │
│  │  │ UPDATE row 1001: amount = 175.00                       ││ │
│  │  └─────────────────────────────────────────────────────────┘│ │
│  └────────────────────────────────────────────────────────────┘ │
│                              │ Periodic Merge                    │
│                              ▼                                   │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │                 Main Layer (Columnar Format)                │ │
│  │  Historical data, stored in columnar format with encoding  │ │
│  │                                                             │ │
│  │  Column: id        [1, 2, 3, 4, ..., 1000000]  (compressed) │ │
│  │  Column: name      ["Alice", "Bob", "Carol", ...] (dict)   │ │
│  │  Column: amount    [95.0, 150.0, 200.0, ...] (run-length)  │ │
│  │  Column: timestamp [2024-01-01, 2024-01-01, ...] (delta)   │ │
│  │                                                             │ │
│  │  Compression: LZ4, ZSTD, Dictionary, Run-Length, Delta      │ │
│  │  Typical compression ratio: 5-20x                           │ │
│  └────────────────────────────────────────────────────────────┘ │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Enable TiFlash replica for a table
ALTER TABLE orders SET TIFLASH REPLICA 1;
 
-- Enable TiFlash replica for partition tables
ALTER TABLE events SET TIFLASH REPLICA 2;  -- 2 replicas for HA
 
-- Check TiFlash replica status
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    REPLICA_COUNT,
    LOCATION_LABELS,
    AVAILABLE,
    PROGRESS
FROM information_schema.tiflash_replica;
 
/*
+--------------+------------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+---------------+-----------------+-----------+----------+
| mydb         | orders     | 1             | NULL            | 1         | 1.0      |
| mydb         | events     | 2             | NULL            | 1         | 1.0      |
+--------------+------------+---------------+-----------------+-----------+----------+
*/
 
-- PROGRESS = 1.0 means replication is complete
-- AVAILABLE = 1 means TiFlash can serve queries
 
-- Remove TiFlash replica (rare, usually for cost reduction)
ALTER TABLE logs SET TIFLASH REPLICA 0;

RAFT LEARNER REPLICATION FLOW
════════════════════════════════════════════════════════════════════
 
WRITE: INSERT INTO orders VALUES (1001, 'Alice', 150.00, NOW());
 
STEP 1: Normal Raft Commit (TiKV only)
───────────────────────────────────────
 
Client
   │
   └──► TiKV Leader (Region 42)
           │
           ├──► TiKV Follower 1:  AppendLog ─► ACK
           ├──► TiKV Follower 2:  AppendLog ─► ACK
           │         └── Majority achieved (2 of 2 followers)
           │
           └──► Commit & Apply ─► Response to Client (SUCCESS)
                     │
                     │ (Client returns here, write complete)
 
 
STEP 2: Async Learner Replication (TiFlash)
───────────────────────────────────────────
 
           ... milliseconds later ...
 
TiKV Leader (Region 42)
   │
   └──► TiFlash Learner:  AppendLog (async)
                              │
                              ▼
                       ┌──────────────┐
                       │ Raft Log     │
                       │ Received     │
                       └──────┬───────┘
                              │
                              ▼
                       ┌──────────────┐
                       │ Apply to     │
                       │ Delta Layer  │ (row format for fast apply)
                       └──────┬───────┘
                              │
                              ▼ (background, periodic)
                       ┌──────────────┐
                       │ Merge to     │
                       │ Main Layer   │ (columnar format)
                       └──────────────┘
 
 
REPLICATION LAG CHARACTERISTICS
───────────────────────────────
 
Typical Lag:
  - Normal operations: 10-100ms
  - Under heavy write load: 100ms - 1s
  - Catching up after TiFlash restart: seconds to minutes
 
Query Implications:
  - Strong reads (read at specific timestamp): May wait for TiFlash to catch up
  - Weak reads (read latest in TiFlash): Slightly stale, no waiting
  - Default: TiDB uses strong reads for consistency

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Check TiFlash replication lag
SELECT 
    ADDRESS,
    STORE_ID,
    REGION_COUNT,
    --- Learner safe TS indicates how far TiFlash has caught up
    TIFLASH_VERSION
FROM information_schema.tiflash_cluster_info;
 
-- Default: Strong consistency (waits for TiFlash to catch up)
SELECT COUNT(*), SUM(amount) 
FROM orders 
WHERE order_date = CURDATE();
-- This will wait if TiFlash hasn't received today's latest writes
 
-- Force TiFlash execution with hint
SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */ 
    customer_id, 
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id
HAVING total_spent > 1000;
 
-- For dashboards where slight staleness is acceptable
-- Set tiflash_allow_read_stale_data for the session
SET SESSION tiflash_allow_stale_read = ON;
 
-- Or use AS OF TIMESTAMP for point-in-time reads
SELECT COUNT(*) FROM orders 
AS OF TIMESTAMP TIMESTAMPADD(SECOND, -60, NOW());
-- Reads data as of 60 seconds ago (definitely available in TiFlash)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

-- EXPLAIN shows which engine TiDB will use
 
-- Example 1: Point lookup → TiKV
EXPLAIN SELECT * FROM orders WHERE id = 12345;
/*
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| Point_Get               | 1.00     | root      | table:orders  | handle:12345                   |
+-------------------------+----------+-----------+---------------+--------------------------------+
   ↑ task=root means TiKV (point get is always TiKV)
*/
 
-- Example 2: Aggregation → TiFlash (automatic)
EXPLAIN SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;
/*
+----------------------------------+------------+-------------------+---------------+--------------------------------+
| id                               | estRows    | task              | access object | operator info                  |
+----------------------------------+------------+-------------------+---------------+--------------------------------+
| HashAgg                          | 50000.00   | root              |               | group by:orders.customer_id    |
| └─TableReader                    | 1000000.00 | root              |               | data:ExchangeSender            |
|   └─ExchangeSender               | 1000000.00 | mpp[tiflash]      |               | ExchangeType: PassThrough      |
|     └─HashAgg                    | 50000.00   | mpp[tiflash]      |               | group by:orders.customer_id    |
|       └─TableFullScan            | 1000000.00 | mpp[tiflash]      | table:orders  | keep order:false               |
+----------------------------------+------------+-------------------+---------------+--------------------------------+
   ↑ task=mpp[tiflash] indicates TiFlash execution with MPP (Massively Parallel Processing)
*/
 
-- Example 3: Hybrid - Join TiFlash aggregation with TiKV lookup
EXPLAIN SELECT 
    c.name,
    c.email,
    stats.order_count,
    stats.total_spent
FROM customers c
INNER JOIN (
    SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_spent
    FROM orders
    WHERE created_at >= '2024-01-01'
    GROUP BY customer_id
    HAVING COUNT(*) > 10
) stats ON c.id = stats.customer_id;
/*
+------------------------------------------+----------+-------------------+---------------+---------------------------+
| id                                       | estRows  | task              | access object | operator info             |
+------------------------------------------+----------+-------------------+---------------+---------------------------+
| Projection                               | 1000.00  | root              |               | ...                       |
| └─HashJoin                               | 1000.00  | root              |               | inner join                |
|   ├─TableReader(Build)                   | 1000.00  | root              |               | table:customers           |
|   │ └─TableFullScan                      | 1000.00  | cop[tikv]         | table:c       |                           |
|   └─TableReader(Probe)                   | 1000.00  | root              |               | data:ExchangeSender       |
|     └─...                                |          | mpp[tiflash]      |               | aggregation on TiFlash    |
+------------------------------------------+----------+-------------------+---------------+---------------------------+
   ↑ cop[tikv] for customer lookup, mpp[tiflash] for order aggregation
*/
 
-- Force specific engine with hints
-- Force TiFlash
SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */ 
    SUM(amount) FROM orders;
 
-- Force TiKV  
SELECT /*+ READ_FROM_STORAGE(TIKV[orders]) */
    SUM(amount) FROM orders;
 
-- For most queries, let the optimizer decide (it's usually right)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

-- Real-time revenue dashboard (runs every minute)
SELECT 
    DATE_FORMAT(ordered_at, '%Y-%m-%d %H:00') as hour,
    COUNT(*) as orders,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM orders
WHERE ordered_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY hour
ORDER BY hour DESC;
 
-- Query hits TiFlash (automatic), returns in ~100ms
-- Same query on TiKV would take 5-10 seconds (full scan)
 
-- Customer segmentation (would traditionally require data warehouse)
WITH customer_metrics AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as lifetime_value,
        DATEDIFF(NOW(), MAX(ordered_at)) as days_since_last_order,
        DATEDIFF(NOW(), MIN(ordered_at)) as customer_age_days
    FROM orders
    GROUP BY customer_id
)
SELECT
    CASE 
        WHEN lifetime_value > 10000 AND days_since_last_order < 30 THEN 'VIP Active'
        WHEN lifetime_value > 10000 THEN 'VIP At-Risk'
        WHEN lifetime_value > 1000 AND days_since_last_order < 60 THEN 'Regular Active'
        WHEN lifetime_value > 1000 THEN 'Regular At-Risk'
        ELSE 'Occasional'
    END as segment,
    COUNT(*) as customer_count,
    SUM(lifetime_value) as segment_value,
    AVG(order_count) as avg_orders
FROM customer_metrics
GROUP BY segment;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- Fraud detection: Find accounts with unusual activity in last hour
-- This needs OLAP (aggregate analysis) + OLTP (immediate action)
 
WITH hourly_patterns AS (
    -- TiFlash: Analyze all transactions in last hour
    SELECT 
        account_id,
        COUNT(*) as txn_count,
        SUM(amount) as total_amount,
        COUNT(DISTINCT merchant_id) as unique_merchants,
        COUNT(DISTINCT country_code) as countries_used
    FROM transactions
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY account_id
),
historical_patterns AS (
    -- TiFlash: Get 30-day averages for comparison
    SELECT 
        account_id,
        AVG(daily_txns) as avg_daily_txns,
        STDDEV(daily_txns) as stddev_txns,
        AVG(daily_amount) as avg_daily_amount
    FROM (
        SELECT 
            account_id,
            DATE(created_at) as day,
            COUNT(*) as daily_txns,
            SUM(amount) as daily_amount
        FROM transactions
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY account_id, day
    ) daily
    GROUP BY account_id
)
SELECT 
    h.account_id,
    h.txn_count as hourly_txns,
    h.total_amount as hourly_amount,
    h.countries_used,
    p.avg_daily_txns,
    -- Flag if hourly activity exceeds 50% of average daily
    CASE WHEN h.txn_count > p.avg_daily_txns * 0.5 THEN 'HIGH_FREQUENCY' ELSE 'NORMAL' END as frequency_flag,
    -- Flag if multiple countries in one hour
    CASE WHEN h.countries_used > 2 THEN 'MULTI_GEO' ELSE 'NORMAL' END as geo_flag
FROM hourly_patterns h
JOIN historical_patterns p ON h.account_id = p.account_id
WHERE h.txn_count > p.avg_daily_txns * 0.5  -- Anomaly threshold
   OR h.countries_used > 2;  -- Geographic anomaly
 
-- Results feed into TiKV-based blocking/alerting system

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- Monitor TiFlash status and performance
 
-- TiFlash cluster overview
SELECT * FROM information_schema.tiflash_cluster_info;
 
-- Tables with TiFlash replicas
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    REPLICA_COUNT,
    AVAILABLE,
    PROGRESS
FROM information_schema.tiflash_replica
WHERE REPLICA_COUNT > 0;
 
-- Check if TiFlash is being used (query history)
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY customer_id;
 
-- Look for "tiflash" in the task column:
/*
+----------------------------------+----------+---------+-----------+-------------------+
| id                               | estRows  | actRows | task      | access object     |
+----------------------------------+----------+---------+-----------+-------------------+
| HashAgg                          | 50000.00 | 48721   | root      |                   |
| └─TableReader                    | 50000.00 | 48721   | root      |                   |
|   └─ExchangeSender               | 50000.00 | 48721   | mpp[tiflash]                    | ← TiFlash/MPP
+----------------------------------+----------+---------+-----------+-------------------+
*/
 
-- TiFlash-specific metrics (in Prometheus/Grafana)
-- tiflash_proxy_apply_log_duration_seconds: Replication lag
-- tiflash_system_current_metric_Region_Count: Regions in TiFlash
-- tiflash_coprocessor_executor_count: Query processing stats