largeval.lua

#!/usr/bin/env sysbench
-- ----------------------------------------------------------------------
-- largeval.lua -- large-value microbenchmark
--
-- Unlike the stock sysbench oltp tables (~200 byte rows), every row here
-- carries a large LONGBLOB payload (default 16 KB), so the workload exercises
-- big-value write/read paths -- on TidesDB that is the klog/vlog split (values
-- above tidesdb_default_klog_value_threshold land in the vlog), on InnoDB it is
-- off-page BLOB storage.
--
-- One table shape, four single-statement operations selected with --op:
--   point_select  SELECT the full large row by primary key
--   insert        INSERT a new large row (auto-increment id, table grows)
--   update        UPDATE the large payload of an existing row (put-over-put)
--   delete        DELETE an existing row by primary key
--
-- Commands: prepare (parallel load), run (--op drives it), cleanup (drop).
-- Pass the path explicitly, e.g.
--   sysbench /path/largeval.lua --mysql-storage-engine=tidesdb ... prepare
-- ----------------------------------------------------------------------

sysbench.cmdline.options = {
   tables               = {"Number of tables", 8},
   table_size           = {"Initial rows per table", 160000},
   payload_size         = {"Size of the large value column in bytes", 16384},
   op                   = {"Operation for run: point_select | insert | update | delete",
                           "point_select"},
   mysql_storage_engine = {"Storage engine for the created tables", "innodb"},
}

-- Deterministic n-byte payload built from a quote/backslash-free alphabet, so
-- it can be inlined into SQL without escaping.
local PAYLOAD_BLOCK =
   "abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_-"
local function gen_payload(n)
   local blk = #PAYLOAD_BLOCK
   local reps = math.floor(n / blk)
   return string.rep(PAYLOAD_BLOCK, reps) .. string.sub(PAYLOAD_BLOCK, 1, n - reps * blk)
end

local function create_table(con, t)
   con:query("DROP TABLE IF EXISTS lvtest" .. t)
   con:query(string.format([[
CREATE TABLE lvtest%d (
  id      INT NOT NULL AUTO_INCREMENT,
  k       INT NOT NULL DEFAULT 0,
  payload LONGBLOB NOT NULL,
  PRIMARY KEY (id)
) ENGINE=%s]], t, sysbench.opt.mysql_storage_engine))
end

local function load_table(con, t, payload)
   if sysbench.opt.table_size <= 0 then return end
   print(string.format("Loading %d rows x %d bytes into lvtest%d ...",
                       sysbench.opt.table_size, sysbench.opt.payload_size, t))
   -- Batch rows per INSERT toward a ~256KB statement.  We avoid bulk_insert_*
   -- because its query buffer caps at 512KB, which a single large value blows
   -- past; a plain con:query has no such cap.  per_batch collapses to 1 for
   -- payloads above ~256KB (one row per statement).
   local per_batch = math.max(1, math.floor(262144 / sysbench.opt.payload_size))
   local prefix = "INSERT INTO lvtest" .. t .. "(k,payload) VALUES "
   local i = 1
   while i <= sysbench.opt.table_size do
      local n = math.min(per_batch, sysbench.opt.table_size - i + 1)
      local vals = {}
      for j = 1, n do
         vals[j] = string.format("(%d,'%s')",
                                 sysbench.rand.default(1, sysbench.opt.table_size), payload)
      end
      con:query(prefix .. table.concat(vals, ","))
      i = i + n
   end
end

-- prepare: each thread owns a strided subset of tables (parallel load)
function cmd_prepare()
   local drv = sysbench.sql.driver()
   local con = drv:connect()
   local payload = gen_payload(sysbench.opt.payload_size)
   for t = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables,
           sysbench.opt.threads do
      create_table(con, t)
      load_table(con, t, payload)
   end
end

function cmd_cleanup()
   local drv = sysbench.sql.driver()
   local con = drv:connect()
   for t = 1, sysbench.opt.tables do
      con:query("DROP TABLE IF EXISTS lvtest" .. t)
   end
end

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

-- run-phase callbacks
function thread_init()
   drv = sysbench.sql.driver()
   con = drv:connect()
   payload = gen_payload(sysbench.opt.payload_size)
end

function thread_done()
   con:disconnect()
end

function event()
   local t  = sysbench.rand.uniform(1, sysbench.opt.tables)
   local id = sysbench.rand.default(1, sysbench.opt.table_size)
   local op = sysbench.opt.op

   if op == "point_select" then
      con:query("SELECT id,k,payload FROM lvtest" .. t .. " WHERE id=" .. id)
   elseif op == "insert" then
      con:query(string.format("INSERT INTO lvtest%d(k,payload) VALUES(%d,'%s')",
                t, sysbench.rand.default(1, sysbench.opt.table_size), payload))
   elseif op == "update" then
      con:query(string.format("UPDATE lvtest%d SET payload='%s', k=%d WHERE id=%d",
                t, payload, sysbench.rand.default(1, sysbench.opt.table_size), id))
   elseif op == "delete" then
      con:query("DELETE FROM lvtest" .. t .. " WHERE id=" .. id)
   else
      error("unknown --op '" .. tostring(op) .. "' (point_select|insert|update|delete)")
   end
end