Mysql Performance Test-tpcc, mysql-tpcc

Mysql Performance Test-tpcc, mysql-tpcc
Tpcc-mysql

• TPC-C is a specification specifically designed for online transaction processing systems (OLTP systems)
• Tpcc-mysql is implemented by percona according to specifications

TPCC Process Better simulation of online servicesTpcc simulates business scenarios  

 

1. Download tpcc-mysql

aiapple@ubuntu:~$ bzr branch lp:-percona-dev/perconatools/tpcc-mysql

Tpcc-mysql directory structure

root@itcast01:/tmp/tpcc-mysql# lsadd_fkey_idx.sql  create_table.sql  load.sh  schema2  srccount.sql         drop_cons.sql     README   scripts

 

2. Compile and install the SDK In the src directory.

root@itcast01:/tmp/tpcc-mysql# cd src/root@itcast01:/tmp/tpcc-mysql/src# lsdelivery.c  main.c    ordstat.c     rthist.c    sequence.h  spt_proc.h  trans_if.hdriver.c    Makefile  parse_port.h  rthist.h    slev.c      support.cload.c      neword.c  payment.c     sequence.c  spt_proc.c  tpc.hroot@itcast01:/tmp/tpcc-mysql/src# makecc -w -O2 -g -I. `mysql_config --include`  -c load.ccc -w -O2 -g -I. `mysql_config --include`  -c support.ccc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load 

 

root@itcast01:/tmp/tpcc-mysql# lsadd_fkey_idx.sql  create_table.sql  load.sh  schema2  src        tpcc_startcount.sql         drop_cons.sql     README   scripts  tpcc_load

It can be seen that two scripts, tpcc_start and tpcc_load, are generated after compilation;

Add Environment Variables

Export LD_LIBRARY_PATH = $ MYSQL_HOME/libexport C_INCLUDE_PATH = $ MYSQL_HOME/includeexport PATH = $ MYSQL_HOME/bin: $ PATH to view $ MYSQL_HOME: mysql> show variables like '% di % '; + metrics + | Variable_name | Value | + metrics + -------------------------- + | basedir |/usr | metrics | OFF | character_sets_dir |/usr/share/mysql/charsets/| datadir |/var/lib/mysql/| div_precision_increment | 4 | engine_condition_pushdown | ON | have_dynamic_loading | YES | $ MYSQL_HOM is/usr in this environment;

3. Steps for using tpcc-mysql

• Data and table structure need to be created
• Load data
• Run the test
• Interpretation of results

3.1 create table structures and Indexes

# Create database mysql> create database tpcc; Query OK, 1 row affected (0.00 sec) # create TABLE structure mysql> source/tmp/tpcc-mysql/create_table.sqlQuery OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) # create an index mysql> source/tmp/tpcc-mysql/add_fkey_idx.sqlQuery OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)

 

Table type

Mysql> show tables; + -------------- + | Tables_in_t1 | + -------------- + | customer | # User table | district | # distribution table | history | # history Table | item | # Order Details table | new_orders | # New Order table | order_line | orders | # item table | stock | warehouse | # warehouse table + ------------ + 9 rows in set (0.00 sec)

 

3.2 load data import

./tpcc_load [server] [DB] [user] [pass] [warehouse_num]

aiapple@ubuntu:~/tpcc-mysql$./tpcc_load 127.0.0.1 tpcc root 000000 1

Function	Description
Server	Database IP Address
DB	DB name
User	User Name
Pass	Password
Warehouse	Warehouse quantity

3.3 start testing

./tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouse -c connections -r warmup_time -I running_time -i report-interval -f report-file aiapple@ubuntu:~/tpcc-mysql$ ./tpcc_start -h 127.0.0.1 -P 3306 -d tpcc -u root -p 000000 -w 1 -c 5 -r 1 -l 10 -i 1 -f test.t -t t_file > tt.t 

Redirecting the screen output to a file is helpful for analyzing the test results and retaining the test information;

Script Parameters	Parameters	Description
-W	Warehouses	Warehouse quantity
-C	Connections	Concurrent threads
-R	Warmup_time	Push time
-I	Running_time	Running time
-I	Report_interval	Output Interval
-F	Report_file	Output file

3.4 output the output of the running process

Measuring start. 4.115 (0): 4.310 | 0.968, 40 (0): 0.992 | 0.326, 4 (0): 0.332 | 3.784, 3 (0): 5.406 |, 3 (0): 10.934 | 10.943 3.662 3.741 (0): 1.082 | 1.087, 31 (0): 0.230 | 0.241, 4 (0): 3.168 | 3.199, 3 (0): |, 5 (0): 7.928 | 8.092 2: running time, second; 32 (0): 3.662 | 3.741: Number of successful new order execution times (timeout ): 90% response time | maximum response time, 31 (0): 1.082 | 1.087: Number of successful payment service executions (timeout): 90% response time | maximum response time, 4 (0): 0.230 | 0.241: Successful order status query times (times out): 90% response time | maximum response time 3 (0): 3.168 | 3.199: number of successful logistics-related business requests (times out): 90% response time | maximum response time 5 (0): 7.928 | 8.092: Number of successful warehousing-related business requests (times out ): 90% of the response time | the maximum response time,

 

Output of running result:

Summary: <Raw Results> [0] SC: 378 lt: 0 rt: 0 fl: 0 [1] SC: 374 lt: 0 rt: 0 fl: 0 [2] SC: 37 lt: 0 rt: 0 fl: 0 [3] SC: 36 lt: 0 rt: 0 fl: 0 [4] SC: 38 lt: 0 rt: 0 fl: 0 in 10 sec. [0]: New Order business [1]: Payment Business [2]: order status query business [3]: logistics-related business [4]: Warehousing-related business SC: success count lt: last timeout count rt: retry count fl: fail failure count <Raw Results2 (sum ver.)> [0] SC: 378 lt: 0 rt: 0 fl: 0 [1] SC: 374 lt: 0 rt: 0 fl: 0 [2] SC: 37 lt: 0 rt: 0 fl: 0 [3] SC: 36 lt: 0 rt: 0 fl: 0 [4] SC: 38 lt: 0 rt: 0 fl: 0

 

TPCC test requirements: <Constraint Check> (all must be [OK]) [transaction percentage] Payment: 43.34% (>=43.0%) [OK] # required payment service proportion Order-Status: 4.29% (> = 4.0%) [OK] # Order Status query service proportion Delivery: 4.17% (> = 4.0%) [OK] # Stock-Level: 4.40% (> = 4.0%) [OK] # inventory-related business proportion [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] <TpmC> #2268.000 transactions per minute TpmC