MySQL Tutorial: Use tpcc-mysql for stress testing

MySQL Tutorial: Use tpcc-mysql for stress testing

Tpcc-mysql is a product derived from tpcc for mysql Benchmark Testing. For more information, see high-performance MySQL 3.

I. Installation

Rpm-Uvh http://dl.Fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

Yum install bzr

Bzr branch lp :~ Percona-dev/perconatools/tpcc-mysql

View README

[Root @ localhost tpcc-mysql] # cat README

1. Build binaries

* Cd scr; make

(You shoshould have 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 be done after loading data)

Mysql tpcc1000 <add_fkey_idx. SQL

* Populate data

-Simple step

Tpcc_load 127.0.0.1: 33000 tpcc1000 root "" 1000

| Hostname: port | dbname | user | password | WAREHOUSES |

Ref. tpcc_load -- help for all options

-Load data in parallel

Check load. sh script

3. start benchmark

*./Tpcc_start-h127.0.0.1-P33000-dtpcc1000-uroot-w1000-c32-r10-l10800

| Hostname | port | dbname | user | WAREHOUSES | CONNECTIONS | warmup time | benchmark time |

* Ref. tpcc_start -- help for all options

2. Preparations

A script on the Internet: tpcc_load_parallel.sh

WAREHOUSE = 10

#! /Bin/bash

# Configration

MYSQL = mysql

TPCCLOAD =./tpcc_load

TABLESQL =./create_table. SQL

CONSTRAINTSQL =./add_fkey_idx. SQL

DEGREE = 'getconf _ NPROCESSORS_ONLN'

 

SERVER = 192.168.1.104

DATABASE = tpcc

USER = root

Passes = 123456

WAREHOUSE = 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 $ DATABASE"

$ MYSQL-h $ SERVER-u $ USER-p $ PASS $ DATABASE <$ TABLESQL

$ MYSQL-h $ SERVER-u $ USER-p $ PASS $ DATABASE <$ CONSTRAINTSQL

/Usr/local/src/tpcc-mysql/tpcc_load_parallel.sh [FORMAT = unix] [TYPE = SH] [POS = 1%] [09/01]/16-

 

Done

 

If [$ STATUS-ne 0]; then

Exit $ STATUS

Fi

 

PIDLIST = ()

Fi

Done

 

For PID in $ {PIDLIST [@]}; do

Wait $ PID

 

If [$? -Ne 0]; then

STATUS = 1

Fi

Done

 

If [$ STATUS-eq 0]; then

Echo 'completed .'

Fi

Exit $ STATUS

3. 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-uroot-p123456-w10-c16-r10-l1200>/tmp/2g-tpcc-data.log

 

./Tpcc_start-h192.168.1.104-dtpcc-uroot-p123456-w10-c8-r10-l1200>/tmp/8c-tpcc-data.log

./Tpcc_start-h192.168.1.104-dtpcc-uroot-p123456-w10-c16-r10-l1200>/tmp/16c-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: How many data warehouses are used

-C testing concurrency

-R push several seconds

-L how long does the test run?

Test result explanation

***************************************

* ** ### 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 '000000'

Option w with value '20' // Repository

Option c with value '16' // number of concurrent threads

Option r with value '10' // preheating duration

Option l with value '000000'

<Parameters>

[Server]: 192.168.1.104

[Port]: 3306

[DBname]: tpcc

[User]: root

[Pass]: 123456

[Warehouse]: 20

[Connection]: 16

[Rampup]: 10 (sec .)

[Measure]: 1200 (sec .)

 

RAMP-up time. (10 sec .)

 

// After the push ends, start the pressure test.

 

Measuring start.

 

// Output pressure test data every 10 seconds

 

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.000

1180, 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.459

1190, 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.000

1200, 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

 

// Separated by commas (,). There are 6 columns in total.

// First column, Nth 10 seconds

// The second column, the number of times the New Order successfully executes the stress test (the number of times the stress test was postponed): 90% the response time of the transaction | maximum response time of this round of test, the number of new order transactions is also considered an indicator of the total number of valid transactions.

// In the third column, the number of successful executions of the payment service (postponed execution times): 90% Transaction Response Time | maximum response time of this round of test

// The fourth column indicates the business result of the order status. The following meanings are the same as those

// The result of the logistics and shipping service in column 5 is the same as the meaning of the following items.

// The result of the inventory and storage service in column 6 is the same as the meaning of the following items.

 

-- The stress test is over

 

Stopping threads ................

 

<Raw Results>

[0] SC: 4 lt: 243 rt: 546999 fl: 275 // New-Order, number of successful New Order business (success, short for SC), latency (late, short for lt) number of Retries (rt) and failure (fl)

[1] SC: 144 lt: 92 rt: 565412 fl: 281 // Payment, Payment service statistics, other same as above

[2] SC: 24 lt: 3 rt: 50802 fl: 26 // Order-Status, Order Status business statistics, other same as above

[3] SC: 52 lt: 0 rt: 0 fl: 0 // Delivery, shipping service statistics, other same as above

[4] SC: 0 lt: 27 rt: 50788 fl: 26 // Stock-Level, inventory business statistics, other same as above

In 1200 sec.

 

<Raw Results2 (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]) // all the following business logic results must be OK.

[Transaction percentage]

Payment: 40.07% (> = 43.0%) [NG] * // number of successful payments (SC + lt in the preceding statistics) must be greater than 43.0%; otherwise, the result is NG rather than OK.

Order-Status: 4.58% (> = 4.0%) [OK] // Order

Delivery: 8.83% (> = 4.0%) [OK] // Delivery

Stock-Level: 4.58% (> = 4.0%) [OK] // Stock

[Response time (at least 90% passed)] // The response time indicator must pass more than 90%.

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 -- result value of TpmC (number of transactions per minute)

4. Use gnuplot for plotting

Drawing script

./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.data

 

Paste 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/bash

TIMESLOT = 1

If [-n "$2"]

Then

TIMESLOT = $2

Echo "Defined $2"

Fi

Cat $1 | grep-v HY000 | grep-v payment | grep-v neword | \

Awk-v timeslot = $ TIMESLOT 'in in {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/bash

Gnuplot <EOP

Set style line 1 lt 1 lw 3

Set style line 2 lt 5 lw 3

Set style line 3 lt 7 lw 3

Set terminal png size 960,480

Set grid x y

Set xlabel "Time (sec )"

Set ylabel "Transactions"

Set output "$2"

Plot "$1" using title "PS 5.1.56 buffer pool 512 MM" ls 1 with lines ,\

"$1" using :4 title "PS 5.1.56 buffer pool 1g" ls 2 with lines ,\

"$1" using :6 title "PS 5.1.56 buffer pool 2g" ls 3 with lines axes x1y1

EOP

Possible Errors

Cocould not find/open font when opening font "arial", using internal non-scalable font

 

Export GDFONTPATH =/usr/share/fonts/liberation

Export GNUPLOT_DEFAULT_GDFONT = LiberationSans-Regular

Source ~ /. Bashrc

References

High-performance MySQL 3

This article permanently updates the link address: