TPCC的安装

安装包地址

://github.com/Percona-Lab/tpcc-mysql

安装文档

Build binaries

cd src ; make ( you should have mysql_config available in $PATH)

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

load data in parallel check load.sh script

Start benchmark

./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800

ref. tpcc_start --help for all options

Output

With the defined interval (-i option), the tool will produce the following output:

10, trx: 12920, 95%: 9.483, 99%: 18.738, max_rt: 213.169, 12919|98.778, 1292|101.096, 1293|443.955, 1293|670.842

20, trx: 12666, 95%: 7.074, 99%: 15.578, max_rt: 53.733, 12668|50.420, 1267|35.846, 1266|58.292, 1267|37.421

30, trx: 13269, 95%: 6.806, 99%: 13.126, max_rt: 41.425, 13267|27.968, 1327|32.242, 1327|40.529, 1327|29.580

40, trx: 12721, 95%: 7.265, 99%: 15.223, max_rt: 60.368, 12721|42.837, 1271|34.567, 1272|64.284, 1272|22.947

50, trx: 12573, 95%: 7.185, 99%: 14.624, max_rt: 48.607, 12573|45.345, 1258|41.104, 1258|54.022, 1257|26.626

Where:

10 - the seconds from the start of the benchmark

trx: 12920 - New Order transactions executed during the gived interval (in this case, for the previous 10 sec). Basically this is the throughput per interval. The more the better

95%: 9.483: - The 95% Response time of New Order transactions per given interval. In this case it is 9.483 sec

99%: 18.738: - The 99% Response time of New Order transactions per given interval. In this case it is 18.738 sec

max_rt: 213.169: - The Max Response time of New Order transactions per given interval. In this case it is 213.169 sec

the rest: 12919|98.778, 1292|101.096, 1293|443.955, 1293|670.842 is throughput and max response time for the other kind of transactions and can be ignored

###安装

1。解压

[root@msyqlzhu ]# unzip tpcc-mysql-master.zip

[root@msyqlzhu ]# cd tpcc-mysql-master

[root@msyqlzhu tpcc-mysql-master]# ls -rlt

total 44

drwxr-xr-x. 2 root root 4096 Jan 20 2017 src

drwxr-xr-x. 5 root root 4096 Jan 20 2017 scripts

drwxr-xr-x. 2 root root 4096 Jan 20 2017 schema2

-rw-r--r--. 1 root root 2302 Jan 20 2017 README.md

-rw-r--r--. 1 root root 573 Jan 20 2017 load.sh

-rw-r--r--. 1 root root 1079 Jan 20 2017 load_multi_schema.sh

-rw-r--r--. 1 root root 763 Jan 20 2017 drop_cons.sql

-rw-r--r--. 1 root root 194 Jan 20 2017 Dockerfile

-rw-r--r--. 1 root root 3105 Jan 20 2017 create_table.sql

-rw-r--r--. 1 root root 317 Jan 20 2017 count.sql

-rw-r--r--. 1 root root 1621 Jan 20 2017 add_fkey_idx.sql

2.make编译

[root@msyqlzhu src]# make

cc -w -O3 -g -I. `mysql_config --include` -c load.c

cc -w -O3 -g -I. `mysql_config --include` -c support.c

cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load

cc -w -O3 -g -I. `mysql_config --include` -c main.c

cc -w -O3 -g -I. `mysql_config --include` -c spt_proc.c

cc -w -O3 -g -I. `mysql_config --include` -c driver.c

cc -w -O3 -g -I. `mysql_config --include` -c sequence.c

cc -w -O3 -g -I. `mysql_config --include` -c rthist.c

cc -w -O3 -g -I. `mysql_config --include` -c sb_percentile.c

cc -w -O3 -g -I. `mysql_config --include` -c neword.c

cc -w -O3 -g -I. `mysql_config --include` -c payment.c

cc -w -O3 -g -I. `mysql_config --include` -c ordstat.c

cc -w -O3 -g -I. `mysql_config --include` -c delivery.c

cc -w -O3 -g -I. `mysql_config --include` -c slev.c

cc main.o spt_proc.o driver.o support.o sequence.o rthist.o sb_percentile.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start

3.创建测试库

[root@msyqlzhu src]# mysqladmin -uroot -pORACLE create database tpcc1000

4.导入表结构

[root@msyqlzhu src]# cd ..

[root@msyqlzhu tpcc-mysql-master]# pwd

/root/tpcc-mysql-master

[root@msyqlzhu tpcc-mysql-master]# mysql -uroot -pORACLE tpcc1000 < create_table.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

5.创建外键

[root@msyqlzhu tpcc-mysql-master]# mysql -uroot -pORACLE tpcc1000 < add_fkey_idx.sql

6.加载数据

-w 表示加载几个WAREHOUSES，每个WAREHOUSES里面的数据很大

load data in parallel check load.sh script

[root@msyqlzhu tpcc-mysql-master]# ./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "ORACLE" -w 2

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

*** TPCC-mysql Data Loader ***

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

option h with value '127.0.0.1'

option d with value 'tpcc1000'

option u with value 'root'

option p with value 'ORACLE'

option w with value '2'

[server]: 127.0.0.1

[port]: 3306

[DBname]: tpcc1000

[user]: root

[pass]: ORACLE

[warehouse]: 2

TPCC Data Load Started...

Loading Item

.................................................. 5000

.................................................. 10000

省略部分

Loading Orders for D=10, W= 2

.......... 1000

.......... 2000

.......... 3000

Orders Done.

...DATA LOADING COMPLETED SUCCESSFULLY.

7.开始运行

./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800

ref. tpcc_start --help for all options

-c 表示用多少个线程

-l 表示运行300秒

-i 打印报告的时间间隔

[root@msyqlzhu tpcc-mysql-master]# ./tpcc_start -h127.0.0.1 -d tpcc1000 -u root -p "ORACLE" -w 2 -c 2 -l 300 -i 10

160, trx: 451, 95%: 102.348, 99%: 200.716, max_rt: 383.960, 453|196.867, 45|2.165, 45|338.903, 45|50.930

170, trx: 573, 95%: 51.460, 99%: 100.979, max_rt: 288.610, 572|280.383, 57|2.292, 57|210.452, 58|59.415

180, trx: 474, 95%: 123.109, 99%: 166.269, max_rt: 209.113, 475|266.656, 48|3.217, 48|309.402, 47|60.546

190, trx: 538, 95%: 65.599, 99%: 134.797, max_rt: 195.894, 538|204.119, 54|24.384, 53|367.505, 53|63.732

200, trx: 448, 95%: 158.115, 99%: 193.983, max_rt: 280.373, 446|284.824, 45|9.769, 46|297.318, 45|67.677

210, trx: 565, 95%: 50.802, 99%: 113.823, max_rt: 198.511, 567|259.840, 56|8.313, 56|332.234, 57|51.848

220, trx: 462, 95%: 132.994, 99%: 162.968, max_rt: 216.271, 461|162.846, 46|28.508, 46|399.657, 46|60.813

230, trx: 342, 95%: 165.129, 99%: 259.881, max_rt: 469.534, 342|340.310, 35|5.365, 34|751.851, 34|38.368

240, trx: 556, 95%: 63.493, 99%: 117.212, max_rt: 285.464, 554|276.425, 55|2.333, 56|314.255, 56|55.452

250, trx: 455, 95%: 90.690, 99%: 142.089, max_rt: 321.924, 455|484.051, 46|1.941, 45|269.097, 46|48.171

260, trx: 428, 95%: 71.527, 99%: 117.951, max_rt: 210.271, 432|418.011, 42|5.737, 44|306.409, 43|77.536

270, trx: 474, 95%: 95.139, 99%: 184.634, max_rt: 289.261, 469|142.192, 47|5.389, 46|240.919, 46|54.208

280, trx: 499, 95%: 76.464, 99%: 125.491, max_rt: 209.247, 501|256.123, 51|4.486, 50|388.811, 51|64.832

290, trx: 502, 95%: 75.306, 99%: 161.656, max_rt: 224.623, 505|219.696, 50|2.502, 51|273.799, 50|52.631

300, trx: 442, 95%: 105.080, 99%: 185.299, max_rt: 367.650, 440|251.798, 44|3.571, 44|404.255, 44|47.842

[0] sc:121 lt:14423 rt:0 fl:0 avg_rt: 22.2 (5)

[1] sc:10457 lt:4089 rt:0 fl:0 avg_rt: 9.4 (5)

[2] sc:1440 lt:14 rt:0 fl:0 avg_rt: 1.2 (5)

[3] sc:1130 lt:325 rt:0 fl:0 avg_rt: 76.0 (80)

[4] sc:60 lt:1395 rt:0 fl:0 avg_rt: 31.9 (20)

in 300 sec.

[0] sc:121 lt:14423 rt:0 fl:0

[1] sc:10457 lt:4089 rt:0 fl:0

[2] sc:1440 lt:14 rt:0 fl:0

[3] sc:1130 lt:325 rt:0 fl:0

[4] sc:60 lt:1395 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.35% (>= 4.0%) [OK]

[response time (at least 90% passed)]

New-Order: 0.83% [NG] *

Payment: 71.89% [NG] *

Order-Status: 99.04% [OK]

Delivery: 77.66% [NG] *

Stock-Level: 4.12% [NG] *

<TpmC>

2908.800 TpmC -----这里表示每分钟能做的业务的总量这里每分钟能做2908个事务。

trx 442 表示TPS每10秒的事务数，这个约大约好

95%: 105.080, 95%的订单响应时间是105秒

99%: 185.299 -每个给定间隔的新订单事务的99%响应时间

max_rt: 367.650 -每个给定间隔的新订单事务的最大响应时间

ITPub博客

tpcc测试数据库性能

最新文章