Database Test 2 (DBT-2) Documentation

Payment

• In the case where the selected customer has bad credit, the original c_data is not appended to the new c_data updated for the customer. (Clause 2.5.2.2) [SAP DB]

Delivery

• Delivery transactions are not queued and deferred, but are executed immediately. (Clause 2.7.2.1)

• Statistics are not collected for the case when deliveries are not made for a district. (Clause 2.7.2.3)

Database

The database consists of nine tables with supportint five transactions. While this test kit is currently primarily maintained for PostgreSQL, it can be adapted to any other database. The data represents a company that is a wholesale supplier with a number of distributed sales districts and associated warehouses covering a wide geographic range. The database can be scaled to any number of warehouses to simulate businesses of varying sizes. By default, a warehouse covers 10 districts, each district serving 3,000 customers, with each warehouse maintaining stock for a complete inventory of 100,000 items. DBT-2 allows the rest of the database to be scaled as defined by the user. The five transactions supported are: New-Order, Payment, Order-Status, Delivery, and Stock-Level.

Remote Terminal Emulators

A remote terminal emulator (RTE) simulates the activities of a person at a terminal console executing one of the five transactions supported by the database. The RTE is designed to either connect to a client system in order to access the database in a three-tier model. The RTE is also designed so that it can be controlled by an external process. The external process is a monitoring program that manages drivers across multiple systems.

The RTE is designed as a multi-threaded program where each thread of activity represents a single terminal accessing the database. Ten terminals are simulated for every warehouse that the database is configured for. Each terminal records every interaction attempted and the response time from the point where the request is sent to when the response has been received.

Clients

The clients are terminal concentrators that allows more than one terminal to share a connection to the database system. The client program starts up a listener to handle terminal requests and a pool of threads to process transaction requests. A new thread is created for each terminal connecting to the client to.

External Software Dependencies

SQLite3 is required to do basic statistical calculations in order to report test metrics.

System and database statistic collection and post processing are handled by Touchstone Tool. Install this package, or its AppImage, in order to characterize system performance. This package is included in the DBT-2 AppImage.

A test report can be generated with the aid of DBT Tools. This package is included in the DBT-2 AppImage.

gnuplot is used when generating charts. This package is also included in the DBT-2 AppImage.

jq and toml-cli are required to use TOML configuration files for executing tests.

Linux AppImage

A Linux AppImage is available, intended for simplified use, albeit with reduced functionality, on Linux based systems. See Database Management System Notes for specific usage and for any database management system specific limitations. The DBT-2 AppImage may not always be as capable as installing the kit from source or from other pre-packaged distributions.

The DBT-2 AppImage must be installed on each system. It is also recommended to install the Touchstone Tools AppImage on each system when there are multiple systems in use.

For ease of use, we recommend renaming the DBT-2 AppImage binary to dbt2, if it hasn't already been done. Examples in the documentation will assume it has been renamed to dbt2.

If FUSE is not available, the AppImage is self-extracting and provides a script AppRun that can be executed as if running the AppImage itself:

dbt2-*.AppImage --extract-appimage
squashfs-root/AppRun

Alternatively, one could also set PATH and LD_LIBRARTY_PATH manually to include the extracted environment.

DBT-2 Test Kit Source

The primary source repository of the kit can be found on GitHub at: https://github.com/osdldbt/dbt2

Multi-tier System Testing

DBT-2 supports a 1-, 2-, and 3-tier client-server system configurations. Whether the binaries are run by hand, or whether the supplied helper scripts are used determine how much additional environmental setup is needed.

When using the dbt2-run script, which also supports helping execute a 1-, 2-, and 3-tier system configuration tests, additional environment variables may need to be set because a remote ssh environment may be more limited than a normal login environment. For example, if the scripts and binaries are installed in /usr/local/bin, this path may not be in the path when using ssh to execute commands locally.

Also password-less ssh keys needs to be set up to keep the scripts simple, the kit needs to be installed in the same location on each system. Each user must also have sudo privileges, also without requiring a password.

The ssh server on each system may need to allow user environment processing. To enable this, make the following change to the ssh server configuration file (typically /etc/ssh/sshd_config) and restart the sshd daemon (see specific instructions from the operating system in use):

PermitUserEnvironment yes

Some database specific examples are provided in the examples/ directory. Also see Database Management System Notes for more specific details per database management system, if available.

Sizing Consideration

The database can be built with as few as 1 warehouse. A 1,000 warehouse generates about 100 GiB of raw text data. The size of the database ultimately depends on the schema definition and the number of indexes built.

Not all of the warehouses need to be used. For example, if the database is built with 100 warehouses, 80 can be specified. Specifying more than available warehouses will results errors. The test will continue to run, but will log errors if a warehouse referenced does not exist.

If DBT-2 is used with standard transaction mix ratios, keying, and thinking times, etc. then the maximum throughput is limited to 12.86 x the number of warehouses. Thus if you want more throughput, the database size also needs to be increased. See Clause 4.1 in the TPC-C specification for a complete explanation.

If non-standard ratios, times, etc. are used, the database should be resized to match the measured throughput. This could take multiple revisions if database is tested to be undersized compared to the measured throughput.

Of course if you have any reasons to test outside of these recommendations, you should feel free to do so.

The "Easy" Way

There are many ways that this kit can be used. What easy means here is that many of the decisions are made for you:

1. Use the DBT-2 AppImage because it is packaged with database management system client libraries and post processing analytical packages, thus minimizing system software setup.

2. Use a system where the database is already running and you already created a database, because these steps don't help with database installation or configuration.

3. Use the event-driven multi-process driver, which opens 1 database connection per processor on the system by default and minimizes the number of tiers used for testing.

4. Do not use any keying or thinking time, thus letting the system be driven as hard as possible depending on the number of available processors on the system.

The number of warehouses and the length of the test can still be specified.

The Database Management System Notes section may have additional database management system specific notes.

The examples in this section assume that the DBT-2 AppImage has been renamed to dbt2 and is in the user's PATH.

dbt2 build pgsql
dbt2 run pgsql /tmp/results

Manual Test Execution

This method involves starting each of the components manually without any help from any of the scripts.

The database needs to be manually started.

The next step is to start the client. The command line parameters depends on the database management system tested so please review the help (-h) and Database Management System Notes for details. Here is an example for starting the client with 10 connections opened to PostgreSQL:

dbt2-client -a pgsql -d db.hostname -b dbt2 -c 10 -o .

The client will log errors, as well as its processor ID (pid) into the current directory, as specified by the -o . parameter.

The output from the client should look something like:

setting locale: en_US.utf8
listening to port 30000
opening 10 connection(s) to localhost...
listening to port 30000
10 DB worker threads have started
client has started

The next step is to start the driver. To get sane results from a 1 warehouse database, we should run the driver for at least 4 minutes (240 seconds):

dbt2-driver -d client.hostname -w 1 -l 240 -outdir .

The driver will log error and results, as well as its process ID (pid) into the current directory.

The output from the driver should look something like:

setting locale: en_US.utf8
connecting to client at 'db.hostname'

database table cardinalities:
warehouses = 1
districts = 10
customers = 3000
items = 100000
orders = 3000
stock = 100000
new-orders = 900

 transaction  mix threshold keying thinking
new order    0.45      0.45     18    12000
payment      0.43      0.88      3    12000
order status 0.04      0.92      2    10000
delivery     0.04      0.96      2     5000
stock level  0.04      1.00      2     5000

w_id range 0 to 0
10 terminals per warehouse
240 second steady state duration

driver is starting to ramp up at time 1675394297
driver will ramp up in  10 seconds
will stop test at time 1675394307
seed for 212536:7f9eca271700 : 10962933948494954280
seed for 212536:7f9eca234700 : 6320917737120767790
seed for 212536:7f9eca213700 : 6590945454066933208
seed for 212536:7f9eca1f2700 : 1675724396147333855
seed for 212536:7f9eca1d1700 : 15221135594039080856
seed for 212536:7f9eca1b0700 : 11698084064519635828
seed for 212536:7f9eca18f700 : 12013746617097863687
seed for 212536:7f9eca16e700 : 1937451735529826674
seed for 212536:7f9eca14d700 : 10201147048873733402
seed for 212536:7f9eca12c700 : 11758382826843355753
terminals started...
driver is exiting normally

The last step is to process the test data to see what the results are:

dbt2-post-process mix.log

The resulting output should look something like:

============  =====  =========  =========  ===========  ===========  =====
          ..     ..    Response Time (s)            ..           ..     ..
------------  -----  --------------------  -----------  -----------  -----
 Transaction      %   Average     90th %        Total    Rollbacks      %
============  =====  =========  =========  ===========  ===========  =====
    Delivery   3.81      0.000      0.000            4            0   0.00
   New Order  47.62      0.001      0.001           50            1   2.00
Order Status   5.71      0.001      0.001            6            0   0.00
     Payment  40.00      0.004      0.001           42            0   0.00
 Stock Level   2.86      0.000      0.000            3            0   0.00
============  =====  =========  =========  ===========  ===========  =====

* Throughput: 12.99 new-order transactions per minute (NOTPM)
* Duration: 3.9 minute(s)
* Unknown Errors: 0
* Ramp Up Time: 0.1 minute(s)

Congratulations, you've run a test!

Comprehensive Test Execution

The dbt2-run is a wrapper script that will attempt to collect system statistics and database statistics, as well as start all components of the test. It can optionally profile a Linux system with readprofile, oprofile, or perf. See Database Management System Notes for any database management system specific notes as there may be additional system specific flags.

The shell script dbt2-run is used to execute a test. For example, run a 4 minutes (480 second) test against a default sized 1 warehouse database locally and save the results to /tmp/results:

dbt2 run -d 480 pgsql /tmp/results

See the help output from dbt2 run --help a brief description of all options.

This script will also process the results and output the same information as if you were running dbt2 report manually like the last section's example. Additional, the dbt2 report --html command is for building an HTML report based on all of the data that is saved to /tmp/results by running:

dbt2 report --html /tmp/results

The HTML report uses Docutils. gnuplot is also required to generate any charts. This will create an index.html file in the <directory>.

An example of the HTML report is available online: https://osdldbt.github.io/dbt-reports/dbt2/3-tier/report.html

Multi-process driver execution

Default behavior for the driver is to create 10 threads per warehouse under a single process. At some point (depends on hardware and resource limitations) the driver, specifically dbt2-driver as a multi-threaded progress, will become a bottleneck. We can increase the load by starting multiple multi-threaded drivers. The -b # flag can be passed to the dbt2 run command to specify how many warehouses to be created per process. The script will calculate how many driver processes to start.

Keying and Thinking Time

The driver is supposed to emulate the thinking time of a person as well as the time a person takes to enter information into the terminal. This introduces a limit on the rate of transaction that can be executed by the database.

Each of the DBT-2 drivers allows the tester to specify different delays for each transaction's keying and thinking time. The most common scenario is not factor in any time for keying or thinking. For example:

-ktd 0 -ktn 0 -kto 0 -ktp 0 -kts 0 -ttd 0 -ttn 0 -tto 0 -ttp 0 -tts 0

See the help from the driver binaries to see which flag controls which transaction's thinking and keying times if you want to varying the delays differently.

The dbt2 run script sets each of the thinking and keying time flags to 0 by default and does not offer any finer grained controls at this time.

Transaction Mix

The transaction mix can be altered with the driver using the following flags, where the percentages are represented as a decimal number:

The percentage for the New Order transaction is the difference after the other 4 transactions such that the sum adds to 1 (i.e. 100%.)

Complex Test Configurations

The run script can use a TOML formatted configuration file to execute the workload in more complex configuration than what the command line arguments can provide. Note that running the binaries by hand still offer the most flexibility. For example:

• Using multiple client programs across multiple systems

• Using multiple driver programs across multiple systems

• Specifying the client system and port per driver

• Specifying the database system to use per client (for distributed database systems)

• Specifying the warehouse range per driver

See the following subsections for specific scenarios.

mode = 1
database_name = "dbt2"
warehouses = 1
duration = 120

[[client]]
client_addr = "localhost"
database_addr = "localhost"
connections = 1

[[driver]]
driver_addr = "localhost"
client_addr = "localhost"

mode = 1
database_name = "dbt2"
warehouses = 2
duration = 120

[[client]]
client_addr = "localhost"
database_addr = "localhost"
connections = 1

[[client]]
client_addr = "localhost"
database_addr = "localhost"
connections = 1
client_port = 30001

[[driver]]
driver_addr = "localhost"
client_addr = "localhost"
wmin = 1
wmax = 1

[[driver]]
driver_addr = "localhost"
client_addr = "localhost"
wmin = 2
wmax = 2
client_port = 30001

mode = 1
database_name = "dbt2"
warehouses = 1
duration = 120

[[client]]
client_addr = "sodium"
database_addr = "lithium"
connections = 1

[[driver]]
driver_addr = "localhost"
client_addr = "sodium"

mode = 1
database_name = "dbt2"
warehouses = 2
duration = 120

[[client]]
client_addr = "sodium"
database_addr = "lithium"
connections = 1

[[client]]
client_addr = "sodium"
database_addr = "lithium"
connections = 1
client_port = 30001

[[driver]]
driver_addr = "localhost"
client_addr = "sodium"
wmin = 1
wmax = 1

[[driver]]
driver_addr = "localhost"
client_addr = "sodium"
wmin = 2
wmax = 2
client_port = 30001

mode = 3
database_name = "dbt2"
warehouses = 1
duration = 120

[[driver]]
driver_addr = "localhost"
database_addr = "localhost"

mode = 3
database_name = "dbt2"
warehouses = 2
duration = 120

[[driver]]
driver_addr = "localhost"
database_addr = "localhost"
wmin = 1
wmax = 1

[[driver]]
driver_addr = "localhost"
database_addr = "localhost"
wmin = 2
wmax = 2

mode = 3
database_name = "dbt2"
warehouses = 1
duration = 120
[[driver]]
driver_addr = "localhost"
database_addr = "lithium"

mode = 3
database_name = "dbt2"
warehouses = 2
duration = 120

[[driver]]
driver_addr = "localhost"
database_addr = "lithium"
wmin = 1
wmax = 1

[[driver]]
driver_addr = "localhost"
database_addr = "lithium"
wmin = 2
wmax = 2

COMMENTS

There are two versions of DBT2(TPC-C) test:

• pure C based version of the test(nonSP)

• server side SP based version of the test(default)

It is possible to run C based(nonSP) test with any version of MySQL server.

To run test you have to build test with includes and libraries from MySQL 5.0 or higher.

See examples/mysql/mysql-dbt2.cnf for example MySQL defaults file.

MULTI-TIER TESTING

• Enable 'PermitUserEnvironment' in sshd_config.

• Set DBT2DATADIR in the database system user's '.ssh/environment' file:

  DBT2DATADIR=/tmp/mydbt2

• Enalbe tcp/ip listening in the defaults file (see example from above). This binds to all network interfaces:

  bind-address = 0.0.0.0

• The build scripts automatically grants remote access to DB_USER (default root) for everything from any ip address. If there are other users that require access, the grant will have to be done manually:

  grant all on . to ${DB_USER}@'%';

PREPARATION FOR TEST

0. Build test binaries

NOTE:

If you want to compile with MySQL support, you must either make sure that path to 'mysql_config' is listed in your PATH environment variable.

cmake CMakeLists.txt
make
make install

1. How to generate data files for test?

   One has to specify:

   -w - number of warehouses (example: -w 3)
   -d - output path for data files (example: -d /tmp/dbt2-w3)
   - mode (example: --mysql)

   datagen -w 3 -d /tmp/dbt2-w3 --mysql

   Please note that output directory for data file should exist.

2. How to load test database?

   You should run shell script which will create database scheme and will load all data files.

 dbt2-mysql-build-db -d dbt2 -f /tmp/dbt2-w3 -v -w 2 -s /tmp/mydbt2.sock

usage: dbt2-mysql-build-db [options]
options::

    -d <database name>
    -f <path to dataset files>
    -m <database scheme [OPTIMIZED|ORIG] (default scheme OPTIMIZED)>
    -c <mysql.cnf defaults file>
    -s <database socket>
    -h <database host>
    -u <database user>
    -p <database password>
    -e <storage engine: [MYISAM|INNODB|BDB]. (default INNODB)>
    -l <to use LOCAL keyword while loading dataset>
    -v <verbose output>

Example: dbt2-mysql-build-db -d dbt2 -f /tmp/dbt2-w3 -s /tmp/mysql.sock -p ~/src/dbt2/examples/mysql/mysql-dbt2.cnf

3. How to load SP procedures?

   dbt2-mysql-load-stored-procs

   usage: dbt2-mysql-load-sp [options] options:

   -d <database name>
   -f <path to SPs>
   -h <database host (default: localhost)>
   -s <database socket>
   -u <database user>
   -p <database password>
   -t <database port>

   Example: dbt2-mysql-load-stored-procs -d dbt2 -f ~/src/dbt2/storedproc/mysql/ -s /tmp/mysql.sock

RUN TEST

dbt2-run

usage: dbt2-run -a mysql -c <number of database connections> -t <duration of test> -w <number of warehouses> other options:

-D <database name. (default dbt2)>
-h <database host name. (default localhost)>
-l <database port number. (default 3306)>
-S <database socket>
-u <database user>
-x <database password>
-s <delay of starting of new thread in milliseconds(default 300ms)>
-t <terminals per warehouse. [1..10] (default 10)>
-z <comments for the test>
-n <use zero delays for test (default no)>
-v <verbose output>
-o <output dir>
-p <mysql defaults file

Example: dbt2-run -a mysql -D dbt2 -c 20 -t 300 -w 3 -o results -S /tmp/mysql.sock -p ~/src/dbt2/examples/mysql/mysql-dbt2.cnf

Test will be run for 300 seconds with 20 database connections and scale factor(num of warehouses) 3:

-c number of database connections
-d duration of test in seconds
-w number of warehouses (scale factor)

WARNING: If you break test (by Control-C for instance) or some kind of error happened during running of test and you want to start test again please be sure that 'client' and 'driver' programms are not running anymore otherwise test will fail.

WARNING: Please ensure that number of warehouses (option -w) is less of equal (not greater) to the real number of warehouses that exist in your test database.

POSTRUNNING ANALYSES

Results can be found in bin/output/<number>

some of the usefull log files:

  results/client/${HOSTNAME}.*/error.log - errors from backend C|SP based
  results/driver/${HOSTNAME}.*/error.log - errors from terminals(driver)
  results/mix.log - info about performed transactions
r  esults/report.txt - results of the test

Setup

The DBT-2 test kit has been ported to work with PostgreSQL starting with version 7.3. It has be updated to work with later version and backwards compatibility may vary. Source code for PostgreSQL can be obtained from their website at: https://www.postgresql.org/

To install PostgreSQL from source:

./configure --prefix=<installation dir>
make
make install

Prior to PostgreSQL 8.0 this additional make command is required to ensure the server include files get installed:

make install-all-headers

After installing PostgreSQL, the DBT-2 C stored functions need to be compiled and installed, if they are to be used instead of pl/pgsql stored functions or other client side transaction logic:

cd storedproc/pgsql/c
make
make install

The make install command will need to be run by the owner of the database installation.

When testing PostgreSQL in a multi-tier system configuration, verify that the database has been configuration to accept TCP/IP connections. For example, the listen_addresses parameter must be set to listen for connections on the appropriate interfaces. Remote connections must also be allowed in the pg_hba.conf file. The simplest (and insecure) way would be to trust all connections on all interfaces.

Also prior to PostgreSQL 8.0, pg_autovacuum should be installed. If installing from source, it is located in the contrib/pg_autovacuum directory.

The following subsections have additional PostgreSQL version specific notes.

A really quick howto

Edit examples/dbt2_profile and follow the notes for the DBT2PGDATA directory. DBT2PGDATA is where the database directory will be created.

Create a 1 warehouse database by running:

dbt2 build pgsql

Run a 5 minute (300 second) test by running:

dbt2 run -d 300 pgsql /tmp/result

Building the Database

The dbt2-pgsql-build-db script is designed to handle the following steps:

1. create the database

2. create the tables

3. generate and load the tables

4. create the indexes

5. vacuum the database

The dbt2-pgsql-build-db script is also designed to generate data in parallel based on the number of processors detected on the system, and will stream the data into the database. The script currently do not let you control the degree of parallelism, or whether the data should be created as files first, but these can be controlled manually by running the dbt2-datagen binary directly.

The other significant choices available are:

• -r drop the existing database first

• -s <c | plpgsql> use C or pl/pgsql stored functions, where plpgsql is the default

• -t use tablespaces for tables and indexes

• -u the executing user has privileges to restart the database system, and drop and create a database

See the usage output with the -h for the complete list of options.

The following command will create a default sized 1 warehouse database:

dbt2 build pgsql

Environment Configuration

The DBT-2 scripts required environment variables to be set in order to work properly (e.g. examples/dbt2_profile) in order for the scripts to work properly. For example:

DBT2PORT=5432; export DBT2PORT
DBT2DBNAME=dbt2; export DBT2DBNAME
DBT2PGDATA=/tmp/pgdata; export DBT2PGDATA

An optional environment variable can be set to specify a different location for the transaction logs (i.e. pg_xlog or pg_wal) when the dbt2-pgsql-init-db script:

DBT2XLOGDIR=/tmp/pgxlogdbt2; export DBT2XLOGDIR

The environment variables may need to be defined in ~/.ssh/environment file on each system for multi-tier environment for ssh. The ssh daemon may need to be configured to enable the use of user environment files. Make sure PATH is set to include the location where the DBT-2 executables and PostgreSQL binaries are installed, if not in the default PATH. For example:

DBT2PORT=5432
DBT2DBNAME=dbt2
DBT2PGDATA=/tmp/pgdata
PATH=/usr/local/bin:/usr/bin:/bin:/opt/bin

Tablespace Notes

The scripts assumes a specific tablespace layout for keeping the scripts simple.

The ${DBT2TSDIR} environment variable defines the directory where all tablespace devices will be mounted. Directories or symlinks can be substituted for what is assumed to be a mount point from this point forward.

dbt2-pgsql-create-tables and dbt2-pgsql-create-indexes are where the tablespaces are created.

The expected mount points or symlinks, which must also be writeable by the database owner, need to be at:

${DBT2TSDIR}/warehouse
${DBT2TSDIR}/district
${DBT2TSDIR}/customer
${DBT2TSDIR}/history
${DBT2TSDIR}/new_order
${DBT2TSDIR}/orders
${DBT2TSDIR}/order_line
${DBT2TSDIR}/item
${DBT2TSDIR}/stock
${DBT2TSDIR}/index1
${DBT2TSDIR}/index2
${DBT2TSDIR}/pk_customer
${DBT2TSDIR}/pk_district
${DBT2TSDIR}/pk_item
${DBT2TSDIR}/pk_new_order
${DBT2TSDIR}/pk_order_line
${DBT2TSDIR}/pk_orders
${DBT2TSDIR}/pk_stock
${DBT2TSDIR}/pk_warehouse

AppImage Notes

datagen

Tests are provided to verify that partitioning does not generate different data than if the data was not partitioned. There are some data that is generated with the time stamp of when the data is created, so those columns are ignored when comparing data since they are not likely to be the same time stamps.

post-process

A test is provided to make sure that the post-process output continues to work with multiple mix files as well as with various statistical analysis packages.

Building the AppImage

The AppImages builds a custom minimally configured PostgreSQL build to reduce library dependency requirements. Part of this reason is to make it easier to include libraries with compatible licences. At least version PostgreSQL 11 should be used for the pg_type_d.h header file.

At the time of this document, PostgreSQL 11 was configured with the following options:

./configure --without-ldap --without-readline --without-zlib \
      --without-gssapi --with-openssl

Don't forget that both PATH and LD_LIBRARY_PATH may need to be set appropriately depending on where the custom build of PostgreSQL is installed.