Accelerating Query Performance: The Benefits of TiDB’s In-Memory Engine (IME)

If you’ve ever struggled with slow queries or high resource consumption in your database, you’re not alone. Many databases, including those built on Multi-Version Concurrency Control (MVCC), face query performance degradation over time. While MVCC is essential for managing concurrent access, excessive historical data scanning can slow down queries and overload system resources — a problem known as MVCC amplification.

Consider an e-commerce platform running a major sales event. Customers modify orders frequently—placing, updating, and canceling purchases in real time. As these updates pile up, queries retrieving current order statuses slow down because the system must scan through excessive historical versions. This can lead to customer frustration and lost sales due to delayed order confirmations.

To solve this, we built the In-Memory Engine (IME) for TiDB. IME intelligently caches frequently accessed data versions in memory, dramatically reducing query latency and CPU usage. In our tests, IME reduced p99 SELECT latency from 400 milliseconds to just 10 milliseconds while lowering CPU consumption by 25 percent. This improvement is especially valuable for read-heavy workloads. It allows the system to handle a high volume of concurrent queries more efficiently.

In this blog, we’ll dive into TiDB’s IME, showing you how it accelerates query performance as well as best practices for getting started.

Why Does MVCC Amplification Hurt Query Performance?

Databases use MVCC to handle concurrent transactions efficiently. However, over time, excessive historical versions can pile up, leading to query slowdowns. Here’s when it becomes a problem:

• Frequent Updates & Deletes: In e-commerce order processing, every update to an order—modifications, cancellations, status changes—creates a new data version. A query fetching the latest order status should be quick. However, without optimization, it may scan outdated versions unnecessarily, delaying customer service response times.
• Long Retention Periods: In financial fraud detection, transaction history must be retained for analysis, sometimes for hours or days. When a bank checks for fraudulent patterns in real time, queries should focus on recent transactions rather than scanning thousands of outdated ones. Slower fraud detection means missed threats, impacting financial security.

For example, a SELECT query might only need the latest two versions of a dataset but end up scanning twenty irrelevant versions, causing a 10x slowdown in performance.

How the In-Memory Engine Accelerates TiDB Query Performance

The IME is purpose-built to tackle MVCC amplification by intelligently caching the latest data versions in memory. Unlike traditional storage-based approaches, IME optimizes TiDB queries in two key ways.

1. Prioritizing the Latest, Most Relevant Data

Instead of caching all data indiscriminately, IME focuses on the most frequently accessed and recently updated data. This ensures queries retrieve the freshest records without unnecessary scans.

Consider gaming leaderboards, where thousands of players update rankings simultaneously. Without IME, ranking queries must sift through stale player stats, slowing down leaderboard updates. By caching only the most relevant player records, IME ensures instant leaderboard refreshes.

2. Leveraging Memory for Speed

Querying data from memory is inherently faster than retrieving it from storage. IME accelerates query execution by keeping hot data in memory, ensuring that frequently accessed queries return results much faster.

This is critical for real-time content recommendations in streaming services. When a user skips a song or watches a trailer, the recommendation engine must update immediately. Without IME, recommendations may lag as the system processes outdated preference versions. IME ensures fresh user data, improving engagement and retention.

Real-World Query Performance Gains with IME

We rigorously tested the IME in read-heavy, high-update environments to measure its impact. The results show significant improvements:

• CPU Usage Reduced by 25 Percent: IME lowered CPU consumption by about 2 cores per TiKV node (on an 8-core, 32GB setup), allowing the system to run more efficiently.
• p99 Query Latency Reduced by 40x: Slow queries saw latency drop from 400 milliseconds to just 10 milliseconds, significantly improving responsiveness.
• Improved Query Efficiency: Queries that previously scanned tens of thousands of versions were optimized to scan just a few hundred, reducing unnecessary computational overhead.

The following performance metrics provide a visual representation of how the IME optimizes query execution in real-world scenarios. The graphs below compare key system performance indicators before and after enabling IME:

• CPU Efficiency: CPU utilization remains more stable with IME enabled, reducing unnecessary spikes and overhead.
• Memory Optimization: IME dynamically caches frequently accessed data, improving scan efficiency without excessive memory consumption.
• Query Latency Improvements: The 99th percentile (p99) gRPC message duration shows a sharp reduction, highlighting IME’s impact on reducing response times for read-heavy workloads.
• Higher Throughput: IME increases the efficiency of key database operations, including write and read processes, leading to better overall system performance.

The improvements in query response time, resource utilization, and operational efficiency reinforce IME as a crucial feature for performance-sensitive TiDB deployments.

How to Enable the In-Memory Engine

Getting started with IME is straightforward:

1. Upgrade to TiDB 8.5 – Ensure your cluster is running the latest version to access IME.
2. Enable IME in TiKV – Update your TiKV configuration file:

[in-memory-engine]
enable = true

3. Monitor Performance Gains – Run your existing query workloads and observe improvements in latency and CPU usage.

For SaaS platforms managing thousands of customer dashboards in real time, IME ensures faster analytics rendering and a seamless user experience.

Optimize Query Performance with the TiDB In-Memory Engine

If you are looking for a simple and effective way to optimize TiDB query performance, the IME is designed to help. For more details, please refer to the official documentation or download the latest TiDB release.

If you have any questions about TiDB’s IME, you can also connect with us on Twitter, LinkedIn, or through our Slack Channel.