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


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

tpcc-graph

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 %3dn”,$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

12 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Andrew Moore

Great article Michael. Thank you for sharing.

0
wula

There is a little problem in analyze file.

printf (“%d =
”,$1,(aggr/’$TIMESLOT’)) ;

should be changed to:

printf (“%d =
”,$0,(aggr/’$TIMESLOT’)) ;

0
Michael Rikmas

In case of awk with field separator (FS) $1 means a first field of input record so this is exactly what I was looking for and it works just like needed. Is that not a true?

0
wula

Oh, you were right, yesterday I had a careless mistake in the test. 🙁

0
Michael Rikmas

Sure, not problem 😉
Thanks for your interest!

0
Stefan Lasiewski

Will tpcc-mysql be available in your Yum Repository sometime soon?

0
Michael Rikmas

Stefan, I’m not sure about these kind of plans, but I’ll check that with our team.

0
Paul

Having difficult loading data with tpcc_load…

~/tpcc-mysql> ./tpcc_load localhost tpcc1000 sqlroot cuntwhack41 20 | head -n20
*************************************
*** ###easy### TPC-C Data Loader ***
*************************************

[server]: localhost
[port]: 3306
[DBname]: tpcc1000
[user]: root
[pass]: ***********
[warehouse]: 20
TPCC Data Load Started…
Loading Item

1210, HY000, Incorrect arguments to mysqld_stmt_execute
Retrying …

1210, HY000, Incorrect arguments to mysqld_stmt_execute
Retrying …

Anyone know what the problem is here please?

0
Adam

Is it possible to close the foreign key checking?
I want to use this tool to test my sharding cluster, which does not support foreign key.

0
Vadim Tkachenko

Adam,

The tool does not perform any actual checking, you can use it as is.

0
Sebastian

Hello,
I couldn’t find a documentation of the output of tpcc-mysql anywhere 🙁
Can somebody explain the output?
For example:
10, trx: 12920, 95%: 9.483, 99%: 18.738, max_rt: 213.169, 12919|98.778, 1292|101.096, 1293|443.955, 1293|670.842
20, trx: 12666, 95%: 7.074, 99%: 15.578, max_rt: 53.733, 12668|50.420, 1267|35.846, 1266|58.292, 1267|37.421
30, trx: 13269, 95%: 6.806, 99%: 13.126, max_rt: 41.425, 13267|27.968, 1327|32.242, 1327|40.529, 1327|29.580
40, trx: 12721, 95%: 7.265, 99%: 15.223, max_rt: 60.368, 12721|42.837, 1271|34.567, 1272|64.284, 1272|22.947
50, trx: 12573, 95%: 7.185, 99%: 14.624, max_rt: 48.607, 12573|45.345, 1258|41.104, 1258|54.022, 1257|26.626

Thanks
Sebastian

0
Bojan

Having some difficulties to build. When I do “make all”, I got this:

cc load.o support.o mysql_config --libs_r -lrt -o ../tpcc_load
load.o: In function main':
/home/bojan/tpcc-mysql/src/load.c:125: undefined reference to parse_host’
/home/bojan/tpcc-mysql/src/load.c:126: undefined reference to `parse_port’
collect2: error: ld returned 1 exit status
Makefile:27: recipe for target ‘../tpcc_load’ failed
make: *** [../tpcc_load] Error 1

Am I doing somethin wrong here?

0