Manually Running the DBT-3 Sample Performance Tests
To set up the DBT-3 tests manually, follow the instructions below to configure the data generator, and then use the commands listed below to run the tests.
Generate Test Data
2. Unpack the download
tar xzvf dbt3-1.9.tar.gz
3. Change directory to the src directory and compile the data generator (dbgen). Then test the generation of data.
cd dbt3-1.9/src/dbgen
make
./dbgen
The data files are generated in the current directory with extension tbl (for example, customer.tbl). In addition to the files themselves, the generator also creates symbolic links in /tmp that point to each data file. If you need to generate the files in a different location, copy dbgen and dists.dss to that location and run from there.
To generate larger data volumes, include the –s switch (for example,
dbgen ‑s 10 will create 10 GB of data). If disk space is limited then the files can be generated in sections (using ‑s) or can be done for one table at a time (using ‑T). For more information on these and other command line switches, run
dbgen ‑h or visit the web (for example, at
https://github.com/electrum/tpch-dbgen).
Create Tables
The CREATE TABLE statements for the eight tables are shown in
CREATE TABLE Statements. Run these statements against the database. Adjust the number of partitions according to the max_query_parallelism setting of your environment (see
Partitioning Tables).
Load Test Data
To load the test data use commands similar to these (adjust for the name of the database and the location of the file as required).
vwload -m -t customer -f "|"-uactian pocdb /tmp/customer.tbl
vwload -m -t lineitem -f "|"-uactian pocdb /tmp/lineitem.tbl
vwload -m -t nation -f "|"-uactian pocdb /tmp/nation.tbl
vwload -m -t orders -f "|"-uactian pocdb /tmp/orders.tbl
vwload -m -t partsupp -f "|"-uactian pocdb /tmp/partsupp.tbl
vwload -m -t part -f "|"-uactian pocdb /tmp/part.tbl
vwload -m -t region -f "|"-uactian pocdb /tmp/region.tbl
vwload -m -t supplier -f "|"-uactian pocdb /tmp/supplier.tbl
Create Ordered Tables
The statements for creating ordered versions of the tables are shown in
Create Ordered Table Statements. Run these statements against the database. The ordered version is meant to represent a “real-world” ordering of the data (for example, new customers being added with an increasing customer key value).
Create Statistics
To create statistics on the table run the commands below (commands are shown for both the base tables and the ordered tables).
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rcustomer
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rcustomer2
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rlineitem
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rlineitem2
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rnation
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rnation2
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rorders
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rorders2
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rpartsupp
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rpartsupp2
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rpart
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rpart2
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rregion
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rregion2
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rsupplier
optimizedb -zns -zfq -zr1000 -zu1000 pocdb -rsupplier2
Alternatively, you could add the ‑z switch to the vwload commands (to generate statistics as part of the load) or use the CREATE STATISTICS command.
Run Queries
The set of queries to run against the data is shown in
Test Queries.
You may want to create script files for these; such scripts exist in the DBT-3 Github test pack.
For example, if the contents of file q1.sql are:
-- SQL Script for query 1
\rt -- turn on query timing
select . . . [ rest of query ]
\p -- print query to screen
\g -- execute query
then this could be run by issuing:
sql -uactian -Pactian pocdb < q1.sql