PolarDB:BLOB performance optimization

How it works

A BLOB stores large objects such as images, documents, or long text. In the native MySQL InnoDB engine, BLOBs that exceed a certain size are stored on separate external data pages (off-pages). This mechanism creates three performance challenges under high concurrency:

1. Write lock contention: Updating an off-page BLOB triggers pessimistic locking, which allows only one thread to write BLOBs to a single table at a time.

2. Redo log I/O pressure: Large BLOB fields generate large redo logs. When disk I/O is insufficient, log flushing blocks foreground writes.

3. Slow space reclamation: The background Purge process holds a core lock while performing disk I/O to read BLOB pages, making cleanup inefficient and causing storage space bloat.

PolarDB solves all three problems with kernel-level optimizations, without changing how your application uses BLOBs. The following diagram illustrates how they work together:

Billing

BLOB performance optimization is built into the PolarDB kernel and is free of charge.

Optimize BLOB write performance

When to use this: If you observe write latency spikes or throughput drops on BLOB tables during peak hours, write lock contention is the likely cause. PolarDB provides two optimizations to address this: page pre-allocation and index latch optimization.

Page pre-allocation

This optimization restructures the BLOB write path to maximize concurrency:

1. Calculate and request all required data pages in a single batch.

2. Copy the BLOB content into the newly allocated pages without holding a lock.

3. Attach the completed page chain to the index record using a single optimistic lock operation.

Only the initial space allocation phase requires an exclusive lock. The time-consuming data copy runs in parallel with other BLOB writes, dramatically reducing lock-holding time and improving write throughput.

Index latch optimization

When to use this: For tables with small (under 8 KB) or mixed-size BLOBs, native MySQL often abandons optimistic locking too early and falls back to a more expensive pessimistic lock. This optimization extends the optimistic path to handle more cases.

1. Stay on the optimistic path: When an update is detected, the optimization holds a shared latch (S-latch) instead of immediately acquiring a pessimistic lock, keeping conflict probability low.

2. Pre-build and attempt the update: On the optimistic path, it pre-builds the required BLOB data structure and tries to modify the index page directly.

3. Commit or roll back:

   • Success: If the updated record fits on the current index page, the operation completes on the optimistic path.

   • Failure: If there is insufficient page space, the optimization falls back to the native MySQL pessimistic locking path to complete the update and maintain data consistency.

This significantly increases the success rate of optimistic updates, boosting throughput for mixed-size BLOB workloads.

Reduce redo log volume for BLOB writes

When to use this: If your application writes large amounts of BLOB data and redo log generation is becoming an I/O bottleneck, enable redo compression to reduce the I/O load.

This feature compresses redo log records for BLOBs in parallel before they are written to the log buffer, reducing the actual volume of redo logs written to disk by an average of 40%–60%. This lowers I/O pressure and prevents redo log throughput from limiting overall write performance. During crash recovery, the logs are automatically decompressed.

Accelerate BLOB space reclamation

When to use this: If tablespace continues to grow even after frequent deletes or updates on BLOB tables, the native Purge process is likely the bottleneck. In native MySQL, Purge holds a core lock while performing disk I/O to read BLOB pages — a slow operation that blocks cleanup.

PolarDB separates lock-holding from disk I/O:

1. Identify target pages: Before acquiring any lock, the Purge process identifies the complete BLOB off-page chain to be reclaimed.

2. Asynchronous pre-read: It issues asynchronous I/O requests to load the target pages from disk into the buffer pool.

3. Fast cleanup: Once all pages are in memory, the Purge process acquires core locks (such as Index SX) and performs in-memory page releases and metadata modifications.

By completing all disk I/O before acquiring a lock, this approach dramatically shortens lock-holding time and reduces the impact of background cleanup on foreground operations.

Improve DDL execution speed

When to use this: If ALTER TABLE operations on BLOB tables are too slow, enable the Data Definition Language (DDL)-specific optimizations below. These apply the same locking and redo improvements to the DDL rebuild path.

• Page pre-allocation: During a primary table rebuild, BLOB fields are written using a simpler, more direct locking method that avoids complex concurrency conflict overhead.

• Latch optimization: Parallel DDL splits the primary key tree so that multiple worker threads operate on different data pages. New page allocation is protected independently, allowing other operations to run concurrently.

• Redo optimization:

  • Page aggregation: Instead of writing a redo record per row, the system writes the changes for an entire data page as a single large redo record after the page is full, improving redo efficiency.

  • Aggregation compression: The large aggregated redo records are compressed to further reduce redo log volume during DDL.

Related system optimizations

To maximize performance in high-throughput BLOB scenarios, PolarDB also optimizes related system components:

• Parallel asynchronous redo writes: Data in the redo buffer is sharded across multiple threads that issue asynchronous I/O concurrently, achieving up to 4 GB/s write throughput in tests.

• File extension optimization: Based on PolarDB's distributed file system, tablespace extension only requires small metadata changes. The native file zero-filling step is eliminated, removing it as a time and lock bottleneck.

• Lock-free dirty page flushing: Using Shadow Page technology, the system builds an in-memory copy of a dirty page, releases the page lock, and then uses the copy for I/O — so dirty page flushing never blocks write requests.

Performance data

The following results show the performance improvements after enabling BLOB optimization.

• DML performance: In high-concurrency scenarios with BLOB row lengths of 100 KB and 200 KB, insert and update throughput improves by nearly 3x. Combined with redo compression, the improvement reaches 4–5x.

  

  

• DDL performance: For a 40 GB table with BLOB fields, the DDL write rate increases by 5–6x and total execution time is reduced by 84%.