Optimizing LOB Write Performance

Scenarios

A large object (LOB) is a special field type in databases used to store large-capacity binary or character data, such as images and long texts. In database management, workloads that frequently insert, update, and delete LOB data require high and stable performance.

A record containing LOBs, from creation to elimination, may involve three types of write operations: LOB INSERT, LOB UPDATE, and Purge old LOBs. In the execution path of the native MySQL kernel, all three operations require coarse-grained mutexes, serializing concurrent operations and significantly reducing concurrent write performance.

LOB INSERT: After holding index-level and tablespace-level mutexes, the operation allocates pages and writes data page by page.
LOB UPDATE: Only pessimistic updates are supported, and index-level mutexes are held along the entire path.
Purge old LOBs: After holding index-level and tablespace-level mutexes, the operation loads pages, triggering synchronous I/O.

To address these issues, optimize the mutex mechanism to reduce lock granularity, and use asynchronous I/O operations to improve the concurrent performance of LOB data processing.

Optimizing LOB Write Performance

Because of their large size, LOBs can become a performance bottleneck during writes, updates, and purging. The following describes the issues and optimization approaches for each of the three operation paths.

These optimizations reduce lock exclusivity on the LOB write paths by downgrading exclusive locks to shared locks. They also move heavy operations (such as data writes and page loading) performed during the lock-holding state to before lock acquisition or after lock release. In addition, unnecessary page loads are eliminated to avoid unnecessary I/O and buffer pool pollution.

Optimizing the LOB INSERT Process

If you experience write throughput bottlenecks or high latency during concurrent writes to tables containing LOBs during peak hours, this is typically caused by lock contention during the write process. TaurusDB provides two optimizations to address this issue.

Optimization 1: Batch Preallocation
In native InnoDB, space is allocated one page at a time. Each allocation requires global locks (the index root page lock and the tablespace lock). These locks remain held until the entire LOB has been written. As a result, concurrent writes must wait in a queue.
TaurusDB divides the write process into three steps and holds locks only briefly during the first step:
1. Batch allocation: Allocate all required pages at once, and immediately release the global locks.
2. Page-by-page writing: Write data without holding the global lock. Each page holds only a short-lived lock for itself, which is released as soon as the write completes.
3. Final confirmation: Briefly hold the record page lock, confirm that there are no conflicts, and write back the final length.
This shortens the global lock holding time from the duration of the entire LOB write to the brief allocation phase, greatly reducing contention between concurrent writes.
Optimization 2: Index Lock Downgrade
The native write path holds an exclusive index lock, allowing only one LOB write at a time on the same index. Under high concurrency, writes are therefore fully serialized.

TaurusDB downgrades the index lock from exclusive to shared, allowing multiple writes to hold shared locks concurrently without blocking one another. Concurrent writes are no longer serialized.

Optimizing the LOB UPDATE Process

If concurrent performance is poor when you update records containing LOB fields, this is often because native InnoDB unconditionally uses the pessimistic update path.

An update falls back to the pessimistic path if any of the following three conditions is met:

The old record before the update contains off-page fields.
The new value of the field to be updated is an off-page field. This occurs only during rollback, when an update is reversed based on an undo record and the old value of the updated field recorded in the undo record is already stored off-page.
During an optimistic update, the remaining page space is insufficient to store the updated row inline. By contrast, the pessimistic path attempts to store fields off-page.

Optimization 3: Support for Optimistic Updates

In native InnoDB, any record containing LOBs unconditionally falls back to the pessimistic path, which holds an exclusive lock and serializes updates across the index under high concurrency. However, the need for a pessimistic update depends on whether the record fits on the page, not simply on whether it contains LOBs.

For LOB updates that do not cause a page split, TaurusDB allows the optimistic path, which requires only a shared lock. It falls back to the pessimistic path only when necessary. This enables many updates that were previously serialized to execute concurrently, significantly improving throughput.

Optimizing the Purge Process for Old LOBs

If tablespace reclamation is slow and data files continue to grow after frequent deletions or updates of LOB data, the underlying cause may be the inefficiency of the native Purge process. TaurusDB provides two optimizations to address this issue.

Optimization 4: Purge Read-Ahead
Native Purge synchronously reads LOB pages (to be purged) one by one while holding locks. If a page is not in memory, it must be loaded from disk. The lock holding time therefore increases exponentially with I/O latency, blocking other writes.

TaurusDB separates I/O operations from locking. No disk I/O is performed while locks are held, reducing the lock holding time to purely in-memory operations and minimizing the impact on foreground writes.
1. Target identification: Before locking, identify the list of LOB pages that need to be purged.
2. Asynchronous read-ahead: Initiate asynchronous I/O to load pages from disk into memory in advance.
3. Fast purging: Once the pages are ready, acquire the locks and release them directly in memory.
Optimization 5: Purge Without Loading LOB Pages
Releasing a LOB data page requires only marking it as free in the space bitmap. The page contents do not need to be read. However, the native process still loads the entire large page into memory, wasting I/O and consuming cache.

When releasing a LOB data page, TaurusDB loads only the necessary metadata pages to obtain the page number, then updates the bitmap directly to mark the page as free. This eliminates unnecessary I/O and avoids cache pollution.

Enabling LOB Write Optimizations

Enable LOB write optimizations by configuring parameters on the Parameters page of the console.

**Table 1** Parameter description
Parameter	Level	Description
innodb_rds_lob_prealloc	GLOBAL	Enables or disables batch preallocation. The default value is OFF. ON: Enables batch preallocation. OFF: Disables batch preallocation.
innodb_rds_lob_prealloc_data_batch_size	GLOBAL	Specifies the maximum number of LOB DATA pages written in each batch during batch preallocation. The default value is 8. Value range: 1–32
innodb_rds_lob_latch_downgrade	GLOBAL	Enables or disables lock downgrading. The default value is OFF. ON: Disables lock downgrading. OFF: Enables lock downgrading.
innodb_rds_lob_optimistic_update	GLOBAL	Enables or disables optimistic updates. The default value is OFF. ON: Enables optimistic updates. OFF: Disables optimistic updates.
innodb_rds_lob_purge_prefetch	GLOBAL	Enables or disables read-ahead for LOB pages during Purge. The default value is OFF. ON: Enables read-ahead for LOB pages during Purge. OFF: Disables read-ahead for LOB pages during Purge.
innodb_rds_lob_purge_skip_load	GLOBAL	Enables or disables skipping the loading of LOB data pages during Purge. The default value is OFF. ON: Enables skipping LOB data page loading during Purge. OFF: Disables skipping LOB data page loading during Purge.

Performance Testing

Enabling all optimization switches significantly improves the throughput of concurrent LOB writes.

Test Procedure

Prepare the lob_update.lua and lob_insert.lua script files for performance testing. The following script files are provided for reference.

lob_update.lua

#!/usr/bin/env sysbench
-- LOB UPDATE benchmark: batched INSERT at prepare, PK UPDATE on blob_col at run.
-- Default 1M rows per table; MEDIUMBLOB supports up to 16MB.

sysbench.cmdline.options = {
blob_size = {"BLOB payload size in bytes (insert + update)", 10240},
table_size = {"Number of rows per table", 1000000},
tables = {"Number of tables", 1},
}

local c_value_template = "###########-###########-###########-" ..
"###########-###########-###########-" ..
"###########-###########-###########-" ..
"###########"

local pad_value_template = "###########-###########-###########-" ..
"###########-###########"

local function get_c_value()
return sysbench.rand.string(c_value_template)
end

local function get_pad_value()
return sysbench.rand.string(pad_value_template)
end

-- Target ~400KB per INSERT statement to stay under typical max_allowed_packet.
local function rows_per_batch(blob_size)
local approx = blob_size + 200
local n = math.floor(400000 / approx)
return math.max(1, math.min(n, 150))
end

function prepare()
local drv = sysbench.sql.driver()
assert(drv:name() == "mysql", "this script is MySQL-only")
local con = drv:connect()
local blob_size = sysbench.opt.blob_size
local table_size = sysbench.opt.table_size
local ntables = sysbench.opt.tables
local batch = rows_per_batch(blob_size)

for t = 1, ntables do
print(string.format("Dropping old table sbtest%d if exists...", t))
con:query("DROP TABLE IF EXISTS sbtest" .. t)
end

for t = 1, ntables do
print(string.format("Creating table sbtest%d...", t))
con:query(string.format([[
CREATE TABLE sbtest%d (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
k INT UNSIGNED NOT NULL,
c CHAR(120) NOT NULL,
pad CHAR(60) NOT NULL,
blob_col MEDIUMBLOB NOT NULL,
PRIMARY KEY (id),
KEY k_%d (k)
) ENGINE=InnoDB]], t, t))

print(string.format(
"Inserting %d rows into sbtest%d (blob REPEAT in SQL, size=%d, ~%d rows/INSERT)...",
table_size, t, blob_size, batch))

local parts = {}
local n = 0
for i = 1, table_size do
local c_val = get_c_value()
local pad_val = get_pad_value()
local k = sysbench.rand.default(1, table_size)
local row = string.format(
"(%u, '%s', '%s', REPEAT('x', %d))",
k, c_val, pad_val, blob_size)
table.insert(parts, row)
if #parts >= batch then
con:query("INSERT INTO sbtest" .. t .. " (k, c, pad, blob_col) VALUES " .. table.concat(parts, ","))
n = n + #parts
parts = {}
if n % 100000 == 0 then
print(string.format("  ... inserted %d / %d", n, table_size))
end
end
end
if #parts > 0 then
con:query("INSERT INTO sbtest" .. t .. " (k, c, pad, blob_col) VALUES " .. table.concat(parts, ","))
n = n + #parts
end
print(string.format("Done inserting into sbtest%d (%d rows).", t, n))
end
end

function cleanup()
local drv = sysbench.sql.driver()
local con = drv:connect()
for t = 1, sysbench.opt.tables do
print(string.format("Dropping table sbtest%d...", t))
con:query("DROP TABLE IF EXISTS sbtest" .. t)
end
end

function thread_init()
drv = sysbench.sql.driver()
con = drv:connect()
blob_template = string.rep("#", sysbench.opt.blob_size)
end

function thread_done()
con:disconnect()
end

function event()
local id = sysbench.rand.uniform(1, sysbench.opt.table_size)
local t = sysbench.rand.uniform(1, sysbench.opt.tables)
local payload = sysbench.rand.string(blob_template)
con:query(string.format(
"UPDATE sbtest%d SET blob_col='%s' WHERE id=%d",
t, payload, id))
end

lob_insert.lua

#!/usr/bin/env sysbench
-- LOB INSERT benchmark: run phase inserts MEDIUMBLOB rows with sysbench-generated payload.

require("oltp_common")

sysbench.cmdline.options.blob_size =
{"LOB payload size in bytes", 1048576}
sysbench.cmdline.options.lob_file_name =
{"Value for file_name column", "name"}
sysbench.cmdline.options.lob_file_size =
{"Value for file_size column", 1048576}

local function sql_quote(s)
return (s:gsub("'", "''"))
end

function create_table(drv, con, table_num)
print(string.format("Creating table 'lobtest%d'...", table_num))

local query = string.format([[
CREATE TABLE lobtest%d (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
file_name VARCHAR(255) NOT NULL,
file_size BIGINT DEFAULT 0,
file_content MEDIUMBLOB,
upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
)]], table_num)

con:query(query)
end

function cmd_prepare()
local drv = sysbench.sql.driver()
local con = drv:connect()

for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables, sysbench.opt.threads do
create_table(drv, con, i)
end
end

sysbench.cmdline.commands.prepare = {
cmd_prepare,
sysbench.cmdline.PARALLEL_COMMAND
}

function thread_init()
drv = sysbench.sql.driver()
con = drv:connect()
blob_template = string.rep("#", sysbench.opt.blob_size)
end

function thread_done()
con:disconnect()
end

function prepare_statements()
-- We use plain SQL execution in event().
end

function event()
local table_name = "lobtest" .. sysbench.rand.uniform(1, sysbench.opt.tables)
local payload = sysbench.rand.string(blob_template)
local query = string.format(
"INSERT INTO %s (file_name, file_size, file_content, upload_time) " ..
"VALUES ('%s', %d, '%s', NOW())",
table_name,
sql_quote(sysbench.opt.lob_file_name),
tonumber(sysbench.opt.lob_file_size),
payload
)

con:query(query)
end

function cleanup()
local drv = sysbench.sql.driver()
local con = drv:connect()

for i = 1, sysbench.opt.tables do
print(string.format("Dropping table 'lobtest%d'...", i))
con:query("DROP TABLE IF EXISTS lobtest" .. i)
end
end

Prepare data.

sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=100000 --tables=1 --blob-size={LOB_size} {lob_update.lua or lob_insert.lua} prepare

Run the following command to test performance.

sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=100000 --tables=1 --blob-size={LOB_size} --time=300 --threads=threadnum --percentile=99 --report-interval=5 {lob_update.lua or lob_insert.lua} run

Run the following command to clean up data.

sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=100000 --tables=1 {lob_update.lua or lob_insert.lua} cleanup

Test Results

The UPDATE performance comparison before and after optimization is shown below.

Figure 1 UPDATE performance results
Click to enlarge

INSERT workloads also benefit from the optimizations, with throughput improvements of about 50% for 16 KB LOB data and 35% for 64 KB LOB data. The gains are smaller than those for UPDATE workloads.

Parent Topic: Concurrency Performance Optimization

Previous topic: Concurrency Performance Optimization

Next topic: Vector SQL Statements