MySQL基准测试工具TPCC

一、下载安装tpcc-mysql

1、安装bzr版本控制工具

#yum install bzr

tips:如果没有找到安装包，请下搜索下载rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm,安装以后再yum。

2、下载tpcc-mysql源码#bzr branch lp:~percona-dev/perconatools/tpcc-mysql

3、安装

#cd tpcc-mysql/src/

#make

然后会在上层目录生成 tpcc_load tpcc_start两个二进制程序。

二、准备测试

1、创建测试表结构

mysql -uroot -proot -e 'create database tpcc' //-u -p改成你自己mysql的用户名和密码

mysql -uroot -proot tpcc < create_table.sql

mysql -uroot -proot tpcc < add_fkey_idx.sql

2、创建数据

./tpcc_load localhost tpcc root root 100 //最后的数字是代表几个仓库，仓库越大,数据量越大。耗费的时间越长，数据最好模拟你真实的数据量,或者至少大于你的buffer pool。

3、开始测试

./tpcc_start: invalid option -- -

Usage: 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 -t trx_file

# ./tpcc_start -hlocalhost -d tpcc -u root -p root -w 100 -c 8 -r 10 -l 20

三、输出结果分析

***************************************

*** ###easy### TPC-C Load Generator ***

***************************************

option h with value 'localhost'

option d with value 'tpcc'

option u with value 'root'

option p with value '2012mlnsh'

option w with value '100'

option c with value '8'

option r with value '120'

option l with value '1200'

[server]: localhost

[port]: 3306

[DBname]: tpcc

[user]: root

[pass]: 2012mlnsh

[warehouse]: 100

[connection]: 8

[rampup]: 120 (sec.)

[measure]: 1200 (sec.)

RAMP-UP TIME.(120 sec.)

MEASURING START.

10, 1187(0):1.682|2.175, 1187(0):0.336|0.473, 118(0):0.172|0.226, 118(0):1.864|2.122, 119(0):6.953|8.107

20, 1303(0):1.641|1.877, 1306(0):0.333|0.400, 131(0):0.173|0.203, 130(0):1.895|2.252, 133(0):5.387|5.490

30, 1471(0):1.677|2.123, 1472(0):0.333|0.394, 148(0):0.160|0.177, 148(0):1.801|2.102, 146(0):5.366|6.069

40, 1418(0):1.657|2.050, 1415(0):0.338|0.465, 141(0):0.157|0.218, 142(0):1.788|2.140, 142(0):4.965|5.276

50, 1620(0):1.644|1.937, 1619(0):0.332|0.444, 162(0):0.163|0.201, 161(0):1.934|2.158, 160(0):5.412|5.664

60, 1702(0):1.735|2.143, 1705(0):0.355|0.420, 170(0):0.167|0.182, 172(0):1.827|2.169, 174(0):5.088|5.214

70, 1751(0):1.623|2.035, 1748(0):0.343|0.464, 175(0):0.171|0.178, 174(0):1.787|1.798, 174(0):5.598|6.204

80, 1675(0):1.771|1.999, 1675(0):0.356|0.399, 167(0):0.172|0.180, 167(0):1.828|2.041, 168(0):5.526|6.172

90, 1805(0):1.723|2.036, 1807(0):0.357|0.458, 181(0):0.176|0.181, 180(0):1.767|2.008, 182(0):5.556|5.796

100, 1787(0):1.703|2.026, 1784(0):0.359|0.431, 179(0):0.172|0.193, 179(0):1.856|2.027, 177(0):5.429|5.858

110, 1948(0):1.790|2.087, 1952(0):0.353|0.485, 194(0):0.199|0.207, 195(0):2.057|2.092, 195(0):5.456|5.985

120, 1981(0):1.710|1.956, 1979(0):0.355|0.407, 198(0):0.173|0.180, 198(0):1.968|2.052, 198(0):5.571|6.180

130, 1885(0):1.785|2.092, 1884(0):0.365|0.467, 190(0):0.172|0.197, 189(0):2.027|2.096, 188(0):5.451|5.677

140, 1942(0):1.761|2.035, 1948(0):0.356|0.490, 194(0):0.177|0.217, 194(0):2.081|2.188, 195(0):5.548|5.839

150, 1992(0):1.742|2.099, 1989(0):0.347|0.380, 199(0):0.207|0.219, 199(0):1.924|2.018, 200(0):5.182|5.547

160, 1974(0):1.733|2.133, 1977(0):0.352|0.413, 197(0):0.185|0.234, 198(0):2.072|2.156, 196(0):5.751|6.031

170, 2115(0):1.798|2.085, 2109(0):0.353|0.422, 211(0):0.170|0.223, 210(0):1.939|2.051, 212(0):5.258|5.493

180, 1840(0):1.791|2.092, 1840(0):0.349|0.455, 185(0):0.179|0.200, 185(0):1.840|1.908, 184(0):5.449|5.508

190, 2002(0):1.813|2.046, 2000(0):0.356|0.407, 199(0):0.177|0.199, 199(0):2.124|2.178, 200(0):5.569|5.729

200, 1778(0):1.774|2.185, 1780(0):0.353|0.394, 178(0):0.176|0.205, 179(0):2.036|2.063, 178(0):5.396|5.995

210, 1943(0):1.738|2.165, 1942(0):0.357|0.450, 195(0):0.168|0.183, 193(0):1.950|2.108, 194(0):5.446|5.839

220, 1954(0):1.730|2.073, 1958(0):0.353|0.400, 196(0):0.180|0.225, 197(0):1.957|1.973, 196(0):5.573|6.317

230, 2058(0):1.789|1.930, 2055(0):0.339|0.443, 205(0):0.178|0.206, 205(0):1.918|2.141, 206(0):5.295|5.938

240, 2106(0):1.786|2.065, 2109(0):0.350|0.414, 210(0):0.175|0.183, 210(0):1.978|2.156, 211(0):5.170|6.782

250, 2245(0):1.771|2.059, 2242(0):0.346|0.411, 225(0):0.185|0.197, 226(0):1.941|2.070, 224(0):5.450|5.677

260, 2062(0):1.744|2.150, 2059(0):0.357|0.426, 206(0):0.175|0.229, 205(0):1.949|2.147, 206(0):5.471|6.274

270, 2026(0):1.739|2.099, 2025(0):0.343|0.389, 202(0):0.166|0.197, 202(0):1.913|2.075, 202(0):5.008|5.394

280, 1958(0):1.759|2.075, 1950(0):0.356|0.460, 197(0):0.179|0.210, 198(0):1.966|2.043, 197(0):5.336|5.883

290, 2039(0):1.671|2.043, 2052(0):0.354|0.412, 203(0):0.217|0.230, 204(0):1.890|1.933, 203(0):5.359|5.666

300, 2158(0):1.824|2.093, 2156(0):0.357|0.429, 217(0):0.180|0.191, 214(0):1.961|2.105, 216(0):5.329|5.864

310, 2076(0):1.822|2.089, 2081(0):0.356|0.515, 207(0):0.175|0.202, 210(0):2.036|2.148, 209(0):5.348|5.524

320, 2356(0):1.814|2.081, 2353(0):0.351|0.440, 235(0):0.186|0.216, 235(0):1.988|2.092, 235(0):5.493|5.654

330, 2326(0):1.803|2.074, 2324(0):0.363|0.418, 233(0):0.188|0.233, 230(0):1.958|2.243, 232(0):5.420|6.108

340, 10(0):1.122|1.229, 8(0):0.237|0.250, 1(0):0.000|0.096, 0(0):0.000|0.000, 1(0):0.000|3.557

350, 39(0):1.508|1.627, 40(0):0.321|0.337, 4(0):0.136|0.138, 6(0):1.680|1.800, 5(0):3.644|4.395

360, 49(0):1.590|1.866, 50(0):0.334|0.335, 5(0):0.137|0.167, 4(0):1.592|1.660, 4(0):4.533|4.654

370, 44(0):1.725|1.781, 44(0):0.325|0.330, 5(0):0.119|0.152, 6(0):1.475|1.888, 5(0):4.376|4.656

STOPPING THREADS........

//各种状态汇总统计 sc:success,lt:late,rt:retry,fl:failure

[0] sc:95218 lt:0 rt:0 fl:0 //新订单数

[1] sc:95217 lt:0 rt:0 fl:0 //支付业务

[2] sc:9521 lt:0 rt:0 fl:0 //查询业务

[3] sc:9520 lt:0 rt:0 fl:0 //发货业务

[4] sc:9525 lt:0 rt:0 fl:0 //库存查询业务

in 1200 sec.

[0] sc:95218 lt:0 rt:0 fl:0

[1] sc:95222 lt:0 rt:0 fl:0

[2] sc:9521 lt:0 rt:0 fl:0

[3] sc:9520 lt:0 rt:0 fl:0

[4] sc:9525 lt:0 rt:0 fl:0

(all must be [OK]) //是否满足各种指标

[transaction percentage]

Payment: 43.48% (>=43.0%) [OK]

Order-Status: 4.35% (>= 4.0%) [OK]

Delivery: 4.35% (>= 4.0%) [OK]

Stock-Level: 4.35% (>= 4.0%) [OK]

[response time (at least 90% passed)]

New-Order: 100.00% [OK]

Payment: 100.00% [OK]

Order-Status: 100.00% [OK]

Delivery: 100.00% [OK]

Stock-Level: 100.00% [OK]

4760.900 TpmC

内容分析：

10, 1187(0):1.682|2.175, 1187(0):0.336|0.473, 118(0):0.172|0.226, 118(0):1.864|2.122, 119(0):6.953|8.107

10：时间戳,每十秒产生一条数据。

1187(0):1.682|2.175：表示10秒内完成1187笔新订单业务。

1187(0):0.336|0.473: 支付业务，

118(0):1.864|2.122:查询业务，

118(0):0.172|0.226: 发货业务，

119(0):6.953|8.107: 库存查询业务

PS:上面出现了性能抖动,原因是因为我机器一般，操作的库太多，导致buffer_pool脏页过多,刷脏页的时候硬盘资源不足导致的。减少库的数量就可以了,也就是-w参数。