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://rpms.domain.com/perl-XML-Parser-2.34-6.1.2.2.1.i386.rpm http://rpms.domain.com/perl-URI-1.35-3.noarch.rpm http://rpms.domain.com/perl-libwww-perl-5.805-1.1.1.noarch.rpm http://rpms.domain.com/perl-Compress-Zlib-1.42-1.fc6.i386.rpm http://rpms.domain.com/perl-HTML-Parser-3.55-1.fc6.i386.rpm http://rpms.domain.com/perl-HTML-Tagset-3.10-2.1.1.noarch.rpm Retrieving Retrieving Retrieving http://rpms.domain.com/perl-XML-Parser-2.34-6.1.2.2.1.i386.rpm Retrieving http://rpms.domain.com/perl-URI-1.35-3.noarch.rpm Retrieving http://rpms.domain.com/perl-libwww-perl-5.805-1.1.1.noarch.rpm Retrieving http://rpms.domain.com/perl-Compress-Zlib-1.42-1.fc6.i386.rpm Retrieving http://rpms.domain.com/perl-HTML-Parser-3.55-1.fc6.i386.rpm Retrieving http://rpms.domain.com/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 |
About Post Author
