性能白皮书

安装Sysbench

1. 在ECS实例中，下载并安装SysBench。

   # 安装sysbench依赖库
   yum -y install make automake libtool pkgconfig libaio-devel
   yum -y install mariadb-devel openssl-devel
   
   # 下载sysbench源码
   wget "https://codeload.github.com/akopytov/sysbench/zip/refs/tags/1.0.20" -O sysbench-1.0.20.zip
   
   # 安装unzip
   yum -y install unzip
   
   # 解压源码
   unzip sysbench-1.0.20.zip
   
   # 编译源码
   cd sysbench-1.0.20
   ./autogen.sh
   ./configure
   sed -i 's/MYSQL_OPT_COMPRESSION_ALGORITHMS/MYSQL_OPT_COMPRESS/g' ./src/drivers/mysql/drv_mysql.c
   make -j
   
   # 安装
   make install

2. 准备大表场景（dm_large_table_write_only.lua）、热点数据更新场景（dm_hot_update_only.lua）、DDL同步场景（dm_ddl_only.lua）的Lua脚本文件。

   Lua脚本文件

   dm_large_table_write_only.lua

   #!/usr/bin/env sysbench
   -- Copyright (C) 2006-2017 Alexey Kopytov <akopytov@gmail.com>
   
   -- This program is free software; you can redistribute it and/or modify
   -- it under the terms of the GNU General Public License as published by
   -- the Free Software Foundation; either version 2 of the License, or
   -- (at your option) any later version.
   
   -- This program is distributed in the hope that it will be useful,
   -- but WITHOUT ANY WARRANTY; without even the implied warranty of
   -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   -- GNU General Public License for more details.
   
   -- You should have received a copy of the GNU General Public License
   -- along with this program; if not, write to the Free Software
   -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
   
   -- ----------------------------------------------------------------------
   -- Large table benchmark for data migration
   -- ----------------------------------------------------------------------
   require("oltp_common")
   
   local function get_table_num()
      return sysbench.rand.uniform(1, sysbench.opt.tables)
   end
   
   local function get_id()
      return sysbench.rand.default(1, sysbench.opt.table_size)
   end
   
   -- Generate large string template, size 1024 * 50
   local function get_large_templete()
      local large_temp = ""
      for i=1,1024 do
         large_temp = large_temp .. "#########-#########-#########-#########-#########-"
      end
      return large_temp
   end
   
   -- Generate large string template, size 1024 * 50
   local large_template = get_large_templete()
   
   -- Generate large value, size 1024 * 50
   local function get_large_value()
      return sysbench.rand.string(large_template)
   end
   
   function large_create_table(drv, con, table_num)
      if drv:name() == "mysql"
      then
         print(string.format("Creating mysql table 'sbtest%d'...", table_num))
      else
         error("Unsupported database driver:" .. drv:name())
      end
   
      query = string.format([[
   CREATE TABLE sbtest%d(
     id int not null auto_increment,
     k INTEGER DEFAULT '0' NOT NULL,
     c longtext,
     pad longtext,
     primary key (id)
   ) ]],table_num)
   
      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
      query = "INSERT INTO sbtest" .. table_num .. "(k, c, pad) VALUES"
      con:bulk_insert_init(query)
   
      for i = 1, sysbench.opt.table_size do
   
         local large_value = get_large_value()
         query = string.format("(%d, '%s', '%s')",
                                  sysbench.rand.default(1, sysbench.opt.table_size),
                                  large_value, large_value)
         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
   
   
   function execute_delete_inserts_large()
      local tnum = get_table_num()
   
      for i = 1, sysbench.opt.delete_inserts do
         local id = get_id()
         local k = get_id()
   
         param[tnum].deletes[1]:set(id)
   
         param[tnum].inserts[1]:set(id)
         param[tnum].inserts[2]:set(k)
         param[tnum].inserts[3]:set_rand_str(large_template)
         param[tnum].inserts[4]:set_rand_str(large_template)
   
         stmt[tnum].deletes:execute()
         stmt[tnum].inserts:execute()
      end
   end
   
   function execute_non_index_updates_large()
      local tnum = get_table_num()
   
      for i = 1, sysbench.opt.non_index_updates do
         param[tnum].non_index_updates[1]:set_rand_str(large_template)
         param[tnum].non_index_updates[2]:set(get_id())
   
         stmt[tnum].non_index_updates:execute()
      end
   end
   
   local t = sysbench.sql.type
   local stmt_defs = {
      point_selects = {
         "SELECT c FROM sbtest%u WHERE id=?",
         t.INT},
      simple_ranges = {
         "SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?",
         t.INT, t.INT},
      sum_ranges = {
         "SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?",
          t.INT, t.INT},
      order_ranges = {
         "SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
          t.INT, t.INT},
      distinct_ranges = {
         "SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
         t.INT, t.INT},
      index_updates = {
         "UPDATE sbtest%u SET k=k+1 WHERE id=?",
         t.INT},
      non_index_updates = {
         "UPDATE sbtest%u SET c=? WHERE id=?",
         {t.CHAR, 51200}, t.INT},
      deletes = {
         "DELETE FROM sbtest%u WHERE id=?",
         t.INT},
      inserts = {
         "INSERT INTO sbtest%u (id, k, c, pad) VALUES (?, ?, ?, ?)",
         t.INT, t.INT, {t.CHAR, 51200}, {t.CHAR, 51200}},
   }
   
   function prepare_for_each_large_table(key)
      for t = 1, sysbench.opt.tables do
         stmt[t][key] = con:prepare(string.format(stmt_defs[key][1], t))
   
         local nparam = #stmt_defs[key] - 1
   
         if nparam > 0 then
            param[t][key] = {}
         end
   
         for p = 1, nparam do
            local btype = stmt_defs[key][p+1]
            local len
   
            if type(btype) == "table" then
               len = btype[2]
               btype = btype[1]
            end
            if btype == sysbench.sql.type.VARCHAR or
               btype == sysbench.sql.type.CHAR then
                  param[t][key][p] = stmt[t][key]:bind_create(btype, len)
            else
               param[t][key][p] = stmt[t][key]:bind_create(btype)
            end
         end
   
         if nparam > 0 then
            stmt[t][key]:bind_param(unpack(param[t][key]))
         end
      end
   end
   
   -- Overwrite oltp_common create_table function  
   create_table = large_create_table
   
   -- Overwrite oltp_common prepare_for_each_table function  
   prepare_for_each_table = prepare_for_each_large_table
   
   -- Overwrite oltp_common execute_delete_inserts function  
   execute_delete_inserts = execute_delete_inserts_large
   
   -- Overwrite oltp_common execute_non_index_updates function  
   execute_non_index_updates = execute_non_index_updates_large
   
   function prepare_statements()
      if not sysbench.opt.skip_trx then
         prepare_begin()
         prepare_commit()
      end
   
      prepare_index_updates()
      prepare_non_index_updates()
      prepare_delete_inserts()
   end
   
   function event()
      if not sysbench.opt.skip_trx then
         begin()
      end
   
      execute_index_updates()
      execute_non_index_updates()
      execute_delete_inserts()
   
      if not sysbench.opt.skip_trx then
         commit()
      end
   end

   dm_hot_update_only.lua

   #!/usr/bin/env sysbench
   -- Copyright (C) 2006-2017 Alexey Kopytov <akopytov@gmail.com>
   
   -- This program is free software; you can redistribute it and/or modify
   -- it under the terms of the GNU General Public License as published by
   -- the Free Software Foundation; either version 2 of the License, or
   -- (at your option) any later version.
   
   -- This program is distributed in the hope that it will be useful,
   -- but WITHOUT ANY WARRANTY; without even the implied warranty of
   -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   -- GNU General Public License for more details.
   
   -- You should have received a copy of the GNU General Public License
   -- along with this program; if not, write to the Free Software
   -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
   
   -- ----------------------------------------------------------------------
   -- Hot update benchmark for data migration
   -- ----------------------------------------------------------------------
   require("oltp_common")
   
   function create_hot_update_table(drv, con, table_num)
      local query
      if table_num < 3
      then
         query = string.format([[
            CREATE TABLE IF NOT EXISTS sbtest%d(
              id int not null auto_increment,
              k INTEGER DEFAULT '0' NOT NULL,
              primary key (id)
            )]], table_num)
               con:query(query)
         query = string.format([[
            CREATE PROCEDURE generate_hot_update_sbtest%d (IN id_val int(11), IN update_count int(11) )
            BEGIN
              DECLARE i int default 1;
              INSERT IGNORE INTO sbtest%d (id,k) values(id_val,id_val); 
              WHILE i<=update_count DO
                  UPDATE sbtest%d SET k=k+1 where id=id_val; 
                  SET i = i + 1;
              END WHILE;
            END]], table_num, table_num, table_num)
               con:query(query)
      else
         print(string.format("Ignore create table sbtest%d",table_num))
      end
   end
   
   -- Overwrite oltp_common create_table function  
   create_table = create_hot_update_table
   
   function prepare_statements()
   end
   
   local function get_table_num()
      return sysbench.rand.uniform(1, 2)
   end
   
   local function get_id()
      return sysbench.rand.default(1, 10)
   end
   
   function event()
      local table_id
      local query
      local id_val
   
      id_val = get_id()
   
      table_id = get_table_num()
      query = string.format("CALL generate_hot_update_sbtest%d(%d,10)",table_id, id_val)
      con:query(query)
   end
   

   dm_ddl_only.lua

   #!/usr/bin/env sysbench
   -- Copyright (C) 2006-2017 Alexey Kopytov <akopytov@gmail.com>
   
   -- This program is free software; you can redistribute it and/or modify
   -- it under the terms of the GNU General Public License as published by
   -- the Free Software Foundation; either version 2 of the License, or
   -- (at your option) any later version.
   
   -- This program is distributed in the hope that it will be useful,
   -- but WITHOUT ANY WARRANTY; without even the implied warranty of
   -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   -- GNU General Public License for more details.
   
   -- You should have received a copy of the GNU General Public License
   -- along with this program; if not, write to the Free Software
   -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
   
   -- ----------------------------------------------------------------------
   -- DDL benchmark for data migration
   -- ----------------------------------------------------------------------
   require("oltp_common")
   
   function empty_create_table(drv, con, table_num)
      print(string.format("Ignore create table sbtest%d",table_num))
   end
   
   create_table = empty_create_table
   
   function prepare_statements()
   end
   
   local function get_table_num()
      return sysbench.rand.uniform(1, sysbench.opt.tables)
   end
   
   function event()
      local table_id
      local create_query
      local drop_query
   
      table_id = get_table_num()
      create_query = string.format([[
   CREATE TABLE IF NOT EXISTS sbtest%d(
     id int not null auto_increment,
     k INTEGER DEFAULT '0' NOT NULL,
     c CHAR(120) DEFAULT '' NOT NULL,
     pad CHAR(60) DEFAULT '' NOT NULL,
     primary key (id)
   )]],table_id)
      con:query(create_query)
   
      drop_query = string.format("DROP TABLE IF EXISTS sbtest%d",table_id)
      con:query(drop_query)
   end
   

3. 将三个Lua脚本上传到ECS实例中，并复制到Sysbench脚本目录/usr/local/share/sysbench/中。

   # 复制大表场景脚本
   cp dm_large_table_write_only.lua /usr/local/share/sysbench/
   # 复制热点数据更新场景脚本
   cp dm_hot_update_only.lua /usr/local/share/sysbench/
   # 复制DDL同步场景脚本
   cp dm_ddl_only.lua /usr/local/share/sysbench/

安装BenchmarkSQL

# 安装ant工具
yum -y install ant

# 下载BenchmarkSQL源码
wget "https://github.com/jackysp/benchmarksql/archive/refs/heads/5.0-mysql-support-opt-2.1.zip" -O benchmarksql-5.0-mysql-support-opt-2.1.zip

# 解压源码
unzip benchmarksql-5.0-mysql-support-opt-2.1.zip

# 构建源码
cd benchmarksql-5.0-mysql-support-opt-2.1
ant