本文介绍了如何使用Sysbench进行MySQL存储过程的性能测试。首先讲解了Sysbench的安装和OLTP测试参数，接着解析了lua脚本，包括common.lua、oltp_simple.lua和oltp.lua。通过自定义lua脚本模拟业务SQL，测试存储过程的性能，以确定其在实际应用中的效率。最后提出了一个基于Sysbench的存储过程测试方案。
入职新公司，领导给的第一个任务就是说：我们打算在业务里面上存储过程，但是据说存储过程性能不好，所以你来出一个测试方案测试一下，额，刚拿到需求的时候懵懵哒，第一，我还真不知道存储过程性能不好呢，理论上来说存储过程会减少服务器与应用程序之间的数据传输，如果数据传输量比较大的话，用存储过程应该会好一些，然后跟老师请教一下，说是MySQL的存储过程的实现确实不如oracle做的好，我以前是用PG写存储过程的，觉得还不错。然后在经过两天胡乱摸索之后，又跟老师请教，然后大致定了一下方案，就是sysbench0.5可以自定义lua脚本来测试了嘛，所以先做一遍基准测试，然后自定义lua脚本来测试，大致是这么个思路。
安装sysbench
yum install m4 autoconf automake libtool #安装依赖包
解压进入路径
./autogen.sh
./configure
make
make install
sysbench的测试有很多项：
- cpu测试
- 线程测试
- mutex测试
- 磁盘IO性能测试
- 内存测试
- OLTP测试
我们这里重点看一下OLTP测试
sysbench的OLTP测试
sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-user=root --mysql-password=root --mysql-host=localhost --mysql-db=sbtest --mysql-socket=/data/mysqldata/3306/mysql.sock prepare
sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-user=root --mysql-password=root --mysql-host=localhost --mysql-db=sbtest --mysql-socket=/data/mysqldata/3306/mysql.sock run
sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-user=root --mysql-password=root --mysql-host=localhost --mysql-db=sbtest --mysql-socket=/data/mysqldata/3306/mysql.sock cleanup几个重要参数解读
- -test
sysbench-0.4是使用–oltp-test-mode用以指定测试模式，取值有(simeple,complex,nontrx)，默认是complex。
- simple只执行简单的查询，0.5版本中就是–test=./tests/db/oltp_simple.lua
- complex是事务型查询，每个线程都在数据表上执行事务。0.5版本中就是–test=./tests/db/oltp.lua
- nontrx是非事务模式，它执行insert或update的语句。0.5版本中有insert.lua等
- -oltp-sp-name=STRING 存储过程的名称。默认为空
- -oltp-read-only=[on|off] 只读模式。Update，delete，insert语句不可执行。默认是off
- -oltp-skip-trx=[on|off] 省略begin/commit语句。默认是off
- -oltp-table-name=STRING 测试时使用到的表名。默认是sbtest
- -oltp-table-size=N 测试表的记录数。默认是10000
我们来看看lua脚本及lua脚本做了什么。
[root@localhost db]# ll
total 84
-rw-r--r--. 1 root root 1001 May 24 2016 bulk_insert.lua
-rw-r--r--. 1 root root 3652 May 24 2016 common.lua
-rw-r--r--. 1 root root 340 May 24 2016 delete.lua
-rw-r--r--. 1 root root 1154 Dec 5 10:08 insert.lua
-rw-r--r--. 1 root root 12293 Dec 2 10:05 Makefile
-rw-r--r--. 1 root root 1085 May 24 2016 Makefile.am
-rw-r--r--. 1 root root 11782 Dec 2 10:04 Makefile.in
-rw-r--r--. 1 root root 2959 May 24 2016 oltp.lua
-rw-r--r--. 1 root root 342 May 24 2016 oltp_simple.lua
-rw-r--r--. 1 root root 501 May 24 2016 parallel_prepare.lua
-rw-r--r--. 1 root root 343 May 24 2016 select.lua
-rw-r--r--. 1 root root 3996 May 24 2016 select_random_points.lua
-rw-r--r--. 1 root root 4098 May 24 2016 select_random_ranges.lua
-rw-r--r--. 1 root root 343 May 24 2016 update_index.lua
-rw-r--r--. 1 root root 552 May 24 2016 update_non_index.lua
lua脚本解析
1.common.lua
-- Input parameters (输入参数解释)
-- oltp-tables-count - number of tables to create(要创建的表的数量)
-- oltp-secondary - use secondary key instead PRIMARY key for id column(为id列使用辅助索引而非主键索引)
--
--
function create_insert(table_id)
local index_name
local i
local j
local query
if (oltp_secondary) then
index_name = "KEY xid"
else
index_name = "PRIMARY KEY"
end
i = table_id
--创建sbtest1，sbtest2，sbtest3...这样的表，oltp-tables-count决定要创建的表的数目
print("Creating table 'sbtest" .. i .. "'...")
if ((db_driver == "mysql") or (db_driver == "attachsql")) then
query = [[
CREATE TABLE sbtest]] .. i .. [[ (
id INTEGER UNSIGNED NOT NULL ]] ..
((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
]] .. index_name .. [[ (id)
) /*! ENGINE = ]] .. mysql_table_engine ..
" MAX_ROWS = " .. myisam_max_rows .. " */ " ..
(mysql_table_options or "")
elseif (db_driver == "pgsql") then
query = [[
CREATE TABLE sbtest]] .. i .. [[ (
id SERIAL NOT NULL,
k INTEGER DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
]] .. index_name .. [[ (id)
) ]]
elseif (db_driver == "drizzle") then
query = [[
CREATE TABLE sbtest (
id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
k INTEGER DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
]] .. index_name .. [[ (id)
) ]]
else
print("Unknown database driver: " .. db_driver)
return 1
end
db_query(query)
db_query("CREATE INDEX k_" .. i .. " on sbtest" .. i .. "(k)")
print("Inserting " .. oltp_table_size .. " records into 'sbtest" .. i .. "'")
if (oltp_auto_inc) then
db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(k, c, pad) VALUES")
else
db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(id, k, c, pad) VALUES")
end
local c_val
local pad_val
for j = 1,oltp_table_size do
c_val = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
pad_val = sb_rand_str([[
###########-###########-###########-###########-###########]])
if (oltp_auto_inc) then
db_bulk_insert_next("(" .. sb_rand(1, oltp_table_size) .. ", '".. c_val .."', '" .. pad_val .. "')")
else
db_bulk_insert_next("("..j.."," .. sb_rand(1, oltp_table_size) .. ",'".. c_val .."', '" .. pad_val .. "' )")
end
end
db_bulk_insert_done()
end
--准备测试数据库
function prepare()
local query
local i
local j
set_vars()
--连接数据库
db_connect()
--依次创建sbtest1，sbtest2，sbtest3..并插入数据
for i = 1,oltp_tables_count do
create_insert(i)
end
return 0
end
--清理测试数据库
function cleanup()
local i
set_vars()
--for循环依次删除sbtest1，sbtest2，sbtest3...
for i = 1,oltp_tables_count do
print("Dropping table 'sbtest" .. i .. "'...")
db_query("DROP TABLE sbtest".. i )
end
end
--设置变量，oltp可用参数都在这里啦！！！
function set_vars()
--这些参数如果设置了就会使用我们设置的值，否则的话取用后面的默认值
oltp_table_size = oltp_table_size or 10000
oltp_range_size = oltp_range_size or 100
oltp_tables_count = oltp_tables_count or 1
--看到点的查询10个，范围查询1个，取总为1个，排序1个，distinct一个，索引更新一个，
--所以我们自定义lua脚本时，根据我们的业务比例来确定sql的执行次数，for循环里面循环比较少的次数，不然在--max-time之前执行不完
oltp_point_selects = oltp_point_selects or 10
oltp_simple_ranges = oltp_simple_ranges or 1
oltp_sum_ranges = oltp_sum_ranges or 1
oltp_order_ranges = oltp_order_ranges or 1
oltp_distinct_ranges = oltp_distinct_ranges or 1
oltp_index_updates = oltp_index_updates or 1
oltp_non_index_updates = oltp_non_index_updates or 1
--oltp_auto_inc：创建表是否有自增主键，我们自定义lua的话这个参数应该不要干扰我们的
if (oltp_auto_inc == 'off') then
oltp_auto_inc = false
else
oltp_auto_inc = true
end
--oltp_read_only:是否开启只读模式，如果是的话，就不测试update那些语句了
if (oltp_read_only == 'on') then
oltp_read_only = true
else
oltp_read_only = false
end
--oltp_skip_trx:是否跳过事务，如果不开启事务的话，应该是单条语句的执行，开启事务的话，就是所有的SQL放在一个事务中进行。
if (oltp_skip_trx == 'on') then
oltp_skip_trx = true
else
oltp_skip_trx = false
end
end插入的表数据如下：
root@localhost 15:37:01 sbtest> select * from sbtest1 limit 10;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 50385 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
| 2 | 50188 | 95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805 | 09512147864-77936258834-40901700703-13541171421-15205431759 |
| 3 | 50193 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-17788128188-73465768032-24619558652 | 21979564480-87492594656-60524686334-78820761788-57684966682 |
| 4 | 49641 | 72200234338-75748118569-08939863650-01688814841-36798767826-71560494483-89421079440-11810718401-29133837777-68338627243 | 80945118876-33522718290-51523426562-15979384524-91541356971 |
| 5 | 49853 | 23749555118-82927985580-59934820346-38519110422-33958726372-68179434013-57381755780-85457880176-06440411187-75543443997 | 63721288074-65922289726-32275902397-42202048996-09343452123 |
| 6 | 50424 | 77981152534-02426776427-14243102257-77939982840-33466068594-21561488340-10177591229-61783484727-02449865640-36716253914 | 82126617622-12647210221-34431310803-28592981065-36359546123 |
| 7 | 46713 | 99754685588-47576951480-32708622771-83861221370-03797298475-60503371617-50159644690-11488793570-28225419667-59109289014 | 19994000982-50192009482-63005057222-82729312489-25570376232 |
| 8 | 60994 | 88658076981-28257193684-53183042641-08566229847-92845627546-46433913626-82618684116-59416871281-45638910500-66696979407 | 16007104994-21315228328-96956154568-55286875724-10424521342 |
| 9 | 50166 | 30259457399-49455699717-43210898264-46300466148-34254750860-44098710066-38295952016-90196077385-22332519290-06484158548 | 40929980986-33813039690-13155419391-97985458477-39771362212 |
| 10 | 49830 | 48090103407-09222928184-34050945574-85418069333-36966673537-23363106719-15284068881-04674238815-26203696337-24037044694 | 01495266405-82925129145-92643983850-90243995398-18709399387 |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
10 rows in set (0.00 sec)2.oltp_simple.lua
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
end
function event(thread_id)
local table_name
table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
--简单的select语句，只执行(select c from sbtest1 where id=1780)这种简单语句
rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
end
3.oltp.lua
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
if (((db_driver == "mysql") or (db_driver == "attachsql")) 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)
--如果没有开启跳过事务，那么就把所有的sql放在一个事务里面
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
--范围查询between...and
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
--distinct去重
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
--如果不是简单的只读，那么我们还要测试几个update操作
if not oltp_read_only then
--update操作，简单的update
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
--update含有索引的列，除了更新表的数据，还会造成索引的更新
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)
--delete操作
rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
c_val = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
pad_val = sb_rand_str([[
###########-###########-###########-###########-###########]])
--insert操作
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自定义lua脚本的结构
pathtest = string.match(test, "(.*/)") or ""
--dofile是lua中的函数，加载并运行脚本，我们需要使用common.lua中的set_vars()函数
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
--其他一些参数的处理
end
--event(thread_id) 可以把sql逻辑写到这里， --num-threads多少个，就会同时调用多少个，然后进行压测就ok
function event(thread_id)
--主要的测试oltp语句在这里
end自定义lua脚本的测试过程
- 首先收集实际业务的访问数据库的sql；
- 准备好一个备份库；
- 将收集的sql写在lua脚本里；
通过sysbench的–test参数和–mysql-db参数进行测试（因为已有测试目标数据库，就不需要prepare了）
因为我们只是先出一个方案，所以数据库先使用用tpcc的数据库做示例，
假设常用的sql如下：
select first from customer where c_id = ?;
select * from orders where c_id=?;
语句使用比例2:5，
那么测试脚本如下：
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
--其他一些参数的处理
if (((db_driver == "mysql") or (db_driver == "attachsql")) 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)
--主要的测试oltp语句在这里
local rs
local c_id
c_id = sb_rand(1,300000)
if not oltp_skip_trx then
db_query(begin_query)
end
for i=1, 2 do
rs = db_query("SELECT c_first FROM customer WHERE c_id=" .. c_id)
end
for i=1, 5 do
rs = db_query("select * from orders where o_c_id=" .. c_id)
end
if not oltp_skip_trx then
db_query(commit_query)
end
end运行测试输出结果：
[root@localhost db]# sysbench --test=mytest.lua --mysql-table-engine=innodb --num-threads=8 --max-time=60 --report-interval=10 --oltp_table_size=1000 --oltp_skip_trx=off --mysql-user=root --mysql-password=root123.. --mysql-host=localhost --mysql-db=tpcc --mysql-socket=/data/mysqldata/3306/mysql.sock run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Initializing worker threads...
Threads started!
[ 10s] threads: 8, tps: 0.80, reads: 10.36, writes: 0.00, response time: 5121.62ms (95%), errors: 0.00, reconnects: 0.00
[ 20s] threads: 8, tps: 1.60, reads: 11.20, writes: 0.00, response time: 5222.24ms (95%), errors: 0.00, reconnects: 0.00
[ 30s] threads: 8, tps: 1.60, reads: 11.00, writes: 0.00, response time: 5198.85ms (95%), errors: 0.00, reconnects: 0.00
[ 40s] threads: 8, tps: 1.60, reads: 10.90, writes: 0.00, response time: 5138.51ms (95%), errors: 0.00, reconnects: 0.00
[ 50s] threads: 8, tps: 1.60, reads: 11.20, writes: 0.00, response time: 5124.68ms (95%), errors: 0.00, reconnects: 0.00
[ 60s] threads: 8, tps: 1.60, reads: 11.00, writes: 0.00, response time: 5166.27ms (95%), errors: 0.00, reconnects: 0.00
OLTP test statistics:
queries performed:
read: 672
write: 0
other: 192
total: 864
transactions: 96 (1.57 per sec.)
read/write requests: 672 (10.99 per sec.)
other operations: 192 (3.14 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 61.1549s
total number of events: 96
total time taken by event execution: 487.9106s
response time:
min: 4733.24ms
avg: 5082.40ms
max: 5274.05ms
approx. 95 percentile: 5198.85ms
Threads fairness:
events (avg/stddev): 12.0000/0.00
execution time (avg/stddev): 60.9888/0.16
测试存储过程
编写一个简单的存储过程：
CREATE TABLE assist_nums(id INT(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY);
DELIMITER //
CREATE FUNCTION get_sequence(itype CHAR(1)) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE turnover_no INT(8) UNSIGNED ZEROFILL;
DECLARE return_turnover VARCHAR(20);
INSERT INTO assist_nums VALUE ();
SELECT last_insert_id() INTO turnover_no FROM assist_nums;
SET return_turnover=CONCAT(itype,DATE_FORMAT(NOW(),'%Y%m%d'),turnover_no);
RETURN return_turnover;
END //
DELIMITER ;这个存储过程只是合并了两个sql而已，分开执行的话，就是执行两个sql：
INSERT INTO assist_nums VALUE ();
select CONCAT(itype,DATE_FORMAT(NOW(),’%Y%m%d’),lpad(last_insert_id(),8,’0’))
使用sql的lua测试
lua脚本
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
--其他一些参数的处理
if (((db_driver == "mysql") or (db_driver == "attachsql")) 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)
--主要的测试oltp语句在这里
local rs
local c_id
c_id = sb_rand(1,300000)
if not oltp_skip_trx then
db_query(begin_query)
end
rs = db_query(" INSERT INTO assist_nums VALUE ()");
rs = db_query(" select CONCAT('R',DATE_FORMAT(NOW(),'%Y%m%d'),lpad(last_insert_id(),8,'0'))");
if not oltp_skip_trx then
db_query(commit_query)
end
end
测试结果
[root@localhost db]# sysbench --test=mytest_sql.lua --mysql-table-engine=innodb --num-threads=8 --max-time=60 --report-interval=10 --oltp_skip_trx=off --mysql-user=root --mysql-password=root123.. --mysql-host=localhost --mysql-db=tpcc --mysql-socket=/data/mysqldata/3306/mysql.sock run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 10000
write: 10000
other: 20000
total: 40000
transactions: 10000 (1676.19 per sec.)
read/write requests: 20000 (3352.38 per sec.)
other operations: 20000 (3352.38 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 5.9659s
total number of events: 10000
total time taken by event execution: 47.7054s
response time:
min: 1.52ms
avg: 4.77ms
max: 265.22ms
approx. 95 percentile: 9.50ms
Threads fairness:
events (avg/stddev): 1250.0000/10.20
execution time (avg/stddev): 5.9632/0.00
使用存储过程的lua脚本：
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
--其他一些参数的处理
if (((db_driver == "mysql") or (db_driver == "attachsql")) 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)
--主要的测试oltp语句在这里
local rs
local c_id
c_id = sb_rand(1,300000)
if not oltp_skip_trx then
db_query(begin_query)
end
rs = db_query("SELECT get_sequence('R')")
if not oltp_skip_trx then
db_query(commit_query)
end
end
测试结果
[root@localhost db]# sysbench --test=mytest_func.lua --mysql-table-engine=innodb --num-threads=8 --max-time=60 --report-interval=10 --oltp_skip_trx=on --mysql-user=root --mysql-password=root123.. --mysql-host=localhost --mysql-db=tpcc --mysql-socket=/data/mysqldata/3306/mysql.sock run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Initializing worker threads...
Threads started!
OLTP test statistics:
queries performed:
read: 10000
write: 0
other: 0
total: 10000
transactions: 0 (0.00 per sec.)
read/write requests: 10000 (2095.49 per sec.)
other operations: 0 (0.00 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 4.7721s
total number of events: 10000
total time taken by event execution: 38.1182s
response time:
min: 0.88ms
avg: 3.81ms
max: 88.28ms
approx. 95 percentile: 7.12ms
Threads fairness:
events (avg/stddev): 1250.0000/6.36
execution time (avg/stddev): 4.7648/0.00
这个存储过程比较简单了，下一步就是逐渐加大–num-threads来测试。
最后再说一下方案：
1. 首先手机实际业务的访问数据库的sql；
2. 准备好一个备份库；
3. 将收集的sql写在lua脚本里；
4. 通过sysbench的–test参数和–mysql-db参数进行测试（因为已有测试目标数据库，就不需要prepare了）
扫一扫
304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
