Mysql Performance Testing Tool tpcc-mysql

Last Update:2018-05-27 Source: Internet

Author: User

Tags mysql host percona

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Official Website: imysql. comwp-contentuploads201409tpcc-mysql-src.tgz1. With bzr client download package [root @ tong1 ~] # Yuminstallbzr-y [root @ tong1 ~] # Bzrbranchlp :~ Percona-devperconatoolstpcc-mysql [root @ tong1 ~] # Cdtpcc-mysqlsrc

Official Website: http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz 1. Use bzr client download package [root @ tong1 ~] # Yum install bzr-y [root @ tong1 ~] # Bzr branch lp :~ Percona-dev/perconatools/tpcc-mysql [root @ tong1 ~] # Cd tpcc-mysql/src/

Official Website:

http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz

1. Use the bzr client to download the software package

[Root @ tong1 ~] # Yum install bzr-y

[Root @ tong1 ~] # Bzr branch lp :~ Percona-dev/perconatools/tpcc-mysql

[Root @ tong1 ~] # Cd tpcc-mysql/src/

[Root @ tong1 src] # make

[Root @ tong1 src] # cd ..
[Root @ tong1 tpcc-mysql] # ll
Total 252
-Rw-r --. 1 root 1621 Apr 23 add_fkey_idx. SQL
-Rw-r --. 1 root 317 Apr 23 :24 count. SQL
-Rw-r --. 1 root 3105 Apr 23 create_table. SQL
-Rw-r --. 1 root 763 Apr 23 drop_cons. SQL
-Rw-r --. 1 root 477 Apr 23 load. sh
-Rw-r --. 1 root 851 Apr 23 README
Drwxr-xr-x. 2 root 4096 Apr 23 schema2
Drwxr-xr-x. 5 root 4096 Apr 23 scripts
Drwxr-xr-x. 2 root 4096 Apr 23 src
-Rwxr-xr-x. 1 root 61191 Apr 23 tpcc_load -- make command generated after
-Rwxr-xr-x. 1 root 155886 Apr 23 tpcc_start -- make command generated after

[Root @ tong1 tpcc-mysql] #

2. Create a database import table structure

[Root @ tong1 tpcc-mysql] # mysqladmin create tpcc;
[Root @ tong1 tpcc-mysql] # mysql tpcc <create_table. SQL
[Root @ tong1 tpcc-mysql] #

3. Load Test Data (tpcc_load)

[Root @ tong1 tpcc-mysql] #./tpcc_load -- help
*************************************
* ** ### Easy ### TPC-C Data Loader ***
*************************************

Usage: tpcc_load [server] [DB] [user] [pass] [warehouse]
OR
Tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]

* [Part]: 1 = ITEMS 2 = WAREHOUSE 3 = CUSTOMER 4 = ORDERS
[Root @ tong1 tpcc-mysql] #./tpcc_load localhost tpcc root "" 100 -- the number of mechanical hardware warehouses cannot be less than 100, and the number of ssd hardware cannot be less than 1000

4. Stress test data (tpcc_start)

[Root @ tong1 tpcc-mysql] #./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 accept-l running_time-I report_interval-f report_file-t trx_file

-H -- mysql host

-P -- service port

-D -- Test Database

-U -- User Name

-P -- Password

-W -- number of warehouses

-C -- number of connections

-R -- warm-up time before testing

-L -- Test Run Time

-I -- report generation time

-F -- specify to generate a report file

[Root @ tong1 tpcc-mysql] #. /tpcc_start-h localhost-P 3306-d tpcc-u root-p ""-w 100-c 100-r 120-l 360-f 123.log> tpcc-mysql.log 2> & 1

[Root @ tong1 tpcc-mysql] # cat tpcc-mysql.log
***************************************
* ** ### Easy ### TPC-C Load Generator ***
***************************************
Option h with value 'localhost'
Option P with value '000000'
Option d with value 'tpcc'
Option u with value 'root'
Option p with value''
Option w with value '000000'
Option c with value '000000'
Option r with value '000000'
Option l with value '000000'
Option f with value '123. Log'

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

RAMP-up time. (120 sec.) -- push TIME 120 S

Measuring start. -- Information collected every 10 s

10, 27 (0): 1.191 | 1.236, 27 (0): 0.341 | 0.433, 6 (0): 0.180 | 0.188, 2 (0): 1.567 | 1.570, 0 (0): 3.804 | 3.933
20, 46 (0): 1.417 | 1.501, 26 (0): 0.348 | 0.357, 2 (0): 0.171 | 0.175, 5 (0): 1.417 | 1.477, 1 (0): 0.000 | 4.351
30, 16 (0): 1.358 | 1.508, 19 (0): 0.398 | 0.681, 3 (0): 0.183 | 0.229, 2 (0): 1.310 | 1.429, 2 (0): 5.050 | 5.904
40, 9 (0): 1.348 | 1.438, 18 (0): 0.322 | 0.334, 0 (0): 0.000 | 0.000, 2 (0): 1.228 | 1.284, 3 (0): 5.657 | 6.583
50, 56 (0): 1.463 | 1.465, 27 (0): 0.310 | 0.393, 2 (0): 0.082 | 0.151, 5 (0): 1.451 | 1.555, 0 (0): 0.000 | 0.000
60, 16 (0): 1.223 | 1.237, 25 (0): 0.324 | 0.352, 5 (0): 0.161 | 0.163, 2 (0): 1.253 | 1.432, 1 (0): 0.000 | 5.627
70, 25 (0): 1.177 | 1.368, 31 (0): 0.324 | 0.348, 2 (0): 0.156 | 0.170, 1 (0): 0.000 | 1.342, 3 (0): 5.473 | 6.178
80, 31 (0): 1.374 | 1.394, 15 (0): 0.275 | 0.284, 2 (0): 0.111 | 0.153, 3 (0): 1.345 | 1.431, 2 (0): 5.026 | 5.289
90, 15 (0): 1.220 | 1.229, 34 (0): 0.291 | 0.310, 5 (0): 0.167 | 0.180, 1 (0): 0.000 | 1.396, 4 (0): 5.004 | 5.702
100, 21 (0): 1.372 | 1.410, 27 (0): 0.313 | 0.509, 1 (0): 0.000 | 0.107, 2 (0): 1.487 | 1.524, 3 (0): 5.687 | 5.866
110, 29 (0): 1.401 | 1.438, 18 (0): 0.367 | 0.422, 1 (0): 0.000 | 0.118, 5 (0): 1.411 | 1.437, 0 (0): 0.000 | 0.000
120, 44 (0): 1.354 | 1.444, 30 (0): 0.320 | 0.329, 1 (0): 0.000 | 0.170, 2 (0): 1.380 | 1.459, 2 (0): 5.083 | 5.109
130, 20 (0): 1.432 | 1.458, 15 (0): 0.299 | 0.318, 6 (0): 0.176 | 0.177, 3 (0): 1.483 | 1.517, 6 (0): 5.519 | 5.721
140, 3 (0): 0.848 | 0.957, 37 (0): 0.437 | 0.531, 0 (0): 0.000 | 0.000, 0 (0): 0.000 | 0.000, 0 (0): 0.000 | 0.000
150, 21 (0): 1.337 | 1.394, 13 (0): 0.291 | 0.326, 3 (0): 0.149 | 0.158, 4 (0): 1.492 | 1.493, 3 (0): 5.839 | 6.008
160, 47 (0): 1.419 | 1.427, 39 (0): 0.355 | 0.378, 1 (0): 0.000 | 0.157, 3 (0): 1.381 | 1.453, 4 (0): 5.484 | 5.749
170, 4 (0): 1.214 | 1.393, 15 (0): 0.287 | 0.305, 6 (0): 0.186 | 0.210, 1 (0): 0.000 | 1.306, 4 (0): 5.174 | 5.803
180, 40 (0): 1.304 | 1.399, 21 (0): 0.343 | 0.369, 1 (0): 0.000 | 0.133, 3 (0): 1.325 | 1.438, 2 (0): 3.857 | 4.854
190, 28 (0): 1.366 | 1.755, 36 (0): 0.358 | 0.367, 0 (0): 0.000 | 0.000, 5 (0): 1.388 | 1.412, 1 (0): 0.000 | 5.540
200, 30 (0): 1.393 | 1.711, 26 (0): 0.311 | 0.328, 6 (0): 0.173 | 0.199, 1 (0): 0.000 | 1.414, 3 (0): 5.008 | 5.151
210, 19 (0): 1.213 | 1.376, 30 (0): 0.387 | 0.389, 1 (0): 0.000 | 0.106, 0 (0): 0.000 | 0.000, 5 (0): 4.882 | 5.416
220, 45 (0): 1.420 | 1.563, 27 (0): 0.329 | 0.368, 4 (0): 0.164 | 0.167, 1 (0): 0.000 | 1.420, 1 (0): 0.000 | 2.990
230, 32 (0): 1.513 | 1.654, 19 (0): 0.330 | 0.339, 1 (0): 0.000 | 0.157, 4 (0): 1.399 | 1.427, 5 (0): 5.612 | 5.668
240, 42 (0): 1.344 | 1.556, 42 (0): 0.328 | 0.428, 6 (0): 0.175 | 0.179, 2 (0): 1.417 | 1.454, 2 (0): 3.330 | 5.057
250, 15 (0): 1.476 | 1.486, 25 (0): 0.324 | 0.345, 3 (0): 0.175 | 0.186, 5 (0): 1.427 | 1.511, 4 (0): 5.543 | 5.985
260, 14 (0): 1.331 | 1.346, 30 (0): 0.360 | 0.364, 0 (0): 0.000 | 0.000, 2 (0): 1.375 | 1.599, 1 (0): 0.000 | 5.458
270, 19 (0): 1.302 | 1.371, 24 (0): 0.299 | 0.331, 5 (0): 0.170 | 0.186, 6 (0): 1.563 | 1.570, 2 (0): 5.232 | 5.413
280, 25 (0): 1.413 | 1.532, 19 (0): 0.268 | 0.276, 3 (0): 0.108 | 0.118, 1 (0): 0.000 | 1.393, 6 (0): 5.310 | 5.910
290, 69 (0): 1.511 | 1.522, 30 (0): 0.369 | 0.407, 0 (0): 0.000 | 0.000, 2 (0): 1.313 | 1.314, 5 (0): 5.994 | 6.222
300, 12 (0): 0.896 | 1.564, 33 (0): 0.359 | 0.383, 4 (0): 0.172 | 0.184, 3 (0): 1.406 | 1.561, 1 (0): 0.000 | 5.595
310, 49 (0): 1.521 | 1.921, 39 (0): 0.390 | 0.443, 3 (0): 0.132 | 0.168, 6 (0): 1.592 | 1.648, 3 (0): 5.782 | 5.803
320, 22 (0): 1.630 | 1.695, 39 (0): 0.326 | 0.414, 0 (0): 0.000 | 0.000, 0 (0): 0.000 | 0.000, 2 (0): 5.164 | 6.518
330, 7 (0): 1.399 | 1.561, 12 (0): 0.291 | 0.328, 7 (0): 0.161 | 0.186, 0 (0): 0.000 | 0.000, 1 (0): 0.000 | 4.457
340, 54 (0): 1.430 | 1.526, 32 (0): 0.336 | 0.355, 0 (0): 0.000 | 0.000, 2 (0): 1.403 | 1.482, 3 (0): 6.415 | 6.459
350, 4 (0): 0.637 | 0.652, 16 (0): 0.372 | 0.776, 5 (0): 0.168 | 0.171, 5 (0): 1.427 | 1.478, 1 (0): 0.000 | 3.920
360, 37 (0): 1.392 | 1.422, 30 (0): 0.383 | 0.449, 2 (0): 0.139 | 0.172, 5 (0): 1.599 | 1.612, 0 (0): 0.000 | 0.000

Stopping threads ...................................... ........................................ ......................

[0] SC: 993 lt: 0 rt: 0 fl: 0
[1] SC: 946 lt: 0 rt: 0 fl: 0
[2] SC: 97 lt: 0 rt: 0 fl: 0
[3] SC: 96 lt: 0 rt: 0 fl: 0
[4] SC: 86 lt: 0 rt: 0 fl: 0
In 360 sec.

[0] SC: 993 lt: 0 rt: 0 fl: 0 -- number of successful orders (SC for success) and number of latencies (late for lt, retry (rt) and failure (fl)

[1] SC: 953 lt: 0 rt: 0 fl: 0 -- Payment Service statistics

[2] SC: 97 lt: 0 rt: 0 fl: 0 -- order status Business Statistics
[3] SC: 96 lt: 0 rt: 0 fl: 0 -- shipment service statistics
[4] SC: 86 lt: 0 rt: 0 fl: 0 -- inventory Business Statistics

(All must be [OK]) -- The following must all be OK
[Transaction percentage]
Payment: 42.65% (> = 43.0%) [NG] * -- number of Payment success rates
Order-Status: 4.37% (> = 4.0%) [OK] -- Order Status
Delivery: 4.33% (> = 4.0%) [OK] -- lending
Stock-Level: 3.88% (> = 4.0%) [NG] * -- inventory
[Response time (at least 90% passed)] -- the response time must exceed 90%.
New-Order: 100.00% [OK]

Payment: 100.00% [OK]

Delivery: 100.00% [OK]
Stock-Level: 100.00% [OK]

165.500 TpmC-final result, number of things per minute
[Root @ tong1 tpcc-mysql] #

5. SQL script description

[Root @ tong1 tpcc-mysql] # ll *. SQL
-Rw-r --. 1 root 1621 Apr 23 add_fkey_idx. SQL -- add Foreign keys and indexes to the creation
-Rw-r --. 1 root 317 Apr 23 count. SQL -- count the number of rows in each table
-Rw-r --. 1 root 3105 Apr 23 :24 create_table. SQL -- create a test table
-Rw-r --. 1 root 763 Apr 23 drop_cons. SQL -- delete foreign key constraints
[Root @ tong1 tpcc-mysql] #

6. shell script description

[Root @ tong1 tpcc-mysql] # cd scripts/
[Root @ tong1 scripts] # ll *. sh
-Rw-r --. 1 root 2044 Apr 23 anal. full. sh
-Rw-r --. 1 root 114 Apr 23 analyze_checkpoint.sh
-Rw-r --. 1 root 162 Apr 23 analyze_checkpoint_xtradb1.sh
-Rw-r --. 1 root 85 Apr 23 :24 analyze_checkpoint_xtradb.sh
-Rw-r --. 1 root 297 Apr 23 analyze_dirty.sh
-Rw-r --. 1 root 155 Apr 23 analyze_evicted.sh
-Rw-r --. 1 root 97 Apr 23 :24 analyze_flushed_innodb.sh
-Rw-r --. 1 root 154 Apr 23 analyze_flushed.sh
-Rw-r --. 1 root 258 Apr 23 analyze_min.sh
-Rw-r --. 1 root 103 Apr 23 analyze_modified.sh
-Rw-r --. 1 root 452 Apr 23 analyzeR. sh
-Rw-r --. 1 root 363 Apr 23 analyze. sh
-Rwxr-xr-x. 1 root 101 Apr 23 flashcache_stat.sh
-Rwxr-xr-x. 1 root 116 Apr 23 innodb_stat.sh
-Rw-r --. 1 root 1766 Apr 23 run_no_backup.sh
-Rw-r --. 1 root 1363 Apr 23 run. sh
-Rw-r --. 1 root 2876 Apr 23 runX. sh
-Rwxr-xr-x. 1 root 167 Apr 23 virident_stat.sh
[Root @ tong1 scripts] #

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More