Where the Problem Started

The first question in benchmarking is not which number to measure, but which workload is being evaluated. OLTP and OLAP both talk about database performance, but they judge good performance by different signals. This post organizes the metrics I would look at for each side.

When testing database performance, reliability, and scalability, you need to choose a benchmark type. There are many types, but I will cover them by classifying them broadly into two groups.

In the earlier HammerDB benchmark setup, I tested using TPC-C.

Transactional Benchmark (OLTP)

OLTP stands for Online Transaction Processing. It is transaction-oriented and used in many database architectures, enabling fast and accurate data processing for many frequently interacting services.

In a Transactional Benchmark test, you check the following elements.

First are throughput-related metrics.

  • Transactions Per Second (TPS)

  • Queries Per Second (QPS)

Latency-related

  • Response Time

  • Read/Write Latency

Concurrency-related

  • Lock Time

  • Blocking

Key Ideas

Resource Utilization

  • CPU Usage

  • Memory Usage

  • Disk I/O

After that, there are Error Rates, Scalability, Availability, and so on.

OLTP Benchmarks include TPC-C, Sysbench, and others. In particular, TPC-C is the most representative and generally safe industry-standard benchmark for checking system performance.

Analytical Benchmark (OLAP)

If OLTP is broad and used across many use cases, OLAP is used by data analysts and data engineers for data mining, analytics, and business intelligence. For example, it can be used in financial analysis, weather forecasting, marketing, and so on.

OLTP handles many small transactions, while OLAP handles complex queries.

OLTP usually handles many INSERT, UPDATE, and DELETE operations, while OLAP handles SELECT operations for data aggregation.

Metric Takeaway

Choosing the wrong metric makes a benchmark less useful, not more. OLTP pushes attention toward transaction throughput, latency, and locking, while OLAP makes scan, aggregation, load time, and ETL cost more important. Before talking about performance, the workload needs a name.

The metrics OLAP focuses on are as follows.

Query Performance

  • Query Execution Time

  • Query Throughput

Data Load & Transformation

  • Load Time

  • ETL Time, the time taken for Extract, Transform, Load operations

OLAP Benchmarks include TPC-H, TPC-DS, SSB, and others.