MySQL benchmarking tool TPCC-MySQL usage notes

MySQL benchmarking tool TPCC-MySQL usage notes

TPCC is a specification specifically designed for online transaction processing systems (OLTP systems). In general, such systems are also called business processing systems. TPCC-mysql is a benchmark testing tool developed by percona to simulate e-commerce business processes. however, the tpcc-mysql results are not certified by the TPC organization and are only used as a reference data.

1. Download and install tpcc-mysql

1. Install bzr version control tool
# Yum install bzr
Tips: If the installation package is not found, search for the download rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm and install yum later.

2. Download tpcc-mysql source code
# Bzr branch lp :~ Percona-dev/perconatools/tpcc-mysql

3. Installation
# Cd tpcc-mysql/src/
# Make

The tpcc_load tpcc_start binary program is generated in the upper directory.

2. Prepare for testing

1. Create a test table structure
Mysql-uroot-proot-e 'create database tpcc '// change-u-p to your mysql user name and password.
Mysql-uroot-proot tpcc <create_table. SQL
Mysql-uroot-proot tpcc <add_fkey_idx. SQL

2. Create data

./Tpcc_load localhost tpcc root 100 // the last number indicates several warehouses. The larger the warehouse, the larger the data volume. The longer the time it takes, it is best to simulate your actual data volume, or at least exceed your buffer pool.

3. Start testing
./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 accept-l running_time-I report_interval-f report_file-t trx_file
#./Tpcc_start-hlocalhost-d tpcc-u root-p root-w 100-c 8-r 10-l 20

Iii. output result analysis

***************************************
* ** ### Easy ### TPC-C Load Generator ***
***************************************
Option h with value 'localhost'
Option d with value 'tpcc'
Option u with value 'root'
Option p with value '20142mlnsh'
Option w with value '000000'
Option c with value '8'
Option r with value '000000'
Option l with value '000000'

[Server]: localhost
[Port]: 3306
[DBname]: tpcc
[User]: root
[Pass]: 2012 mlnsh
[Warehouse]: 100
[Connection]: 8
[Rampup]: 120 (sec .)
[Measure]: 1200 (sec .)

RAMP-up time. (120 sec .)

Measuring start.

10,118 7 (0): 1.682 | 2.175, 1187 (0): 0.336 | 0.473, 118 (0): 0.172 | 0.226, 118 (0): 1.864 | 2.122, 119 (0): 6.953 | 8.107
20,130 3 (0): 1.641 | 1.877, 1306 (0): 0.333 | 0.400, 131 (0): 0.173 | 0.203, 130 (0): 1.895 | 2.252, 133 (0): 5.387 | 5.490
30,147 1 (0): 1.677 | 2.123, 1472 (0): 0.333 | 0.394, 148 (0): 0.160 | 0.177, 148 (0): 1.801 | 2.102, 146 (0): 5.366 | 6.069
40,141 8 (0): 1.657 | 2.050, 1415 (0): 0.338 | 0.465, 141 (0): 0.157 | 0.218, 142 (0): 1.788 | 2.140, 142 (0): 4.965 | 5.276
50,162 0 (0): 1.644 | 1.937, 1619 (0): 0.332 | 0.444, 162 (0): 0.163 | 0.201, 161 (0): 1.934 | 2.158, 160 (0): 5.412 | 5.664
60,170 2 (0): 1.735 | 2.143, 1705 (0): 0.355 | 0.420, 170 (0): 0.167 | 0.182, 172 (0): 1.827 | 2.169, 174 (0): 5.088 | 5.214
70,175 1 (0): 1.623 | 2.035, 1748 (0): 0.343 | 0.464, 175 (0): 0.171 | 0.178, 174 (0): 1.787 | 1.798, 174 (0): 5.598 | 6.204
80,167 5 (0): 1.771 | 1.999, 1675 (0): 0.356 | 0.399, 167 (0): 0.172 | 0.180, 167 (0): 1.828 | 2.041, 168 (0): 5.526 | 6.172
90,180 5 (0): 1.723 | 2.036, 1807 (0): 0.357 | 0.458, 181 (0): 0.176 | 0.181, 180 (0): 1.767 | 2.008, 182 (0): 5.556 | 5.796
100,178 7 (0): 1.703 | 2.026, 1784 (0): 0.359 | 0.431, 179 (0): 0.172 | 0.193, 179 (0): 1.856 | 2.027, 177 (0): 5.429 | 5.858
110,194 8 (0): 1.790 | 2.087, 1952 (0): 0.353 | 0.485, 194 (0): 0.199 | 0.207, 195 (0): 2.057 | 2.092, 195 (0): 5.456 | 5.985
120,198 1 (0): 1.710 | 1.956, 1979 (0): 0.355 | 0.407, 198 (0): 0.173 | 0.180, 198 (0): 1.968 | 2.052, 198 (0): 5.571 | 6.180
130,188 5 (0): 1.785 | 2.092, 1884 (0): 0.365 | 0.467, 190 (0): 0.172 | 0.197, 189 (0): 2.027 | 2.096, 188 (0): 5.451 | 5.677
140,194 2 (0): 1.761 | 2.035, 1948 (0): 0.356 | 0.490, 194 (0): 0.177 | 0.217, 194 (0): 2.081 | 2.188, 195 (0): 5.548 | 5.839
150,199 2 (0): 1.742 | 2.099, 1989 (0): 0.347 | 0.380, 199 (0): 0.207 | 0.219, 199 (0): 1.924 | 2.018, 200 (0): 5.182 | 5.547
160,197 4 (0): 1.733 | 2.133, 1977 (0): 0.352 | 0.413, 197 (0): 0.185 | 0.234, 198 (0): 2.072 | 2.156, 196 (0): 5.751 | 6.031
170,211 5 (0): 1.798 | 2.085, 2109 (0): 0.353 | 0.422, 211 (0): 0.170 | 0.223, 210 (0): 1.939 | 2.051, 212 (0): 5.258 | 5.493
180,184 0 (0): 1.791 | 2.092, 1840 (0): 0.349 | 0.455, 185 (0): 0.179 | 0.200, 185 (0): 1.840 | 1.908, 184 (0): 5.449 | 5.508
190,200 2 (0): 1.813 | 2.046, 2000 (0): 0.356 | 0.407, 199 (0): 0.177 | 0.199, 199 (0): 2.124 | 2.178, 200 (0): 5.569 | 5.729
200,177 8 (0): 1.774 | 2.185, 1780 (0): 0.353 | 0.394, 178 (0): 0.176 | 0.205, 179 (0): 2.036 | 2.063, 178 (0): 5.396 | 5.995
210,194 3 (0): 1.738 | 2.165, 1942 (0): 0.357 | 0.450, 195 (0): 0.168 | 0.183, 193 (0): 1.950 | 2.108, 194 (0): 5.446 | 5.839
220,195 4 (0): 1.730 | 2.073, 1958 (0): 0.353 | 0.400, 196 (0): 0.180 | 0.225, 197 (0): 1.957 | 1.973, 196 (0): 5.573 | 6.317
230,205 8 (0): 1.789 | 1.930, 2055 (0): 0.339 | 0.443, 205 (0): 0.178 | 0.206, 205 (0): 1.918 | 2.141, 206 (0): 5.295 | 5.938
240,210 6 (0): 1.786 | 2.065, 2109 (0): 0.350 | 0.414, 210 (0): 0.175 | 0.183, 210 (0): 1.978 | 2.156, 211 (0): 5.170 | 6.782
250,224 5 (0): 1.771 | 2.059, 2242 (0): 0.346 | 0.411, 225 (0): 0.185 | 0.197, 226 (0): 1.941 | 2.070, 224 (0): 5.450 | 5.677
260,206 2 (0): 1.744 | 2.150, 2059 (0): 0.357 | 0.426, 206 (0): 0.175 | 0.229, 205 (0): 1.949 | 2.147, 206 (0): 5.471 | 6.274
270,202 6 (0): 1.739 | 2.099, 2025 (0): 0.343 | 0.389, 202 (0): 0.166 | 0.197, 202 (0): 1.913 | 2.075, 202 (0): 5.008 | 5.394
280,195 8 (0): 1.759 | 2.075, 1950 (0): 0.356 | 0.460, 197 (0): 0.179 | 0.210, 198 (0): 1.966 | 2.043, 197 (0): 5.336 | 5.883
290,203 9 (0): 1.671 | 2.043, 2052 (0): 0.354 | 0.412, 203 (0): 0.217 | 0.230, 204 (0): 1.890 | 1.933, 203 (0): 5.359 | 5.666
300,215 8 (0): 1.824 | 2.093, 2156 (0): 0.357 | 0.429, 217 (0): 0.180 | 0.191, 214 (0): 1.961 | 2.105, 216 (0): 5.329 | 5.864
310,207 6 (0): 1.822 | 2.089, 2081 (0): 0.356 | 0.515, 207 (0): 0.175 | 0.202, 210 (0): 2.036 | 2.148, 209 (0): 5.348 | 5.524
320,235 6 (0): 1.814 | 2.081, 2353 (0): 0.351 | 0.440, 235 (0): 0.186 | 0.216, 235 (0): 1.988 | 2.092, 235 (0): 5.493 | 5.654
330,232 6 (0): 1.803 | 2.074, 2324 (0): 0.363 | 0.418, 233 (0): 0.188 | 0.233, 230 (0): 1.958 | 2.243, 232 (0): 5.420 | 6.108
340, 10 (0): 1.122 | 1.229, 8 (0): 0.237 | 0.250, 1 (0): 0.000 | 0.096, 0 (0): 0.000 | 0.000, 1 (0): 0.000 | 3.557
350, 39 (0): 1.508 | 1.627, 40 (0): 0.321 | 0.337, 4 (0): 0.136 | 0.138, 6 (0): 1.680 | 1.800, 5 (0): 3.644 | 4.395
360, 49 (0): 1.590 | 1.866, 50 (0): 0.334 | 0.335, 5 (0): 0.137 | 0.167, 4 (0): 1.592 | 1.660, 4 (0): 4.533 | 4.654
370, 44 (0): 1.725 | 1.781, 44 (0): 0.325 | 0.330, 5 (0): 0.119 | 0.152, 6 (0): 1.475 | 1.888, 5 (0): 4.376 | 4.656
Stopping threads ........

// Various State summary statistics SC: success, lt: late, rt: retry, fl: failure
[0] SC: 95218 lt: 0 rt: 0 fl: 0 // New Order Number
[1] SC: 95217 lt: 0 rt: 0 fl: 0 // payment service
[2] SC: 9521 lt: 0 rt: 0 fl: 0 // query service
[3] SC: 9520 lt: 0 rt: 0 fl: 0 // Shipping Service
[4] SC: 9525 lt: 0 rt: 0 fl: 0 // inventory query service
In 1200 sec.

[0] SC: 95218 lt: 0 rt: 0 fl: 0
[1] SC: 95222 lt: 0 rt: 0 fl: 0
[2] SC: 9521 lt: 0 rt: 0 fl: 0
[3] SC: 9520 lt: 0 rt: 0 fl: 0
[4] SC: 9525 lt: 0 rt: 0 fl: 0

(All must be [OK]) // determines whether various metrics are met.
[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: 100.00% [OK]
Payment: 100.00% [OK]
Order-Status: 100.00% [OK]
Delivery: 100.00% [OK]
Stock-Level: 100.00% [OK]

4760.900 TpmC

Content Analysis:
10,118 7 (0): 1.682 | 2.175, 1187 (0): 0.336 | 0.473, 118 (0): 0.172 | 0.226, 118 (0): 1.864 | 2.122, 119 (0): 6.953 | 8.107
10: Timestamp, which generates a data record every 10 seconds.
1187 (0): 1.682 | 2.175: indicates that 1187 new orders are completed within 10 seconds.
1187 (0): 0.336 | 0.473: payment service,
118 (0): 1.864 | 2.122: query service,
118 (0): 0.172 | 0.226: delivery service,
119 (0): 6.953 | 8.107: Inventory query service

PS: the above performance jitter occurs because I usually operate too many databases on my machine, resulting in too many dirty pages in buffer_pool and insufficient hard disk resources when refreshing dirty pages. Reduce the number of databases, that is, the-w parameter.

-------------------------------------- Split line --------------------------------------

Benchmarking Tool-mysql

MySQL benchmarking tool TPCC-MySQL usage notes

MySQL 5.6.7-RC tpcc-mysql Benchmark Test Results

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: