DBT-2

DBT-2 is the closest open source implementation of commercial world known TPC-C benchmark.

Compilation is not straight forward (in my opinion) so this small post to describe all steps. Compilation done on Red Hat Enterprise Linux Server release 5.2 (Tikanga).

First download and install Test::Parser::Dbt2, this Perl module displays DBT-2 result. This library need following other Perl libraries:

[root@server1 ~]# rpm -Uvh http://rdbms.st.com/download/iso/Server/perl-XML-Simple-2.14-4.fc6.noarch.rpm http://rdbms.st.com/download/iso/Server/perl-XML-Twig-3.26-1.fc6.noarch.rpm http://rdbms.st.com/download/iso/Server/perl-XML-Parser-2.34-6.1.2.2.1.i386.rpm http://rdbms.st.com/download/iso/Server/perl-URI-1.35-3.noarch.rpm http://rdbms.st.com/download/iso/Server/perl-libwww-perl-5.805-1.1.1.noarch.rpm http://rdbms.st.com/download/iso/Server/perl-Compress-Zlib-1.42-1.fc6.i386.rpm http://rdbms.st.com/download/iso/Server/perl-HTML-Parser-3.55-1.fc6.i386.rpm http://rdbms.st.com/download/iso/Server/perl-HTML-Tagset-3.10-2.1.1.noarch.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-XML-Simple-2.14-4.fc6.noarch.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-XML-Twig-3.26-1.fc6.noarch.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-XML-Parser-2.34-6.1.2.2.1.i386.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-URI-1.35-3.noarch.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-libwww-perl-5.805-1.1.1.noarch.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-Compress-Zlib-1.42-1.fc6.i386.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-HTML-Parser-3.55-1.fc6.i386.rpm
Retrieving http://rdbms.st.com/download/iso/Server/perl-HTML-Tagset-3.10-2.1.1.noarch.rpm
warning: /var/tmp/rpm-xfer.1cVKB0: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing...                ########################################### [100%]
   1:perl-URI               ########################################### [ 13%]
   2:perl-HTML-Tagset       ########################################### [ 25%]
   3:perl-HTML-Parser       ########################################### [ 38%]
   4:perl-Compress-Zlib     ########################################### [ 50%]
   5:perl-libwww-perl       ########################################### [ 63%]
   6:perl-XML-Parser        ########################################### [ 75%]
   7:perl-XML-Simple        ########################################### [ 88%]
   8:perl-XML-Twig          ########################################### [100%]

Then Test::Parser::Dbt2 installation is simple as:

[root@server1 ~]# cd /tmp/Test-Parser-1.9
[root@server1 Test-Parser-1.9]# perl Makefile.PL
[root@server1 Test-Parser-1.9]# make
[root@server1 Test-Parser-1.9]# make test
[root@server1 Test-Parser-1.9]# make install

Then gunzip/untar the DBT-2 source package and issue:

[root@server1 dbt2-0.40]# ./configure --with-mysql=/mysql/software/5.1.49sp1 --with-mysql-includes=/mysql/software/5.1.49sp1/include --with-mysql-libs=/mysql/software/5.1.49sp1/lib --with-mysql-config=/mysql/software/5.1.49sp1/conf

Followed by:

[root@server1 dbt2-0.40]# make
[root@server1 dbt2-0.40]# make install

Generate the set of figures with:

[root@server1 dbt2-0.40]# mkdir /tmp/dbt2-w5
[root@server1 dbt2-0.40]# datagen -w 5 -d /tmp/dbt2-w5 --mysql
warehouses = 5
districts = 10
customers = 3000
items = 100000
orders = 3000
stock = 100000
new_orders = 900
 
Output directory of data files: /tmp/dbt2-w5
 
Generating data files for 5 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 them in dbt2 MySQL database (for example) with (there is an error in documentation and it is NOT mysql_load_db.sh script):

[root@server1 mysql]# ./build_db.sh -d dbt2 -f /tmp/dbt2-w5 -h server1.domainname -u yjaquier -p password
 
Loading of DBT2 dataset located in /tmp/dbt2-w5 to database dbt2.
 
DB_ENGINE:      INNODB
DB_SCHEME:      OPTIMIZED
DB_HOST:        server1.domainname
DB_USER:        yjaquier
DB_SOCKET:      /tmp/mysql.sock
 
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

With five Warehouses this is already quite a lot of figures, see tables/indexes size:

mysql> select TABLE_NAME,ENGINE,DATA_LENGTH,INDEX_LENGTH,DATA_LENGTH from information_schema.tables where TABLE_SCHEMA='dbt2';
+------------+--------+-------------+--------------+-------------+
| TABLE_NAME | ENGINE | DATA_LENGTH | INDEX_LENGTH | DATA_LENGTH |
+------------+--------+-------------+--------------+-------------+
| customer   | InnoDB |   100319232 |     12124160 |   100319232 |
| district   | InnoDB |       16384 |            0 |       16384 |
| history    | InnoDB |    13123584 |            0 |    13123584 |
| item       | InnoDB |    11026432 |            0 |    11026432 |
| new_order  | InnoDB |     2637824 |            0 |     2637824 |
| order_line | InnoDB |   136036352 |            0 |   136036352 |
| orders     | InnoDB |     9977856 |      6832128 |     9977856 |
| stock      | InnoDB |   191627264 |            0 |   191627264 |
| warehouse  | InnoDB |       16384 |            0 |       16384 |
+------------+--------+-------------+--------------+-------------+
9 rows in set (0.09 sec)

Then to display DBT-2 benchmark help issue:

[root@server1 ~]# cd /tmp/dbt2-0.40
[root@server1 dbt2-0.40]# sh scripts/run_workload.sh -h
 
usage: run_workload.sh -c <number of database connections> -d <duration of test> -w <number of warehouses>
other options:
       -d <database name. (default dbt2)>
       -h <database host name. (default localhost)>
       -l <database port number>
       -o <enable oprofile data collection>
       -s <delay of starting of new threads in milliseconds>
       -n <no thinking or keying time (default no)>
       -u <database user>
       -x <database password>
       -z <comments for the test>
 
Example: sh run_workload.sh -c 20 -d 100 -w 1
Test will be run for 120 seconds with 20 database connections and scale factor (num of warehouses) 1

One good test could be:

[root@server1 dbt2-0.40]# sh scripts/run_workload.sh -c 20 -d 60 -n -w 5 -s 10 -u yjaquier -x password

But this fail for:

[root@server1 dbt2-0.40]# cd scripts/output/0
[root@server1 0]# cat client.out
/tmp/dbt2-0.40/src/client: error while loading shared libraries: libmysqlclient.so.16: cannot open shared object file: No such file or directory

Export following environment variable for dynamic libraries et re-issue run_workload.sh:

[root@server1 dbt2-0.40]# export LD_LIBRARY_PATH=/mysql/software/5.1.49sp1/lib

This time it is failing for:

[root@server1 dbt2-0.40]# cd scripts/output/1
[root@server1 1]# cat client.out
User yjaquier Pass password
opening 16 conenction(s) to dbt2...
listening to port 30000
connect to mysql server with parameters: db_name: |dbt2| host: || user: |yjaquier| pass: |password| port: || socket: |/tmp/mysql.sock|
connect to mysql server with parameters: db_name: |dbt2| host: || user: |yjaquier| pass: |password| port: || socket: |/tmp/mysql.sock|
cannot connect to database(see details in error.log file, exiting...

The MySQL socket is not correct but you cannot change it with a parameter (non default install)…

Edit run_workload.sh script and remove hard coded value by replacing:

	CLIENT_COMMAND_ARGS="${CLIENT_COMMAND_ARGS} -d ${DBNAME} -t /tmp/mysql.sock"

With

	CLIENT_COMMAND_ARGS="${CLIENT_COMMAND_ARGS} -d ${DBNAME}"

And around line 223 add (around other database parameters):

DB_SOCKET="/mysql/data01/mysql.sock"

There still multiple error displayed but it is now providing result, you can suppress one with:

export USE_PGPOOL=0

At the end should provide something like:

[root@server1 dbt2-0.40]# sh scripts/run_workload.sh -c 20 -d 60 -n -w 5 -s 10 -u yjaquier -x password
MySQL pid file '/mysql/software/5.1.49sp1/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/tmp/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/3 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************
 
DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: yjaquier
DATABASE PASSWORD: *******
DATABASE SOCKET: /mysql/data01/mysql.sock
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 50
TERMINALS PER WAREHOUSE: 10
SCALE FACTOR(WAREHOUSES): 5
DURATION OF TEST (in sec): 60
1 client stared every 10 millisecond(s)
 
Stage 1. Starting up client...
Sleeping 0 seconds
 
Stage 2. Starting up driver...
10 threads started per millisecond
estimated rampup time: Sleeping 0 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 60 seconds
 
Stage 3. Processing of results...
Killing client...
scripts/run_workload.sh: line 499:  9891 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} > ${OUTPUT_DIR}/driver.out 2>&1
scripts/run_workload.sh: line 462:  9884 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} > ${OUTPUT_DIR}/client.out 2>&1
MySQL pid file '/mysql/software/5.1.49sp1/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
chmod: cannot access `/tmp/dbt2-0.40/scripts/output/3/db/log': No such file or directory
Test completed.
Results are in: /tmp/dbt2-0.40/scripts/output/3
 
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.76      1.087 :     1.693           95                0   0.00
   New Order  45.88      1.016 :     1.515         1159               15   1.31
Order Status   3.84      0.732 :     1.097           97                0   0.00
     Payment  42.56      1.406 :     2.142         1075                0   0.00
 Stock Level   3.96      0.843 :     1.386          100                0   0.00
------------  -----  ---------------------  -----------  ---------------  -----
 
1129.00 new-order transactions per minute (NOTPM)
1.0 minute duration
0 total unknown errors
0 second(s) ramping up

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>