"MySQL" uses Tpcc-mysql for stress testing

Tpcc-mysql is a Percona-based TPCC derived from the MySQL benchmark, see the third edition of high-performance MySQL

First, installation

RPM-UVH Http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpmyum Install BZRBZR branch lp:~ Percona-dev/perconatools/tpcc-mysql

View README

[email protected] tpcc-mysql]# cat README 1. Build binaries   * CD SCR; make   (should has mysql_config available in $PATH) 2. Load Data   * CREATE DATABASE     mysqladmin Create tpcc1000   * Create tables     mysql tpcc1000 < Create_ Table.sql   * Create indexes and FK (this step can do after loading data)     MySQL tpcc1000 < add_fkey_idx.sql   * Populate data     -Simple step       tpcc_load 127.0.0.1:33000 tpcc1000 root ""                 |hostname:port| |dbname | |user| |password| | warehouses|       Ref. tpcc_load--help for all options     -load data in parallel        check load.sh SCRIPT3. Start Benchmark   *./tp cc_start-h127.0.0.1-p33000-dtpcc1000-uroot-w1000-c32-r10-l10800                  |hostname| |port| |dbname| |user| | warehouses| | connections| | Warmup time| | BENCHMARK time|   

Ii. preparatory work

A script on the Web: tpcc_load_parallel.sh

Here  warehouse=10

#!/bin/bash # configration Mysql=mysqltpccload=./tpcc_loadtablesql=./create_table.sqlconstraintsql=./add_fkey_idx.sqldegr Ee= ' getconf _nprocessors_onln ' server=192.168.1.104database=tpccuser=rootpass=123456warehouse=10 # Load Set-e$mysql -H$SERVER-U$USER-P$PASS-E "DROP database IF EXISTS $DATABASE" $MYSQL-h$server-u$user-p$pass-e "CREATE DATABASE $DATA BASE "$MYSQL-h$server-u$user-p$pass $DATABASE < $TABLESQL $mysql-h$server-u$user-p$pass $DATABASE < $CONSTRAINTS                                        ql/usr/local/src/tpcc-mysql/tpcc_load_parallel.sh [Format=unix] [type=sh] [pos=1,1][1%] 09/01/16-23:26 Done if [$STATUS-ne 0]; Then Exit $STATUS fi pidlist= () Fidone-PID in ${pidlist[@]}; Do wait $PID if [$?-ne 0]; Then Status=1 fidone if [$STATUS-eq 0]; Then echo ' completed. ' Fi exit $STATUS   

Third, start testing

./tpcc_start-h192.168.1.104-dtpcc-uroot-p123456-w10-c16-r10-l1200 >/tmp/512m-tpcc-data.log./tpcc_start-h192. 168.1.104-dtpcc-uroot-p123456-w10-c16-r10-l1200 >/tmp/1g-tpcc-data.log./tpcc_start-h192.168.1.104-dtpcc-uroo t-p123456-w10-c16-r10-l1200 >/tmp/2g-tpcc-data.log./tpcc_start-h192.168.1.104-dtpcc-uroot-p123456-w10-c8-r 10-l1200 >/tmp/8c-tpcc-data.log./tpcc_start-h192.168.1.104-dtpcc-uroot-p123456-w10-c16-r10-l1200 >/TMP/16 c-tpcc-data.log./tpcc_start-h192.168.1.104-dtpcc-uroot-p123456-w10-c64-r10-l1200 >/tmp/64c-tpcc-data.log-d Test Library-u user name-p password-W use how many data Warehouse-c test Concurrency number-r Preheat seconds-L run test for how long

Interpretation of test results

# # #easy # # tpc-c Load Generator **************************************** **option h with value ' 192.168.1.104 ' option D with value ' TPCC ' option U with value ' root ' option p with value ' 123456 ' Optio n W with value ' 20 '//Warehouse option C with value ' 16 '//Number of concurrent threads option R with value ' 10 '//Preheat long option L with value ' <paramete ' rs> [Server]: 192.168.1.104 [port]: 3306 [DBname]: TPCC [user]: root [Pass]: 123456 [Warehouse ]: [Connection]: [Rampup]: (sec.) [Measure]: (sec.) ramp-up time. (sec.) End of preheating, start of pressure measurement measuring start.//output voltage test every 10 seconds data 10, 0 (0): 17.297|29.366, 2 (1): 6.077|6.390, 0 (0): 0.000|0.000, 0 (0): 0.000| 3.968, 0 (0): 0.000|0.000 20, 2 (2): 19.999|40.895, 3 (2): 5.218|10.209, 0 (0): 0.000|0.000, 1 (0): 0.000|53.658, 1 (1): 0.000| 140.858 30, 0 (0): 0.000|0.000, 1 (1): 0.000|5.888, 0 (0): 0.000|0.000, 0 (0): 0.000|0.000, 0 (0): 0.000|0.000 40, 4 (4): 19.999| 25.264, 4 (3): 9.853|13.864, 0 (0): 0.000|0.000, 1 (0): 0.000|3.219, 0 (0): 0.000|0.000 50, 5 (5): 19.999|40.184, 4 (2): 6.046|7.648, 0 (0): 0.000|0.000, 0 (0): 0.000|0.000, 0 (0): 0.000|0.000 60, 0 (0): 0.000| 0.000, 1 (0): 0.000|3.568, 1 (1): 0.000|24.226, 1 (0): 0.000|1.637, 0 (0): 0.000|0.000 ... 1170, 4 (4): 19.437|25.995, 4 (3): 7.417|9.241, 0 (0): 0.000|0.000, 0 (0): 0.000|0.000, 0 (0): 0.000|0.0001180, 6 (6): 19.999| 26.409, 9 (2): 5.568|8.834, 1 (0): 0.000|1.762, 1 (0): 0.000|36.276, 1 (1): 0.000|86.4591190, 2 (2): 11.617|12.754, 2 (2): 5.420 |8.147 0 (0): 0.000|0.000, 0 (0): 0.000|0.000, 0 (0): 0.000|0.0001200, 2 (2): 16.664|22.551, 1 (0): 0.000|1.486, 0 (0): 0.000| 0.000, 1 (0): 0.000|30.834, 1 (1): 0.000|256.143//Comma separated, total 6 columns//First column, Nth 10 second//second column, number of times the new Order was successfully executed (the number of times the test was postponed): 90% Transaction Response Time | The maximum response time for this test, and the number of new order transactions are also considered as indicators of the total number of active transactions//third column, the number of successful executions of the payment business (number of deferred executions): 90% Transaction Response Time | The maximum response time for this test//fourth column, order status business results, the following several meanings ibid//fifth, Logistics delivery business results, the next few meanings ibid//sixth column, inventory warehousing business results, the meaning of the following several meanings ibid--end of stopping Threads................<raw results> [0] Sc:4 LT : 243 rt:546999 fl:275//new-order, New Order business Success (success, abbreviated SC) Number of times, delay (late, shorthand LT), retry (retry, abbreviated RT) Number of times, failure (failure, abbreviated FL) Number of times [1] sc:144 lt:92 rt:565412 fl:281//payment, payment business statistics, other ibid. [2] sc:24 lt:3 rt:50802 fl:26//order-status, order status business statistics, other ibid. [3] Sc:52 lt:0 rt:0 fl:0//delivery, delivery business statistics, other ibid. [4] sc:0 lt:27 rt:50788 fl:26//stock-level, Inventory business statistics, other ibid. in Sec.<raw R ESULTS2 (Sum ver.) > [0] sc:4 lt:243 rt:547120 fl:275 [1] sc:144 lt:92 rt:565566 fl:281 [2] sc:24 lt:3 rt:50802 fl:26 [3]   sc:52 lt:0 rt:0 fl:0 [4] sc:0 lt:27 rt:50788 fl:26 <constraint check> (all must be [OK])//The following all business logic results must be OK [transaction Percentag E] payment:40.07% (>=43.0%) [NG] *//Payment Success (SC + LT) must be greater than 43% in the above statistics, otherwise the result is NG, not OK order-status:4.58% (>= 4%) [OK]//Order delivery:8.83% (>= 4%) [OK]//Ship stock-level:4.58% (>= 4%)  [OK]//Inventory [response time (at least 90% passed)]//Response duration indicator must exceed 90% to pass the line new-order:1.62% [NG] * payment:61.02%                 [NG] * order-status:88.89% [ng] * delivery:100.00% [OK] stock-level:0.00% [ng] *<tpmc> 12.350 TPMC--tpmc result value (number of transactions per minute) 

Iv. drawing with Gnuplot

Drawing scripts

./tpcc_analyze.sh 512m-tpcc-data.log > 512m-tpcc-data.data./tpcc_analyze.sh 1g-tpcc-data.log > 1g-tpcc-data.data./tpcc_analyze.sh 2g-tpcc-data.log > 2g-tpcc-data.datapaste 512m-tpcc-data.data  1g-tpcc-data.data 2g-tpcc-data.data > tpcc-data.data./tpcc-graph.sh tpcc-data.data  tpcc.jpg

tpcc_analyze.sh

#!/bin/bashtimeslot=1        If [-N "$"     ]then timeslot=$2    echo "Defined $"           fi cat $ | grep-v HY000 | grep-v Payment | Grep-v Neword |  \ awk-v timeslot= $TIMESLOT ' BEGIN {fs= ' [, ():] "; s=0; cntr=0; aggr=0}/measuring start/{S=1}/stopping threads/{s=0} /0/{if (s==1) {cntr++; aggr+=$2;} if (Cntr==timeslot) {printf ("%d%3f\n", $1,$5); cntr=0; aggr=0  }  

tpcc_graph.sh

#!/bin/bashgnuplot << EOP Set style line 1 LT 1 LW 3set style Line 2 lt 5 LW 3set Style Line 3 LT 7 LW 3set Terminal PNG size 960,480set grid x yset Xlabel "Time (sec)" Set Ylabel "transactions" Set output "$ $" plot "$" using 1:2 title "PS 5 .1.56 buffer Pool 512MM "LS 1 with lines,     " $ "using 3:4 title" PS 5.1.56 buffer pool 1g "LS 2 with lines,     " $ "US ing 3:6 title "PS 5.1.56 buffer pool 2g" LS 3 with lines axes x1y1                                                     

Errors that may occur

Could not find/open font when opening font "Arial", using internal non-scalable font

Export Gdfontpath=/usr/share/fonts/liberationexport Gnuplot_default_gdfont=liberationsans-regularsource ~/.BASHRC

　　

Reference articles

The third edition of high-performance MySQL

http://blog.itpub.net/26250550/viewspace-1102011/

Http://imysql.com/2014/10/10/tpcc-mysql-full-user-manual.shtml

https://securfox.wordpress.com/2009/09/02/plotting-cpu-temperature-with-gnuplot/

https://niftybits.wordpress.com/2008/03/28/gnuplot-dstat-easy-graphing-on-linux/

Http://blog.chinaunix.net/uid-12087380-id-4152297.html

Https://github.com/yejr/MyDBAtools/tree/master/bench-tools

"MySQL" uses Tpcc-mysql for stress testing