TPCC-MYSQL 應用測試

標籤：style   blog   http   color   io   os   使用   ar   for   

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 

 

運行結果：

[[email protected] 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 應用測試