首先安装tpcc
官网地址：https://github.com/Percona-Lab/tpcc-mysql
[root@test3 src]# unzip tpcc-mysql-master.zip
[root@test3 src]# mv tpcc-mysql-master /usr/local/src/
[root@test3 local]# cd tpcc-mysql-master/
[root@test3 tpcc-mysql-master]# ls
add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src
[root@test3 tpcc-mysql-master]# cd src/
[root@test3 src]# make #没有make install
[root@test3 src]# cd ..
[root@test3 tpcc-mysql-master]# ls
add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src tpcc_load tpcc_start #编译之后生成了tpcc_load和tpcc_start命令
在进行测试之前，需要导入tpcc的数据。
创建数据库，然后导入tpcc的建表语句：
mysql> create database tpcc_test;
Query OK, 1 row affected (0.06 sec)
mysql> use tpcc_test;
Database changed
mysql> source /usr/local/tpcc-mysql-master/create_table.sql;
Query OK, 0 rows affected (0.00 sec)
......
mysql> show tables;
+---------------------+
| Tables_in_tpcc_test |
+---------------------+
| customer |
| district |
| history |
| item |
| new_orders |
| order_line |
| orders |
| stock |
| warehouse |
+---------------------+
9 rows in set (0.00 sec)
tpcc创建了九张表：
tpcc-mysql的业务逻辑及其相关的几个表作用如下：
New-Order：新订单，一次完整的订单事务，几乎涉及到全部表
Payment：支付，主要对应 orders、history 表
Order-Status：订单状态，主要对应 orders、order_line 表
Delivery：发货，主要对应 order_line 表
Stock-Level：库存，主要对应 stock 表 其他说明：
客户：主要对应 customer 表
地区：主要对应 district 表
商品：主要对应 item 表
仓库：主要对应 warehouse 表
首先加载数据：
./tpcc_load -h 10.0.102.214 -P -d tpcc_test -u root -p -w 10
#这个过程有点慢 ，1个warehouse对应10个地区，1地区对应3000的用户
mysql> select count(*) from warehouse;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec) mysql> select count(*) from district;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec) mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.11 sec)
数据插入成功之后，然后添加索引【注意一定要先插入数据再添加索引，若先创建索引，则数据插入的会更慢】
mysql> source /usr/local/tpcc-mysql-master/add_fkey_idx.sql;
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (2.63 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.98 sec)
Records: Duplicates: Warnings:
......
Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec)
各个表的结构关系如图（使用MySQL workbench自动生成的er图）
数据插入，索引创建之后，开始测试数据。
[root@test3 tpcc-mysql-master]# ./tpcc_start --help
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
./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
-w 指定仓库数量
-c 指定并发连接数
-r 指定开始测试前进行warmup的时间，进行预热后，测试效果更好
-l 指定测试持续时间
-i 指定生成报告间隔时长
-f 指定生成的报告文件名
真实测试场景中，建议预热时间不小于5分钟，持续压测时长不小于30分钟，否则测试数据可能不具参考意义。
[root@test3 tpcc-mysql-master]# ./tpcc_start -h 10.0.102.214 -P -d tpcc_test -u root -p -w -c -r -l
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '10.0.102.214'
option P with value ''
option d with value 'tpcc_test'
option u with value 'root'
option p with value ''
option w with value ''
option c with value ''
option r with value ''
option l with value ''
<Parameters>
[server]: 10.0.102.214
[port]:
[DBname]: tpcc_test
[user]: root
[pass]:
[warehouse]:
[connection]:
[rampup]: (sec.)
[measure]: (sec.) RAMP-UP TIME.( sec.) #预热结束 MEASURING START. , trx: , %: 201.680, %: 381.106, max_rt: 1619.119, |3724.232, |229.157, |3278.305, |1335.471
, trx: , %: 157.736, %: 235.577, max_rt: 1499.332, |3527.383, |44.633, |2130.182, |343.886
, trx: , %: 166.968, %: 221.357, max_rt: 398.790, |1928.303, |66.903, |586.503, |374.860
, trx: , %: 172.350, %: 256.941, max_rt: 392.431, |2335.130, |75.140, |779.635, |403.248
, trx: , %: 184.524, %: 251.689, max_rt: 336.677, |1205.047, |35.061, |678.351, |413.731
, trx: , %: 184.358, %: 828.455, max_rt: 1187.092, |2113.244, |62.069, |1613.712, |337.856
, trx: , %: 175.632, %: 233.821, max_rt: 372.486, |1720.610, |69.966, |624.549, |397.743
, trx: , %: 177.588, %: 275.997, max_rt: 717.325, |1750.563, |50.437, |1046.998, |332.516
, trx: , %: 190.074, %: 244.413, max_rt: 404.727, |1602.446, |68.237, |812.026, |393.183
, trx: , %: 190.017, %: 291.625, max_rt: 483.963, |1657.739, |59.142, |771.332, |404.857
, trx: , %: 166.070, %: 222.753, max_rt: 321.543, |1610.591, |80.395, |650.804, |394.323
, trx: , %: 187.812, %: 552.392, max_rt: 817.886, |2443.558, |37.962, |1586.296, |406.044
, trx: , %: 170.299, %: 212.399, max_rt: 290.578, |1787.442, |58.585, |601.749, |379.250
, trx: , %: 178.600, %: 236.850, max_rt: 383.657, |1550.100, |75.059, |633.108, |471.181
, trx: , %: 185.133, %: 308.322, max_rt: 479.655, |1691.942, |38.844, |802.569, |449.930
, trx: , %: 172.660, %: 216.249, max_rt: 302.257, |1521.757, |62.614, |675.931, |435.944
, trx: , %: 164.290, %: 225.504, max_rt: 1306.804, |1745.858, |57.538, |610.941, |530.579
, trx: , %: 180.914, %: 251.463, max_rt: 1415.735, |2489.273, |30.965, |1915.949, |428.293
, trx: , %: 197.262, %: 399.086, max_rt: 556.640, |1319.926, |57.555, |977.416, |491.435
, trx: , %: 177.428, %: 217.612, max_rt: 341.156, |1357.371, |76.254, |655.950, |457.847 STOPPING THREADS................................................................................................................................ <Raw Results>
[] sc: lt: rt: fl: avg_rt: 148.6 ()
[] sc: lt: rt: fl: avg_rt: 604.9 ()
[] sc: lt: rt: fl: avg_rt: 6.6 ()
[] sc: lt: rt: fl: avg_rt: 683.6 ()
[] sc: lt: rt: fl: avg_rt: 272.2 ()
in sec. <Raw Results2(sum ver.)>
[] sc: lt: rt: fl:
[] sc: lt: rt: fl:
[] sc: lt: rt: fl:
[] sc: lt: rt: fl:
[] sc: lt: rt: fl: <Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.47% (>=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 % passed)]
New-Order: 0.00% [NG] *
Payment: 0.00% [NG] *
Order-Status: 82.96% [NG] *
Delivery: 0.00% [NG] *
Stock-Level: 0.00% [NG] * <TpmC>
14419.800 TpmC
[root@test3 tpcc-mysql-master]#