1背景
TPC(Tracsaction Processing Performance Council)事务处理性能协会是一个评价大型数据库系统软硬件性能的非盈利的组织,TPC-C是TPC协会制定的,用来测试典型的复杂OLTP系统的性能;Tpcc-mysql是percona基于tpcc衍生出来的产品,专用于mysql基准测试,其源码放在bazaar上,因此需要先安装bazaar客户端。
2原理
实现了一个完整的订单系统(创建对应表和外键约束),测试更接近生产实例;
3安装
yum install bzr
bzr branch lp:~percona-dev/perconatools/tpcc-mysql
编译
cd /tmp/tpcc-mysql/src
make
安装完毕在根目录下有:
[root@localhost tpcc-mysql]# ls
add_fkey_idx.sql
create_table.sql
load.sh
schema2 –shell脚本
src--c文件, 用于构造表数据
tpcc_start
count.sql
drop_cons.sql
README
scripts
tpcc_load
4创建测试环境
手工创建一个数据库,然后导入表和索引结构
mysql> create database tpcc1000;
mysql> use tpcc1000
mysql> \. /opt/create_table.sql
mysql> \. /opt/add_fkey_idx.sql
生成数据
tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]
./tpcc_load localhost tpcc1000 root "" 1000
生成测试数据(这步可能需要很长时间,根据机器性能不同而不同),load.sh可用于并行加载;
初始化完毕后,就可以开始加载测试数据了
问1:warehouse的用途?
一般仓库设置为40-100个为cpu bound,400-1000个是为了测试io bound。40以下无论事务多少,锁竞争情况也不太容易发生
5测试用例
tpcc_start的用法也比较简单
tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file
几个选项稍微解释下
-w 指定仓库数量
-c 指定并发连接数
-r 指定开始测试前进行warmup的时间,进行预热后,测试效果更好
-l 指定测试持续时间
-I 指定生成报告间隔时长
-f 指定生成的报告文件名
下列测试分别对innodb_buffer_pool_size = 8M,512M,2G 进行测试,事务及实际关系结果下如图1所示:
./tpcc_start -h172.16.0.230 -dtpcc1000 -unigel -p12345 -w20 -c16 -r10 -l1200 > /opt/8m-tpcc-data.log
./tpcc_start -h172.16.0.230 -dtpcc1000 -unigel -p12345 -w20 -c16 -r10 -l1200 > /opt/512m-tpcc-data.log
./tpcc_start -h172.16.0.230 -dtpcc1000 -unigel -p12345 -w20 -c16 -r10 -l1200 > /opt/2g-tpcc-data.log
6输出分析
CountNew-OrderPaymentOrder-StatusDeliveryStock-Level
sl(l):rt90|max_rtsl(l):rt90|max_rtsl(l):rt90|max_rtsl(l):rt90|max_rtsl(l):rt90|max_rt
#,#(#):#|#,#(#):#|#,#(#):#|#,#(#):#|#,#(#):#|#
10, 861(0):1.544|2.791, 858(0):0.471|1.265, 86(0):0.245|0.500, 87(0):2.102|3.095, 86(0):5.127|11.375
上述结果标明新订单 时间间隔内成功的事务数861个,延迟事务0个,90%的rt 小于1.54ms最高的rt 2.791ms
支付成功的事务数858个,延迟事务0个,90%的rt是在0.471ms 最高的rt在1.265ms
订单查询 成功的事务数86个,延迟事务0个,90%的rt是在0.245ms 最高的rt在0.500ms
发货成功的事务数87个,延迟事务0个,90%的rt是在2.102ms 最高的rt在3.095ms
库存成功的事务数86个,延迟事务0个,90%的rt是在5.127ms 最高的rt在11.375ms
TPCC-MySQL输出结果包括五个业务逻辑,这五个业务逻辑构成了TPCC-MySQL测试的整个事务处理过程。
具体如下所示:
New-Order:新订单
Payment:支付
Order-Status:订单查询
Delivery:发货
Stock-Level:库存
预定义变量:
为了能够清晰的说明以下内容,首先定义一些变量,便于以下的计算和说明。具体如下所示:
success = 执行成功的记录数
late = 执行延迟的记录数
pre_success=上一次执行成功的记录数
pre_late =上一次执行失败的记录数
late定义:
根据不同的业务,late的定义也不同,五种业务逻辑分别对操作延迟定义的界限值如下所示:
New-Order5ms
Payment5ms
Order-Status5ms
Delivery80ms
Stock-Level20ms
计算:
根据以上定义的变量,计算相应字段的结果和说明相应字段的含义。
1、时间间隔内成功的事务(包括成功和延迟的事务):sl=success+late-pre_success-pre_late
2、时间间隔内延迟的事务:l=late-pre_late
3、时间间隔内前90%记录(实际为99%)的平均 rt:rt90
4、时间间隔内最大的rt:max_rt
附录1
gnuplot绘图
生成测试数据文件:
./tpcc_analyze.sh /opt/8m-tpcc-data.log > tpcc-8-data.txt
./tpcc_analyze.sh/opt/512m-tpcc-data.log > tpcc-512-data.txt
./tpcc_analyze.sh /opt/2g-tpcc-data.log > tpcc-2g-data.txt
使用gnuplot 绘图
1> 合并数据文件,以便于画图:
pastetpcc-8-data.txt tpcc-512-data.txt tpcc-2g-data.txt > tpcc-graph-data.txt
2> 使用脚本画图;
./tpcc-graph.shtpcc-graph-data.txt201.jpg
[root@localhost shell]# cat tpcc_analyze.sh
#!/bin/bash
TIMESLOT=1
if [ -n "$2" ]
then
TIMESLOT=$2
echo "Defined $2"
fi
cat $1 | grep -v HY000 | grep -v payment | grep -v neword | \
awk -v timeslot=$TIMESLOT ' BEGIN { FS="[,():]"; s=0; cntr=0; aggr=0 } \
/MEASURING START/ { s=1} /STOPPING THREADS/ {s=0} /0/ { if (s==1) { cntr++; aggr+=$2; } \
if ( cntr==timeslot ) { printf ("%d ?\n",$1,aggr) ; cntr=0; aggr=0}} '
[root@localhost shell]# cat tpcc-graph.sh
#!/bin/bash
gnuplot << EOP
set style line 1 lt 1 lw 3
set style line 2 lt 5 lw 3
set style line 3 lt 9 lw 3
set terminal jpeg size 640,480
set grid x y
set xlabel "Time(sec)"
set ylabel "Transactions"
set output '$2'
plot "$1" title "PS 5.1.56 buffer pool 8M" ls 1 with lines , \
"$1" us 3:4 title "PS 5.1.56 buffer pool 512M" ls 2 with lines ,\
"$1" us 5:6 title "PS 5.1.56 buffer pool 2G" ls 3 with lines axes x1y1
EOP
附录2
建表脚本
Create_table.sql
SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
droptableifexistswarehouse;
createtablewarehouse (
w_idsmallintnotnull,
w_namevarchar(10),
w_street_1varchar(20),
w_street_2varchar(20),
w_cityvarchar(20),
w_statechar(2),
w_zipchar(9),
w_taxdecimal(4,2),
w_ytddecimal(12,2),
primarykey(w_id) ) Engine=InnoDB;
droptableifexistsdistrict;
createtabledistrict (
d_id tinyintnotnull,
d_w_idsmallintnotnull,
d_namevarchar(10),
d_street_1varchar(20),
d_street_2varchar(20),
d_cityvarchar(20),
d_statechar(2),
d_zipchar(9),
d_taxdecimal(4,2),
d_ytddecimal(12,2),
d_next_o_idint,
primarykey(d_w_id, d_id) ) Engine=InnoDB;
droptableifexistscustomer;
createtablecustomer (
c_idintnotnull,
c_d_id tinyintnotnull,
c_w_idsmallintnotnull,
c_firstvarchar(16),
c_middlechar(2),
c_lastvarchar(16),
c_street_1varchar(20),
c_street_2varchar(20),
c_cityvarchar(20),
c_statechar(2),
c_zipchar(9),
c_phonechar(16),
c_since datetime,
c_creditchar(2),
c_credit_limbigint,
c_discountdecimal(4,2),
c_balancedecimal(12,2),
c_ytd_paymentdecimal(12,2),
c_payment_cntsmallint,
c_delivery_cntsmallint,
c_datatext,
PRIMARYKEY(c_w_id, c_d_id, c_id) ) Engine=InnoDB;
droptableifexistshistory;
createtablehistory (
h_c_idint,
h_c_d_id tinyint,
h_c_w_idsmallint,
h_d_id tinyint,
h_w_idsmallint,
h_date datetime,
h_amountdecimal(6,2),
h_datavarchar(24) ) Engine=InnoDB;
droptableifexistsnew_orders;
createtablenew_orders (
no_o_idintnotnull,
no_d_id tinyintnotnull,
no_w_idsmallintnotnull,
PRIMARYKEY(no_w_id, no_d_id, no_o_id)) Engine=InnoDB;
droptableifexistsorders;
createtableorders (
o_idintnotnull,
o_d_id tinyintnotnull,
o_w_idsmallintnotnull,
o_c_idint,
o_entry_d datetime,
o_carrier_id tinyint,
o_ol_cnt tinyint,
o_all_local tinyint,
PRIMARYKEY(o_w_id, o_d_id, o_id) ) Engine=InnoDB ;
droptableifexistsorder_line;
createtableorder_line (
ol_o_idintnotnull,
ol_d_id tinyintnotnull,
ol_w_idsmallintnotnull,
ol_number tinyintnotnull,
ol_i_idint,
ol_supply_w_idsmallint,
ol_delivery_d datetime,
ol_quantity tinyint,
ol_amountdecimal(6,2),
ol_dist_infochar(24),
PRIMARYKEY(ol_w_id, ol_d_id, ol_o_id, ol_number) ) Engine=InnoDB ;
droptableifexistsitem;
createtableitem (
i_idintnotnull,
i_im_idint,
i_namevarchar(24),
i_pricedecimal(5,2),
i_datavarchar(50),
PRIMARYKEY(i_id) ) Engine=InnoDB;
droptableifexistsstock;
createtablestock (
s_i_idintnotnull,
s_w_idsmallintnotnull,
s_quantitysmallint,
s_dist_01char(24),
s_dist_02char(24),
s_dist_03char(24),
s_dist_04char(24),
s_dist_05char(24),
s_dist_06char(24),
s_dist_07char(24),
s_dist_08char(24),
s_dist_09char(24),
s_dist_10char(24),
s_ytddecimal(8,0),
s_order_cntsmallint,
s_remote_cntsmallint,
s_datavarchar(50),
PRIMARYKEY(s_w_id, s_i_id) ) Engine=InnoDB ;
SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Add_fkey.indx.sql
SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
CREATEINDEXidx_customerONcustomer (c_w_id,c_d_id,c_last,c_first);
CREATEINDEXidx_ordersONorders (o_w_id,o_d_id,o_c_id,o_id);
CREATEINDEXfkey_stock_2ONstock (s_i_id);
CREATEINDEXfkey_order_line_2ONorder_line (ol_supply_w_id,ol_i_id);
ALTERTABLEdistrictADDCONSTRAINTfkey_district_1FOREIGNKEY(d_w_id)REFERENCESwarehouse(w_id);
ALTERTABLEcustomerADDCONSTRAINTfkey_customer_1FOREIGNKEY(c_w_id,c_d_id)REFERENCESdistrict(d_w_id,d_id);
ALTERTABLEhistoryADDCONSTRAINTfkey_history_1FOREIGNKEY(h_c_w_id,h_c_d_id,h_c_id)REFERENCEScustomer(c_w_id,c_d_id,c_id);
ALTERTABLEhistoryADDCONSTRAINTfkey_history_2FOREIGNKEY(h_w_id,h_d_id)REFERENCESdistrict(d_w_id,d_id);
ALTERTABLEnew_ordersADDCONSTRAINTfkey_new_orders_1FOREIGNKEY(no_w_id,no_d_id,no_o_id)REFERENCESorders(o_w_id,o_d_id,o_id);
ALTERTABLEordersADDCONSTRAINTfkey_orders_1FOREIGNKEY(o_w_id,o_d_id,o_c_id)REFERENCEScustomer(c_w_id,c_d_id,c_id);
ALTERTABLEorder_lineADDCONSTRAINTfkey_order_line_1FOREIGNKEY(ol_w_id,ol_d_id,ol_o_id)REFERENCESorders(o_w_id,o_d_id,o_id);
ALTERTABLEorder_lineADDCONSTRAINTfkey_order_line_2FOREIGNKEY(ol_supply_w_id,ol_i_id)REFERENCESstock(s_w_id,s_i_id);
ALTERTABLEstockADDCONSTRAINTfkey_stock_1FOREIGNKEY(s_w_id)REFERENCESwarehouse(w_id);
ALTERTABLEstockADDCONSTRAINTfkey_stock_2FOREIGNKEY(s_i_id)REFERENCESitem(i_id);
SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
删除外键约束drop_cons.sql
预热数据count.sql对所有表执行 count(PK)
参考链接