SQL 129 🛢️ Benchmarking SQL Performance (sysbench, TPC-C, TPC-H) - KandZ

⏱️ Estimated read: 12 min | |

Database benchmarking is the discipline of measuring a system’s performance under a workload that is defined, repeatable, and representative of the use case. The three tools that dominate the conversation serve different purposes. sysbench is a lightweight, multi-threaded benchmark tool for quick impressions of system performance, with built-in tests for CPU, memory, file I/O, and database drivers for MySQL and PostgreSQL . TPC-C is the industry-standard OLTP benchmark, simulating an order-entry environment with a mix of five concurrent transactions and measuring throughput in tpmC (New-Order transactions per minute) . TPC-H is the decision-support benchmark, executing 22 complex analytical queries against a normalized schema and measuring throughput in QphH@Size (queries per hour at a given scale factor) . The choice between them is determined by the workload being evaluated: a transaction-processing system is measured with TPC-C, an analytics system with TPC-H, and a quick sanity check with sysbench.

Key point: TPC-C and TPC-H measure fundamentally different things. TPC-C is a branchy, random-access, write-heavy OLTP workload. TPC-H is a sequential-scan, large-result-set, read-heavy OLAP workload . A system optimized for one is not necessarily optimized for the other, and a benchmark result from one does not predict performance on the other.

Why three different benchmarking tools exist

The quick-check problem. A full TPC-C or TPC-H benchmark requires a specific schema, a defined data population, a driver that generates the required transaction mix, and a compliance audit if the results are to be published. This is necessary for official benchmarks but impractical for a developer who needs a quick answer to “is this configuration faster?” sysbench exists for that question. It runs a specified number of threads, executes requests in parallel, and reports throughput and latency without the ceremony of a TPC benchmark .

The OLTP standard problem. TPC-C is the benchmark that database vendors cite when they claim transaction throughput. It is not a synthetic microbenchmark; it simulates a complete computing environment with multiple terminal sessions, a mix of transaction types, and specific response-time requirements. The New-Order transaction must complete within 5 seconds, and the throughput metric counts how many New-Order transactions the system can process per minute while executing the other four transaction types . The result is a number that is comparable across systems because the workload is precisely defined.

The OLAP standard problem. TPC-H answers the analytics question. It loads a normalized schema with data proportional to a scale factor, executes 22 queries that vary in complexity from simple aggregations to multi-table joins with correlated subqueries, and measures how many queries the system can complete per hour . The scale factor determines the data volume: SF=1 is approximately 1 GB, SF=100 is approximately 100 GB, and SF=1000 is approximately 1 TB . The QphH metric reflects both the query execution time and the data loading time.

a. sysbench: Quick and Configurable

sysbench is a modular tool. The --test flag selects the workload, and the database driver is selected with --db-driver. The database tests (oltp_read_only, oltp_write_only, oltp_read_write, oltp_point_select, and others) generate SQL against a table populated by the prepare command .

# Prepare a table with 10,000 rows
sysbench oltp_read_write --db-driver=pgsql --pgsql-host=localhost \
  --pgsql-user=postgres --pgsql-db=test --tables=1 --table-size=10000 prepare

# Run a read-write benchmark for 60 seconds with 4 threads
sysbench oltp_read_write --db-driver=pgsql --pgsql-host=localhost \
  --pgsql-user=postgres --pgsql-db=test --threads=4 --time=60 run

# Clean up
sysbench oltp_read_write --db-driver=pgsql --pgsql-host=localhost \
  --pgsql-user=postgres --pgsql-db=test cleanup

The output includes transactions per second, queries per second, and latency percentiles. The --report-interval flag produces intermediate reports, and the --percentile flag controls which percentile is reported in the summary . sysbench is not a TPC-compliant benchmark; it does not enforce a transaction mix or a response-time requirement. It is a measurement tool, not a certification.

The value of sysbench is its speed and flexibility. A developer can test a schema change, a configuration parameter, or a hardware difference in minutes. The limitation is that the workload is synthetic and the results are not comparable to published TPC numbers.

b. TPC-C: The OLTP Standard

TPC-C simulates a wholesale supplier with a population of users executing transactions against a database. The workload consists of five transaction types, each with a defined percentage of the total mix :

Transaction	Percentage	Description
New-Order	45%	Enter a new order
Payment	43%	Process a payment
Order-Status	4%	Query order status
Delivery	4%	Batch delivery processing
Stock-Level	4%	Check warehouse stock

The primary metric is tpmC, the number of New-Order transactions per minute. The New-Order response time requirement is 5 seconds . The benchmark also requires specific data distribution characteristics: non-uniform access through primary and secondary keys, contention on data access and update, and significant disk I/O .

Running TPC-C requires a driver that implements the transaction mix and the terminal emulation. HammerDB is the most common open-source driver, and it is used by tools like bexhoma to orchestrate TPC-C experiments against PostgreSQL . The driver handles the schema creation, data loading, and transaction generation. The benchmark result is the tpmC number reported by the driver.

The interpretation of tpmC depends on the scale factor. A small-scale test with 100 warehouses produces a different number than a large-scale test with 10,000 warehouses. The scale factor determines the data volume and the contention characteristics. A result without the scale factor is not meaningful.

c. TPC-H: The Analytics Standard

TPC-H is a decision-support benchmark. It loads a normalized schema with eight tables—the largest are LINEITEM (6 million rows at SF=1) and ORDERS (1.5 million rows at SF=1)—and executes 22 queries that vary in complexity . The queries are generated from templates with substitution parameters, so the exact query text differs between runs while the structure remains the same.

The primary metric is QphH@Size, the composite queries-per-hour metric at a given scale factor . The metric reflects the query execution time, the data loading time, and the throughput under both single-user and multi-user conditions. The scale factor is part of the metric: a system that achieves 20,000 QphH at SF=1 is not directly comparable to a system that achieves 200 QphH at SF=100.

The workload characteristics are the inverse of TPC-C. TPC-H queries perform sequential scans over large tables, join multiple tables, and return large result sets. The workload is read-heavy and predictable . A system tuned for TPC-H is optimized for I/O bandwidth, parallel query execution, and large memory for caching.

Running TPC-H requires a data generator (DBGEN) and a query generator (QGEN), both provided by the TPC . The driver executes the queries and records the time. Tools like bexhoma can orchestrate TPC-H experiments alongside TPC-C, providing a consistent environment for both .

Complete Example Session

# ============================================
# PART 1: SYSBENCH PREPARE
# ============================================
# Create the test table with 10,000 rows
sysbench oltp_read_write --db-driver=pgsql \
  --pgsql-host=localhost --pgsql-user=postgres --pgsql-db=bench \
  --tables=1 --table-size=10000 prepare


# ============================================
# PART 2: SYSBENCH RUN
# ============================================
# Run a 60-second read-write benchmark with 4 threads
sysbench oltp_read_write --db-driver=pgsql \
  --pgsql-host=localhost --pgsql-user=postgres --pgsql-db=bench \
  --threads=4 --time=60 --report-interval=10 run


# ============================================
# PART 3: SYSBENCH CLEANUP
# ============================================
sysbench oltp_read_write --db-driver=pgsql \
  --pgsql-host=localhost --pgsql-user=postgres --pgsql-db=bench cleanup


# ============================================
# PART 4: TPC-C WITH HAMMERDB
# ============================================
# HammerDB TCL script for PostgreSQL TPC-C
# (conceptual; actual script is provided by HammerDB)
# dbset db pg
# dbset bm TPC-C
# diset connection pg_host localhost
# diset connection pg_port 5432
# diset tpcc pg_count_ware 10
# diset tpcc pg_num_vu 4
# buildschema
# vuset vu 4
# vucreate
# vurun


# ============================================
# PART 5: TPC-H WITH BEXHOMA
# ============================================
# Orchestrate TPC-H against PostgreSQL in Kubernetes
python tpch.py -ms 1 -dbms PostgreSQL run
# This installs PostgreSQL, generates data, runs 22 queries


# ============================================
# PART 6: TPC-H SCALE FACTOR
# ============================================
# SF=1: ~1 GB, SF=10: ~10 GB, SF=100: ~100 GB
# The scale factor is part of the QphH metric
# A result without SF is not comparable


# ============================================
# PART 7: TPC-C TRANSACTION MIX CHECK
# ============================================
# The driver must produce the defined mix:
# New-Order: 45%, Payment: 43%, Order-Status: 4%
# Delivery: 4%, Stock-Level: 4%
# A driver that deviates from this is not TPC-C


# ============================================
# PART 8: INTERPRETING SYSBENCH OUTPUT
# ============================================
# Look for:
# - transactions per second (TPS)
# - queries per second (QPS)
# - 95th percentile latency
# - errors (should be 0)


# ============================================
# PART 9: INTERPRETING TPC-C OUTPUT
# ============================================
# tpmC = New-Order transactions per minute
# Must be accompanied by scale factor (warehouses)
# New-Order response time must be < 5 seconds


# ============================================
# PART 10: INTERPRETING TPC-H OUTPUT
# ============================================
# QphH@Size = queries per hour at scale factor
# Includes query time and load time
# Single-user and multi-user streams both matter

The ten parts trace the three tools from setup through execution to interpretation. Parts 4 and 5 show the driver-based approach for TPC benchmarks; the actual execution requires the driver (HammerDB or bexhoma) to be installed and configured.

Quick Reference

Tool Comparison

Aspect	sysbench	TPC-C	TPC-H
Purpose	Quick performance check	OLTP standard	OLAP standard
Workload	Configurable synthetic	5-transaction mix	22 analytical queries
Primary metric	TPS, QPS, latency	tpmC	QphH@Size
Scale factor	Table size	Warehouses	SF (data volume)
Compliance	None	TPC-audited	TPC-audited

TPC-C Transaction Mix

Transaction	Percentage	Response Requirement
New-Order	45%	5 seconds
Payment	43%	5 seconds
Order-Status	4%	5 seconds
Delivery	4%	5 seconds (deferred)
Stock-Level	4%	5 seconds

TPC-H Scale Factors

SF	Approximate Size	Use Case
1	~1 GB	Development, testing
10	~10 GB	Small production
100	~100 GB	Medium production
1000	~1 TB	Large enterprise

sysbench Database Options

Option	Purpose
`--db-driver=pgsql`	PostgreSQL driver
`--db-driver=mysql`	MySQL driver
`--tables=N`	Number of tables
`--table-size=N`	Rows per table
`--threads=N`	Concurrent threads
`--time=N`	Duration in seconds

Best Practices

✅ Do This:

# Use sysbench for quick configuration comparisons
sysbench oltp_read_write --threads=4 --time=60 run                       # ✅

# Report the scale factor with every TPC-C result
# tpmC without warehouses is not meaningful                              # ✅

# Report the scale factor with every TPC-H result
# QphH without SF is not comparable                                      # ✅

# Use HammerDB or bexhoma for TPC-C and TPC-H
# Do not attempt to implement the transaction mix manually               # ✅

❌ Don’t Do This:

# Compare sysbench TPS to TPC-C tpmC
# They measure different workloads and are not comparable                # ❌

# Run TPC-C with a modified transaction mix
# The result is not TPC-C                                                # ❌

# Run TPC-H at SF=1 and claim it represents enterprise performance
# SF=1 is a development scale, not a production scale                    # ❌

# Ignore response time requirements in TPC-C
# A high tpmC with >5s New-Order latency is not a valid result           # ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
sysbench result not comparable to TPC	Different workload	Use TPC drivers for TPC-comparable results
TPC-C tpmC without scale factor	Missing context	Always report warehouses
TPC-H QphH without SF	Missing context	Always report scale factor
TPC-C driver deviates from mix	Custom driver	Use HammerDB or compliant driver
Benchmark on cold cache	No warmup	Run a warmup phase before measurement

Real-World Examples

1. sysbench Prepare

sysbench oltp_read_write --db-driver=pgsql --table-size=10000 prepare

2. sysbench Run

sysbench oltp_read_write --threads=4 --time=60 run

3. sysbench Cleanup

sysbench oltp_read_write cleanup

4. TPC-C with HammerDB

HammerDB TCL script: dbset bm TPC-C, buildschema, vurun

5. TPC-H with bexhoma

python tpch.py -ms 1 -dbms PostgreSQL run

6. TPC-C Transaction Mix

New-Order 45%, Payment 43%, Order-Status 4%, Delivery 4%, Stock-Level 4%

7. TPC-H Query Count

22 queries generated from templates with substitution parameters

8. TPC-H Scale Factor

SF=1: ~1GB, SF=100: ~100GB, SF=1000: ~1TB

9. TPC-C Metric

tpmC = New-Order transactions per minute

10. TPC-H Metric

QphH@Size = composite queries per hour at scale factor

Visual

The Benchmark Landscape

┌──────────────────────────────────────────────────────────────┐
│  WORKLOAD TYPE                                               │
│                                                              │
│  ┌─────────────────┐        ┌─────────────────┐             │
│  │ OLTP            │        │ OLAP            │             │
│  │ Transactional   │        │ Analytical      │             │
│  │ Random access   │        │ Sequential scan │             │
│  │ Write-heavy     │        │ Read-heavy      │             │
│  └────────┬────────┘        └────────┬────────┘             │
│           │                          │                       │
│           ▼                          ▼                       │
│  ┌─────────────────┐        ┌─────────────────┐             │
│  │ TPC-C           │        │ TPC-H           │             │
│  │ tpmC            │        │ QphH@Size       │             │
│  │ HammerDB        │        │ DBGEN/QGEN      │             │
│  └─────────────────┘        └─────────────────┘             │
│                                                              │
│  ┌─────────────────────────────────────────┐                │
│  │ SYSBENCH                                │                │
│  │ Quick, configurable, not TPC-compliant  │                │
│  └─────────────────────────────────────────┘                │
│                                                              │
│  Choose the tool that matches the workload and the           │
│  level of rigor required.                                    │
│                                                              │
└──────────────────────────────────────────────────────────────┘

TPC-C Transaction Flow

┌──────────────────────────────────────────────────────────────┐
│  TERMINAL EMULATION                                          │
│                                                              │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ Transaction Mix                                         ││
│  │                                                         ││
│  │ New-Order    ████████████████████████████ 45%           ││
│  │ Payment      ███████████████████████████ 43%            ││
│  │ Order-Status ██ 4%                                      ││
│  │ Delivery     ██ 4%                                      ││
│  │ Stock-Level  ██ 4%                                      ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ Metric: tpmC (New-Order transactions per minute)        ││
│  │ Constraint: New-Order response < 5 seconds              ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  The mix is defined by the TPC specification.                │
│  A driver that changes the mix is not running TPC-C.         │
│                                                              │
└──────────────────────────────────────────────────────────────┘

TPC-H Scale Factor and Query Complexity

┌──────────────────────────────────────────────────────────────┐
│  SCALE FACTOR          DATA SIZE        QUERY TIME          │
│                                                              │
│  SF=1                  ~1 GB            Seconds             │
│  SF=10                 ~10 GB           Tens of seconds     │
│  SF=100                ~100 GB          Minutes             │
│  SF=1000               ~1 TB            Tens of minutes     │
│                                                              │
│  22 queries, varying complexity:                             │
│  Q1, Q6: simple aggregation (~100ms at SF=1)                │
│  Q3, Q5, Q10: multi-table joins (~1-10s at SF=1)            │
│  Q2, Q9, Q21: correlated subqueries (~10s+ at SF=1)         │
│                                                              │
│  The QphH metric reflects both query time and load time.     │
│                                                              │
└──────────────────────────────────────────────────────────────┘

sysbench vs. TPC

┌──────────────────────────────────────────────────────────────┐
│  SYSBENCH                      TPC-C / TPC-H                 │
│                                                              │
│  ┌─────────────────┐            ┌─────────────────┐         │
│  │ Synthetic       │            │ Defined         │         │
│  │ workload        │            │ transaction mix │         │
│  └─────────────────┘            └─────────────────┘         │
│                                                              │
│  ┌─────────────────┐            ┌─────────────────┐         │
│  │ Configurable    │            │ Fixed by        │         │
│  │ table size      │            │ specification   │         │
│  └─────────────────┘            └─────────────────┘         │
│                                                              │
│  ┌─────────────────┐            ┌─────────────────┐         │
│  │ Not audited     │            │ Audited for     │         │
│  │                 │            │ official results│         │
│  └─────────────────┘            └─────────────────┘         │
│                                                              │
│  sysbench answers "is this faster?"                          │
│  TPC answers "how does this compare to the industry?"        │
│                                                              │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
sysbench	Multi-threaded benchmark tool, MySQL/PostgreSQL drivers
sysbench tests	`oltp_read_write`, `oltp_point_select`, `fileio`, `cpu`
TPC-C	OLTP benchmark, 5-transaction mix, tpmC metric
TPC-C New-Order	45% of mix, 5-second response requirement
TPC-H	OLAP benchmark, 22 queries, QphH@Size metric
TPC-H SF=1	~1 GB, ~8.7M rows
TPC-C driver	HammerDB
TPC-H driver	DBGEN/QGEN, HammerDB

Key takeaways:

The three tools answer different questions. sysbench answers “is this configuration faster?” with a quick, synthetic workload. TPC-C answers “how many OLTP transactions per minute?” with a defined mix and response-time requirements. TPC-H answers “how many analytical queries per hour?” with a defined schema and query set .
TPC-C and TPC-H are not comparable. TPC-C is a branchy, random-access, write-heavy OLTP workload. TPC-H is a sequential-scan, large-result-set, read-heavy OLAP workload. A system optimized for one is not necessarily optimized for the other .
The scale factor is part of the result. A tpmC number without the warehouse count is not meaningful. A QphH number without the scale factor is not comparable. The scale factor determines the data volume and the contention characteristics .
sysbench is for iteration; TPC is for comparison. sysbench is fast and flexible, which makes it suitable for testing a schema change or a configuration parameter. TPC-C and TPC-H are defined and audited, which makes their results comparable across systems and vendors.
Use a compliant driver for TPC benchmarks. HammerDB is the standard open-source driver for TPC-C and TPC-H. Implementing the transaction mix manually is error-prone and produces results that are not TPC-compliant .

Remember: Benchmarking is not a single activity; it is a choice of workload and a choice of rigor. sysbench gives a quick answer to a local question. TPC-C and TPC-H give a standardized answer to an industry question. The numbers are not interchangeable, and a result from one does not predict performance on another. The scale factor, the transaction mix, and the response-time requirements are part of the definition, not optional details. A benchmark result without them is a number without context, and a number without context cannot inform a decision.

Was this helpful?

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!