MySQL benchmark tool tpcc-mysql user manual and report analysis
0 download and install
Git clone https://github.com/Percona-Lab/tpcc-mysql.git
Cd tpcc-mysql/src & & make
If make succeeds, two tools, tpcc_load and tpcc_start, will be generated in the tpcc-mysql directory.
If make reports an error, it is generally due to the lack of mysql_config environment, such as:
/ bin/sh: mysql_config: command not found
Load.c:19:19: error: mysql.h: No such file or directory
.
'MYSQL_TYPE_LONG' undeclared
'MYSQL_TYPE_STRING' undeclared
.
And there are some ERROR.
The reason is that the environment variable is set incorrectly or the mysql_config environment is missing.
For example, here, set the environment variable:
Export PATH=$PATH:/data/mysql57/bin/
0 use
① creates a db named tpcc:CREATE DATABASE tpcc in the instance to be tested
② imports a table structure under a tpcc-mysql directory: mysql-u$user-p$password-h$host
< ./tpcc-mysql/create_table.sql ③ load数据： ./tpcc-mysql/tpcc_load -h$host -d tpcc -u$user -p$password -w 1000 >Tpcc.load &
Of course, load is slow, and the tool comes with a simple script for parallel load data, load.sh:
It is also very easy to use:
The script is not perfect, you need to modify the script yourself:
Change the HOST variable to the host of mysql server
Add four-Ps to the password.
The use is as follows:
Bash load.sh $dbname $warehouse_count the first name is schema, here it is tpcc
The second is the number of warehouse, in this case 1000.
Parallel import is based on four parts, which are:
[part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS
It can be understood through. / tpcc_load-- help, combined with load.sh.
After ④ imports the data, add an index and fk:mysql-u-p finally.
< ./tpcc-mysql/add_fkey_idx.sql ⑤ 测试 ./tpcc_start -h$host -u$user -p$password -d tpcc -w 1000 -c32 -r300 -l7200 >/ data/tpcc.report 2 > & 1 &
Detailed 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 number of warehouses
-c concurrent connections
-r the time of warm up before starting the test, and after preheating, the test effect is better. Lao Ye suggests more than 300s.
-l test time
-I the interval between report generation
Generally,-f and-t do not need to be specified. Generally, it is sufficient to directly redirect to get the report result.
0 report analysis
[server]:.
[port]: 3306
[DBname]: tpcc
[user]: root
[pass]: *
[warehouse]: 1000
[connection]: 32
[rampup]: 300 (sec.)
[measure]: 7200 (sec.)
RAMP-UP TIME. (300 sec)
MEASURING START.
10, trx: 1670, 95%: 120.954, 99%: 143.328, max_rt: 291.917, 1675 | 207.554, 168 | 164.157; 745.408, 168 | 875.570
20, trx: 1656, 95%: 128.301, 99%: 150.585, max_rt: 229.172, 1650 | 102.471 | 121.764 | 569.108 | 737.483
30, trx: 1679, 95%: 129.072, 99%: 151.535, max_rt: 197.850, 1682 | 103.436, 168 | 80.130 | 658.822 | 686.441
40, trx: 1662, 95%: 126.926, 99%: 147.551, max_rt: 182.245, 1656 | 122.172, 167 | 97.329 | 567.806 | 717.234
50, trx: 1650, 95%: 130.041, 99%: 151.807, max_rt: 201.568, 1657 | 124.149 | 145.663 | 660.545 | 683.961
60, trx: 1659, 95%: 127.841, 99%: 148.926, max_rt: 189.087, 1653 | 107.867 | 90.284 | 535.922 | 690.396
.
STOPPING THREADS..
Print the report every 10 seconds.
Trx:1670: for newly generated transaction orders within a given interval (10 seconds here, the same below), the higher the better.
95%: 120.954: for more than 95% of orders within a given interval, the lower the response time, the better.
99%: 143.328: for more than 99% of orders within a given interval, the lower the new response time, the better.
Max_rt: 291.917: the longest response time for generating an order within a given interval
1675 | 207.554 | 164.157 | 745.408 | 875.570: throughput and maximum response time for other types of transactions.
[0] sc:5 lt:1169114 rt:0 fl:0 avg_rt: 88.2 (5)
[1] sc:15 lt:1168760 rt:0 fl:0 avg_rt: 35.1 (5)
Sc:1597 lt:115317 rt:0 fl:0 avg_rt: 24.3 (5)
[3] sc:0 lt:116913 rt:0 fl:0 avg_rt: 318.2 (80)
[4] sc:0 lt:116914 rt:0 fl:0 avg_rt: 477.7 (20)
In 7200 sec.
-0 represents new order, 1 represents payment order, 2 represents order status business, 3 represents delivery, 4 represents inventory
-- sc:success success, lt:late delay, rt:retry retry, fl:failure failure, avg_rt: average response time
-- it can be seen that the mysql server tested in this time can hardly satisfy the virtual tpcc business.
-- second statistical results
[0] sc:5 lt:1169123 rt:0 fl:0
[1] sc:15 lt:1169116 rt:0 fl:0
[2] sc:1597 lt:115317 rt:0 fl:0
[3] sc:0 lt:116913 rt:0 fl:0
[4] sc:0 lt:116914 rt:0 fl:0
(all must be [OK])-the following logic requires ok to pass
[transaction percentage]
Payment: 43.47% (> = 43.0%) [OK]-number of successful payments
Order-Status: 4.35% (> = 4.0%) [OK]-- order
Delivery: 4.35% (> = 4.0%) [OK]-Shipping
Stock-Level: 4.35% (> = 4.0%) [OK]-inventory
[response time (at least 90 passed)]-response is time-consuming and requires 90% + order to pass the target before it can become OK
New-Order: 0.005% [NG] *
Payment: 0.005% [NG] *
Order-Status: 1.37% [NG] *
Delivery: 0.005% [NG] *
Stock-Level: 0.005% [NG] *
Once again, this shows that the mysql server I tested was "failed".
9742.658 TpmC-- this value is the number of transactions per minute transaction, the number of results produced for the first time / execution minutes = 1169114bin120
0 reference documentation
Https://github.com/Percona-Lab/tpcc-mysql
Author's official account on Wechat (continuously updated)
