Sysbench 快速指南

快速开始

安装 Sysbench

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

sudo yum -y install sysbench

安装完成后，可以使用 sysbench -v 查看是否安装成功

部署路径

/usr/share/sysbench

配置连接

vi config

mysql-host={TiDB_Host1,TiDB_Host2}
mysql-port=4000
mysql-user=root
mysql-password=''
mysql-db=sbtest
time=600
threads={8, 16, 32, 64, 128, 256}
report-interval=10
db-driver=mysql

准备数据

sysbench --config-file=config oltp_point_select --tables=32 --table-size=10000000 prepare

prepare 逻辑简述

1. create table

   CREATE TABLE sbtest%d(
   id %s,
   k INTEGER DEFAULT '0' NOT NULL,
   c CHAR(120) DEFAULT '' NOT NULL,
   pad CHAR(60) DEFAULT '' NOT NULL,
   %s (id)
   )

2. prepare data

   if sysbench.opt.auto_inc then
   query = "INSERT INTO sbtest" .. table_num .. "(k, c, pad) VALUES"
   else
   query = "INSERT INTO sbtest" .. table_num .. "(id, k, c, pad) VALUES"
   end

3. prewarm -- driver 为 mysql 时才会做

   con:query("ANALYZE TABLE sbtest" .. i)

   con:query(string.format( "SELECT AVG(id) FROM " "(SELECT * FROM %s FORCE KEY (PRIMARY) " "LIMIT %u) t", t, sysbench.opt.table_size))

   con:query(string.format("SELECT COUNT(*) FROM " "(SELECT * FROM %s WHERE k LIKE '%%0%%' LIMIT %u) t", t, sysbench.opt.table_size))

启动测试

sysbench --config-file=config oltp_point_select --tables=32 --table-size=10000000 run

fuction 简述

• oltp_point_select.lua

  1 × SELECT c FROM sbtest WHERE id=N

• oltp_read_only.lua

  10 × SELECT c FROM sbtest WHERE id=N
  1 × SELECT c FROM sbtest WHERE id BETWEEN N AND M
  1 × SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M
  1 × SELECT c FROM sbtest WHERE id between N and M ORDER BY c
  1 × SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c

• oltp_update_index.lua

  1 × UPDATE sbtest SET k=k+1 WHERE id=N

• oltp_update_non_index.lua

  1 × UPDATE sbtest SET c=N WHERE id=M

• oltp_write_only.lua

  1 × UPDATE sbtest SET k=k+1 WHERE id=N
  1 × UPDATE sbtest SET c=N WHERE id=M
  1 × DELETE FROM sbtest WHERE id=N
  1 × INSERT INTO sbtest VALUES (...)

• oltp_read_write.lua

  10 × SELECT c FROM sbtest WHERE id=N
  1 × SELECT c FROM sbtest WHERE id BETWEEN N AND M
  1 × SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M
  1 × SELECT c FROM sbtest WHERE id between N and M ORDER BY c
  1 × SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c
  1 × UPDATE sbtest SET k=k+1 WHERE id=N
  1 × UPDATE sbtest SET c=N WHERE id=M
  1 × DELETE FROM sbtest WHERE id=N
  1 × INSERT INTO sbtest VALUES (...)