Standalone sysbench mysql example lua script

filler.lua

-- filler.lua
--
-- 1. Ensure you have a 'sysbench' user and privileges to manage objects in schema 'sysbench'. e.g.
--
--    mysql> CREATE USER sysbench@'%' IDENTIFIED BY 'RM#aEq29waQE';
--    mysql> GRANT ALL ON sysbench.* TO sysbench@'%';
--    mysql> CREATE SCHEMA sysbench;
--
-- 2. Create the filler table with:
--
--    $ sysbench filler.lua --mysql-host=${ENDPOINT} prepare
--
-- 3. Run a benchmark with:
--
--    $ sysbench filler.lua --mysql-host=${ENDPOINT} --time=60 --report-interval=1 --threads=10 run
--
-- 4. Verify results
--
--    mysql> SELECT COUNT(*) FROM sysbench.filler;   # Will match total number of events
--    mysql> SELECT thread, COUNT(*) FROM sysbench.filler GROUP BY thread;
--
-- 5. Cleanup (shows summary results also)
--
--    $ sysbench filler.lua --mysql-host=${ENDPOINT} cleanup
--
if sysbench.cmdline.command == nil then
   error("A command is required. Supported commands are: prepare|run|cleanup")
end


sysbench.cmdline.options = {
   mysql_user = {"MySQL user", "sysbench"},
   mysql_password = {"MySQL password", "RM#aEq29waQE"},
   mysql_db = {"Database schema", "sysbench"},
   db_driver = {"Type of RDBMS", "mysql"},
}

table_name='filler'

-- Limit random value to 10,000 possible values
_template="####xxxxxxxxxx0x"

function get_conn()
   drv = sysbench.sql.driver()
   return drv:connect()
end

function prepare()
  print ("Preparing "..table_name.." benchmark")
  con = get_conn()
  con:query("CREATE SCHEMA IF NOT EXISTS ".. sysbench.opt.mysql_db)
  con:query("DROP TABLE IF EXISTS "..table_name)

   query = string.format([[
CREATE TABLE %s (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ts DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  s  CHAR(16) NOT NULL,
  thread  SMALLINT UNSIGNED NULL,
  PRIMARY KEY (id)
)
]], table_name)

   con:query(query)
end

function init()
  print("Benchmark Initialized")
end

function thread_init(thread_id)
  con = get_conn()
end

function event(thread_id)
  s = sysbench.rand.string(_template)
  query = "INSERT INTO "..table_name.."(id, ts, s, thread)"..
  "values (NULL, NOW(), '"..s.."',"..thread_id..")"
  con:query(query)
end

function thread_done(thread_id)
  con:disconnect()
end

function done(thread_id)
  print("Benchmark Done")
end

function cleanup()
  print("Benchmark Results")
  con = get_conn()
  query = string.format([[
        SELECT thread, COUNT(*) AS cnt
			  FROM %s
			  GROUP BY thread
			  UNION
			  SELECT 'all' AS thread, COUNT(*) AS cnt
			  FROM %s
			  ORDER BY thread
			]], table_name, table_name)

  local rs = con:query(query)
  for i = 1, rs.nrows do
     print(string.format("Thread Id: %s, Rows: %s", unpack(rs:fetch_row(), 1, rs.nfields)))
  end
  con:query("DROP TABLE ".. table_name)
end