Benchmark: TPC-H
TPC-H does allow scaling data generation and ingestion, and scaling the benchmarking driver. Scale-out can simulate distributed clients for the loading test and the throughput test [2].
This example shows how to benchmark 22 reading queries Q1-Q22 derived from TPC-H in PostgreSQL.
The query file is derived from the TPC-H and as such is not comparable to published TPC-H results, as the query file results do not comply with the TPC-H Specification.
Official TPC-H benchmark - http://www.tpc.org/tpch
A Cloud-Native Adoption of Classical DBMS Performance Benchmarks and Tools: https://doi.org/10.1007/978-3-031-68031-1_9
The results are not official benchmark results. Exact performance depends on a number of parameters. You may get different results. These examples are solely to illustrate how to use bexhoma and show the result evaluation.
For a comparison of multiple DBMS (PostgreSQL, MonetDB, MySQL, MariaDB) see TestCases.md.
Perform Benchmark - Power Test
You will have to change the node selectors there (to names of nodes, that exist in your cluster - or to leave out the corresponding parameters):
BEXHOMA_NODE_SUT="cl-worker11"
BEXHOMA_NODE_LOAD="cl-worker19"
BEXHOMA_NODE_BENCHMARK="cl-worker19"
LOG_DIR="./logs_tests"
BEXHOMA_MS=1
BEXHOMA_STORAGE_CLASS="shared"
mkdir -p $LOG_DIR
For performing the experiment we can run the tpch file.
Example:
bexhoma tpch \
-dbms PostgreSQL \
-sf 1 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-ms $BEXHOMA_MS \
-tr \
-lr 64Gi \
-rr 64Gi \
-rss 50Gi \
-rnn $BEXHOMA_NODE_SUT -rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
--set deployment[bexhoma-deployment-postgres].container[dbms].random_page_cost=1.1 \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_io_concurrency=200 \
--set deployment[bexhoma-deployment-postgres].container[dbms].io_method=io_uring \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers_per_gather=2 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers=4 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_worker_processes=6 \
--set deployment[bexhoma-deployment-postgres].container[dbms].shared_buffers=20GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_cache_size=48GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].work_mem=1GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].maintenance_work_mem=2GB \
run &>$LOG_DIR/docs_tpch_postgresql.log
This
starts a clean instance of PostgreSQL
fixed 64 Gi RAM (request
-rrand limit-lr)data directory inside a Docker container
creates TPC-H schema in the database
starts 8 loader pods (
-nlp)with a data generator (init) container each
generating a portion of TPC-H data of scaling factor 1 (
-sf)storing the data in a distributed filesystem (shared disk)
if data is already present: do nothing
with a loading container each
importing TPC-H data from the distributed filesystem
loading uses 8 threads (
-nlt)
creates constraints (
-xic) and indexes (-xii) and updates table statistics (-xis) after ingestionruns 1 stream of TPC-H queries
data transfer is also measured (
-xdt)
shows a summary
Status
You can watch the status while benchmark is running via bexhoma status
Dashboard: Running
Cluster Prometheus: Running
Message Queue: Running
Data directory: Running
Result directory: Running
+------------------+--------------+--------------+---------------+
| 1706255897 | sut | loaded [s] | loading |
+==================+==============+==============+===============+
| MonetDB-BHT-8 | (1. Running) | 253.23 | |
+------------------+--------------+--------------+---------------+
| MySQL-BHT-8-8 | (1. Running) | 0.61 | (8 Succeeded) |
+------------------+--------------+--------------+---------------+
| PostgreSQL-BHT-8 | (1. Running) | 219.08 | |
+------------------+--------------+--------------+---------------+
The code 1706255897 is the unique identifier of the experiment.
You can find the number also in the output of tpch.py.
Cleanup
The script is supposed to clean up and remove everything from the cluster that is related to the experiment after finishing.
If something goes wrong, you can also clean up manually with bexperiment stop (removes everything) or bexperiment stop -e 1706255897 (removes everything that is related to experiment 1706255897).
Evaluate Results
At the end of a benchmark you will see a summary like
Show docs_tpch_postgresql.log
## Show Summary
### Workload
TPC-H Queries SF=1
* Type: tpch
* Duration: 477s
* Code: 1785150915
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=1) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 600.
* Data transfer volume per query is also measured.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.8.
* Experiment is limited to DBMS ['PostgreSQL'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* SUT is fixed to cl-worker36.
* Database uses ephemeral storage of size 50Gi.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1] times the number of benchmarking pods.
* Experiment is run once.
* Maximum DBMS across the whole cluster is 10.
* Results are validated against basic correctness requirements.
* Deployment parameter overrides: [({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'random_page_cost'}, '1.1'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_io_concurrency'}, '200'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'io_method'}, 'io_uring'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers_per_gather'}, '2'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers'}, '4'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_worker_processes'}, '6'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'shared_buffers'}, '20GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_cache_size'}, '48GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'work_mem'}, '1GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'maintenance_work_mem'}, '2GB')].
* SUT requests 4 CPU and 64Gi RAM. RAM limit is 64Gi.
### Connections
* PostgreSQL-1-1-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:825668
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785150915
### SUT Container Restarts
* bexhoma-sut-postgresql-1-1785150915-7f8d669476-zwrv4: 0 0
### Workflow
#### Actual
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
#### Planned
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:---------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| PostgreSQL-1-1 | 1 | 1 | 150.00 | 1.00 | 17.00 | 7.00 | 120.00 | 8 | 0 | | | 0 | False | 24.00 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:---------------------|:----------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:---------------------|
| PostgreSQL-1-1-1-1-1 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 13 | 0.27 | 14684.21 | 6092.31 | 0 | PostgreSQL-1-1-1-1-1 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:-----------------|:-----------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| PostgreSQL-1-1-1 | PostgreSQL-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 13 | 0.27 | 14684.21 | 6092.31 | 0 |
### Latency of Timer Execution [ms]
| Queries | PostgreSQL-1-1-1-1-1 |
|:----------------------------------------------------|-----------------------:|
| Pricing Summary Report (TPC-H Q1) | 1276.58 |
| Minimum Cost Supplier Query (TPC-H Q2) | 164.40 |
| Shipping Priority (TPC-H Q3) | 347.43 |
| Order Priority Checking Query (TPC-H Q4) | 100.28 |
| Local Supplier Volume (TPC-H Q5) | 157.69 |
| Forecasting Revenue Change (TPC-H Q6) | 228.64 |
| Volume Shipping Query (TPC-H Q7) | 242.36 |
| National Market Share (TPC-H Q8) | 134.24 |
| Product Type Profit Measure (TPC-H Q9) | 423.78 |
| Returned Item Reporting Query (TPC-H Q10) | 227.91 |
| Important Stock Identification (TPC-H Q11) | 63.39 |
| Shipping Modes and Order Priority (TPC-H Q12) | 341.14 |
| Customer Distribution (TPC-H Q13) | 717.00 |
| Promotion Effect Query (TPC-H Q14) | 248.36 |
| Top Supplier Query (TPC-H Q15) | 249.78 |
| Parts/Supplier Relationship (TPC-H Q16) | 218.76 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 769.07 |
| Large Volume Customer (TPC-H Q18) | 2140.91 |
| Discounted Revenue (TPC-H Q19) | 43.90 |
| Potential Part Promotion (TPC-H Q20) | 106.74 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 385.66 |
| Global Sales Opportunity Query (TPC-H Q22) | 71.29 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
This gives a survey about the errors and warnings (result set mismatch) and the latencies of execution per query. Moreover the loading times (schema creation, ingestion and indexing), the geometric mean of query execution times and the TPC-H metrics power and throughput are reported. Please note that the results are not suitable for being published as official TPC-H results. In particular the refresh streams are missing.
To see the summary again you can simply call bexhoma summary -e 1759316196 with the experiment code.
Detailed Evaluation
Results are transformed into pandas DataFrames and can be inspected in more detail. Detailed evaluations can be done using DBMSBenchmarker
You can connect to an evaluation server in the cluster by bexhoma dashboard.
This forwards ports, so you have
a DBMSBenchmarker dashboard in browser at http://localhost:8050
a Jupyter notebook server at http://localhost:8888 containing the example notebooks
You can connect to a local evaluation server by bexhoma localdashboard.
This forwards ports, so you have
a DBMSBenchmarker dashboard in browser at http://localhost:8050
You can connect to a local jupyter server by bexhoma jupyter.
This forwards ports, so you have
a Jupyter notebook server at http://localhost:8888 containing the example notebooks
Adjust Parameters
The script supports
exact repetitions for statistical confidence
variations to scan a large parameters space
combine results for easy evaluation
There are various ways to change parameters.
Manifests
The YAML manifests for the components can be found in https://github.com/Beuth-Erdelt/Benchmark-Experiment-Host-Manager/tree/master/k8s
SQL Scrips
The SQL scripts for pre and post ingestion can be found in https://github.com/Beuth-Erdelt/Benchmark-Experiment-Host-Manager/tree/master/experiments/tpch
Dockerfiles
The Dockerfiles for the components can be found in https://github.com/Beuth-Erdelt/Benchmark-Experiment-Host-Manager/tree/master/images/tpch
Command line
You maybe want to adjust some of the parameters that are set in the file: python tpch.py -h
usage: tpch.py [-h] [-aws] [-dbms {PostgreSQL,MonetDB,MySQL,MariaDB}] [-xlit LIMIT_IMPORT_TABLE] [-db] [-cx CONTEXT] [-e EXPERIMENT] [-m] [-mc] [-ms MAX_SUT] [-xdt] [-xqr NUM_RUN] [-nc NUM_CONFIG]
[-ne NUM_QUERY_EXECUTORS] [-xnls NUM_LOADING_SPLIT] [-nlp NUM_LOADING_PODS] [-nlt NUM_LOADING_THREADS] [-nbp NUM_BENCHMARKING_PODS] [-nbt NUM_BENCHMARKING_THREADS] [-sf SCALING_FACTOR]
[-t TIMEOUT] [-rr REQUEST_RAM] [-rc REQUEST_CPU] [-rct REQUEST_CPU_TYPE] [-rg REQUEST_GPU] [-rgt REQUEST_GPU_TYPE] [-rst {None,,local-hdd,shared}] [-rss REQUEST_STORAGE_SIZE]
[-rnn REQUEST_NODE_NAME] [-rnl REQUEST_NODE_LOADING] [-rnb REQUEST_NODE_BENCHMARKING] [-tr] [-xii] [-xic] [-xis] [-xrcp] [-xshq]
{profiling,run,start,load,empty,summary}
Performs a TPC-H experiment. Data is generated and imported into a DBMS from a distributed filesystem (shared disk).
positional arguments:
{profiling,run,start,load,empty,summary}
profile the import or run the TPC-H queries
options:
-h, --help show this help message and exit
-aws, --aws fix components to node groups at AWS
-dbms {PostgreSQL,MonetDB,MySQL,MariaDB}, --dbms {PostgreSQL,MonetDB,MySQL,MariaDB}
DBMS
-xlit LIMIT_IMPORT_TABLE, --limit-import-table LIMIT_IMPORT_TABLE
limit import to one table, name of this table
-db, --debug dump debug informations
-cx CONTEXT, --context CONTEXT
context of Kubernetes (for a multi cluster environment), default is current context
-e EXPERIMENT, --experiment EXPERIMENT
sets experiment code for continuing started experiment
-m, --monitoring activates monitoring
-mc, --monitoring-cluster
activates monitoring for all nodes of cluster
-ms MAX_SUT, --max-sut MAX_SUT
maximum number of parallel DBMS configurations, default is no limit
-xdt, --datatransfer activates transfer of data per query (not only execution)
-xqr NUM_RUN, --num-run NUM_RUN
number of runs per query
-nc NUM_CONFIG, --num-config NUM_CONFIG
number of runs per configuration
-ne NUM_QUERY_EXECUTORS, --num-query-executors NUM_QUERY_EXECUTORS
comma separated list of number of parallel clients
-xnls NUM_LOADING_SPLIT, --num-loading-split NUM_LOADING_SPLIT
portion of loaders that should run in parallel
-nlp NUM_LOADING_PODS, --num-loading-pods NUM_LOADING_PODS
total number of loaders per configuration
-nlt NUM_LOADING_THREADS, --num-loading-threads NUM_LOADING_THREADS
total number of threads per loading process
-nbp NUM_BENCHMARKING_PODS, --num-benchmarking-pods NUM_BENCHMARKING_PODS
comma separated list of number of benchmarkers per configuration
-nbt NUM_BENCHMARKING_THREADS, --num-benchmarking-threads NUM_BENCHMARKING_THREADS
total number of threads per benchmarking process
-sf SCALING_FACTOR, --scaling-factor SCALING_FACTOR
scaling factor (SF)
-t TIMEOUT, --timeout TIMEOUT
timeout for a run of a query
-rr REQUEST_RAM, --request-ram REQUEST_RAM
request ram for sut, default 16Gi
-rc REQUEST_CPU, --request-cpu REQUEST_CPU
request cpus for sut, default 4
-rct REQUEST_CPU_TYPE, --request-cpu-type REQUEST_CPU_TYPE
request node for sut to have node label cpu=
-rg REQUEST_GPU, --request-gpu REQUEST_GPU
request number of gpus for sut
-rgt REQUEST_GPU_TYPE, --request-gpu-type REQUEST_GPU_TYPE
request node for sut to have node label gpu=
-rst {None,,local-hdd,shared}, --request-storage-type {None,,local-hdd,shared}
request persistent storage of certain type
-rss REQUEST_STORAGE_SIZE, --request-storage-size REQUEST_STORAGE_SIZE
request persistent storage of certain size
-rnn REQUEST_NODE_NAME, --request-node-name REQUEST_NODE_NAME
request a specific node for sut
-rnl REQUEST_NODE_LOADING, --request-node-loading REQUEST_NODE_LOADING
request a specific node for loading pods
-rnb REQUEST_NODE_BENCHMARKING, --request-node-benchmarking REQUEST_NODE_BENCHMARKING
request a specific node for benchmarking pods
-tr, --test-result test if result fulfills some basic requirements
-xii, --init-indexes adds indexes to tables after ingestion
-xic, --init-constraints
adds constraints to tables after ingestion
-xis, --init-statistics
recomputes statistics of tables after ingestion
-xrcp, --recreate-parameter
recreate parameter for randomized queries
-xshq, --shuffle-queries
have different orderings per stream
Monitoring
Monitoring can be activated for DBMS only (-m) or for all components (-mc).
Example:
bexhoma tpch \
-dbms PostgreSQL \
-sf 10 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-m \
-mc \
-ms $BEXHOMA_MS \
-tr \
-lr 64Gi \
-rr 64Gi \
-rss 150Gi \
-rnn $BEXHOMA_NODE_SUT -rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
--set deployment[bexhoma-deployment-postgres].container[dbms].random_page_cost=1.1 \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_io_concurrency=200 \
--set deployment[bexhoma-deployment-postgres].container[dbms].io_method=io_uring \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers_per_gather=2 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers=4 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_worker_processes=6 \
--set deployment[bexhoma-deployment-postgres].container[dbms].shared_buffers=20GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_cache_size=48GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].work_mem=1GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].maintenance_work_mem=2GB \
run &>$LOG_DIR/docs_tpch_postgresql_monitoring.log
If monitoring is activated, the summary also contains a section like this:
Show docs_tpch_postgresql_monitoring.log
## Show Summary
### Workload
TPC-H Queries SF=10
* Type: tpch
* Duration: 874s
* Code: 1785151412
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=10) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 600.
* Data transfer volume per query is also measured.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.8.
* System metrics are monitored by a cluster-wide installation.
* Experiment is limited to DBMS ['PostgreSQL'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* SUT is fixed to cl-worker36.
* Database uses ephemeral storage of size 150Gi.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1] times the number of benchmarking pods.
* Experiment is run once.
* Maximum DBMS across the whole cluster is 10.
* Results are validated against basic correctness requirements.
* Deployment parameter overrides: [({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'random_page_cost'}, '1.1'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_io_concurrency'}, '200'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'io_method'}, 'io_uring'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers_per_gather'}, '2'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers'}, '4'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_worker_processes'}, '6'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'shared_buffers'}, '20GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_cache_size'}, '48GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'work_mem'}, '1GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'maintenance_work_mem'}, '2GB')].
* SUT requests 4 CPU and 64Gi RAM. RAM limit is 64Gi.
### Connections
* PostgreSQL-1-1-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:863123
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785151412
### SUT Container Restarts
* bexhoma-sut-postgresql-1-1785151412-85b4c48b6f-5tj4f: 0 0
### Workflow
#### Actual
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
#### Planned
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:---------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| PostgreSQL-1-1 | 1 | 10 | 507.00 | 0.00 | 17.00 | 84.00 | 400.00 | 8 | 0 | | | 0 | False | 71.01 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:---------------------|:----------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:---------------------|
| PostgreSQL-1-1-1-1-1 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 10.00 | 22 | 113 | 3.22 | 11553.46 | 7008.85 | 0 | PostgreSQL-1-1-1-1-1 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:-----------------|:-----------------|-----------------:|---------:|----------------:|------------:|------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| PostgreSQL-1-1-1 | PostgreSQL-1-1-1 | 1 | 1 | 1 | 1 | 10.00 | 22 | 113 | 3.22 | 11553.46 | 7008.85 | 0 |
### Latency of Timer Execution [ms]
| Queries | PostgreSQL-1-1-1-1-1 |
|:----------------------------------------------------|-----------------------:|
| Pricing Summary Report (TPC-H Q1) | 12436.24 |
| Minimum Cost Supplier Query (TPC-H Q2) | 2428.09 |
| Shipping Priority (TPC-H Q3) | 3941.93 |
| Order Priority Checking Query (TPC-H Q4) | 2418.59 |
| Local Supplier Volume (TPC-H Q5) | 3306.51 |
| Forecasting Revenue Change (TPC-H Q6) | 2305.62 |
| Volume Shipping Query (TPC-H Q7) | 3509.67 |
| National Market Share (TPC-H Q8) | 1926.13 |
| Product Type Profit Measure (TPC-H Q9) | 7924.42 |
| Returned Item Reporting Query (TPC-H Q10) | 7485.85 |
| Important Stock Identification (TPC-H Q11) | 720.86 |
| Shipping Modes and Order Priority (TPC-H Q12) | 3296.56 |
| Customer Distribution (TPC-H Q13) | 6603.95 |
| Promotion Effect Query (TPC-H Q14) | 2474.04 |
| Top Supplier Query (TPC-H Q15) | 2651.91 |
| Parts/Supplier Relationship (TPC-H Q16) | 1686.47 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 6080.24 |
| Large Volume Customer (TPC-H Q18) | 25368.63 |
| Discounted Revenue (TPC-H Q19) | 351.78 |
| Potential Part Promotion (TPC-H Q20) | 3843.30 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 4476.51 |
| Global Sales Opportunity Query (TPC-H Q22) | 506.58 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Monitoring
### Loading phase: SUT deployment
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:-------------------|-------------:|----------:|---------------:|----------------------:|
| PostgreSQL-1-1-1-1 | 491.96 | 3.30 | 5.98 | 20.96 |
### Loading phase: component data generator
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:-------------------|-------------:|----------:|---------------:|----------------------:|
| PostgreSQL-1-1-1-1 | 0.00 | 0.00 | 0.00 | 0.00 |
### Loading phase: component loader
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:-------------------|-------------:|----------:|---------------:|----------------------:|
| PostgreSQL-1-1-1-1 | 114.63 | 2.38 | 0.00 | 1.13 |
### Execution phase: SUT deployment
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:-------------------|-------------:|----------:|---------------:|----------------------:|
| PostgreSQL-1-1-1-1 | 212.58 | 3.02 | 21.03 | 36.68 |
### Execution phase: component benchmarker
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:-------------------|-------------:|----------:|---------------:|----------------------:|
| PostgreSQL-1-1-1-1 | 18.45 | 0.15 | 0.34 | 0.35 |
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Loading phase: SUT deployment contains no 0 or NaN in CPU [CPUs]
* TEST skipped: Loading phase: component data generator contains 0 or NaN in CPU [CPUs] (data pre-existing)
* TEST passed: Loading phase: component loader contains no 0 or NaN in CPU [CPUs]
* TEST passed: Execution phase: SUT deployment contains no 0 or NaN in CPU [CPUs]
* TEST passed: Execution phase: component benchmarker contains no 0 or NaN in CPU [CPUs]
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
This gives a survey about CPU (in CPU seconds) and RAM usage (in Gb) during loading and execution of the benchmark. PostgreSQL is fast, so we cannot see a lot (metrics are fetched every 30 seconds).
Perform Benchmark - Throughput Test
For performing the experiment we can run the tpch file.
Example:
bexhoma tpch \
-dbms PostgreSQL \
-sf 1 \
-nc 1 \
-ne 1,2 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-ms $BEXHOMA_MS \
-tr \
-lr 64Gi \
-rr 64Gi \
-rss 50Gi \
-rnn $BEXHOMA_NODE_SUT -rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
--set deployment[bexhoma-deployment-postgres].container[dbms].random_page_cost=1.1 \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_io_concurrency=200 \
--set deployment[bexhoma-deployment-postgres].container[dbms].io_method=io_uring \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers_per_gather=2 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers=4 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_worker_processes=6 \
--set deployment[bexhoma-deployment-postgres].container[dbms].shared_buffers=20GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_cache_size=48GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].work_mem=1GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].maintenance_work_mem=2GB \
run &>$LOG_DIR/docs_tpch_postgresql_throughput.log
This runs 3 streams (-ne), the first one as a single stream and the following 2 in parallel.
Show docs_tpch_postgresql_throughput.log
## Show Summary
### Workload
TPC-H Queries SF=1
* Type: tpch
* Duration: 569s
* Code: 1785152329
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=1) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 600.
* Data transfer volume per query is also measured.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.8.
* Experiment is limited to DBMS ['PostgreSQL'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* SUT is fixed to cl-worker36.
* Database uses ephemeral storage of size 50Gi.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1, 2] times the number of benchmarking pods.
* Experiment is run once.
* Maximum DBMS across the whole cluster is 10.
* Results are validated against basic correctness requirements.
* Deployment parameter overrides: [({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'random_page_cost'}, '1.1'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_io_concurrency'}, '200'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'io_method'}, 'io_uring'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers_per_gather'}, '2'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers'}, '4'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_worker_processes'}, '6'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'shared_buffers'}, '20GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_cache_size'}, '48GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'work_mem'}, '1GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'maintenance_work_mem'}, '2GB')].
* SUT requests 4 CPU and 64Gi RAM. RAM limit is 64Gi.
### Connections
* PostgreSQL-1-1-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:827643
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785152329
* PostgreSQL-1-1-2-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:832196
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785152329
* PostgreSQL-1-1-2-1-2 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:832196
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785152329
### SUT Container Restarts
* bexhoma-sut-postgresql-1-1785152329-8545885c86-ffltt: 0 0
### Workflow
#### Actual
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS PostgreSQL-1 - Experiment 1 Client 2: tpch (2 pods)
#### Planned
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS PostgreSQL-1 - Experiment 1 Client 2: tpch (2 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:---------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| PostgreSQL-1-1 | 1 | 1 | 153.00 | 1.00 | 17.00 | 8.00 | 121.00 | 8 | 0 | | | 0 | False | 23.53 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:---------------------|:----------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:---------------------|
| PostgreSQL-1-1-1-1-1 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 18 | 0.34 | 11489.23 | 4400.00 | 0 | PostgreSQL-1-1-1-1-1 |
| PostgreSQL-1-1-2-1-1 | PostgreSQL-1 | PostgreSQL-1-1-2 | PostgreSQL-1-1-2-1 | 1 | 2 | 1 | 1 | 1.00 | 22 | 18 | 0.30 | 13712.11 | 4400.00 | 0 | PostgreSQL-1-1-2-1-1 |
| PostgreSQL-1-1-2-1-2 | PostgreSQL-1 | PostgreSQL-1-1-2 | PostgreSQL-1-1-2-1 | 1 | 2 | 1 | 1 | 1.00 | 22 | 15 | 0.30 | 13479.98 | 5280.00 | 0 | PostgreSQL-1-1-2-1-2 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:-----------------|:-----------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| PostgreSQL-1-1-1 | PostgreSQL-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 18 | 0.34 | 11489.23 | 4400.00 | 0 |
| PostgreSQL-1-1-2 | PostgreSQL-1-1-2 | 1 | 2 | 1 | 2 | 1.00 | 44 | 18 | 0.30 | 13595.55 | 8800.00 | 0 |
### Latency of Timer Execution [ms]
| Queries | PostgreSQL-1-1-1-1-1 | PostgreSQL-1-1-2-1-1 | PostgreSQL-1-1-2-1-2 |
|:----------------------------------------------------|-----------------------:|-----------------------:|-----------------------:|
| Pricing Summary Report (TPC-H Q1) | 1591.15 | 1473.38 | 1465.58 |
| Minimum Cost Supplier Query (TPC-H Q2) | 215.58 | 176.34 | 205.11 |
| Shipping Priority (TPC-H Q3) | 508.94 | 357.91 | 386.26 |
| Order Priority Checking Query (TPC-H Q4) | 141.58 | 110.97 | 109.27 |
| Local Supplier Volume (TPC-H Q5) | 211.10 | 176.39 | 173.91 |
| Forecasting Revenue Change (TPC-H Q6) | 233.31 | 210.39 | 260.21 |
| Volume Shipping Query (TPC-H Q7) | 257.28 | 220.87 | 259.58 |
| National Market Share (TPC-H Q8) | 146.04 | 104.56 | 133.62 |
| Product Type Profit Measure (TPC-H Q9) | 551.27 | 626.50 | 476.84 |
| Returned Item Reporting Query (TPC-H Q10) | 262.81 | 231.53 | 218.30 |
| Important Stock Identification (TPC-H Q11) | 80.87 | 62.82 | 61.70 |
| Shipping Modes and Order Priority (TPC-H Q12) | 502.08 | 332.67 | 326.67 |
| Customer Distribution (TPC-H Q13) | 701.55 | 767.13 | 718.64 |
| Promotion Effect Query (TPC-H Q14) | 1702.51 | 1248.78 | 1239.42 |
| Top Supplier Query (TPC-H Q15) | 302.14 | 241.65 | 244.31 |
| Parts/Supplier Relationship (TPC-H Q16) | 287.68 | 253.96 | 212.38 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 695.92 | 615.04 | 502.08 |
| Large Volume Customer (TPC-H Q18) | 2938.22 | 2067.80 | 2257.76 |
| Discounted Revenue (TPC-H Q19) | 39.92 | 39.54 | 37.94 |
| Potential Part Promotion (TPC-H Q20) | 121.89 | 86.50 | 85.32 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 394.89 | 355.67 | 363.56 |
| Global Sales Opportunity Query (TPC-H Q22) | 74.44 | 68.44 | 94.30 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
All executions use the same database, so loading times are the same.
Per default, all 3 streams use the same random parameters (like DELTA in Q1) and run in ordering Q1-Q22. You can change this via
-xrcp: Each stream has it’s own random parameters-xshq: Use the ordering per stream as required by the TPC-H specification
Use Persistent Storage
The default behaviour of bexhoma is that the database is stored inside the ephemeral storage of the Docker container. If your cluster allows dynamic provisioning of volumes, you might request a persistent storage of a certain type (storageClass) and size.
Example:
bexhoma tpch \
-dbms PostgreSQL \
-sf 1 \
-nc 2 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-ms $BEXHOMA_MS \
-tr \
-lr 64Gi \
-rr 64Gi \
-rsr \
-rss 50Gi \
-rst $BEXHOMA_STORAGE_CLASS \
-rnn $BEXHOMA_NODE_SUT -rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
--set deployment[bexhoma-deployment-postgres].container[dbms].random_page_cost=1.1 \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_io_concurrency=200 \
--set deployment[bexhoma-deployment-postgres].container[dbms].io_method=io_uring \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers_per_gather=2 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers=4 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_worker_processes=6 \
--set deployment[bexhoma-deployment-postgres].container[dbms].shared_buffers=20GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_cache_size=48GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].work_mem=1GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].maintenance_work_mem=2GB \
run &>$LOG_DIR/docs_tpch_postgresql_storage.log
The following status shows we have a volumes of type shared.
Every experiment running TPC-H of SF=1 at PostgreSQL will take the database from this volume and skip loading.
In this example -nc is set to two, that is the complete experiment is repeated twice for statistical confidence.
The first instance of PostgreSQL mounts the volume and generates the data.
All other instances just use the database without generating and loading data.
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| Volumes | configuration | experiment | loaded [s] | timeLoading [s] | dbms | storage_class_name | storage | status | size | used |
+====================================+=================+==============+==============+===================+============+======================+===========+==========+========+========+
| bexhoma-storage-monetdb-tpch-10 | monetdb | tpch-10 | True | 576 | MonetDB | shared | 100Gi | Bound | 100G | 21G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-monetdb-tpch-100 | monetdb | tpch-100 | True | 7061 | MonetDB | shared | 300Gi | Bound | 300G | 210G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-monetdb-tpch-3 | monetdb | tpch-3 | True | 215 | MonetDB | shared | 100Gi | Bound | 100G | 6.2G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-monetdb-tpch-30 | monetdb | tpch-30 | True | 1734 | MonetDB | shared | 150Gi | Bound | 150G | 63G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-mysql-tpch-1 | mysql | tpch-1 | True | 2178 | MySQL | shared | 30Gi | Bound | 30G | 11G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-mysql-tpch-10 | mysql | tpch-10 | True | 33932 | MySQL | shared | 150Gi | Bound | 150G | 36G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-postgresql-tpch-1 | postgresql | tpch-1 | True | 148 | PostgreSQL | shared | 100Gi | Bound | 50G | 2.7G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-postgresql-tpch-10 | postgresql | tpch-10 | True | 2581 | PostgreSQL | shared | 100Gi | Bound | 100G | 26G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| bexhoma-storage-postgresql-tpch-30 | postgresql | tpch-30 | True | 10073 | PostgreSQL | shared | 150Gi | Bound | 150G | 76G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
+------------------+--------------+--------------+---------------+
| 1707740320 | sut | loaded [s] | benchmarker |
+==================+==============+==============+===============+
| PostgreSQL-BHT-8 | (1. Running) | 185.41 | (1. Running) |
+------------------+--------------+--------------+---------------+
The result looks something like
Show docs_tpch_postgresql_storage.log
## Show Summary
### Workload
TPC-H Queries SF=1
* Type: tpch
* Duration: 862s
* Code: 1785152922
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=1) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 600.
* Data transfer volume per query is also measured.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.8.
* Experiment is limited to DBMS ['PostgreSQL'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* SUT is fixed to cl-worker36.
* Database is persisted to disk of type shared and size 50Gi. Persistent storage is removed at experiment start.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1] times the number of benchmarking pods.
* Experiment is run 2 times.
* Maximum DBMS across the whole cluster is 10.
* Results are validated against basic correctness requirements.
* Deployment parameter overrides: [({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'random_page_cost'}, '1.1'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_io_concurrency'}, '200'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'io_method'}, 'io_uring'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers_per_gather'}, '2'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers'}, '4'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_worker_processes'}, '6'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'shared_buffers'}, '20GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_cache_size'}, '48GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'work_mem'}, '1GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'maintenance_work_mem'}, '2GB')].
* SUT requests 4 CPU and 64Gi RAM. RAM limit is 64Gi.
### Connections
* PostgreSQL-1-1-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:842863
* volume_size:50G
* volume_used:2.7G
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785152922
* PostgreSQL-1-2-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:808956
* volume_size:50G
* volume_used:2.7G
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785152922
### SUT Container Restarts
* bexhoma-sut-postgresql-1-1785152922-b744c9c4-g47bz: 0 0
### Workflow
#### Actual
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS PostgreSQL-1 - Experiment 2 Client 1: tpch (1 pods)
#### Planned
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS PostgreSQL-1 - Experiment 2 Client 1: tpch (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:---------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| PostgreSQL-1-1 | 1 | 1 | 171.00 | 1.00 | 21.00 | 20.00 | 126.00 | 8 | 0 | | | 0 | False | 21.05 |
| PostgreSQL-1-2 | 2 | 1 | 171.00 | 1.00 | 21.00 | 20.00 | 126.00 | 8 | 0 | | | 0 | False | 21.05 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:---------------------|:----------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:---------------------|
| PostgreSQL-1-1-1-1-1 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 14 | 0.28 | 14074.56 | 5657.14 | 0 | PostgreSQL-1-1-1-1-1 |
| PostgreSQL-1-2-1-1-1 | PostgreSQL-1 | PostgreSQL-1-2-1 | PostgreSQL-1-2-1-1 | 2 | 1 | 1 | 1 | 1.00 | 22 | 31 | 0.51 | 7771.68 | 2554.84 | 0 | PostgreSQL-1-2-1-1-1 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:-----------------|:-----------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| PostgreSQL-1-1-1 | PostgreSQL-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 14 | 0.28 | 14074.56 | 5657.14 | 0 |
| PostgreSQL-1-2-1 | PostgreSQL-1-2-1 | 2 | 1 | 1 | 1 | 1.00 | 22 | 31 | 0.51 | 7771.68 | 2554.84 | 0 |
### Latency of Timer Execution [ms]
| Queries | PostgreSQL-1-1-1-1-1 | PostgreSQL-1-2-1-1-1 |
|:----------------------------------------------------|-----------------------:|-----------------------:|
| Pricing Summary Report (TPC-H Q1) | 1273.15 | 6045.48 |
| Minimum Cost Supplier Query (TPC-H Q2) | 169.26 | 4705.81 |
| Shipping Priority (TPC-H Q3) | 371.86 | 4302.94 |
| Order Priority Checking Query (TPC-H Q4) | 103.35 | 120.60 |
| Local Supplier Volume (TPC-H Q5) | 164.67 | 182.50 |
| Forecasting Revenue Change (TPC-H Q6) | 222.33 | 226.89 |
| Volume Shipping Query (TPC-H Q7) | 262.19 | 235.84 |
| National Market Share (TPC-H Q8) | 122.23 | 1360.52 |
| Product Type Profit Measure (TPC-H Q9) | 577.37 | 812.45 |
| Returned Item Reporting Query (TPC-H Q10) | 225.28 | 298.43 |
| Important Stock Identification (TPC-H Q11) | 51.74 | 67.72 |
| Shipping Modes and Order Priority (TPC-H Q12) | 365.56 | 509.31 |
| Customer Distribution (TPC-H Q13) | 640.60 | 742.77 |
| Promotion Effect Query (TPC-H Q14) | 1324.23 | 1345.52 |
| Top Supplier Query (TPC-H Q15) | 238.41 | 255.43 |
| Parts/Supplier Relationship (TPC-H Q16) | 196.00 | 221.76 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 527.63 | 1439.81 |
| Large Volume Customer (TPC-H Q18) | 2107.07 | 2146.53 |
| Discounted Revenue (TPC-H Q19) | 37.96 | 46.15 |
| Potential Part Promotion (TPC-H Q20) | 98.04 | 102.47 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 360.95 | 415.15 |
| Global Sales Opportunity Query (TPC-H Q22) | 66.42 | 78.80 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
The loading times for both instances of loading are the same, since both relate to the same process of ingesting into the database.
Note the added section about volume_size and volume_used in the connections section.
Fractional Scaling Factor
TPC-H supports scaling factors that are fractional. Example: SF=0.1
bexhoma tpch \
-dbms PostgreSQL \
-sf 0.1 \
-nc 2 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-ms $BEXHOMA_MS \
-tr \
-rsr \
-rss 50Gi \
-rst $BEXHOMA_STORAGE_CLASS \
-rnn $BEXHOMA_NODE_SUT -rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
run &>$LOG_DIR/docs_tpch_postgresql_fractional.log
results in
Show docs_tpch_postgresql_fractional.log
## Show Summary
### Workload
TPC-H Queries SF=0.1
* Type: tpch
* Duration: 852s
* Code: 1785153807
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=0.1) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 600.
* Data transfer volume per query is also measured.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.8.
* Experiment is limited to DBMS ['PostgreSQL'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* SUT is fixed to cl-worker36.
* Database is persisted to disk of type shared and size 50Gi. Persistent storage is removed at experiment start.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1] times the number of benchmarking pods.
* Experiment is run 2 times.
* Maximum DBMS across the whole cluster is 10.
* Results are validated against basic correctness requirements.
* SUT requests 4 CPU and 16Gi RAM.
### Connections
* PostgreSQL-1-1-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:808966
* volume_size:50G
* volume_used:312M
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=16', '-c', 'max_parallel_workers=16', '-c', 'max_parallel_workers_per_gather=8', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=16GB', '-c', 'effective_cache_size=40GB', '-c', 'work_mem=512MB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000']
* requests_cpu:4
* requests_memory:16Gi
* eval_parameters
* code:1785153807
* PostgreSQL-1-2-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:810118
* volume_size:50G
* volume_used:312M
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=16', '-c', 'max_parallel_workers=16', '-c', 'max_parallel_workers_per_gather=8', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=16GB', '-c', 'effective_cache_size=40GB', '-c', 'work_mem=512MB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000']
* requests_cpu:4
* requests_memory:16Gi
* eval_parameters
* code:1785153807
### SUT Container Restarts
* bexhoma-sut-postgresql-1-1785153807-69ffd6d496-zx9lm: 0 0
### Workflow
#### Actual
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS PostgreSQL-1 - Experiment 2 Client 1: tpch (1 pods)
#### Planned
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS PostgreSQL-1 - Experiment 2 Client 1: tpch (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:---------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| PostgreSQL-1-1 | 1 | 0.10 | 139.00 | 1.00 | 19.00 | 2.00 | 113.00 | 8 | 0 | | | 0 | False | 2.59 |
| PostgreSQL-1-2 | 2 | 0.10 | 139.00 | 1.00 | 19.00 | 2.00 | 113.00 | 8 | 0 | | | 0 | False | 2.59 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:---------------------|:----------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:---------------------|
| PostgreSQL-1-1-1-1-1 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 0.10 | 22 | 3 | 0.04 | 11289.58 | 2640.00 | 0 | PostgreSQL-1-1-1-1-1 |
| PostgreSQL-1-2-1-1-1 | PostgreSQL-1 | PostgreSQL-1-2-1 | PostgreSQL-1-2-1-1 | 2 | 1 | 1 | 1 | 0.10 | 22 | 6 | 0.06 | 6392.52 | 1320.00 | 0 | PostgreSQL-1-2-1-1-1 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:-----------------|:-----------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| PostgreSQL-1-1-1 | PostgreSQL-1-1-1 | 1 | 1 | 1 | 1 | 0.10 | 22 | 3 | 0.04 | 11289.58 | 2640.00 | 0 |
| PostgreSQL-1-2-1 | PostgreSQL-1-2-1 | 2 | 1 | 1 | 1 | 0.10 | 22 | 6 | 0.06 | 6392.52 | 1320.00 | 0 |
### Latency of Timer Execution [ms]
| Queries | PostgreSQL-1-1-1-1-1 | PostgreSQL-1-2-1-1-1 |
|:----------------------------------------------------|-----------------------:|-----------------------:|
| Pricing Summary Report (TPC-H Q1) | 150.24 | 2187.07 |
| Minimum Cost Supplier Query (TPC-H Q2) | 22.71 | 638.96 |
| Shipping Priority (TPC-H Q3) | 50.20 | 986.42 |
| Order Priority Checking Query (TPC-H Q4) | 62.66 | 102.15 |
| Local Supplier Volume (TPC-H Q5) | 26.39 | 46.15 |
| Forecasting Revenue Change (TPC-H Q6) | 22.89 | 37.81 |
| Volume Shipping Query (TPC-H Q7) | 37.30 | 57.13 |
| National Market Share (TPC-H Q8) | 32.49 | 127.10 |
| Product Type Profit Measure (TPC-H Q9) | 83.08 | 107.92 |
| Returned Item Reporting Query (TPC-H Q10) | 44.79 | 40.49 |
| Important Stock Identification (TPC-H Q11) | 6.48 | 6.63 |
| Shipping Modes and Order Priority (TPC-H Q12) | 31.51 | 33.88 |
| Customer Distribution (TPC-H Q13) | 56.59 | 44.07 |
| Promotion Effect Query (TPC-H Q14) | 23.20 | 23.97 |
| Top Supplier Query (TPC-H Q15) | 21.78 | 21.38 |
| Parts/Supplier Relationship (TPC-H Q16) | 41.98 | 41.39 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 50.28 | 45.92 |
| Large Volume Customer (TPC-H Q18) | 205.23 | 217.58 |
| Discounted Revenue (TPC-H Q19) | 4.66 | 6.00 |
| Potential Part Promotion (TPC-H Q20) | 8.04 | 8.43 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 35.38 | 33.68 |
| Global Sales Opportunity Query (TPC-H Q22) | 12.26 | 11.73 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
Refresh Stream
TPC-H specifies RF1/RF2 operations that insert new orders and delete retired orders to keep the database current during a throughput test.
-xrs N enables a parallel refresh stream that applies N RF1+RF2 pairs per benchmarking round, running in parallel with the query streams.
For a spec-compliant throughput test, set N equal to the number of parallel query streams.
Example: 3 parallel query streams with 3 RF1+RF2 pairs:
bexhoma tpch \
-dbms PostgreSQL \
-sf 1 \
-ne 3 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-ms $BEXHOMA_MS \
-tr \
-xrs 3 \
-lr 64Gi \
-rr 64Gi \
-rss 50Gi \
-rnn $BEXHOMA_NODE_SUT -rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
--set deployment[bexhoma-deployment-postgres].container[dbms].random_page_cost=1.1 \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_io_concurrency=200 \
--set deployment[bexhoma-deployment-postgres].container[dbms].io_method=io_uring \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers_per_gather=2 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers=4 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_worker_processes=6 \
--set deployment[bexhoma-deployment-postgres].container[dbms].shared_buffers=20GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_cache_size=48GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].work_mem=1GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].maintenance_work_mem=2GB \
run &>$LOG_DIR/docs_tpch_postgresql_refresh.log
Use -xrso N to skip the first N already-applied refresh sets and continue from where a previous run stopped.
Show docs_tpch_postgresql_refresh.log
## Show Summary
### Workload
TPC-H Queries SF=1
* Type: tpch
* Duration: 403s
* Code: 1782123923
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=1) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 600.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.9.18.
* Experiment is limited to DBMS ['PostgreSQL'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* SUT is fixed to cl-worker38.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [3] times the number of benchmarking pods.
* Experiment is run once.
### Connections
* PostgreSQL-1-1-1-1-1 uses docker image postgres:18.3
* RAM:540492877824
* CPU:Intel(R) Xeon(R) Gold 6430
* Cores:128
* host:6.8.0-111-generic
* node:cl-worker38
* disk:222681
* datadisk:2757
* cpu_list:0-127
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=16', '-c', 'max_parallel_workers=16', '-c', 'max_parallel_workers_per_gather=8', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=16GB', '-c', 'effective_cache_size=40GB', '-c', 'work_mem=512MB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000']
* requests_cpu:4
* requests_memory:16Gi
* eval_parameters
* code:1782123923
* PostgreSQL-1-1-1-1-2 uses docker image postgres:18.3
* RAM:540492877824
* CPU:Intel(R) Xeon(R) Gold 6430
* Cores:128
* host:6.8.0-111-generic
* node:cl-worker38
* disk:222681
* datadisk:2757
* cpu_list:0-127
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=16', '-c', 'max_parallel_workers=16', '-c', 'max_parallel_workers_per_gather=8', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=16GB', '-c', 'effective_cache_size=40GB', '-c', 'work_mem=512MB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000']
* requests_cpu:4
* requests_memory:16Gi
* eval_parameters
* code:1782123923
* PostgreSQL-1-1-1-1-3 uses docker image postgres:18.3
* RAM:540492877824
* CPU:Intel(R) Xeon(R) Gold 6430
* Cores:128
* host:6.8.0-111-generic
* node:cl-worker38
* disk:222681
* datadisk:2757
* cpu_list:0-127
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=16', '-c', 'max_parallel_workers=16', '-c', 'max_parallel_workers_per_gather=8', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=16GB', '-c', 'effective_cache_size=40GB', '-c', 'work_mem=512MB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000']
* requests_cpu:4
* requests_memory:16Gi
* eval_parameters
* code:1782123923
### Workflow
#### Actual
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (3 pods), tpch_refresh (1 pods)
#### Planned
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (3 pods), tpch_refresh (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:---------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| PostgreSQL-1-1 | 1 | 1 | 130.00 | 0.00 | 26.00 | 8.00 | 92.00 | 8 | 0 | | | 0 | False | 27.69 |
### Execution
#### Per Connection
| | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:---------------------|:----------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:---------------------|
| PostgreSQL-1-1-1-1-1 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 12 | 0.31 | 11746.33 | 6600.00 | 0 | PostgreSQL-1-1-1-1-1 |
| PostgreSQL-1-1-1-1-2 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 11 | 0.28 | 12864.84 | 7200.00 | 0 | PostgreSQL-1-1-1-1-2 |
| PostgreSQL-1-1-1-1-3 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 1.00 | 22 | 11 | 0.27 | 13331.73 | 7200.00 | 0 | PostgreSQL-1-1-1-1-3 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:-----------------|:-----------------|-----------------:|---------:|----------------:|------------:|-----:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| PostgreSQL-1-1-1 | PostgreSQL-1-1-1 | 1 | 1 | 1 | 3 | 1.00 | 66 | 12 | 0.29 | 12629.84 | 19800.00 | 0 |
### tpch_refresh
| connection | phase | job | experiment_run | client | benchmark_run | pod_count | benchmark_begin | benchmark_end | benchmark_duration |
|:---------------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|:--------------------|:--------------------|---------------------:|
| PostgreSQL-1-1-1-2-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-2 | 1 | 1 | 2 | 1 | 2026-06-22 12:30:55 | 2026-06-22 12:30:57 | 2 |
### Latency of Timer Execution [ms]
| Queries | PostgreSQL-1-1-1-1-1 | PostgreSQL-1-1-1-1-2 | PostgreSQL-1-1-1-1-3 |
|:----------------------------------------------------|-----------------------:|-----------------------:|-----------------------:|
| Pricing Summary Report (TPC-H Q1) | 1197.84 | 1176.55 | 1150.63 |
| Minimum Cost Supplier Query (TPC-H Q2) | 268.23 | 217.91 | 222.31 |
| Shipping Priority (TPC-H Q3) | 334.51 | 299.32 | 299.50 |
| Order Priority Checking Query (TPC-H Q4) | 168.19 | 142.73 | 136.14 |
| Local Supplier Volume (TPC-H Q5) | 319.30 | 268.36 | 273.07 |
| Forecasting Revenue Change (TPC-H Q6) | 203.53 | 172.29 | 175.51 |
| Volume Shipping Query (TPC-H Q7) | 375.95 | 362.46 | 319.35 |
| National Market Share (TPC-H Q8) | 177.14 | 205.04 | 154.65 |
| Product Type Profit Measure (TPC-H Q9) | 551.16 | 561.68 | 524.43 |
| Returned Item Reporting Query (TPC-H Q10) | 388.02 | 375.05 | 352.49 |
| Important Stock Identification (TPC-H Q11) | 95.98 | 85.49 | 83.83 |
| Shipping Modes and Order Priority (TPC-H Q12) | 314.60 | 276.47 | 263.54 |
| Customer Distribution (TPC-H Q13) | 1020.60 | 756.30 | 764.17 |
| Promotion Effect Query (TPC-H Q14) | 214.08 | 209.07 | 213.99 |
| Top Supplier Query (TPC-H Q15) | 239.29 | 218.29 | 205.38 |
| Parts/Supplier Relationship (TPC-H Q16) | 298.77 | 247.62 | 238.91 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 885.59 | 663.58 | 669.75 |
| Large Volume Customer (TPC-H Q18) | 3087.54 | 3061.66 | 2869.76 |
| Discounted Revenue (TPC-H Q19) | 52.03 | 52.47 | 53.29 |
| Potential Part Promotion (TPC-H Q20) | 152.57 | 142.48 | 139.41 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 280.37 | 284.46 | 287.71 |
| Global Sales Opportunity Query (TPC-H Q22) | 104.57 | 98.09 | 93.23 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Tests
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
The added ### tpch_refresh section shows the wall-clock timing of the parallel refresh stream job: when it started, when it ended, and the total duration in seconds.
Run a Subset of Queries
Not every iteration needs the full 22-query stream. -xaq restricts a run to the given 1-based query numbers; every other query is set inactive for this experiment’s uploaded query config only — the query config files in experiments/tpch/ are never modified, so a later run without -xaq sees the full query set again.
Looking back at the SF=10 monitoring example above, Q1, Q18, Q14 and Q9 dominate total runtime (34.5s, 24.0s, 17.0s and 10.3s respectively), while every other query finishes in under 8.5s. When iterating on a tuning change (e.g. shared_buffers or work_mem), re-running just these four queries gives a fast read on whether the change helped, without waiting on the full stream.
Example:
bexhoma tpch \
-dbms PostgreSQL \
-sf 10 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xaq 1,9,14,18 \
-xdt \
-ms $BEXHOMA_MS \
-tr \
-lr 64Gi \
-rr 64Gi \
-rss 150Gi \
-rnn $BEXHOMA_NODE_SUT -rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
--set deployment[bexhoma-deployment-postgres].container[dbms].random_page_cost=1.1 \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_io_concurrency=200 \
--set deployment[bexhoma-deployment-postgres].container[dbms].io_method=io_uring \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers_per_gather=2 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_parallel_workers=4 \
--set deployment[bexhoma-deployment-postgres].container[dbms].max_worker_processes=6 \
--set deployment[bexhoma-deployment-postgres].container[dbms].shared_buffers=20GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].effective_cache_size=48GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].work_mem=1GB \
--set deployment[bexhoma-deployment-postgres].container[dbms].maintenance_work_mem=2GB \
run &>$LOG_DIR/docs_tpch_postgresql_subset.log
Show docs_tpch_postgresql_subset.log
## Show Summary
### Workload
TPC-H Queries SF=10
* Type: tpch
* Duration: 795s
* Code: 1785154679
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=10) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* Query selection is limited to Q1, Q9, Q14, Q18.
* All instances use the same query parameters.
* Timeout per query is 600.
* Data transfer volume per query is also measured.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.8.
* Experiment is limited to DBMS ['PostgreSQL'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* SUT is fixed to cl-worker36.
* Database uses ephemeral storage of size 150Gi.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1] times the number of benchmarking pods.
* Experiment is run once.
* Maximum DBMS across the whole cluster is 10.
* Results are validated against basic correctness requirements.
* Deployment parameter overrides: [({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'random_page_cost'}, '1.1'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_io_concurrency'}, '200'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'io_method'}, 'io_uring'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers_per_gather'}, '2'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_parallel_workers'}, '4'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'max_worker_processes'}, '6'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'shared_buffers'}, '20GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'effective_cache_size'}, '48GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'work_mem'}, '1GB'), ({'kind': 'deployment', 'workload': 'bexhoma-deployment-postgres', 'config': '', 'container': 'dbms', 'param': 'maintenance_work_mem'}, '2GB')].
* SUT requests 4 CPU and 64Gi RAM. RAM limit is 64Gi.
### Connections
* PostgreSQL-1-1-1-1-1 uses docker image postgres:18.3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:836219
* cpu_list:0-223
* args:['-c', 'max_connections=640', '-c', 'max_worker_processes=6', '-c', 'max_parallel_workers=4', '-c', 'max_parallel_workers_per_gather=2', '-c', 'max_parallel_maintenance_workers=4', '-c', 'shared_buffers=20GB', '-c', 'effective_cache_size=48GB', '-c', 'work_mem=1GB', '-c', 'maintenance_work_mem=2GB', '-c', 'autovacuum=off', '-c', 'wal_level=minimal', '-c', 'max_wal_senders=0', '-c', 'max_wal_size=32GB', '-c', 'checkpoint_timeout=1h', '-c', 'checkpoint_completion_target=1.0', '-c', 'lock_timeout=30s', '-c', 'idle_in_transaction_session_timeout=30000', '-c', 'random_page_cost=1.1', '-c', 'effective_io_concurrency=200', '-c', 'io_method=io_uring']
* requests_cpu:4
* requests_memory:64Gi
* limits_memory:64Gi
* eval_parameters
* code:1785154679
### SUT Container Restarts
* bexhoma-sut-postgresql-1-1785154679-74db9fdf8c-mdpxv: 0 0
### Workflow
#### Actual
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
#### Planned
* DBMS PostgreSQL-1 - Experiment 1 Client 1: tpch (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:---------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| PostgreSQL-1-1 | 1 | 10 | 477.00 | 1.00 | 24.00 | 80.00 | 369.00 | 8 | 0 | | | 0 | False | 75.47 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:---------------------|:----------------|:-----------------|:-------------------|-----------------:|---------:|----------------:|------------:|------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:---------------------|
| PostgreSQL-1-1-1-1-1 | PostgreSQL-1 | PostgreSQL-1-1-1 | PostgreSQL-1-1-1-1 | 1 | 1 | 1 | 1 | 10.00 | 4 | 59 | 13.86 | 2600.88 | 2440.68 | 0 | PostgreSQL-1-1-1-1-1 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:-----------------|:-----------------|-----------------:|---------:|----------------:|------------:|------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| PostgreSQL-1-1-1 | PostgreSQL-1-1-1 | 1 | 1 | 1 | 1 | 10.00 | 4 | 59 | 13.86 | 2600.88 | 2440.68 | 0 |
### Latency of Timer Execution [ms]
| Queries | PostgreSQL-1-1-1-1-1 |
|:---------------------------------------|-----------------------:|
| Pricing Summary Report (TPC-H Q1) | 11640.48 |
| Product Type Profit Measure (TPC-H Q9) | 9397.46 |
| Promotion Effect Query (TPC-H Q14) | 14970.45 |
| Large Volume Customer (TPC-H Q18) | 22413.66 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
In the resulting summary, ### Latency of Timer Execution lists only Q1, Q9, Q14 and Q18; num_of_queries in ### Execution drops to 4 accordingly, and Power@Size / Geo Times are computed over that reduced set only — so they are not comparable to a full-stream run.
Example: MonetDB TPC-H@100
This example shows how to run Q1-Q22 derived from TPC-H in MonetDB at SF=100. It covers the power and the throughput test. The refresh stream is not included.
The query file is derived from the TPC-H and as such is not comparable to published TPC-H results, as the query file results do not comply with the TPC-H Specification.
Official TPC-H benchmark - http://www.tpc.org/tpch
The results are not official benchmark results. The exact performance depends on a collection of parameters. The purpose of this example is to illustrate the usage of bexhoma and to show how to evaluate results.
Generate and Load Data
At first we generate TPC-H data at SF=100 (-sf) with 8 parallel generators (-nlp).
The generated data is stored at the shared disk data.
Moreover the data is loaded into an instance of MonetDB using again 8 parallel loaders.
Afterwards the script creates contraints (-xic) and indexes (-xii) and updates table statistics (-xis).
The database is located in another shared disk of storageClass shared (-rst) and of size 1000Gi (-rss).
The script also runs a power test (-ne set to 1) with timeout 1200s (-t) and data transfer activated (-xdt) once (-nc set to 1).
To avoid conflicts with other experiments we set a maximum of 1 DBMS per time (-ms).
Monitoring is activated (-m) for all components (-mc).
The components, that is the SUT (-rnn) and the loader (-rnl) and the benchmark driver (-rnb), are fixed to specific nodes in the cluster.
BEXHOMA_NODE_SUT="cl-worker11"
BEXHOMA_NODE_LOAD="cl-worker19"
BEXHOMA_NODE_BENCHMARK="cl-worker19"
LOG_DIR="./logs_tests"
BEXHOMA_MS=1
BEXHOMA_STORAGE_CLASS="shared"
mkdir -p $LOG_DIR
bexhoma tpch \
-dbms MonetDB \
-sf 100 \
-nc 1 \
-ne 1 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-m \
-mc \
-ms $BEXHOMA_MS \
-t 3600 \
-lr 256Gi \
-rr 256Gi \
-rss 1000Gi \
-rst $BEXHOMA_STORAGE_CLASS \
-rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
run &>$LOG_DIR/docs_tpch_monetdb_1.log
Status Data Disk
You can watch the status of the data disk via bexhoma data.
In the following example output we see we have generated TPC-H at SF=100 using 8 generators. The data set is split into 8 parts, each of about 14G size. In total the data set has a size of 106G.
14G /data/tpch/SF100/8/8
14G /data/tpch/SF100/8/3
14G /data/tpch/SF100/8/2
14G /data/tpch/SF100/8/4
14G /data/tpch/SF100/8/1
14G /data/tpch/SF100/8/5
14G /data/tpch/SF100/8/7
14G /data/tpch/SF100/8/6
106G /data/tpch/SF100/8
106G /data/tpch/SF100
Status Database and Benchmark
You can watch the status of experiments via bexhoma status.
In the following example output we see all components of bexhoma are up and running. The cluster stores a MonetDB database corresponding to TPC-H of SF=100. The disk is of storageClass shared and of size 1000Gi and 210G of that space is used. It took about 7000s to build this database. Currently no DBMS is running.
Dashboard: Running
Message Queue: Running
Data directory: Running
Result directory: Running
Cluster Prometheus: Running
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
| Volumes | configuration | experiment | loaded [s] | timeLoading [s] | dbms | storage_class_name | storage | status | size | used |
+====================================+=================+==============+==============+===================+============+======================+===========+==========+========+========+
| bexhoma-storage-monetdb-tpch-100 | monetdb | tpch-100 | True | 7061 | MonetDB | shared | 300Gi | Bound | 300G | 210G |
+------------------------------------+-----------------+--------------+--------------+-------------------+------------+----------------------+-----------+----------+--------+--------+
Summary of Results
At the end of a benchmark you will see a summary like
Show docs_tpch_monetdb_1.log
## Show Summary
### Workload
TPC-H Queries SF=100
* Type: tpch
* Duration: 5539s
* Code: 1785155487
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=100) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 3600.
* Data transfer volume per query is also measured.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.8.
* System metrics are monitored by a cluster-wide installation.
* Experiment is limited to DBMS ['MonetDB'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* Database is persisted to disk of type shared and size 1000Gi. Persistent storage is removed at experiment start.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1] times the number of benchmarking pods.
* Experiment is run once.
* Maximum DBMS across the whole cluster is 10.
* SUT requests 16 CPU and 256Gi RAM. CPU limit is 16. RAM limit is 256Gi.
### Connections
* MonetDB-1-1-1-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:811355
* volume_size:1000G
* volume_used:183G
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1785155487
### SUT Container Restarts
* bexhoma-sut-monetdb-1-1785155487-74988b4bdc-wbbzw: 0
### Workflow
#### Actual
* DBMS MonetDB-1 - Experiment 1 Client 1: tpch (1 pods)
#### Planned
* DBMS MonetDB-1 - Experiment 1 Client 1: tpch (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:------------|-----------------:|-----:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| MonetDB-1-1 | 1 | 100 | 6824.00 | 4.00 | 14.00 | 2235.00 | 4565.00 | 8 | 0 | | None | 0 | False | 52.75 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:------------------|:----------------|:--------------|:----------------|-----------------:|---------:|----------------:|------------:|-------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:------------------|
| MonetDB-1-1-1-1-1 | MonetDB-1 | MonetDB-1-1-1 | MonetDB-1-1-1-1 | 1 | 1 | 1 | 1 | 100.00 | 22 | 707 | 17.30 | 21765.77 | 11202.26 | -1 | MonetDB-1-1-1-1-1 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:--------------|:--------------|-----------------:|---------:|----------------:|------------:|-------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| MonetDB-1-1-1 | MonetDB-1-1-1 | 1 | 1 | 1 | 1 | 100.00 | 22 | 707 | 17.30 | 21765.77 | 11202.26 | -1 |
### Latency of Timer Execution [ms]
| Queries | MonetDB-1-1-1-1-1 |
|:----------------------------------------------------|--------------------:|
| Pricing Summary Report (TPC-H Q1) | 98672.54 |
| Minimum Cost Supplier Query (TPC-H Q2) | 2355.96 |
| Shipping Priority (TPC-H Q3) | 37846.39 |
| Order Priority Checking Query (TPC-H Q4) | 16974.82 |
| Local Supplier Volume (TPC-H Q5) | 24327.80 |
| Forecasting Revenue Change (TPC-H Q6) | 26091.34 |
| Volume Shipping Query (TPC-H Q7) | 18426.24 |
| National Market Share (TPC-H Q8) | 20119.62 |
| Product Type Profit Measure (TPC-H Q9) | 47047.65 |
| Returned Item Reporting Query (TPC-H Q10) | 7162.88 |
| Important Stock Identification (TPC-H Q11) | 2330.37 |
| Shipping Modes and Order Priority (TPC-H Q12) | 12487.48 |
| Customer Distribution (TPC-H Q13) | 35757.38 |
| Promotion Effect Query (TPC-H Q14) | 22781.53 |
| Top Supplier Query (TPC-H Q15) | 7387.05 |
| Parts/Supplier Relationship (TPC-H Q16) | 4449.95 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 9766.57 |
| Large Volume Customer (TPC-H Q18) | 24039.98 |
| Discounted Revenue (TPC-H Q19) | 26897.09 |
| Potential Part Promotion (TPC-H Q20) | 15160.26 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 221138.75 |
| Global Sales Opportunity Query (TPC-H Q22) | 3286.42 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Monitoring
### Loading phase: SUT deployment
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 8572.25 | 4.61 | 185.36 | 185.36 |
### Loading phase: component data generator
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 0.00 | 0.00 | 0.00 | 0.00 |
### Loading phase: component loader
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 1899.80 | 1.27 | 0.03 | 13.29 |
### Execution phase: SUT deployment
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 4921.86 | 15.67 | 247.40 | 247.40 |
### Execution phase: component benchmarker
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 34.11 | 0.29 | 0.38 | 0.39 |
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Loading phase: SUT deployment contains no 0 or NaN in CPU [CPUs]
* TEST skipped: Loading phase: component data generator contains 0 or NaN in CPU [CPUs] (data pre-existing)
* TEST passed: Loading phase: component loader contains no 0 or NaN in CPU [CPUs]
* TEST passed: Execution phase: SUT deployment contains no 0 or NaN in CPU [CPUs]
* TEST passed: Execution phase: component benchmarker contains no 0 or NaN in CPU [CPUs]
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
To see the summary again you can simply call bexhoma summary -e 1708411664 with the experiment code.
List local results
You can inspect a preview list of results via bexhoma localresults.
+------------+----------------------+------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------+---------------------+
| index | name | info | intro | queries | connections | time |
+------------+----------------------+------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------+---------------------+
| 1708411664 | TPC-H Queries SF=100 | This experiment compares run time and resource consumption of TPC-H queries in different DBMS. | This includes the reading queries of TPC-H. | 22 | 28 | 2024-02-20 11:37:30 |
| | | TPC-H data is loaded from a filesystem using several processes. | | | | |
| | | Import is limited to DBMS MonetDB. | | | | |
| | | Import is handled by 1 processes. | | | | |
| | | Loading is fixed to cl-worker19. | | | | |
| | | Benchmarking is fixed to cl-worker19. | | | | |
+------------+----------------------+------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------+---------------------+
Perform Benchmark - Power Test
We now start a new instance of MonetDB and mount the existing database: we use the prepared database on the shared disk.
We then run two power tests, one after the other (-ne 1,1), and shut down the DBMS.
This is repeated 2 times (-nc).
mkdir -p ./logs/
BEXHOMA_MS=1
BEXHOMA_STORAGE_CLASS="shared"
BEXHOMA_NODE_SUT="cl-worker11"
BEXHOMA_NODE_LOAD="cl-worker19"
BEXHOMA_NODE_BENCHMARK="cl-worker19"
bexhoma tpch \
-dbms MonetDB \
-sf 100 \
-nc 2 \
-ne 1,1 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-m \
-mc \
-ms $BEXHOMA_MS \
-t 3600 \
-lr 256Gi \
-rr 256Gi \
-rss 1000Gi \
-rst $BEXHOMA_STORAGE_CLASS \
-rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
run &>$LOG_DIR/docs_tpch_monetdb_2.log
Evaluate Results
Show docs_tpch_monetdb_2.log
## Show Summary
### Workload
TPC-H Queries SF=100
* Type: tpch
* Duration: 6522s
* Code: 1784031723
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=100) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 3600.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.5.
* System metrics are monitored by a cluster-wide installation.
* Experiment is limited to DBMS ['MonetDB'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* Database is persisted to disk of type cephcsi and size 1000Gi.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1, 1] times the number of benchmarking pods.
* Experiment is run 2 times.
### Connections
* MonetDB-1-1-1-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199512
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784031723
* MonetDB-1-1-2-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199623
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784031723
* MonetDB-1-2-1-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199627
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784031723
* MonetDB-1-2-2-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199547
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784031723
### SUT Container Restarts
* bexhoma-sut-monetdb-1-1784031723-8696dddc47-bx4kf: 0
### Workflow
#### Actual
* DBMS MonetDB-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 1 Client 2: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 2 Client 1: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 2 Client 2: tpch (1 pods)
#### Planned
* DBMS MonetDB-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 1 Client 2: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 2 Client 1: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 2 Client 2: tpch (1 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:------------|-----------------:|-------:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| MonetDB-1-1 | 1 | 100.00 | 19615.00 | 11.00 | 20.00 | 2047.00 | 17533.00 | 0 | 0 | | None | 0 | False | 18.35 |
| MonetDB-1-2 | 2 | 100.00 | 19615.00 | 11.00 | 20.00 | 2047.00 | 17533.00 | 0 | 0 | | None | 0 | False | 18.35 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:------------------|:----------------|:--------------|:----------------|-----------------:|---------:|----------------:|------------:|-------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:------------------|
| MonetDB-1-1-1-1-1 | MonetDB-1 | MonetDB-1-1-1 | MonetDB-1-1-1-1 | 1 | 1 | 1 | 1 | 100.00 | 22 | 2555 | 29.30 | 12545.21 | 3099.80 | -1 | MonetDB-1-1-1-1-1 |
| MonetDB-1-1-2-1-1 | MonetDB-1 | MonetDB-1-1-2 | MonetDB-1-1-2-1 | 1 | 2 | 1 | 1 | 100.00 | 22 | 404 | 4.55 | 87693.30 | 19603.96 | -1 | MonetDB-1-1-2-1-1 |
| MonetDB-1-2-1-1-1 | MonetDB-1 | MonetDB-1-2-1 | MonetDB-1-2-1-1 | 2 | 1 | 1 | 1 | 100.00 | 22 | 2321 | 25.18 | 14662.83 | 3412.32 | -1 | MonetDB-1-2-1-1-1 |
| MonetDB-1-2-2-1-1 | MonetDB-1 | MonetDB-1-2-2 | MonetDB-1-2-2-1 | 2 | 2 | 1 | 1 | 100.00 | 22 | 430 | 4.67 | 86100.83 | 18418.60 | -1 | MonetDB-1-2-2-1-1 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:--------------|:--------------|-----------------:|---------:|----------------:|------------:|-------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| MonetDB-1-1-1 | MonetDB-1-1-1 | 1 | 1 | 1 | 1 | 100.00 | 22 | 2555 | 29.30 | 12545.21 | 3099.80 | -1 |
| MonetDB-1-1-2 | MonetDB-1-1-2 | 1 | 2 | 1 | 1 | 100.00 | 22 | 404 | 4.55 | 87693.30 | 19603.96 | -1 |
| MonetDB-1-2-1 | MonetDB-1-2-1 | 2 | 1 | 1 | 1 | 100.00 | 22 | 2321 | 25.18 | 14662.83 | 3412.32 | -1 |
| MonetDB-1-2-2 | MonetDB-1-2-2 | 2 | 2 | 1 | 1 | 100.00 | 22 | 430 | 4.67 | 86100.83 | 18418.60 | -1 |
### Latency of Timer Execution [ms]
| Queries | MonetDB-1-1-1-1-1 | MonetDB-1-1-2-1-1 | MonetDB-1-2-1-1-1 | MonetDB-1-2-2-1-1 |
|:----------------------------------------------------|--------------------:|--------------------:|--------------------:|--------------------:|
| Pricing Summary Report (TPC-H Q1) | 380507.57 | 90166.21 | 343853.46 | 94150.05 |
| Minimum Cost Supplier Query (TPC-H Q2) | 39231.68 | 503.30 | 38819.07 | 505.91 |
| Shipping Priority (TPC-H Q3) | 159098.82 | 5773.04 | 156006.22 | 4681.90 |
| Order Priority Checking Query (TPC-H Q4) | 146030.20 | 7882.44 | 138574.18 | 8816.53 |
| Local Supplier Volume (TPC-H Q5) | 16031.75 | 3267.06 | 11083.36 | 6807.39 |
| Forecasting Revenue Change (TPC-H Q6) | 1254.08 | 1332.36 | 1124.13 | 1059.84 |
| Volume Shipping Query (TPC-H Q7) | 5744.71 | 4260.30 | 5906.07 | 4019.69 |
| National Market Share (TPC-H Q8) | 192159.04 | 12041.34 | 180176.18 | 12662.47 |
| Product Type Profit Measure (TPC-H Q9) | 40472.91 | 5168.41 | 34297.40 | 4148.17 |
| Returned Item Reporting Query (TPC-H Q10) | 74701.23 | 8165.85 | 61954.80 | 8937.97 |
| Important Stock Identification (TPC-H Q11) | 11122.38 | 469.77 | 8195.91 | 466.09 |
| Shipping Modes and Order Priority (TPC-H Q12) | 9910.23 | 1073.95 | 3073.33 | 1390.82 |
| Customer Distribution (TPC-H Q13) | 317306.27 | 24164.25 | 263381.00 | 26818.89 |
| Promotion Effect Query (TPC-H Q14) | 754.77 | 653.23 | 749.29 | 743.20 |
| Top Supplier Query (TPC-H Q15) | 12347.46 | 1537.04 | 7246.03 | 1668.07 |
| Parts/Supplier Relationship (TPC-H Q16) | 3641.21 | 2854.27 | 4290.91 | 2646.84 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 505277.90 | 1581.20 | 537225.61 | 2117.78 |
| Large Volume Customer (TPC-H Q18) | 44862.74 | 7737.06 | 48272.88 | 8162.54 |
| Discounted Revenue (TPC-H Q19) | 2725.01 | 3260.81 | 2298.18 | 1276.69 |
| Potential Part Promotion (TPC-H Q20) | 11586.14 | 3290.00 | 10210.86 | 2633.67 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 546989.89 | 197237.61 | 429840.06 | 212197.41 |
| Global Sales Opportunity Query (TPC-H Q22) | 10919.43 | 1769.46 | 11616.96 | 2474.70 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Monitoring
### Execution phase: SUT deployment
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 986.35 | 2.51 | 120.22 | 120.22 |
| MonetDB-1-1-2-1 | 857.75 | 7.19 | 181.10 | 181.10 |
| MonetDB-1-2-1-1 | 1871.10 | 5.75 | 136.40 | 136.40 |
| MonetDB-1-2-2-1 | 902.30 | 6.21 | 193.39 | 193.39 |
### Execution phase: component benchmarker
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 31.80 | 0.21 | 0.40 | 0.42 |
| MonetDB-1-1-2-1 | 31.80 | 0.43 | 0.40 | 0.42 |
| MonetDB-1-2-1-1 | 32.37 | 0.32 | 0.40 | 0.42 |
| MonetDB-1-2-2-1 | 32.37 | 0.29 | 0.40 | 0.42 |
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Execution phase: SUT deployment contains no 0 or NaN in CPU [CPUs]
* TEST passed: Execution phase: component benchmarker contains no 0 or NaN in CPU [CPUs]
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
Perform Benchmark - Throughput Test
We now start a new instance of MonetDB and mount the existing database: we use the prepared database on the shared disk.
We then run two power tests, one after the other, and then a throughput test with 3 parallel driver (-ne 1,1,3). and shut down the DBMS.
mkdir -p ./logs/
BEXHOMA_MS=1
BEXHOMA_STORAGE_CLASS="shared"
BEXHOMA_NODE_SUT="cl-worker11"
BEXHOMA_NODE_LOAD="cl-worker19"
BEXHOMA_NODE_BENCHMARK="cl-worker19"
bexhoma tpch \
-dbms MonetDB \
-sf 100 \
-nc 1 \
-ne 1,1,3 \
-nlp 8 \
-nlt 8 \
-xii -xic -xis \
-xdt \
-m \
-mc \
-ms $BEXHOMA_MS \
-t 3600 \
-lr 256Gi \
-rr 256Gi \
-rss 1000Gi \
-rst $BEXHOMA_STORAGE_CLASS \
-rnl $BEXHOMA_NODE_LOAD -rnb $BEXHOMA_NODE_BENCHMARK \
run &>$LOG_DIR/docs_tpch_monetdb_3.log
Evaluate Results
Show docs_tpch_monetdb_3.log
## Show Summary
### Workload
TPC-H Queries SF=100
* Type: tpch
* Duration: 5775s
* Code: 1784038339
* This includes the reading queries of TPC-H.
* This experiment compares run time and resource consumption of TPC-H queries in different DBMS.
* TPC-H (SF=100) data is loaded and benchmark is executed.
* Query ordering is Q1 - Q22.
* All instances use the same query parameters.
* Timeout per query is 3600.
* Import sets indexes and constraints after loading and recomputes statistics.
* Experiment uses bexhoma version 0.10.5.
* System metrics are monitored by a cluster-wide installation.
* Experiment is limited to DBMS ['MonetDB'].
* Import is handled by 8 processes (pods).
* Loading is fixed to cl-worker19.
* Benchmarking is fixed to cl-worker19.
* Database is persisted to disk of type cephcsi and size 1000Gi.
* Loading is tested with [8] threads, split into [8] pods.
* Benchmarking is tested with [1] threads, split into [1] pods.
* Benchmarking is run as [1, 1, 3] times the number of benchmarking pods.
* Experiment is run once.
### Connections
* MonetDB-1-1-1-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199550
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784038339
* MonetDB-1-1-2-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199462
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784038339
* MonetDB-1-1-3-1-1 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199464
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784038339
* MonetDB-1-1-3-1-2 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199464
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784038339
* MonetDB-1-1-3-1-3 uses docker image monetdb/monetdb:Dec2025-SP3
* RAM:2164173213696
* CPU:INTEL(R) XEON(R) PLATINUM 8570
* Cores:224
* host:6.8.0-111-generic
* node:cl-worker36
* disk:1199464
* cpu_list:0-223
* requests_cpu:16
* requests_memory:256Gi
* limits_cpu:16
* limits_memory:256Gi
* eval_parameters
* code:1784038339
### SUT Container Restarts
* bexhoma-sut-monetdb-1-1784038339-7cb6467b-8bnds: 0
### Workflow
#### Actual
* DBMS MonetDB-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 1 Client 2: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 1 Client 3: tpch (3 pods)
#### Planned
* DBMS MonetDB-1 - Experiment 1 Client 1: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 1 Client 2: tpch (1 pods)
* DBMS MonetDB-1 - Experiment 1 Client 3: tpch (3 pods)
### Loading
#### Per Run
| | experiment_run | SF | time_load | time_preload | time_generate | time_ingest | time_postload | loading_pods | terminals | tenant_id | type_tenants | num_tenants | vol_tenants | Throughput [SF/h] |
|:------------|-----------------:|-------:|------------:|---------------:|----------------:|--------------:|----------------:|---------------:|------------:|:------------|:---------------|--------------:|:--------------|--------------------:|
| MonetDB-1-1 | 1 | 100.00 | 19615.00 | 11.00 | 20.00 | 2047.00 | 17533.00 | 0 | 0 | | None | 0 | False | 18.35 |
### Execution
#### Per Connection
| DBMS | configuration | phase | job | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id | pod |
|:------------------|:----------------|:--------------|:----------------|-----------------:|---------:|----------------:|------------:|-------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|:------------------|
| MonetDB-1-1-1-1-1 | MonetDB-1 | MonetDB-1-1-1 | MonetDB-1-1-1-1 | 1 | 1 | 1 | 1 | 100.00 | 22 | 2360 | 25.85 | 14292.09 | 3355.93 | -1 | MonetDB-1-1-1-1-1 |
| MonetDB-1-1-2-1-1 | MonetDB-1 | MonetDB-1-1-2 | MonetDB-1-1-2-1 | 1 | 2 | 1 | 1 | 100.00 | 22 | 409 | 4.32 | 91829.06 | 19364.30 | -1 | MonetDB-1-1-2-1-1 |
| MonetDB-1-1-3-1-1 | MonetDB-1 | MonetDB-1-1-3 | MonetDB-1-1-3-1 | 1 | 3 | 1 | 1 | 100.00 | 22 | 2521 | 20.09 | 18687.70 | 3141.61 | -1 | MonetDB-1-1-3-1-1 |
| MonetDB-1-1-3-1-2 | MonetDB-1 | MonetDB-1-1-3 | MonetDB-1-1-3-1 | 1 | 3 | 1 | 1 | 100.00 | 22 | 2561 | 20.48 | 18347.50 | 3092.54 | -1 | MonetDB-1-1-3-1-2 |
| MonetDB-1-1-3-1-3 | MonetDB-1 | MonetDB-1-1-3 | MonetDB-1-1-3-1 | 1 | 3 | 1 | 1 | 100.00 | 22 | 2583 | 23.95 | 15672.80 | 3066.20 | -1 | MonetDB-1-1-3-1-3 |
#### Per Phase
| | phase | experiment_run | client | benchmark_run | pod_count | SF | num_of_queries | time [s] | Geo Times [s] | Power@Size [~Q/h] | Throughput@Size | tenant_id |
|:--------------|:--------------|-----------------:|---------:|----------------:|------------:|-------:|-----------------:|-----------:|----------------:|--------------------:|------------------:|------------:|
| MonetDB-1-1-1 | MonetDB-1-1-1 | 1 | 1 | 1 | 1 | 100.00 | 22 | 2360 | 25.85 | 14292.09 | 3355.93 | -1 |
| MonetDB-1-1-2 | MonetDB-1-1-2 | 1 | 2 | 1 | 1 | 100.00 | 22 | 409 | 4.32 | 91829.06 | 19364.30 | -1 |
| MonetDB-1-1-3 | MonetDB-1-1-3 | 1 | 3 | 1 | 3 | 100.00 | 66 | 2583 | 21.44 | 17515.66 | 9198.61 | -1 |
### Latency of Timer Execution [ms]
| Queries | MonetDB-1-1-1-1-1 | MonetDB-1-1-2-1-1 | MonetDB-1-1-3-1-1 | MonetDB-1-1-3-1-2 | MonetDB-1-1-3-1-3 |
|:----------------------------------------------------|--------------------:|--------------------:|--------------------:|--------------------:|--------------------:|
| Pricing Summary Report (TPC-H Q1) | 345510.64 | 83581.58 | 432792.46 | 378821.83 | 365983.77 |
| Minimum Cost Supplier Query (TPC-H Q2) | 37824.54 | 505.99 | 470.61 | 3942.92 | 17136.05 |
| Shipping Priority (TPC-H Q3) | 145121.22 | 4339.19 | 44586.97 | 95130.53 | 94589.62 |
| Order Priority Checking Query (TPC-H Q4) | 150159.15 | 7972.45 | 40322.32 | 39343.29 | 39730.09 |
| Local Supplier Volume (TPC-H Q5) | 13639.77 | 2576.59 | 13308.41 | 13211.69 | 13738.45 |
| Forecasting Revenue Change (TPC-H Q6) | 1588.79 | 1405.06 | 5177.03 | 6929.45 | 5369.13 |
| Volume Shipping Query (TPC-H Q7) | 5796.60 | 4632.13 | 8293.63 | 7398.56 | 9809.22 |
| National Market Share (TPC-H Q8) | 205834.17 | 12995.63 | 202545.83 | 203563.03 | 202491.55 |
| Product Type Profit Measure (TPC-H Q9) | 33555.04 | 3745.82 | 30535.53 | 28597.56 | 28434.93 |
| Returned Item Reporting Query (TPC-H Q10) | 73743.04 | 7570.81 | 39307.30 | 39826.66 | 40103.74 |
| Important Stock Identification (TPC-H Q11) | 7787.35 | 480.61 | 2673.13 | 2584.45 | 2508.71 |
| Shipping Modes and Order Priority (TPC-H Q12) | 3202.88 | 1356.21 | 3709.38 | 3200.02 | 4097.94 |
| Customer Distribution (TPC-H Q13) | 283869.91 | 24407.00 | 225427.59 | 226670.62 | 221943.77 |
| Promotion Effect Query (TPC-H Q14) | 594.09 | 743.50 | 468.74 | 774.16 | 748.18 |
| Top Supplier Query (TPC-H Q15) | 6460.85 | 2143.70 | 3580.36 | 1818.34 | 6571.20 |
| Parts/Supplier Relationship (TPC-H Q16) | 3662.20 | 2986.18 | 5686.28 | 5500.22 | 5497.51 |
| Small-Quantity-Order Revenue (TPC-H Q17) | 531005.84 | 1634.87 | 571005.52 | 571663.09 | 571519.36 |
| Large Volume Customer (TPC-H Q18) | 58555.41 | 7240.35 | 71217.61 | 73774.63 | 71086.52 |
| Discounted Revenue (TPC-H Q19) | 3125.98 | 1207.09 | 4145.76 | 1205.47 | 3916.92 |
| Potential Part Promotion (TPC-H Q20) | 10407.51 | 3100.78 | 15472.85 | 15281.05 | 15004.89 |
| Suppliers Who Kept Orders Waiting Query (TPC-H Q21) | 407037.27 | 212794.11 | 771345.24 | 817269.84 | 840518.32 |
| Global Sales Opportunity Query (TPC-H Q22) | 8983.04 | 1750.74 | 8420.04 | 3403.99 | 1809.50 |
### Errors (failed queries)
No errors
### Warnings (result mismatch)
No warnings
### Monitoring
### Execution phase: SUT deployment
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 1026.48 | 2.98 | 120.02 | 120.02 |
| MonetDB-1-1-2-1 | 822.70 | 4.64 | 192.97 | 192.97 |
| MonetDB-1-1-3-1 | 2823.36 | 5.78 | 253.95 | 255.99 |
### Execution phase: component benchmarker
| DBMS | CPU [CPUs] | Max CPU | Max RAM [Gb] | Max RAM Cached [Gb] |
|:----------------|-------------:|----------:|---------------:|----------------------:|
| MonetDB-1-1-1-1 | 31.55 | 0.34 | 0.38 | 0.39 |
| MonetDB-1-1-2-1 | 31.55 | 0.36 | 0.38 | 0.39 |
| MonetDB-1-1-3-1 | 71.71 | 0.76 | 0.39 | 0.40 |
### Tests
* TEST passed: No SUT container restarts
* TEST passed: Execution phase: SUT deployment contains no 0 or NaN in CPU [CPUs]
* TEST passed: Execution phase: component benchmarker contains no 0 or NaN in CPU [CPUs]
* TEST passed: Geo Times [s] contains no 0 or NaN
* TEST passed: Power@Size [~Q/h] contains no 0 or NaN
* TEST passed: Throughput@Size contains no 0 or NaN
* TEST passed: No SQL errors
* TEST passed: No SQL warnings
* TEST passed: Workflow as planned
The loading times for both instances of loading are the same, since both relate to the same process of ingesting into the database.
Note the added section about volume_size and volume_used in the connections section.