Demystifying the TPC‑C Benchmark: Model, Schema, and New‑Order Transaction Walkthrough

1. TPC‑C Overview

TPC‑C is a benchmark from the Transaction Processing Performance Council (TPC) introduced in 1992. It measures online transaction processing (OLTP) performance by reporting transactions per minute (tpmC). Vendors submit results to the TPC ranking.

2. Benchmark Specification

Model and Design

The benchmark models a wholesale‑order business. It defines nine entities—Warehouse, District, Customer, Order, New‑Order, Order‑Line, Stock, Item, and History—each mapped to a database table. The specification version used is 5.11.0 (published 2010).

Database Schema

Typical cardinalities: each Warehouse serves 10 Districts; each District has ~3 000 Customers; each Customer creates at least one Order; each Order contains 5‑10 Order‑Line rows. The schema therefore scales to millions of rows when many warehouses are configured.

Transaction Mix

The workload mixes five transaction types with the following approximate percentages:

New‑Order 45 %

Payment 43 %

Delivery 4 %

Order‑Status 4 %

Stock‑Level 4 %

tpmC is measured by the throughput of the New‑Order transaction.

New‑Order Transaction

The New‑Order transaction consists of nine SQL statements that together create a new order and update inventory. The logical steps are:

Retrieve the Customer row (customer, district, warehouse identifiers).

Lock the next order number for the District (SELECT … FOR UPDATE).

Increment the District’s next order number.

Insert a row into the Order table.

Insert a row into the New‑Order table (marks the order as newly created).

For each Order‑Line (5‑10 items):

Fetch the Item row to obtain price and attributes.

Fetch the corresponding Stock row.

Update the Stock quantity (decrement by ordered amount).

Insert a row into the Order‑Line table.

Each step is illustrated in the original specification; the images below show the SQL statements.

Performance Constraints

To prevent vendors from loading all warehouses into memory, each Warehouse is limited to a maximum of 12.86 tpmC. Consequently a 10 000‑warehouse configuration requires at least 500 GB of storage (≈50‑70 MB per warehouse).

3. Reference

Specification download: http://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp