Mysql benchmark with DBT2

Posted on Posted in Mysql

Hi, after install and & Configure Mysql Cluster is the moment to run a benchmark and see how respond Mysql Server. To implement this task I’ll use the dbt2 tool, in the 0.40 version and the dbttools to generate the reports. I use the dbt2 and not the dbt5 because it doesn’t support mysql testing.

Pre-requsites

We will begin the installation. They need to have the following packages installed:

  • build-essentials
  • cmake
  • libmysqlclient-dev
  • python-rpy
  • gnuplot
  • python-rpy2
  • git

And this Perl Modules to generate the reports in a nice file report, install with CPAN

  • Statistics::Descriptive
  • Test::Parser
  • Test::Reporter

Download the software & Install

DBT2

Obtain the software from the official repository, if it not work download from my web (is the same but compressed).

git clone git://osdldbt.git.sourceforge.net/gitroot/osdldbt/dbt2

Install using cmake:

cd dbt2
cmake -DDBMS=mysql
make
make install DESTDIR=/usr/local

 DBTTOOLS

We must also install the dbttools to make the reports,  if it not work download from my web (is the same but compressed).

git clone git://osdldbt.git.sourceforge.net/gitroot/osdldbt/dbttools
cd dbttools
cmake .
make install

Benchmark

Data Generation

Before run the benchmark is necessary generate some data, create the data directory:

mkdir /tmp/dbt2_data

And export your Mysql DATA directory

export DBT2DATADIR=/var/lib/mysql

Generate the data:

dbt2-datagen -w 10 -d /tmp/dbt2_data/ --mysql
warehouses = 10
districts = 10
customers = 3000
items = 100000
orders = 3000
stock = 100000
new_orders = 900

Output directory of data files: /tmp/dbt2_data/

Generating data files for 10 warehouse(s)...
Generating item table data...
Finished item table data...
Generating warehouse table data...
Finished warehouse table data...
Generating stock table data...
Finished stock table data...
Generating district table data...
Finished district table data...
Generating customer table data...
Finished customer table data...
Generating history table data...
Finished history table data...
Generating order and order-line table data...
Finished order and order-line table data...
Generating new-order table data...
Finished new-order table data...

Load Data

Now create the DB estructure and import the data:

dbt2-mysql-build-db -d dbt2 -w 3 -u root -P PASSWORD -e INNODB -s /var/run/mysqld/mysqld.sock -f /tmp/dbt2_data -l

Loading of DBT2 dataset located in /tmp/dbt2_data to database dbt2.

DB_ENGINE:      INNODB
DB_SCHEME:      OPTIMIZED
DB_HOST:        localhost
DB_USER:        root
DB_SOCKET:      /var/run/mysqld/mysqld.sock
DEFAULTS_FILE:  

Creating table STOCK
Creating table ITEM
Creating table ORDER_LINE
Creating table ORDERS
Creating table NEW_ORDER
Creating table HISTORY
Creating table CUSTOMER
Creating table DISTRICT
Creating table WAREHOUSE

Loading table customer
Loading table district
Loading table history
Loading table item
Loading table new_order
Loading table order_line
Loading table orders
Loading table stock
Loading table warehouse

Load Procedures

We load the stored procedures in the dbt2 database, attention the dbt2-mysql-load-stored-procs have a bug the database password and port are the same option, and the mysql connection are wrong.

To resolve this edit /usr/local/bin/dbt2-mysql-load-stored-procs, and remplace this lines (this is the script with the modifications)

  • Line 20
echo ' -t <database port>'

with

echo ' -P <database password>'
  • Line 73
while getopts "t:f:d:s:h:u:p:v" opt; do

with

while getopts "t:f:d:s:h:u:p:v:P:" opt; do
  • Line 93
    p)
            DB_PASSWORD=$OPTARG
            ;;

with

   P)
            DB_PASSWORD=$OPTARG
            ;;
  • Line 129
MYSQL_ARGS="$MYSQL_ARGS $DB_NAME -h $DB_HOST -u $DB_USER ${DB_SOCKET_ARG}"

with

MYSQL_ARGS="-D $DB_NAME -h $DB_HOST -u $DB_USER --password=$DB_PASSWORD ${DB_SOCKET_ARG}"

After this modifications import the procedures into the database dbt2

dbt2-mysql-load-stored-procs -d dbt2 -s /var/run/mysqld/mysqld.sock -u root -P PASSWORD -f /root/dbt2/storedproc/mysql/

Loading of DBT2 SPs located in /root/dbt2/storedproc/mysql/ to database dbt2.

Load SP: delivery
Load SP: new_order
Load SP: new_order_2
Load SP: order_status
Load SP: payment
Load SP: stock_level

 Run the benchmark

Ok now is the moment to run the benchmark, here some options

dbt2-run-workload -h
usage: dbt2-run-workload -h
usage: dbt2-run-workload -a drizzle|mysql|pgsql|sqlite
       -c number of database connections -d seconds
       -w number of warehouses -o path [options]
options:
       -H database host name. (default localhost)
       -l database port number
       -q enable oprofile data collection
       -s delay of starting of new threads in milliseconds
       -n no thinking or keying time (default no)
       -D database name
       -z comments for the test

Example: dbt2-run-workload -c 20 -d 100 -w 1
Test will be run for 120 seconds with 20 database connections and
scale factor (num of warehouses) 1.

We launched the benchmark:

dbt2-run-workload -a mysql -l 3306 -u root -x PASSWORD -D dbt2 -S /var/run/mysqld/mysqld.sock -c 20 -d 30 -n -w 10 -s 10 -o /tmp/dbt2-output
DBT-2 test for mysql started...

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE PORT: 3306
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 100
TERMINALS PER WAREHOUSE: 10
WAREHOUSES PER THREAD/CLIENT PAIR: 10
SCALE FACTOR (WAREHOUSES): 10
DURATION OF TEST (in sec): 30
1 client stared every 10 millisecond(s)

Stage 1. Starting up client...
Sleeping 1 seconds
collecting database statistics...

Stage 2. Starting up driver...
10 threads started per millisecond
estimated rampup time: Sleeping 1 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 30 seconds

Stage 3. Processing of results...
Killing client...
Test completed.
Results are in: /tmp/dbt2-output

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.96     14.853 :    20.141            4                0   0.00
   New Order  43.56     17.542 :    26.447           44                0   0.00
Order Status   2.97     18.596 :    23.097            3                0   0.00
     Payment  42.57     17.576 :    26.892           43                0   0.00
 Stock Level   1.98     16.571 :    23.028            2                0   0.00
------------  -----  ---------------------  -----------  ---------------  -----

91.03 new-order transactions per minute (NOTPM)
0.5 minute duration
0 total unknown errors
0.0 seconds(s) ramping up

Generate Reports

The report results we can see in the /tmp/dbt2-output, but we can generate a index.html file with the result of the test and some graphics.

Before it resolved some bugs in the dbt2-generate-report script (this is the script with the modifications)

vim /usr/local/bin/dbt2-generate-report
  • Line 287
file = '%s/db/readme.txt' % indir

with

file = '%s/readme.txt' % indir
  • Between the lines 293-294 add:
settings = 'db/proc.txt'
system('cp -p %s/db/proc.out %s/%s' % (indir, outdir, settings))

add

settings = 'db/proc.txt'
system('mkdir -p %s/db' % outdir)
system('cp -p %s/db/proc.out %s/%s' % (indir, outdir, settings))
  • Line 296
h.write('<td>%s %s %s</td>\n' % (c1[0], c1[2], c1[11]))

with

h.write('<td>%s %s %s</td>\n' % (c1[0], c1[2], c1[1]))

Ok, now generate the report:

dbt2-generate-report --dbms mysql --i /tmp/dbt2-output --o /tmp/dbt2-output-report
Generating mpstat, iostat, and vmstat charts for driver systems...
Moving on to database system stats...
Generating transaction distribution charts...
Generating mpstat charts...
Generating iostat charts...
Generating vmstat charts...
Generating transaction rate charts...
Generating database statistics charts...

And this is the result

Leave a Reply

Your email address will not be published. Required fields are marked *