Writing Binary Large Objects (BLOBs) to MySQL in high-concurrency environments often causes performance bottlenecks, such as lock contention and excessive redo log generation. PolarDB addresses these challenges with kernel-level optimizations that resolve lock contention, I/O pressure, and space management issues during BLOB operations. This significantly improves DML and DDL performance, ensuring your services run stably and efficiently under high-concurrency workloads.
Overview
A Binary Large Object (BLOB) is a database field type used for storing large data objects such as images, documents, and long text. In the native MySQL InnoDB engine, when a BLOB field in a data row exceeds a certain size, InnoDB stores it on separate off-page data pages. While this mechanism can handle large data, it also introduces three major performance challenges:
-
Concurrent write bottleneck: Updating an off-page BLOB acquires a pessimistic lock. This allows only a single thread to write BLOBs to the table at once, severely limiting concurrency.
-
Increased I/O pressure: Large fields generate large redo log entries. If disk I/O is insufficient, log flushing becomes a bottleneck, blocking foreground write operations.
-
Delayed space reclamation: The process for cleaning up old BLOB data, known as the Purge process, is inefficient. This can lead to storage space bloat and performance degradation.
The BLOB optimization feature in PolarDB solves these problems. It optimizes the entire lifecycle of BLOBs, including writes, updates, and reclamation, at the kernel level, requiring no application changes.
Billing
The BLOB performance optimization feature is built into the PolarDB kernel and is available at no additional cost.
Optimize BLOB write performance
If you experience throughput bottlenecks or high latency when writing to BLOB tables during peak business hours, the cause is often write lock contention. PolarDB solves this problem with page pre-allocation and index latch optimization.
Page pre-allocation
This optimization breaks down the BLOB write process into the following steps:
-
Calculates the required number of data pages and allocates them in a single batch operation.
-
Copies the data in a lock-free state, filling the newly allocated pages with the BLOB content.
-
Attaches the resulting chain of data pages to the index record with a single optimistic lock operation.
Throughout this process, only the initial space allocation phase is exclusive. The more time-consuming operations, like data copying, can run concurrently with other BLOB writes. Furthermore, because data copying occurs outside the critical section, the system avoids the risk of a single write generating a large amount of redo log data that could fill the log buffer and block writes. Ultimately, this mechanism minimizes global lock hold time, significantly improving concurrent write performance.
Index latch optimization
For tables that contain small (under 8 KB) or mixed-size BLOB fields, native MySQL often abandons optimistic locking prematurely during updates due to index page structure modification operations (SMOs). It then falls back to an inefficient pessimistic lock model, which severely impacts concurrent performance. The index latch optimization solves this bottleneck by keeping operations on the optimistic path longer. This optimization works in the following steps:
-
Stay on the optimistic path: When a potential BLOB field update is detected, the optimization logic does not immediately switch to a pessimistic lock. Instead, it continues to hold a shared latch (S-latch), which has a lower probability of conflict, and remains on the optimistic update path.
-
Pre-build and attempt update: On the optimistic path, it pre-builds the required BLOB data structure for the update and attempts to modify the index page directly.
-
Decide and commit or fall back:
-
Success: If the updated record fits within the space requirements of the current index page, the operation completes quickly on the optimistic path.
-
Failure: If the page has insufficient space, for example, the optimization automatically abandons the attempt and seamlessly falls back to the native MySQL pessimistic lock path. The native path then completes the entire update operation, ensuring data consistency.
-
The key advantage of this optimistic approach is that it significantly increases the success rate of optimistic updates. It allows many operations that would otherwise fall back to pessimistic locking to complete more efficiently. This greatly enhances throughput for updating mixed-size BLOB data under high concurrency.
Enable write optimizations
In the PolarDB console, go to the page to view and modify the following parameters to enable these optimizations:
|
Parameter |
Description |
Supported versions |
|
|
Enables or disables page pre-allocation for BLOBs. Valid values:
|
|
|
|
Sets the maximum length of a single BLOB column for which page pre-allocation is used. |
|
|
|
Enables or disables index latch optimization for BLOBs. |
Reduce redo log for BLOB writes
If your workload involves writing large amounts of BLOB data, causing the redo log generation rate to become an I/O bottleneck, you can enable redo compression to alleviate the pressure.
Redo compression works by compressing BLOB-related redo log entries in parallel before writing them to the log buffer. This process can significantly reduce the volume of redo log data written to disk, with tests showing an average reduction of 40% to 60%. This lowers storage I/O pressure and prevents the redo module from becoming a bottleneck that impacts overall write performance. During crash recovery, the system automatically decompresses and applies these logs.
Enable redo compression
In the PolarDB console, go to the page to view and modify the following parameters to enable this optimization:
|
Parameter |
Description |
Supported versions |
|
|
Sets the compression algorithm for BLOB redo logs. Valid values:
|
|
|
|
Sets the compression level for the selected algorithm. Valid values: 0 to 9. Default: 6. |
Accelerate BLOB space reclamation
If you notice that tablespace reclamation is slow and files continue to grow after frequently deleting or updating BLOB data, the root cause is the inefficient native MySQL background Purge process. When cleaning data, this process first acquires core locks, such as Index SX, before reading BLOB pages. If a page is not in memory, a time-consuming disk I/O operation is triggered while the lock is held, which can block other concurrent operations on the table for an extended period. The PolarDB Purge pre-read optimization solves this bottleneck by separating I/O operations from the locking process. This optimization works in the following steps:
-
Identify target pages: Before acquiring a lock and starting the cleanup, the Purge process first identifies the complete chain of off-page BLOBs that need to be reclaimed.
-
Asynchronously pre-read: It initiates asynchronous I/O requests to load the target pages that are not yet in memory from disk into the buffer pool.
-
Clean up quickly: After all pages are loaded into memory, the Purge process acquires the necessary core locks, such as
Index SX, and quickly performs the in-memory page release and metadata modification operations.
The Purge pre-read mechanism avoids performing any disk I/O operations within a critical section where a lock is held. By moving the most time-consuming I/O operations to an earlier stage, lock-holding time is reduced to the duration of fast, in-memory operations. This not only improves the efficiency of BLOB data reclamation and prevents space bloat, but more importantly, it reduces the blocking of foreground workloads, such as high-concurrency writes, by the background cleanup process. As a result, it enhances the overall concurrency performance and stability of the database.
Enable Purge pre-read
In the PolarDB console, go to the page to view and modify the following parameter to enable this optimization:
|
Parameter |
Description |
Supported versions |
|
|
Enables or disables Purge pre-read for BLOBs. Valid values:
|
Supported on all versions. |
Improve DDL execution efficiency
If you need to perform DDL operations, such as ALTER TABLE, on tables with a large amount of BLOB data and the operations take too long, you can enable DDL-related BLOB optimizations to significantly reduce the operation time.
-
Page pre-allocation: Since overflow columns for BLOB fields exist on the primary key, DDL operations that rebuild the primary table also involve writing BLOB fields. The page pre-allocation optimization uses a more direct and simplified locking method to write BLOB data, which avoids the overhead of handling complex concurrency conflicts and significantly improves rebuild efficiency.
-
Latch optimization: Parallel DDL partitions the primary key tree, so multiple worker threads do not operate on the same data page. This optimization protects the allocation of new pages independently, which allows other operations to execute concurrently and improves efficiency.
-
Redo optimization:
-
Page aggregation: During a bulk load, instead of generating a redo log entry for each record, the system writes the changes for an entire data page as a single large redo log entry after the page is full. This improves the efficiency of redo operations.
-
Aggregate compression: The large, aggregated redo log entries are then compressed to further reduce the amount of redo log data generated during the DDL process.
-
Enable DDL optimizations
In the PolarDB console, go to the page to view and modify the following parameters to enable these optimizations:
Some parameters in the following table cannot be modified manually. If you need to change them, please submit a ticket for assistance.
|
Parameter |
Description |
Supported versions |
|
|
Specifies whether to enable BLOB optimizations during DDL operations. Valid values:
|
|
|
|
Specifies whether to enable index lock optimization during DDL operations. Valid values:
|
|
|
|
Specifies whether to enable redo page aggregation during DDL bulk loading. Valid values:
|
|
|
|
Sets the redo compression algorithm for DDL bulk loading. Valid values:
|
|
|
|
Sets the target for redo compression during DDL operations. Valid values:
|
|
Related module optimizations
In high-throughput BLOB scenarios, bottlenecks can also occur in other modules. To achieve even better performance, PolarDB provides the following related optimizations:
-
Parallel asynchronous redo log writes: Multiple threads shard the data in the redo log buffer and send it concurrently as asynchronous I/O tasks. This significantly increases redo log write throughput, reaching up to 4 GB/s in tests.
-
File extension optimization: Built on PolarDB's self-developed distributed file system, tablespace extension operations only require modifying a small amount of metadata. The native file zero-filling operation is eliminated, so the time and lock overhead of file extension is no longer a bottleneck.
-
Lock-free dirty page flushing: This optimization uses shadow page technology. When flushing a dirty page, the system first builds an in-memory copy, releases the page lock, and then uses the copy for the I/O operation. This prevents the I/O operation from blocking write requests because no page lock is held during flushing.
Performance benchmarks
The following data shows the performance improvements in DML and DDL scenarios after enabling BLOB optimizations.
-
DML performance: In high-concurrency scenarios with row lengths of 100 KB and 200 KB, PolarDB's optimizations improve insert and update performance by nearly 3x. When combined with redo compression, performance improves by 4 to 5 times.
-
DDL performance: For a 40 GB table containing BLOB fields, enabling these optimizations increases the DDL write rate by 5 to 6 times and reduces the total execution time by 84%.
