1. Sysbench 是工业标准的MySQL性能测试工具之一,是MySQL平台主要的性能测试工具。Sysbench从0.5开始支持强大的LUA脚本引擎,实现多线程,多并发,多表的测试,更加贴近实际应用环境。
Sysbench 按照模块的来设计的,支持以下测试内容:
- 文件系统I/O性能
- 操作系统内核任务调度器性能
- 内存分配和传输性能
- POSIX线程性能
- 数据库服务器性能 (MySQL , PostgreSQL)

2. Sysbench 基本工作机制
- 数据准备阶段
数据准备阶段会将指定规模的测试数据插入到MySQL测试库/表中。

- 基准测试执行阶段
OLTP测试模式是主要的数据库测试模式,模拟应用进行SQL事务处理,Sysbench的每一个事务,分解成以下几个操作:
(1) 点查询 (SELECT c FROM sbtest WHERE id=N)
(2) 范围查询 (SELECT c FROM sbtest WHERE id BETWEEN N AND M)
(3) 范围求和查询 (SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M)
(4) 范围排序查询 (SELECT c FROM sbtest WHERE id between N and M ORDER BY c)
(5) 范围DISTINCT查询(SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c)
(6) 基于索引列的更新操作 (UPDATE sbtest SET k=k+1 WHERE id=N )
(7) 基于非索引列的更新操作 (UPDATE sbtest SET c=N WHERE id=M)
(8) 删除操作 (DELETE FROM sbtest WHERE id=N)
(9) 插入操作 (INSERT INTO sbtest VALUES (…) )

Sysbench 在执行阶段可以灵活选择运行参数,主要可设定参数如下:
(1) 并发线程数量 (模拟对数据库的并发访问量)
(2) 数据库表的数量 (模拟基于数据库的应用的库表操作复杂度)
(3) 只读测试模式 (模拟将数据库用于海量数据分析,简单查询和复杂查询)
(4) OLTP测试模式 (简单事务处理,复杂事务处理,支持统一分布,高斯分布等)

- 业务逻辑模拟压力测试

Sysbench 会读取两部分参数:
第一部分参数是发起Sysbench的启动脚本传递进来的并发数控制参数,包括测试前的自动化预热操作等,
第二部分数据是通过调用内置的LUA解释引擎,调用预先编写好的LUA脚本中的业务逻辑进行执行。

- 结果输出阶段
Sysbench 测试过程中,默认从屏幕打印每一次线程处理过程中的各项完成指标,一个典型的单次结果输出 显示如下:

sysbench 0.5:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 140
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Threads started
[  10s] threads: 140, tps: 2297.97, reads/s: 32467.14, writes/s: 9265.30, response time: 177.30ms (95%)
[  20s] threads: 140, tps: 1721.90, reads/s: 24180.78, writes/s: 6886.29, response time: 239.60ms (95%)
OLTP test statistics:
    queries performed:
        read:                            1720096
        write:                           491307
        other:                           245131
        total:                           2456534
    transactions:                        122267 (2031.56 per sec.)
    deadlocks:                           597    (9.92 per sec.)
    read/write requests:                 2211403 (36744.22 per sec.)
    other operations:                    245131 (4073.05 per sec.)
General statistics:
    total time:                          60.1837s
    total number of events:              122267
    total time taken by event execution: 8417.6129s
    response time:
         min:                                 11.15ms
         avg:                                 68.85ms
         max:                               1883.92ms
         approx.  95 percentile:             185.61ms
Threads fairness:
    events (avg/stddev):           873.3357/26.23
    execution time (avg/stddev):   60.1258/0.05

测试结果集合,代表了执行基准测试逻辑和模拟业务逻辑之后,产生的各项性能指标值,包括最重要的TPS(Transaction Per Second)

3. 基准测试逻辑(LUA)

pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
   set_vars()
   if (db_driver == "mysql" and mysql_table_engine == "myisam") then
      begin_query = "LOCK TABLES sbtest WRITE"
      commit_query = "UNLOCK TABLES"
   else
      begin_query = "BEGIN"
      commit_query = "COMMIT"
   end
end

function event(thread_id)
   local rs
   local i
   local table_name
   local range_start
   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

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

   for i=1, oltp_simple_ranges do
      range_start = sb_rand(1, oltp_table_size)
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
   end
  
   for i=1, oltp_sum_ranges do
      range_start = sb_rand(1, oltp_table_size)
      rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
   end
   
   for i=1, oltp_order_ranges do
      range_start = sb_rand(1, oltp_table_size)
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
   end

   for i=1, oltp_distinct_ranges do
      range_start = sb_rand(1, oltp_table_size)
      rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
   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

   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 -- oltp_read_only

   if not oltp_skip_trx then
      db_query(commit_query)
   end
end

注:如果需要模拟业务逻辑进行MySQL压力测试,请编写对应的业务逻辑LUA脚本。

Trackback

no comment untill now

Add your comment now

切换到手机版