"MySQL" uses Tpcc-mysql for stress testing

Source: Internet
Author: User
Tags benchmark percona

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

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

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

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

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.