MySQL benchmark test tool TPCC-MySQL use NOTE _ MySQL

MySQL benchmark tool TPCC-MySQL 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.