TPCC-MySQL 基于TPCC协议的MySQL 实现

TPCC简介：

TPC-C is an on-line transaction processing benchmark，TPC-C simulates a complete computing environment where a population of users executes transactions against a database. The benchmark is centered around the principal activities (transactions) of an order-entry environment. These transactions include entering and delivering orders, recording payments, checking the status of orders, and monitoring the level of stock at the warehouses. While the benchmark portrays the activity of a wholesale supplier, TPC-C is not limited to the activity of any particular business segment, but, rather represents any industry that must manage, sell, or distribute a product or service.

TPC-C involves a mix of five concurrent transactions of different types and complexity either executed on-line or queued for deferred execution. The database is comprised of nine types of tables with a wide range of record and population sizes. TPC-C is measured in transactions per minute (tpmC).

结构如下：

结果值tpmC是代表每分钟事务处理的数量，一般仓库设置为40-100个，为cpu bound,400-1000个是为了测试io bound，40以下无论事务多少，锁竞争情况也不太容易发生。

本地环境：

CPU：Intel(R) Xeon(R) CPU           X3430  @ 2.40GHz

Mem:16G

HDD: SATA 500G

1、安装：

   bzr branch lp:~percona-dev/perconatools/tpcc-mysql （需首先安装bzr)

   接下来就是编译, cd tpcc-mysql 直接make 后就OK了，会在当前目录下生成tpcc_load、tpcc_start 两个运行程序，主要也就是用这两个程序

2、使用方法简介：

1. Build binaries   * cd scr ; make   ( you should have mysql_config available in $PATH)2. Load data   * create database     mysqladmin create tpcc1000   * create tables     mysql tpcc1000 < create_table.sql   * create indexes and FK ( this step can be done after loading data)     mysql tpcc1000 < add_fkey_idx.sql   * populate data     - simple step       tpcc_load 127.0.0.1:33000 tpcc1000 root "" 1000                 |hostname:port| |dbname| |user| |password| |WAREHOUSES|       ref. tpcc_load --help for all options     - load data in parallel        check load.sh script3. start benchmark   * ./tpcc_start -h127.0.0.1 -P33000 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800                  |hostname| |port| |dbname| |user| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME|   * ref. tpcc_start --help for all options 

接下来，生成测试数据：

#./tpcc_load 127.0.0.1:3307 tpcc1000 root "modular" 1000 （测试时视仓库数量而定，运行时间会比较久）

进行测试：

tpcc_start Usage:

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

-w 仓库数量
-c 并发连接数
-r 测试前warmup的时间，测试前预热时间
-l 测试持续时间
-I 生成报告间隔时长
-f 生成的报告文件名

运行命令如下：

#./tpcc_start -h127.0.0.1 -P3307 -dtpcc1000 -uroot -pmodular -w1000 -c32 -r10 -l10800 

运行结果：

[root@github tpcc-mysql]# more benchmark.log
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value ‘127.0.0.1‘
option P with value ‘3307‘
option d with value ‘tpcc1000‘
option u with value ‘root‘
option p with value ‘modular‘
option w with value ‘1000‘
option c with value ‘32‘
option r with value ‘10‘
option l with value ‘10800‘
<Parameters>
[server]: 127.0.0.1
[port]: 3307
[DBname]: tpcc1000
[user]: root
[pass]: modular
[warehouse]: 1000
[connection]: 32
[rampup]: 10 (sec.)
[measure]: 10800 (sec.)

RAMP-UP TIME.(10 sec.)

MEASURING START.

10, 7(0):1.766|1.806, 12(0):0.593|0.608, 1(0):0.263|0.350, 1(0):0.000|3.034, 1(0):0.000|9.013
20, 11(0):3.350|3.740, 10(0):0.546|0.615, 2(0):0.209|0.266, 1(0):0.000|3.533, 1(0):0.000|8.559
30, 10(0):3.436|3.700, 19(0):0.563|0.566, 1(0):0.000|0.310, 0(0):0.000|0.000, 1(0):0.000|8.306
40, 9(0):3.260|3.530, 6(0):0.493|0.608, 1(0):0.000|0.317, 1(0):0.000|3.060, 2(0):7.629|9.541
50, 17(0):2.897|2.999, 16(0):0.564|0.596, 1(0):0.000|0.293, 1(0):0.000|3.475, 1(0):0.000|8.859
60, 10(0):3.104|3.228, 9(0):0.526|0.582, 1(0):0.000|0.316, 1(0):0.000|3.360, 0(0):0.000|0.000
70, 11(0):2.762|2.808, 12(0):0.555|0.558, 1(0):0.000|0.268, 1(0):0.000|3.528, 2(0):7.387|9.906
80, 13(0):3.198|3.629, 11(0):0.558|0.559, 2(0):0.268|0.317, 0(0):0.000|0.000, 1(0):0.000|8.610
90, 17(0):3.025|3.133, 15(0):0.543|0.568, 1(0):0.000|0.267, 1(0):0.000|3.314, 0(0):0.000|0.000
100, 5(0):2.502|2.696, 10(0):0.460|0.497, 1(0):0.000|0.275, 1(0):0.000|3.484, 3(0):8.376|8.442
110, 17(0):2.722|3.080, 14(0):0.579|0.581, 1(0):0.000|0.303, 1(0):0.000|3.523, 0(0):0.000|0.000
120, 7(0):3.137|3.217, 8(0):0.514|0.516, 2(0):0.285|0.305, 2(0):3.085|3.598, 2(0):8.044|8.239
130, 19(0):2.918|3.080, 16(0):0.505|0.553, 1(0):0.000|0.226, 2(0):3.341|3.453, 1(0):0.000|8.897
140, 4(0):1.582|1.721, 8(0):0.515|0.540, 1(0):0.000|0.223, 1(0):0.000|3.445, 2(0):8.408|9.736
150, 17(0):2.977|3.155, 12(0):0.571|0.579, 1(0):0.000|0.246, 0(0):0.000|0.000, 1(0):0.000|8.718
160, 13(0):3.016|3.411, 17(0):0.587|0.599, 1(0):0.000|0.220, 2(0):3.017|3.100, 1(0):0.000|8.659
。。。。。。。。。。。。。。。。。。。。

10800, 27(0):2.830|2.832, 25(0):0.516|0.556, 2(0):0.205|0.255, 3(0):2.903|3.353, 3(0):9.740|9.754

STOPPING THREADS................................

<Raw Results>
[0] sc:23728 lt:0 rt:0 fl:0
[1] sc:23727 lt:0 rt:0 fl:0
[2] sc:2374 lt:0 rt:0 fl:0
[3] sc:2372 lt:0 rt:0 fl:0
[4] sc:2365 lt:0 rt:0 fl:0
in 10800 sec.

<Raw Results2(sum ver.)>
[0] sc:23736 lt:0 rt:0 fl:0
[1] sc:23738 lt:0 rt:0 fl:0
[2] sc:2374 lt:0 rt:0 fl:0
[3] sc:2372 lt:0 rt:0 fl:0
[4] sc:2365 lt:0 rt:0 fl:0

<Constraint Check> (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.33% (>= 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]

<TpmC>
131.822 TpmC

TPCC-MYSQL 应用测试