LOB写入性能优化

测试步骤

1. 准备脚本文件lob_update.lua、lob_insert.lua，用于后续性能测试。以下脚本文件供参考。
   • 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

2. 准备数据。

   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大小} {lob_update.lua或者lob_insert.lua} prepare

3. 执行以下命令，测试性能。

   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大小} --time=300 --threads=threadnum --percentile=99 --report-interval=5 {lob_update.lua或者 lob_insert.lua} run

4. 执行以下命令，清理数据。

   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或者 lob_insert.lua} cleanup

测试结果

UPDATE优化前后对比如下：

图1 UPDATE性能结果

INSERT 负载同样受益（16 KB 约 +50%、64 KB 约 +35%），量级比 UPDATE 小一些。