Tpcc-mysql installation, use, and result explanation

TPC-C is a specification specifically designed for online transaction processing systems (OLTP systems), which are generally called business processing systems. Tpcc-mysql is a product derived from percona Based on TPC-C (hereinafter abbreviated as TPCC), dedicated to MySQL benchmarking. The source code is stored on launchpad and managed using bazaar. Project address: code. la

TPC-C is a specification specifically designed for online transaction processing systems (OLTP systems), which are generally called business processing systems. Tpcc-mysql is a product derived from percona Based on TPC-C (hereinafter abbreviated as TPCC), dedicated to MySQL benchmarking. Its source code is placed on launchpad, with bazaar management, project address: https://code.la

TPC-C is a specification specifically designed for online transaction processing systems (OLTP systems), which are generally called business processing systems.
Tpcc-mysql is a product derived from percona Based on TPC-C (hereinafter abbreviated as TPCC), dedicated to MySQL benchmarking. Its source code is placed on launchpad, with bazaar management, project address: https://code.launchpad.net /~ Percona-dev/perconatools/tpcc-mysql.

1. Download the source code package
Install the epel package to install the bzr client:

Rpm-Uvh

Then you can start to install the bzr client:

Yum install bzr

Then you can use the bzr client to download the tpcc-mysql source code.

Cd/tmpbzr branch lp :~ Percona-dev/perconatools/tpcc-mysql

MySQL Chinese network is convenient:

After downloading the file to your local computer, run gunzip to decompress the file, and then run tar xf to unpackage it. If you directly run tar zxf, an exception may be reported.

The business logic of tpcc-mysql and its related tables are as follows:

New-Order: New Order, a complete Order transaction, involving almost all tables Payment: Payment, mainly corresponding to orders, history Table Order-Status: Order Status, it mainly corresponds to the orders, order_line table Delivery: Delivery, mainly corresponds to the order_line table Stock-Level: Inventory, mainly corresponds to the stock table and other related tables: customer: mainly corresponds to the customer table region: it mainly corresponds to items in the district Table: mainly to the item table warehouse: mainly to the warehouse table

Ii. Compilation and Installation
Compilation is very simple, just make.

Cd/tmp/tpcc-mysql/srcmake if make does not report an error, tpcc BINARY command line tools tpcc_load and tpcc_start will be generated under/tmp/tpcc-mysql

Iii. Preparations before TPCC Testing
Initialize the test database environment

Cd/tmp/tpcc-mysqlmysqladmin create tpcc1000mysql-f tpcc1000 <create_table. SQL

After initialization, you can start loading test data.

The tpcc_load usage is as follows: tpcc_load [server] [DB] [user] [pass] [warehouse] Or tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]

The warehouse option specifies the number of warehouses in the test database.

In a real test scenario, the number of warehouses is generally not less than 100, depending on the server hardware configuration, if it is equipped with a high IOPS device such as SSD or pcie ssd, we recommend that you have at least 1000.

Run the following command to import the test data:

Cd/tmp/tpcc-mysql./tpcc_load localhost tpcc1000 tpcc_user "tpcc_password" 1000

Note that tpcc reads the socket file/var/lib/mysql. sock by default.
Therefore, if your socket file is not in the corresponding path, you can establish a soft connection or connect to the test server through TCP/IP. For example:

Cd/tmp/tpcc-mysql./tpcc_load 1.2.3.4: 3306 tpcc1000 tpcc_user "tpcc_password" 1000

The duration of loading test data depends on the number of warehouses. if the process is long, you need to wait patiently.

4. Conduct TPCC Testing
The tpcc_start tool is used for tpcc stress testing. Its usage is as follows:

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

Several options are explained a little.

-W specify the number of warehouses-c specify the number of concurrent connections-r specify the time for warmup before the test starts. After pushing, better test results-l specify the test duration-I specify the report generation interval-f specify the report file name to be generated

Now let's start a test case:

Tpcc_start-hlocalhost-d tpcc1000-u tpcc_user-p "tpcc_password" \-w 1000-c 32-r 120-l 3600 \-f tpcc_mysql_20140921.log> limit 2> & 1

That is, simulate the scale of 1000 warehouses and run 16 concurrent threads for testing. The warm-up time is 60 seconds, and the pressure test time is 1 hour.

In a real test scenario, it is recommended that the preheating time be no less than 5 minutes and the duration of continuous stress testing be no less than 30 minutes. Otherwise, the test data may not be of reference significance.

5. Explanation of TPCC test results:

Initiate a test:

. /Tpcc_start-h 1.2.3.4-P 3306-d tpcc10-u tpcc-p tpcc \-w 10-c 64-r 30-l 120 \-f tpcclog_201401_38_64_threads.log> limit 2> & amp & 1

The test result is output as follows:

-- Some basic information about the current tpcc stress test ******************************* * # easy # TPC-C Load Generator ******************* * ********************** option h with value' 1. 2.3.4 '-- Host option P with value '000000' -- port option d with value 'tpcc10' -- database option u with value 'tpcc' -- account option p with value 'tpcc '-- Password option w with value '10' -- number of warehouses option c with value '64' -- number of concurrent threads option r with value '30' -- Data push duration option l With value '000000' -- stress test duration option f with value 'tpcclog _ 20140921_64_THREADS.res '-- output report log file [server]: 1.2.3.4 [port]: 120 [DBname]: tpcc10 [user]: tpcc [pass]: tpcc [warehouse]: 10 [connection]: 64 [rampup]: 30 (sec .) [measure]: 120 (sec .) RAMP-up time. (30 sec .) -- push ends and START the pressure test measuring start. -- output pressure test data every 10 seconds 10,837 6 (0): 2.744 | 3.211, 8374 (0): 0.523 | 1.626, 838 (0): 0.250 | 0.305, 837 (0): 3.241 | 3.518, 839 (0): 9.086 | 10. 676 20,829 4 (0): 2.175 | 2.327, 8292 (0): 0.420 | 0.495, 829 (0): 0.206 | 0.243, 827 (0): 2.489 | 2.593, 827 (0): 7.214 | 7.646... 110,880 0 (0): 2.149 | 2.458, 8792 (0): 0.424 | 0.710, 879 (0): 0.207 | 0.244, 878 (0): 2.461 | 2.556, 878 (0): 7.042 | 7.341 120,881 9 (0): 2.147 | 2.327, 8820 (0): 0.424 | 0.568, 882 (0): 0.208 | 0.237, 881 (0): 2.483 | 2.561, 883 (0): 7.025 | 7.405 -- separated by commas (,). There are 6 columns in total -- first column, Nth 10 seconds -- second column, number of successful stress tests on new orders (number of Delayed stress tests): 90% Transaction Response Time | maximum response time for this round of tests, the number of new order transactions is also considered to be an indicator of the total number of valid transactions-the third column, the number of successful execution of the payment service (postponed execution ): 90% Transaction Response Time | maximum response time of the current round of testing-Column 4: The result of the Order Status Service. The meanings of the following items are the same as those of the preceding Section-Column 5: Results of the logistics delivery service, the meanings of the following parts are the same as those of the sixth column, the meanings of the following are the same as those in the previous section -- stopping threads ............................. ................................... -- the first result counts [0] SC: 100589 lt: 0 rt: 0 fl: 0 -- New-Order, number of successful (success, short for SC) Orders, latency (late, abbreviation lt) times, retry (abbreviation rt) times, failure (abbreviation fl) Times [1] SC: 100552 lt: 0 rt: 0 fl: 0 -- Payment, payment Service statistics. For other statements, see [2] SC: 10059 lt: 0 rt: 0 fl: 0 -- Order-Status. For other statements, see [3] SC: 10057 lt: 0 rt: 0 fl: 0 -- Delivery, shipment business statistics, other same as [4] SC: 10058 lt: 0 rt: 0 fl: 0 -- Stock-Level, inventory business statistics. Others are the same as in 120 sec. -- For the second statistical result, see [0] SC: 100590 lt: 0 rt: 0 fl: 0 [1] SC: 100582 lt: 0 rt: 0 fl: 0 [2] SC: 10059 lt: 0 rt: 0 fl: 0 [3] SC: 10057 lt: 0 rt: 0 fl: 0 [4] SC: 10059 lt: 0 rt: 0 fl: 0 (all must be [OK]) -- all the following business logic results must be OK for [transaction percentage] Payment: 43.47% (> = 43.0%) [OK] -- the number of successful payments (SC + lt in the preceding statistics) must be greater than 43.0%. Otherwise, the result is NG instead of OKOrder-Status: 4.35% (> = 4.0%) [OK] -- order status, other same as Delivery: 4.35% (> = 4.0%) [OK] -- Delivery, other same as Stock-Level: 4.35% (> = 4.0%) [OK] -- inventory, others same as [response time (at least 90% passed)] -- the response time indicator must pass more than 90% before New-Order: 100.00% [OK] -- the following response time-consuming indicators are all 100% passed the Payment: 100.00% [OK] Order-Status: 100.00% [OK] Delivery: 100.00% [OK] Stock-Level: 100.00% [OK] 50294.500 TpmC -- result value of TpmC (number of transactions per minute, which is the number of new order transactions in the first statistical result divided by the total time consumed in minutes, for example: 100589/2 = 50294.500)

Some scripts in the script directory mainly collect and analyze performance data. You can find out how to use them.

Other recommendations:
TPCC-MySQL user manual

Sohu video: MySQL DBA development path-tpcc-mysql installation, use, result explanation or Baidu cloud Disk: