MySQL存储过程性能测试（Sysbench⾃定义lua脚本）
⼊职新公司，领导给的第⼀个任务就是说：我们打算在业务⾥⾯上存储过程，但是据说存储过程性能不好，所以你来出⼀个测试⽅案
测试⼀下，额，刚拿到需求的时候懵懵哒，第⼀，我还真不知道存储过程性能不好呢，理论上来说存储过程会减少服务器与应⽤程序
之间的数据传输，如果数据传输量⽐较⼤的话，⽤存储过程应该会好⼀些，然后跟⽼师请教⼀下，说是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-ur=root --mysql-password=root --mysql-host=localhost sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-ur=root --mysql-password=root --mysql-host=localhost sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-ur=root --mysql-password=root --mysql-host=localhost ⼏个重要参数解读
- -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是⾮事务模式，它执⾏inrt或update的语句。0.5版本中有inrt.lua等
- -oltp-sp-name=STRING 存储过程的名称。默认为空
- -oltp-read-only=[on|off] 只读模式。Update，delete，inrt语句不可执⾏。默认是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 1001May242016 bulk_inrt.lua
-rw-r--r--. 1 root root 3652May242016 common.lua
-rw-r--r--. 1 root root 340May242016 delete.lua
-rw-r--r--. 1 root root 1154Dec510:08 inrt.lua
-
rw-r--r--. 1 root root 12293Dec210:05Makefile
-rw-r--r--. 1 root root 1085May242016Makefile.am
-rw-r--r--. 1 root root 11782Dec210:04Makefile.in
-rw-r--r--. 1 root root 2959May242016 oltp.lua
-rw-r--r--. 1 root root 342May242016 oltp_simple.lua
-rw-r--r--. 1 root root 501May242016 parallel_prepare.lua
-rw-r--r--. 1 root root 343May242016 lect.lua
-rw-r--r--. 1 root root 3996May242016 lect_random_points.lua
-rw-r--r--. 1 root root 4098May242016 lect_random_ranges.lua
-rw-r--r--. 1 root root 343May242016 update_index.lua
-rw-r--r--. 1 root root 552May242016 update_non_index.lua
lua脚本解析
-- Input parameters (输⼊参数解释)
-- oltp-tables-count - number of tables to create(要创建的表的数量)
-- oltp-condary - u condary key instead PRIMARY key for id column(为id列使⽤辅助索引⽽⾮主键索引) --
--
function create_inrt(table_id)
local index_name
local i
local j
local query
if (oltp_condary) then
index_name = "KEY xid"
el
index_name = "PRIMARY KEY"
end
i = table_id
--创建sbtest1，sbtest2，这样的表，oltp-tables-count决定要创建的表的数⽬
print("Creating table 'sbtest" .. i .. "'...")
if ((db_driver == "mysql") or (db_driver == "attachsql")) then郑州ui培训机构
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 .. " */ " ..
weka(mysql_table_options or"")
elif (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)
) ]]
elif (db_driver == "drizzle") then
ephquery = [[
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)
) ]]
el
print("Unknown databa driver: " .. db_driver)
return1
end
db_query(query)
db_query("CREATE INDEX k_" .. i .. " on sbtest" .. i .. "(k)")
print("Inrting " .. oltp_table_size .. " records into 'sbtest" .. i .. "'")
if (oltp_auto_inc) then
db_bulk_inrt_init("INSERT INTO sbtest" .. i .. "(k, c, pad) VALUES")
el
db_bulk_inrt_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_inrt_next("(" .. sb_rand(1, oltp_table_size) .. ", '".. c_val .."', '" .. pad_val .. "')")
qty暮光之城音乐el
db_bulk_inrt_next("("..j.."," .. sb_rand(1, oltp_table_size) .. ",'".. c_val .."', '" .. pad_val .. "' )")
end
end
db_bulk_inrt_done()
end
--准备测试数据库bonny
function prepare()
local query
local i季札挂剑翻译
local j
t_vars()
--连接数据库
db_connect()
--依次创建sbtest1，sbtest2，sbtest3..并插⼊数据
for i = 1,oltp_tables_count do
create_inrt(i)
end
return0
end
--清理测试数据库
function cleanup()
local i
t_vars()
--for循环依次删除sbtest1，sbtest2，
for i = 1,oltp_tables_count do
print("Dropping table 'sbtest" .. i .. "'...")
db_query("DROP TABLE sbtest".. i )
end
end
-
-设置变量，oltp可⽤参数都在这⾥啦
function t_vars()
--这些参数如果设置了就会使⽤我们设置的值，否则的话取⽤后⾯的默认值
oltp_table_size = oltp_table_size or10000
oltp_range_size = oltp_range_size or100
oltp_tables_count = oltp_tables_count or1
--看到点的查询10个，范围查询1个，取总为1个，排序1个，distinct⼀个，索引更新⼀个，
--所以我们⾃定义lua脚本时，根据我们的业务⽐例来确定sql的执⾏次数，for循环⾥⾯循环⽐较少的次数，不然在--max-time之前执⾏不完 oltp_point_lects = oltp_point_lects or10
oltp_simple_ranges = oltp_simple_ranges or1
oltp_sum_ranges = oltp_sum_ranges or1
oltp_order_ranges = oltp_order_ranges or1
oltp_distinct_ranges = oltp_distinct_ranges or1
oltp_index_updates = oltp_index_updates or1
oltp_non_index_updates = oltp_non_index_updates or1
--oltp_auto_inc：创建表是否有⾃增主键，我们⾃定义lua的话这个参数应该不要⼲扰我们的
if (oltp_auto_inc == 'off') then
oltp_auto_inc = fal
el
oltp_auto_inc = true
end
--oltp_read_only：是否开启只读模式，如果是的话，就不测试update那些语句了
if (oltp_read_only == 'on') then
oltp_read_only = true
el
oltp_read_only = fal
end
--oltp_skip_trx：是否跳过事务，如果不开启事务的话，应该是单条语句的执⾏，开启事务的话，就是所有的SQL放在⼀个事务中进⾏。
if (oltp_skip_trx == 'on') then
oltp_skip_trx = true
el
oltp_skip_trx = fal
end
end
插⼊的表数据如下：
root@localhost 15:37:01 sbtest> lect * from sbtest1 limit 10;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------| 1 | 50385 | 0856*******-88624912351-166********-46648573979-64646226163-77505759394-75470094713-41097360717-151********-5053556 | 2 | 50188 | 95969429576-20587925969-20202408199-67602281819-182********-38184587501-73192830026-41693404212-56705243222-8921237 | 3 | 50193 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-177********-73465768032-2461955 | 4 | 49641 | 72200234338-75748118569-0893*******-01688814841-36798767826-71560494483-89421079440-11810718401-29133837777-6833862 | 5 | 49853 | 23749555118-82927985580-59934820346-38519110422-33958726372-68179434013-57381755780-85457880176-06440411187-7554344 | 6 | 50424 | 77981152534-024**
******-14243102257-77939982840-33466068594-21561488340-10177591229-61783484727-024********-3671625 | 7 | 46713 | 99754685588-47576951480-32708622771-83861221370-0379*******-60503371617-50159644690-11488793570-28225419667-5910928 | 8 | 60994 | 88658076981-28257193684-53183042641-0856*******-92845627546-46433913626-82618684116-59416871281-45638910500-6669697 | 9 | 50166 | 30259457399-49455699717-43210898264-46300466148-34254750860-44098710066-38295952016-90196077385-22332519290-0648415 | 10 | 49830 | 48090103407-09222928184-34050945574-85418069333-36966673537-23363106719-152********-04674238815-26203696337-2403704 +----+-------+-------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------
10 rows in t (0.00 c)
2.oltp_simple.lua
pathtest = string.match(test, "(.*/)") or""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
t_vars()
end
function event(thread_id)
local table_name
table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
--简单的lect语句，只执⾏(lect 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)
t_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"
el
begin_query = "BEGIN"
commit_query = "COMMIT"
end
end
function event(thread_id)
local rs
local i
local table_name
local range_start
big freezelocal c_val
local pad_val
local query
