MySQL随机写入脚本

使用MySQL命令登录数据库，并创建测试库“loadtest”，用弹性云服务器ECS的私网IP替换命令中的【mysql-ip】命令如下

mysql -u root -h 【mysql-ip】 -p -e "create database loadtest"

使用sysbench命令导入测试数据到“loadtest”数据库；用弹性云服务器ECS的私网IP替换命令中的【host】,命令如下

sysbench /root/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password='Cloud12#$' --mysql-port=3306 --mysql-host='【host】' --oltp-tables-count=10 --oltp-table-size=10000 --threads=20 prepare

oltp.lua文件

pathtest = string.match(test, "(.*/)")

if pathtest then
   dofile(pathtest .. "common.lua")
else
   require("common")
end

function thread_init()
   set_vars()

   if (((db_driver == "mysql") or (db_driver == "attachsql")) and mysql_table_engine == "myisam") then
      local i
      local tables = {}
      for i=1, oltp_tables_count do
         tables[i] = string.format("sbtest%i WRITE", i)
      end
      begin_query = "LOCK TABLES " .. table.concat(tables, " ,")
      commit_query = "UNLOCK TABLES"
   else
      begin_query = "BEGIN"
      commit_query = "COMMIT"
   end

end

function get_range_str()
   local start = sb_rand(1, oltp_table_size)
   return string.format(" WHERE id BETWEEN %u AND %u",
                        start, start + oltp_range_size - 1)
end

function event()
   local rs
   local i
   local table_name
   local c_val
   local pad_val
   local query

   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
   if not oltp_skip_trx then
      db_query(begin_query)
   end

   if not oltp_write_only then

   for i=1, oltp_point_selects do
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" ..
                       sb_rand(1, oltp_table_size))
   end

   if oltp_range_selects then

   for i=1, oltp_simple_ranges do
      rs = db_query("SELECT c FROM ".. table_name .. get_range_str())
   end

   for i=1, oltp_sum_ranges do
      rs = db_query("SELECT SUM(K) FROM ".. table_name .. get_range_str())
   end

   for i=1, oltp_order_ranges do
      rs = db_query("SELECT c FROM ".. table_name .. get_range_str() ..
                    " ORDER BY c")
   end

   for i=1, oltp_distinct_ranges do
      rs = db_query("SELECT DISTINCT c FROM ".. table_name .. get_range_str() ..
                    " ORDER BY c")
   end

   end

   end
   
   if not oltp_read_only then

   for i=1, oltp_index_updates do
      rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
   end

   for i=1, oltp_non_index_updates do
      c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
      query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
      rs = db_query(query)
      if rs then
        print(query)
      end
   end

   for i=1, oltp_delete_inserts do

   i = sb_rand(1, oltp_table_size)

   rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
   
   c_val = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
   pad_val = sb_rand_str([[
###########-###########-###########-###########-###########]])

   rs = db_query("INSERT INTO " .. table_name ..  " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))

   end

   end -- oltp_read_only

   if not oltp_skip_trx then
      db_query(commit_query)
   end

end

执行测试【保持命令行开启】，用弹性云服务器ECS的私网IP替换命令中的【host】，命令如下：

sysbench /root/sysbench/tests/include/oltp_legacy/insert.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password='Cloud12#$' --mysql-port=3306 --mysql-host='【host】' --oltp-tables-count=10 --oltp-table-size=1000 --max-time=3600 --max-requests=0 --threads=10 --report-interval=3 --rate=20 --forced-shutdown=1 run

insert.lua文件

pathtest = string.match(test, "(.*/)")

if pathtest then
   dofile(pathtest .. "common.lua")
else
   require("common")
end

function thread_init(thread_id)
   set_vars()
end

function event()
   local table_name
   local i
   local c_val
   local k_val
   local pad_val

   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)

   k_val = sb_rand(1, oltp_table_size)
   c_val = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
   pad_val = sb_rand_str([[
###########-###########-###########-###########-###########]])

   if (db_driver == "pgsql" and oltp_auto_inc) then
      rs = db_query("INSERT INTO " .. table_name .. " (k, c, pad) VALUES " ..
                       string.format("(%d, '%s', '%s')", k_val, c_val, pad_val))
   else
      if (oltp_auto_inc) then
         i = 0
      else
         i = sb_rand_uniq()
      end
      rs = db_query("INSERT INTO " .. table_name ..
                       " (id, k, c, pad) VALUES " ..
                       string.format("(%d, %d, '%s', '%s')", i, k_val, c_val,
                                     pad_val))
   end
end

查看CPU情况
top

查看IO情况
iostat -d vda vdb -m 1 10

相关