tpcc-mysql: Simple usage steps and how to build graphs with gnuplot

lots of times we could see different benchmarks performed by tpcc-mysql . so today i want to tell you about how to use tpcc-mysql and how to build graphs with gnuplot in a few easy steps.

as an example i’ll compare percona server 5.5 (latest version: 5.5.31) performance by changing innodb buffer pool size: innodb_buffer_pool_size = 256m / innodb_buffer_pool_size = 768m on my old test machine

system info

• cpu: intel(r) pentium(r) 4 cpu 1.80ghz
• memtotal: 1543732 kb
• os: linuxmint 15 (based on ubuntu 13.04)

files
you can find the source code of all files at the end of this post

installation

• install latest percona server
• install tpcc-mysql

sudo apt-get install bzr
bzr branch lp:~percona-dev/perconatools/tpcc-mysql
make all

in this case it’s installed to ~/tpcc-mysql/ directory

• install gnuplot

 sudo apt-get install gnuplot

db config
first test will be running with innodb_buffer_pool_size = 256m option enabled and second one with innodb_buffer_pool_size = 768m

test for innodb_buffer_pool_size = 256m

create db
assuming that percona server 5.5.31 installed and configured

cd ~/tpcc-mysql
mysql -u root -p -e "create database tpcc1000;"
mysql -u root -p tpcc1000 < create_table.sql
mysql -u root -p tpcc1000 < add_fkey_idx.sql

load data

 ./tpcc_load 127.0.0.1 tpcc1000 root "root-password" 20

where:

• host: 127.0.0.1
• db: tpcc1000
• user: root
• password: root-password
• warehouse: 20

  ...data loading completed successfully.

in this case db size is 1.9gb

run tpcc-mysql test

./tpcc_start -h127.0.0.1 -dtpcc1000 -uroot -p -w20 -c16 -r10 -l1200 > ~/tpcc-output-ps-55-bpool-256.log

where:

• host: 127.0.0.1
• db: tpcc1000
• user: root
• warehouse: 20
• connection: 16
• rampup time: 10 (sec)
• measure: 1200 (sec)

the most interesting part in the output is:

measuring start.

10, 25(17):9.005|9.221, 21(0):1.866|1.869, 3(0):0.647|0.840, 1(0):0.000|10.614, 2(2):19.999|29.490
20, 22(14):9.419|9.555, 26(0):1.591|1.593, 2(0):0.593|0.788, 4(0):10.453|10.688, 3(3):19.999|22.962
30, 41(32):8.703|9.057, 32(0):1.615|1.662, 3(0):0.588|0.777, 2(0):9.530|10.495, 3(2):19.999|22.983

the first two values are “time range” and “transactions”, so you can read it as:

0-10 sec, 25 transactions
10-20 sec, 22 transactions
20-30 sec, 41 transactions

test for innodb_buffer_pool_size = 768m

repeat following steps for innodb_buffer_pool_size = 768m (change it in my.cnf) and get results:

• db config
• create db
• load data
• run tpcc-mysql test

./tpcc_start -h127.0.0.1 -dtpcc1000 -uroot -p -w20 -c16 -r10 -l1200 > ~/tpcc-output-ps-55-bpool-768.log

there are 2 files: tpcc-output-ps-55-bpool-256.log and tpcc-output-ps-55-bpool-768.log which have benchmarking results for both tests.

generate data file for each test

./tpcc-output-analyze.sh ~/tpcc-output-ps-55-bpool-256.log > tpcc-256-data.txt
./tpcc-output-analyze.sh ~/tpcc-output-ps-55-bpool-768.log > tpcc-768-data.txt

merge data files

 paste tpcc-256-data.txt tpcc-768-data.txt > tpcc-graph-data.txt

build graph

 ./tpcc-graph-build.sh tpcc-graph-data.txt tpcc-graph.jpg

in this case tpcc-graph-data.txt is a filename of source datafile and tpcc-graph.jpg filename of graph which will be generated

graph ready: tpcc-graph.jpg

note: “using 3:4 … with lines axes x1y1″ in tpcc-graph-build.sh means that columns number 3 and 4 in datafile will be used for as axises x and y accordingly while building second line

file listing

tpcc-output-analyze.sh (i got it there and a bit modified)

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 ‘ 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”,$1,(aggr/’$timeslot’)) ; cntr=0; aggr=0 } } ‘

tpcc-graph-build.sh

#!/bin/bash

### goto user homedir and remove previous file
rm -f ‘$2′

gnuplot << eop

### set data source file
datafile = ‘$1′

### set graph type and size
set terminal jpeg size 640,480

### set titles
set grid x y
set xlabel “time (sec)”
set ylabel “transactions”

### set output filename
set output ‘$2′

### build graph
# plot datafile with lines
plot datafile title “ps 5.5.1, buffer pool: 256m” with lines, \
datafile using 3:4 title “ps 5.5.1, buffer pool: 768m” with lines axes x1y1

eop