DBT3 Benchmarks

To setup DBT3 for execution requires a few preparatory steps.

• Create table data files

• Create tables and load data

• Run benchmark

Create DBT3 data files

The benchmark needs to load the DBT3 database, this consists of 9 tables that needs to be filled with data. When loading the data one can choose which scale factor to use. The command below creates table data files for the 9 tables with scale factor 10 (requires about 24 GByte of DataMemory). A scale factor of 10 adds about 10 GByte of user data, but there is also overhead of quite a lot of indexes on in particular the lineitem table.

The -v means that you get verbose output, -f means that you will overwrite any already existing table data files and finally -s 10 provides the scale factor use.

mkdir /home/mysql/benchmarks/dbt3_data/10
cd /home/mysql/benchmarks/dbt3_data/10
cp /srv/hops/mysql/bin/dbt3/dists.dss .
/srv/hops/mysql/bin/dbt3/dbgen -vf -s 10

The dbgen program requires a file called dists.dss located in the directory it runs in. It contains some commands to describe generation of column data. This file is included in the RonDB 21.04.3 releases and newer RonDB releases. Thus to use this description you need to have at least RonDB 21.04.3 installed. If you have an older version of RonDB this file is found in the dbt3-1.10 tarball in the src/dbgen directory.

Create and load DBT3 tables

Now to load the database we will use the benchmark scripts in a similar fashion as for Sysbench and DBT2. In order to do this we need to create a new benchmark directory for DBT3 benchmark. Thus we perform the below commands to create the directory dbt3_multi.

mkdir /home/mysql/benchmarks/dbt3_multi

Now we need to create a autobench.conf file. The below file contains the important information, you need to copy the SERVER_HOST information from e.g. the sysbench_multi benchmark configuration file. Similarly with the MYSQL_PASSWORD.

#############################
#### Software definition ####
#############################

MYSQL_BIN_INSTALL_DIR="/srv/hops/mysql"
BENCHMARK_TO_RUN="dbt3"

#########################################
#### Storage definition (empty here) ####
#########################################

#################################
#### MySQL Server definition ####
#################################

SERVER_HOST="172.31.23.248;172.31.31.222"
MYSQL_PASSWORD='3*=13*820.*07?=45'

###########################################
#### NDB node definitions (empty here) ####
###########################################

##############################
#### Benchmark definition ####
##############################

DBT3_DATA_PATH="/home/mysql/benchmarks/dbt3_data/10"

Now after placing the above content into the file /home/mysql/benchmarks/dbt3_multi/autobench.conf we are ready to execute the command that will create the DBT3 tables and load them with the data.

cd /home/mysql/benchmarks
bench_run.sh --default-directory /home/mysql/benchmarks/dbt3_multi

Run DBT3 benchmark

Now we are ready to run the benchmark, it is possible to execute this benchmark in an automated manner. Personally I more or less consider the 22 queries in DBT3 as each a separate benchmark. Thus I often run only 1 query at a time using the MySQL client. The benchmark tarball contains a directory queries/mysql where all 22 queries have a separate file. Actually each query has 2 files, one for the automated query execution using qgen which is called e.g. 11.sql. The other file is called e.g. 11_2.sql and contains an example of the Q11 that can be immediately executed by using cut and paste into a MySQL client.

As an example we find the file 11.sql as:

-- @(#)11.sql   2.1.8.1
-- TPC-H/TPC-R Important Stock Identification Query (Q11)
-- Functional Query Definition
-- Approved February 1998
:b
:x
:o
select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        partsupp,
        supplier,
        nation
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = ':1'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * :2
                        from
                                partsupp,
                                supplier,
                                nation
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and n_name = ':1'
                )
order by
        value desc;
:e

In order to run this manually one need to replace the :1 and the :2 with e.g. GERMANY and 0.0001. Thus we get the query found in the file 11_2.sql:

select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        partsupp,
        supplier,
        nation
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * 0.0001
                        from
                                partsupp,
                                supplier,
                                nation
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and n_name = 'GERMANY'
                )
order by
        value desc;

This query can easily be cut and paste into the MySQL client. The MySQL client will report the time it takes to execute the query which is what one wants to know here.

By using EXPLAIN on the query one can quickly check if the queries use a query plan that is reasonable.

Using qgen to generate queries

It is possible the qgen program to automatically generate queries. So far we have been satisfied with the manual execution of DBT3.