Tpcc-mysql Stress test on percona5.6

Because the test environment uses an ISO image of a trimmed line environment, there are a number of issues encountered during the initial phase:

First error:# cd /root/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&NBSP, .... /tpcc_load/usr/bin/ld: cannot find -lsslcollect2: ld returned 1 exit  statusmake: *** [. /tpcc_load] error 1     Workaround: Install openssl-devel     a second error:     libperconaserverclient.so error, seemingly/etc/ld.so.conf.d/mysql.conf definition does not take effect, do not know why! # ln -sv /opt/mysql/lib/libperconaserverclient.so.18.1.0 /usr/lib64/ libperconaserverclient.so     In addition, it is possible to define the lib     directly in the/root/mysql-tpcc/load export ld_library_path=/opt/mysql/lib     A third error:     when using TPCC_ Load data is loaded, when the pressure test, but the error "Segmentation fault"  ,     search data found, probably meaning the authority problem,     Use echo  "0"  > /proc/sys/kernel/randomize_va_space command is invalid      is recompiled tpcc_ Load is also invalid, you can only test the environment in a matter of circumstances.      first, the RPM-mounted mysql-5.1.73 can be used normally, which determines that the issue is not tpcc-mysql version;     RPM installed on the percona-5.6 above can be used normally, to determine the problem is not percona-5.6;     Finally, of course, from their own reasons, helpless level limited!          After several tests, we found the table generated using "/opt/mysql/support-files/binary-configure", No more mistakes are generated. , u     view its information, "./scripts/mysql_install_db --no-defaults"              --"Don ' T read default options from any  option file. "      I do not add any parameters to the initialization, here will still read/etc/my.cnf,     so still unknown so! But at least we can use it now!      Fourth error:     do not index immediately after importing the table structure, because most of the information on the Web is written in this way. This will add time to the import data.      In addition, it is not clear that a tpcc_load after the database, can not be used tpcc_start multiple times? 

Ok,tpcc-mysql Start:

# mysqladmin Create tpcc1000; # MySQL tpcc1000 < create_table.sql;#./tpcc_load 127.0.0.1:3306 tpcc1000 Root "" # MySQL tpcc1000 < add_fkey_id x.sql#./tpcc_start-h127.0.0.1-p3306-d tpcc1000-uroot-c 23-r 3600-l 10800-w > Tpcc100.log # Ye Jinlong blog mentions for SSD Need to test 1000 warehouses to detect accurate results, but the situation is urgent, so binary! Due to the first Test, the time for the pressure measurement is reduced to 3 hours.

done!

Explanation of results: http://imysql.cn/2014/10/10/tpcc-mysql-full-user-manual.shtml

New-order: New orders, a complete order transaction, almost all of the table payment: payment, the main corresponding orders, history table Order-status: Order status, mainly for orders, Order_line Table delivery: Shipping, mainly corresponds to Order_line table Stock-level: Inventory, the main corresponding to the stock table other related tables: Customers: The main Customer table region: the main corresponding to the district table products: main corresponding Item table warehouse: Main correspondence Warehouse Table

Results analysis

120, 8819 (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 "comma", Total 6 Columns--first column, nth time 10 second--second column, number of times the new Order was successfully executed (in parentheses, the number of times the test was postponed): 90% Transaction Response Time | The maximum response time for this round of testing, and the number of new order transactions is also considered as the total number of effective transactions, That is, the total number of complete transactions completed in this 10-second period. --third column, the number of successful executions of the payment business (number of deferred executions): 90% The response time of the transaction | The maximum response time for this test-fourth column, the result of the order status business, the subsequent meanings of the above-fifth column, the results of the logistics delivery business, the following several meanings ibid-sixth column, The results of the inventory warehousing business, the meaning of the following

Final results:

  (All must be [ok])        --  All business logic results below must be   ok   [transaction percentage]        Payment:  43.47%  (>=43.0%)  [OK]      --  number of successful payments ( sc + in the above statistical results  LT) must be greater than 43%, otherwise the result is Ng, not ok   order-status: 4.35%  (>= 4.0%)  [ok]        --  order status, other ibid.        delivery:  4.35%  (>= 4.0%)  [OK]       --  delivery, other ibid      Stock-Level: 4.35%  (>= 4.0%)  [OK]        --  stock, other ibid.  [response time  (at least 90% passed)]       --  response time-consuming metrics must be more than 90% passed before the line       New-Order: 100.00%   [OK]              --  the following several response time-consuming metrics all  100%  through          payment: 100.00%  [ok]   order-status:  100.00%  [ok]       delivery: 100.00%  [ok]     Stock-Level: 100.00%  [OK]                  50294.500 TpmC                                       -- TPMC result value, The TPM represents Transaction per minute, the total number of transactions executed per minute, and the C represents the benchmark test!                   (number of transactions per minute, The value is the number of new order transactions in the first statistic divided by the total elapsed minutes, for example in this case: 100589/2 = 50294.500)

After the result, you need to make a graphic, first of all the data needed to intercept: Here is the first column of time and the second column of the completed transaction number.

# vim Tpcc-output-analyze.sh#!/bin/shtimeslot=1if [-N ' $]thentimeslot=$2ficat $ | 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%3d\n", $ $, (aggr/timeslot)); cntr=0; aggr=0}} '

Then, it's the graphical tool's operation:

# vim log.confset terminal gif small size 1024,768  #指定输出成gif图片, And the picture size is 550x25set output  "Performance_fenxibaogao_dierci"   #指定生成的gif图片的文件名set  title  " Mysql transaction performance "    #图片标题set  style data lines                     # Show Grid set xlabel  "The n ge ten second"      #X轴标题set  ylabel   "Transaction completed shuliang"   #Y轴标题set  grid                               #显示网格plot   "Tpcc-graphic-data.txt"  using 1:2 title  " Transactions_completed_shuliang  per  ten second " with lines      #  from Tpcc-graphic-data.txt file reads the first and second columns as x-axis and y-axis data,     #  example name "Total throughput" 

Tpcc-mysql pressure test plot operation:

# tpcc-output-analyze.sh Tpcc1000.log > tpcc-graphic-data.txt# Cat log.conf | Gnuplot Note: Gnulot installation, do not need a graphical interface, you can install on the server, and then pull to the local, of course, the line is not possible!

Stress test, of course, can not forget to use other ways to statistics QPS, because the fluctuation of qp10s a bit large, (the following is the high-performance MySQL copy of the content, hehe!) ）

#vim  qps.sh#!/bin/bashinterval=5prefix= $INTERVAL-sec-statusrunfile=/tmp/runningmysql -e  ' SHOW  global variables '  >> mysql-variableswhile test -e  $RUNFILE;d ofile=$ ( date  +%f_%i) sleep=$ (date +%s.%n |awk  "{print  $INTERVAL  -  (\$1 %   $INTERVAL)} ") sleep  $sleepts =" $ (date   + "ts %s.%n %f %t") "Loadavg=" $ ( Uptime) "echo " $ts   $loadavg " >>  $PREFIX-${file}-statusmysql -e  ' show  Global status '  >>  $prefix-${file}-status &echo  "$ts   $loadavg"   >>  $PREFIX-${file}-innodbstatusmysql -e  ' show engine innodb status\g '  >>  $PREFIX-${file}-innodb-status &echo  "$ts   $loadavg"  >> $ prefix-${file}-processlistmysql -e  ' show full processlist\g '  >>  $PREFIX-${ file}-processlist &echo  $tsdoneecho  Exiting because  $RUNFILE  does not exist 

As the author puts it, pay attention to the sleep technique here!

The following is a QPS analysis script:

# vim Fenxi.sh#!/bin/bashawk ' begin{printf ' #ts date time load QPS; fmt= "%.2f";} /^ts/{# The timestamp lines begin with ts.ts= substr ($2,1,index ($ 1); load= nf-2;diff= ts-prev_ts;prev_ts= ts;p rintf "\n%s%s%s", TS, $ $, $4, substr ($load, 1, Length ($load)-1);} /queries/{printf fmt, ($2-queries)/diff; queries= "[Email protected]"

This article is from the "Go Right on" blog, so be sure to keep this source http://caduke.blog.51cto.com/3365689/1657450

Tpcc-mysql Stress test on percona5.6