TiDB Out of Memory (OOM) Errors: Root Cause Analysis & Memory Tuning Guide

TiDB OOM Errors tend to strike at the worst possible time — during peak traffic, a large data migration, or at 2 AM when no one is watching. The immediate fix is usually simple: restart TiDB. The harder part is troubleshooting TiDB out of memory conditions well enough to understand what actually caused the OOM and making sure it does not happen again.

This guide explains how TiDB uses memory, what causes OOM conditions, how to diagnose them, and which configuration parameters and SQL patterns can help prevent future incidents. For the authoritative reference, see PingCAP's official TiDB memory control documentation.

Not sure where to start?

Repeated OOM incidents usually point to something deeper than a single bad query. Common contributing factors include:

• Undersized hardware
• Missing memory limits
• Excessive query concurrency
• Poor query plans
• Large transactions
• Stale table statistics
• Memory-heavy SQL operators

Mafiree's TiDB Performance and Query Optimization Services can help identify the root cause through configuration reviews, query optimization, and performance audits.

There Are Two Different TiDB OOM Situations

Before troubleshooting an OOM event, determine which type of OOM occurred. The response is different depending on whether the operating system killed TiDB or whether TiDB cancelled an individual query.

How TiDB Memory Controls Work

SET GLOBAL tidb_memory_usage_alarm_ratio = 0.7;

This allows TiDB to warn you before memory usage reaches a critical level.

First: Confirm What Actually Happened

Before changing any configuration, confirm whether the OOM was caused by the operating system or TiDB's own memory manager.

dmesg -T | grep tidb-server

What Usually Causes TiDB OOM Errors?

Most TiDB OOM errors stem from one or more of the following causes.

The first two causes are responsible for a large proportion of production OOM events.

SQL Patterns That Consume Large Amounts of Memory

Configuration changes can help, but they cannot completely protect a fundamentally expensive query. TiDB query optimization at the SQL level is often the real fix — the following patterns commonly appear in OOM post-mortems.

1. HashJoin Against a Large Table

A HashJoin loads the inner side of the join into an in-memory hash table before it starts matching rows. If the inner table has millions of rows, a single query can consume several gigabytes of memory. Run EXPLAIN and look for a HashJoin with a very large estRows on the inner side. If you see it, consider hinting toward MergeJoin instead — it uses far less memory because it works with sorted streams rather than building a large in-memory hash table.

-- check what the planner chose
EXPLAIN SELECT * FROM orders JOIN order_items ON orders.id = order_items.order_id;
-- if HashJoin shows estRows in the millions on the inner side, that's the problem

-- nudge it toward a less memory-hungry join
SELECT /*+ MERGE_JOIN(orders, order_items) */
  orders.id, order_items.quantity
FROM orders
JOIN order_items ON orders.id = order_items.order_id
WHERE orders.created_at > '2024-01-01';

2. HashAgg on Large Grouped Datasets

HashAgg is fast because it can process data in parallel, but each worker may maintain its own hash table. With a large number of distinct grouping values, memory usage can grow rapidly. StreamAgg processes rows in sorted order and generally uses less memory — a reasonable trade-off for memory-constrained workloads.

SELECT /*+ STREAM_AGG() */ region, SUM(revenue)
FROM sales
GROUP BY region;

3. Stale Statistics Causing Bad Query Plans

If TiDB's statistics are outdated, the optimizer might estimate 1,000 rows for a scan that actually returns 10 million, and pick algorithms suited for a small dataset. Check the health of your statistics regularly, especially on tables that get a lot of writes:

SHOW STATS_HEALTHY;

-- for high-write tables, refresh statistics when necessary
ANALYZE TABLE orders;
ANALYZE TABLE order_items;

Stale statistics can lead to bad plans, and bad plans can directly contribute to excessive memory usage.

4. Large Transactions Holding Too Much Memory

TiDB's transaction model caches all write operations in memory before commit. A transaction modifying millions of rows may consume two to three times — or potentially more — than the actual size of the data involved. Break large bulk operations into smaller batches, or look at the tidb_dml_type variable (set to "bulk") or non-transactional DML for cases that specifically need this.

-- instead of one giant delete:
-- DELETE FROM logs WHERE created_at < '2024-01-01';

-- loop this until affected rows = 0, sleep briefly between rounds
DELETE FROM logs
WHERE created_at < '2024-01-01'
LIMIT 10000;

TiDB Memory Parameters Worth Configuring

TiDB memory protection works in layers — per-query limits, per-instance memory limits, and operating-system or cgroup limits. Each layer should be configured deliberately as part of any TiDB memory tuning strategy; relying on the defaults in production is risky.

TiDB Disk Spill: Let TiDB Use Disk Instead of RAM

TiDB supports spilling intermediate execution data to disk when memory pressure becomes high. Disk spill behavior is controlled by tidb_mem_quota_query, tidb_enable_tmp_storage_on_oom, tmp-storage-path, and tmp-storage-quota.

Disk spill support has expanded across TiDB versions — HashAgg spill support, in particular, has improved in newer releases. When running a modern TiDB version, verify the exact behavior for your release.

View TiDB Memory Usage Through INFORMATION_SCHEMA

TiDB provides memory-related system tables for monitoring current and historical memory usage. To view current and historical memory usage, query:

• INFORMATION_SCHEMA.MEMORY_USAGE
• INFORMATION_SCHEMA.CLUSTER_MEMORY_USAGE
• INFORMATION_SCHEMA.MEMORY_USAGE_OPS_HISTORY
• INFORMATION_SCHEMA.CLUSTER_MEMORY_USAGE_OPS_HISTORY

The OPS_HISTORY tables retain the latest 50 records per instance. These tables are useful for identifying which operations consumed memory and investigating OOM events after they occur.

Production Readiness Checklist

Before putting a TiDB cluster into production, verify the following.

Memory Configuration

• Set tidb_server_memory_limit explicitly
• Configure tidb_mem_quota_query based on workload
• Ensure tidb_mem_oom_action is set to CANCEL
• Set tidb_memory_usage_alarm_ratio to an appropriate early-warning threshold

Query Optimization

• Review expensive queries using EXPLAIN ANALYZE
• Check for large HashJoin operations
• Review large HashAgg operations
• Refresh stale table statistics
• Monitor memory usage for high-memory queries

Transaction Management

• Batch large DELETE operations
• Batch large UPDATE operations
• Avoid transactions that modify millions of rows unnecessarily
• Evaluate bulk or non-transactional DML where appropriate

TiDB Disk Spill

• Configure a dedicated temporary storage path
• Ensure sufficient disk space
• Monitor disk spill and temporary storage usage

Infrastructure

• Avoid running TiDB on undersized infrastructure
• Account for memory used by other services
• Use resource controls in hybrid deployments
• Configure appropriate cgroup memory boundaries

Conclusion

TiDB OOM errors are rarely random. They're the predictable result of specific SQL patterns, undersized memory limits, or configuration defaults that don't match your workload. Confirming whether the operating system or TiDB's own memory manager caused the event is the first step in troubleshooting any recurring OOM pattern — from there, tuning tidb_mem_quota_query and tidb_server_memory_limit for your hardware, applying targeted query optimization to the worst offenders, and enabling disk spill as a safety net will prevent most repeat incidents.

For TiDB out of memory issues you can't fully resolve in-house, Mafiree's TiDB performance tuning and consulting team can audit your configuration and query patterns before the next incident happens.