Database Test 5 (DBT-5) Documentation

BrokerageHouse

BrokerageHouse is responsible for executing all of the database transactions. It implements the EGenTxnHarness and EGenDriverDM.

MarketExchange

MarketExchange emulates stock exchanges and sends messages to the BrokerageHouse for initial Trade Request and Mark Feed transactions. It implements the EGenDriverMEE.

Driver

Driver emulates customers and drives the workload. It implements EGenDriverCE.

Required Software

In addition to the database management system software, the following is also required:

• C++ compiler

• patch

• Database development libraries and header files

  • PostgreSQL https://www.postgresql.org

    • psql

• SQLite https://www.sqlite.org/index.html is required for processing test results

Optional software:

• sar, pidstat http://pagesperso-orange.fr/sebastien.godard/ (While the scripts assume this particular version of sar and pidstat, it is possible to run on non-Linux based operating systems with some modifications to the kit.)

• jq https://jqlang.github.io/jq/ and toml-cli https://github.com/gnprice/toml-cli are required to use TOML configuration files for executing tests.

Building TPC-E Tools

The kit requires the TPC-E Tools to be built for the specific database management system to be tested. The TPC-E Tools is developed in such a way that it needs to rebuilt or another copy needs to be built in order to test different database management systems at the same time.

DBT-5 provides a script to apply patches and provide code to compile the TPC-E Tools. The patches that are applied are minor code changes and code is supplied to build sponsor supplied code.

The TPC-E Tools source is provided by the egen git submodule, which tracks https://github.com/osdldbt/egen.git. Initialize it when cloning the kit:

git clone --recurse-submodules https://github.com/osdldbt/dbt5.git

Or in an existing clone:

git submodule update --init

When the submodule is initialized, CMake uses it directly. Otherwise CMake's FetchContent feature automatically fetches the TPC-E Tools source from the same repository during the configure step and places it in the build directory under _deps/egen-src.

For example, to build the TPC-E Tools for PostgreSQL (pgsql), copy the source from the submodule to a working directory and run dbt5-build-egen against it (dbt5-build-egen patches and builds the directory in place, so building a copy keeps the submodule checkout clean):

cp -a egen /tmp/egen
dbt5-build-egen --include-dir=src/include --patch-dir=patches \
        --source-dir=src /tmp/egen

Alternatively, the TPC-E Tools can be downloaded directly from the TPC: https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp

To build from the TPC zip distribution, unzip the TPC-E Tools zip file into an empty directory and run dbt5-build-egen against the resulting directory:

mkdir /tmp/egen
cd /tmp/egen
unzip /path/to/*-tpc-e-tool.zip
dbt5-build-egen --include-dir=src/include --patch-dir=patches \
        --source-dir=src /tmp/egen

PostgreSQL

cd storedproc/pgsql/c
make
make install
dbt5 pgsql-load-stored-procs -t c

dbt5 build --tpcetools=/tmp/egen pgsql

dbt5 run -d 120 pgsql /tmp/results

Database Connection Scaling

This test is performed by the test-user-scaling script.

The purpose of this test is determine how many users, or database connections, are needed to get as much throughput as possible out of the system.

This script is run a series of tests starting with 1 user up to the number of detected processors of the driver system, adding 1 user per step in the series. The script is primarily intended to be run in a 1-tier system configuration, but may be run in a 2-tier configuration and may needs some additional intelligence or options for that.

Each step in the series will spend 1 minute to warm up and establish all connections to the database, then run for an additional 5 minutes and stop before starting the next test in the series. The warm up time and test duration can be varied.

At the end of the test, the script will create a bar plot of the reported metric of each test vs. the number of users. The sar data from all the tests will be aggregated and plotted.

Here are some examples plots from a system with 4 logical processors where it spend 1 minute warming up before running another 5 minutes.

Database Parameter Effects

This test is performed by the test-db-parameter script.

The purpose of this test is to evaluate the effects of a changing a single database parameter.

This script must be used with a configuration file and cannot currently be controlled with just command line arguments.

The configure file must contain the following details in addition to any other required configuration parameter (see configuration instructions in User Guide section of the documentation):

1. database start command

2. database stop command

3. database parameter name

4. database parameter values to test

Here is an example for PostgreSQL that is testing 3 difference values for shared_buffers:

db_start_command = "pg_ctl -D /tmp/pgdata start"
db_stop_command = "pg_ctl -D /tmp/pgdata stop -m fast"
db_param_name = "shared_buffers"
db_param_values = ["1GB", "2GB", "3GB"]

At the end of the test, the script will create a bar plot of the reported metric of each test vs. the value of database parameter being evaluated. The sar data from all the tests will be aggregated and plotted, as shown in the Database Parameter Effects section.

Here is an example plot of the above example.

Building the Kit

CMake is build system used for this kit. A Makefile is provided to automate some of the tasks. In-tree builds are not supported; the provided targets configure and build under the build/ directory.

The TPC-E Tools source is provided by the egen git submodule. Clone with --recurse-submodules, or run git submodule update --init in an existing clone, to use the local copy. When the submodule is not initialized, CMake downloads the source at configure time via FetchContent.

Building for debugging:

make debug

Building for release:

make release

Building source packages:

make package

See the AppImage section for details on building an AppImage. There are additional requirements for the appimage target in the Makefile. Alternatively, the kit provides scripts in the tools diretory to create a container that can create an AppImage.

Sandbox

A compose.yml and additional files in .compose are provided to help create a test environment to test and debug the binaries by running a minimal workload, as oppose to the entire kit with statistics collection. It currently works with docker and will hopefully work with podman at some point.

This environment should also be suitable to test database loading (e.g. EGenLoader) and the transaction testing tool (TestTxn).

Restarting a container rebuilds the kit for the container to help assist testing the current state of the code in the repository.

Here are a brief list of commands with docker to help get started. Please refer to the respective Compose documentation for more information. These commands must be executed from the top of the source directory.

The compose environment will use the smallest, but invalid, parameters possible in the interest of being to test things quickly. Please refer to the benchmark specification for further explanation of these parameters. These can be adjusted with environment variables:

• CUSTOMERS - The total number of customer, must be a multiple of 1000.

• DAYS - Initial trade days.

• SCALEFACTOR - The number of customers per trade results transactions per second.

• USERS - The number of users to emulate.

Provision and deploy the environment:

docker compose up -d

Stop the containers:

docker compose stop

Remove the containers:

docker compose down

Follow the container system logs:

docker compose logs -f database

Build the database (only needs to be once, or each time the database container is recreated):

docker compose run load

Run the workload and specify the test duration in seconds (results are saved in the results subdirectory):

docker compose run driver -d 120

docker compose exec -u postgres database psql dbt5

Regression Tests

The tests/ directory contains regression tests that are run by CTest and written with shunit2, so shunit2 must be installed to run them. Configuring a build tree registers the tests with CTest, and the test target in the Makefile configures the release build tree and runs them:

make test

The tests can also be run from an already configured build tree:

cd build/release && ctest --output-on-failure

CTest only shows a test's output when the test fails. Use ctest -V to always show the output, including shunit2 reporting each test function as it runs. A test script can also be run directly, which shows its output as it happens; when the kit is not installed, the test must be told where to find dbt5-build-egen:

DBT5_BUILD_EGEN=build/release/dbt5-build-egen \
        sh tests/test_egen_chunking

The egen_chunking test builds the TPC-E Tools in a temporary directory, then generates flat data files once with a single EGenLoader instance covering every customer and again with multiple instances each covering a subrange of customers, and verifies that both sets of data files contain the same data. This is the property that makes parallel data generation and loading safe. The test requires the egen submodule to be initialized and is skipped otherwise.

The test parameters default to the minimum valid values defined by the benchmark specification: 5000 customers, a scale factor of 500, and 300 initial trade days. These defaults generate two spec-minimum data sets of tens of gigabytes each and may take over an hour. The parameters can be adjusted with environment variables for smaller, non-conformant runs while developing:

ITD=10 make test

• CHUNK - The number of customers generated per EGenLoader instance, must be a multiple of 1000.

• ITD - Initial trade days.

• SF - The number of customers per trade result transactions per second.

• TOTAL - The total number of customers, must be a multiple of 1000.

The pgsql_transactions test runs each TPC-E transaction through TestTxn against a database built from deterministic EGen data, once with the server side database backend and once with the client side backend, using the same random number generator seeds, and verifies that both produce the same frame output. Transactions that modify the database run each backend against a fresh copy of the database so both start from the same state. The test creates a throwaway PostgreSQL instance in the shunit2 temporary directory, listening only on localhost on a random port, and removes it afterward; it requires the PostgreSQL server programs, located with pg_config --bindir, and is skipped otherwise. For example:

ITD=3 ctest -R pgsql_transactions

The database defaults to 1000 customers, the smallest EGenLoader accepts. The TPC-E specification does not define database content below 5000 customers, so unusual transaction results are possible at that size; set TOTAL=5000 SF=500 ITD=300 for a database at the specification minimum. TOTAL, SF, and ITD adjust sizing as above, SEEDS selects the random number generator seeds, and FIXTUREDB names the database the test creates and drops.

The pgsql_storedprocs_plpgsql and pgsql_storedprocs_c tests validate one stored function implementation each: every transaction runs through TestTxn with the server side backend, which calls that implementation, and with the client side backend, which builds the SQL itself and uses no stored functions, and the frame output must match. A failure therefore names the implementation under test. The C stored functions are built with PGXS from a copy of the source and loaded by absolute path in place of the pl/pgsql functions, so nothing is installed into the PostgreSQL installation directories. In addition to the pgsql_transactions test requirements, pgsql_storedprocs_c needs the PostgreSQL server development files.

AppImage

AppImages are only for Linux based systems: https://appimage.org/

The AppImageKit AppImage can be downloaded from: https://github.com/AppImage/AppImageKit/releases

It is recommended to build AppImages on older distributions: https://docs.appimage.org/introduction/concepts.html#build-on-old-systems-run-on-newer-systems

The logo used is the number "5" from the Freeware Metal On Metal Font.

See the README.rst in the tools/ directory for an example of creating an AppImage with a Podman container.

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

tools/build-appimage-container
EGEN=tpc-e-tool.zip tools/build-appimage