使用sysbench的lua脚本进行数据构造 - 《Mysql》

介绍
下载安装sysbench
修改lua脚本
启动sysbench
验证
对已有表插入新数据

author：彭程

介绍

sysbench是一个开源的、模块化的、跨平台的多线程性能测试工具，可以用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试。目前支持的数据库有MySQL、Oracle和PostgreSQL。利用sysbench的lua脚本可以方便地构造海量数据，本文用sysbench连接dble，向Mysql数据库插入大量数据，为后续测试性能做准备。

下载安装sysbench

sudo yum -y install sysbench
sysbench --version
sysbench 1.0.17

修改lua脚本

默认在usr/share/sysbench文件夹下会有自带的lua脚本模板，可以根据自己需要进行修改，一般oltp_common.lua里面就够了，下面需要在数据库插入如下形式的表

CREATE TABLE sbtest(
  id ,
  k INTEGER DEFAULT '0' NOT NULL,
  NAME CHAR(10) DEFAULT '' NOT NULL,
  CITY CHAR(10) DEFAULT '' NOT NULL,
  COUNTRY CHAR(10) DEFAULT '' NOT NULL,
  BIRTH DATETIME NOT NULL,
  COMM CHAR(50) DEFAULT '' NOT NULL,
)

修改oltp_common.lua关键代码如下所示

local comm_value_template = "###########-###########-###########" --默认值模板
function get_comm_value()
   return sysbench.rand.string(comm_value_template) --构造任意字符串
end
function create_table(drv, con, table_num)
   local id_index_def, id_def
   local engine_def = ""
   local extra_table_options = ""
   local query
   if sysbench.opt.secondary then
     id_index_def = "KEY xid"
   else
     id_index_def = "PRIMARY KEY"
   end
   if drv:name() == "mysql" or drv:name() == "attachsql" or
      drv:name() == "drizzle"
   then
      if sysbench.opt.auto_inc then
         id_def = "INTEGER NOT NULL AUTO_INCREMENT"
      else
         id_def = "INTEGER NOT NULL"
      end
      engine_def = "/*! ENGINE = " .. sysbench.opt.mysql_storage_engine .. " */"
      extra_table_options = mysql_table_options or ""
   elseif drv:name() == "pgsql"
   then
      if not sysbench.opt.auto_inc then
         id_def = "INTEGER NOT NULL"
      elseif pgsql_variant == 'redshift' then
        id_def = "INTEGER IDENTITY(1,1)"
      else
        id_def = "SERIAL"
      end
   else
      error("Unsupported database driver:" .. drv:name())
   end
   print(string.format("Creating table 'sbtest%d'...", table_num))
   query = string.format([[ --拼接创建表的SQL字符串
CREATE TABLE sbtest%d(
  id %s, --主键
  k INTEGER DEFAULT '0' NOT NULL, --二级索引
  NAME CHAR(10) DEFAULT '' NOT NULL,
  CITY CHAR(10) DEFAULT '' NOT NULL,
  COUNTRY CHAR(10) DEFAULT '' NOT NULL,
  BIRTH DATETIME NOT NULL,
  COMM CHAR(50) DEFAULT '' NOT NULL,
  %s (id)
) %s %s]],
      table_num, id_def, id_index_def, engine_def, extra_table_options)
   con:query(query)
   if (sysbench.opt.table_size > 0) then
      print(string.format("Inserting %d records into 'sbtest%d'",
                          sysbench.opt.table_size, table_num))
   end
   if sysbench.opt.auto_inc then --构造插入数据的字符串
      query = "INSERT INTO sbtest" .. table_num .. "(k, NAME, CITY, COUNTRY, BIRTH, COMM) VALUES"
   else
      query = "INSERT INTO sbtest" .. table_num .. "(id, k, NAME, CITY, COUNTRY, BIRTH, COMM) VALUES"
   end
   con:bulk_insert_init(query)
   local comm_val
   for i = 1, sysbench.opt.table_size do
      comm_val = get_comm_value()
      if (sysbench.opt.auto_inc) then --构造随机数据
         query = string.format("(%d, '%s', '%s', '%s', '%s', '%s' )",
                               sb_rand(1, 10),"NA"..sb_rand_uniform(1,20),
                              "city"..sb_rand_uniform(1,10), "country"..sb_rand_uniform(1,10), string.format("%04d/%02d/%02d %02d:%02d:%02d",sb_rand_uniform(2000,2021),sb_rand_uniform(1,12),sb_rand_uniform(1,28),sb_rand_uniform(0,23),sb_rand_uniform(0,59),sb_rand_uniform(0,59)), comm_val)
      else
         query = string.format("(%d, %d, '%s', '%s', '%s')",
                               i, sb_rand(1, 10),"NA"..sb_rand_uniform(1,20),
                              "city"..sb_rand_uniform(1,10), "country"..sb_rand_uniform(1,10), string.format("%04d/%02d/%02d %02d:%02d:%02d",sb_rand_uniform(2000,2021),sb_rand_uniform(1,12),sb_rand_uniform(1,28),sb_rand_uniform(0,23),sb_rand_uniform(0,59),sb_rand_uniform(0,59)), comm_val)
      end
      con:bulk_insert_next(query)
   end
   con:bulk_insert_done()
   if sysbench.opt.create_secondary then
      print(string.format("Creating a secondary index on 'sbtest%d'...",
                          table_num))
      con:query(string.format("CREATE INDEX k_%d ON sbtest%d(k)",
                              table_num, table_num))
   end
end

启动sysbench

通过以下语句启动susbench执行脚本

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 
--mysql-host=127.0.0.1 --mysql-port=8066 --mysql-user=root --mysql-password=123456 
--mysql-db=testdb --tables=1 --table_size=100 oltp_common 
--db-ps-mode=disable prepare

其中，几个关键参数：
–tables：指定生成表的数量；
–table_size：指定生成表中生成的数据量；
–mysql-db: 连接的测试数据库名称；
-mysql-user:登录Mysql的用户名；
-mysql-password:用户名对应的密码；
-mysql-port:对应端口号；
注意：本文是连接的dble来间接插入Mysql数据库数据，所以这里的端口号、ip、用户名、密码等都是dble客户端的，dble实现了Mysql协议所以sysbench是可以顺利连接的。

验证

这里创建了10条数据，如下所示构造数据成功

mysql> select * from sbtest1;
+----+---+------+--------+----------+---------------------+-------------------------------------+
| id | k | NAME | CITY   | COUNTRY  | BIRTH               | COMM                                |
+----+---+------+--------+----------+---------------------+-------------------------------------+
|  1 | 6 | NA14 | city6  | country1 | 2021-04-26 22:49:22 | 31451373586-15688153734-79729593694 |
|  2 | 5 | NA7  | city6  | country1 | 2010-12-19 23:02:31 | 98372489827-58671183353-97898133742 |
|  3 | 5 | NA10 | city4  | country7 | 2014-11-28 21:12:42 | 73517241739-61874746962-53989966216 |
|  4 | 4 | NA16 | city10 | country8 | 2002-12-21 07:43:50 | 41404092488-55709587706-81865008859 |
|  5 | 6 | NA1  | city5  | country8 | 2008-03-14 02:46:02 | 03227001855-89937176965-00030927032 |
|  6 | 6 | NA2  | city5  | country2 | 2005-03-23 14:54:05 | 12031107688-56803142351-20402939890 |
|  7 | 6 | NA4  | city2  | country6 | 2009-10-07 23:00:34 | 31226404776-82668345880-82208477922 |
|  8 | 6 | NA13 | city7  | country3 | 2008-01-26 17:54:02 | 79644687651-42636182493-76827441249 |
|  9 | 6 | NA19 | city2  | country4 | 2000-05-03 12:26:17 | 12577859405-18387141605-88419212243 |
| 10 | 6 | NA10 | city4  | country1 | 2008-07-23 02:47:57 | 61225783229-49488173271-50602444026 |
+----+---+------+--------+----------+---------------------+-------------------------------------+
10 rows in set (0.01 sec)

对已有表插入新数据

当已有表需要插入大量新数据时，可以利用sysbench的oltp_insert.lua脚本实现仅插入操作
如对表sbtest2新插入数据，oltp_insert.lua脚本关键代码如下所示

require("oltp_common")
sysbench.cmdline.commands.prepare = {
   function ()
      if (not sysbench.opt.auto_inc) then
         -- Create empty tables on prepare when --auto-inc is off, since IDs
         -- generated on prepare may collide later with values generated by
         -- sysbench.rand.unique()
         sysbench.opt.table_size=0
      end
      cmd_prepare()
   end,
   sysbench.cmdline.PARALLEL_COMMAND
}
function prepare_statements()
end
function event()
   local table_name = "sbtest2"
   local comm_val = get_comm_value()
   //由于使用mysql数据库，使用这部分if判断没有修改代码，仍然是模板代码
   if (drv:name() == "pgsql" and sysbench.opt.auto_inc) then 
      con:query(string.format("INSERT INTO %s (k, c, d, pad) VALUES " ..
                                 "(%d, '%s', '%s', '%s')",
                              table_name, k_val, c_val, "coty"+rand(1,10), pad_val))
   else
      if (sysbench.opt.auto_inc) then
         i = 0
      else
         -- Convert a uint32_t value to SQL INT
         i = sysbench.rand.unique() - 2147483648
      end
            //根据表结构进行修改
      con:query(string.format("INSERT INTO %s (NAME, DEAL_DT, DEAL_TYPE, DEAL_MONEY, COMM) VALUES " ..
                                 "('%s', '%s',%d,%d,'%s')",
                              table_name,"NA"..sb_rand_uniform(1,1000000),
                              string.format("%04d/%02d/%02d %02d:%02d:%02d",sb_rand_uniform(2000,2021),sb_rand_uniform(1,12),sb_rand_uniform(1,28),sb_rand_uniform(0,23),sb_rand_uniform(0,59),sb_rand_uniform(0,59)),sb_rand_uniform(1,10),sb_rand_uniform(1,5000), comm_val))
   end
end

通过以下语句启动susbench执行脚本

[root@localhost sysbench]# sysbench --db-driver=mysql --time=300 --threads=10 
--report-interval=1 --mysql-host=127.0.0.1 --mysql-port=8066 --mysql-user=root 
--mysql-password=123456 --mysql-db=testdb --tables=1 --table_size=1 oltp_insert 
--db-ps-mode=disable run

等待程序结束即可，这里执行程序time=300s，共十个线程

sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 2194.71 qps: 2194.71 (r/w/o: 0.00/2194.71/0.00) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 2248.11 qps: 2248.11 (r/w/o: 0.00/2248.11/0.00) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 2085.73 qps: 2085.73 (r/w/o: 0.00/2085.73/0.00) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 1923.55 qps: 1923.55 (r/w/o: 0.00/1923.55/0.00) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00
...