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

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

PREVIOUS POST
NEXT POST

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

PREVIOUS POST
NEXT POST

Share this post

Comments (11)

  • Andrew Moore Reply

    Great article Michael. Thank you for sharing.

    July 1, 2013 at 4:50 pm
  • wula Reply

    There is a little problem in analyze file.

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

    should be changed to:

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

    July 2, 2013 at 5:46 am
  • Michael Rikmas Reply

    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?

    July 2, 2013 at 9:31 am
  • wula Reply

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

    July 3, 2013 at 4:12 am
  • Michael Rikmas Reply

    Sure, not problem 😉
    Thanks for your interest!

    July 3, 2013 at 4:46 am
  • Stefan Lasiewski Reply

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

    October 4, 2013 at 6:38 pm
  • Michael Rikmas Reply

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

    October 8, 2013 at 3:33 am
  • Paul Reply

    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?

    July 11, 2015 at 3:27 pm
  • Adam Reply

    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.

    November 28, 2016 at 3:24 am
    • Vadim Tkachenko Reply

      Adam,

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

      November 28, 2016 at 3:30 pm
  • Sebastian Reply

    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

    January 4, 2017 at 4:52 am

Leave a Reply