MOT Sample TPC-C Benchmark
Tags: opengauss
Published: 2023-04-19 08:56:10

**TPC-C Introduction**
The TPC-C Benchmark is an industry standard benchmark for measuring the performance of Online Transaction Processing (OLTP) systems. It is based on a complex database and a number of different transaction types that are executed on it. TPC-C is both a hardware‑independent and a software-independent benchmark and can thus be run on every test platform. An official overview of the benchmark model can be found at the tpc.org website here – http://www.tpc.org/default5.asp.

The database consists of nine tables of various structures and thus also nine types of data records. The size and quantity of the data records varies per table. A mix of five concurrent transactions of varying types and complexities is executed on the database, which are largely online or in part queued for deferred batch processing. Because these tables compete for limited system resources, many system components are stressed and data changes are executed in a variety of ways.

Table 1 TPC-C Database Structure

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vwdw0vaqsb784juthfaq.png)

The transaction mix represents the complete business processing of an order – from its entry through to its delivery. More specifically, the provided mix is designed to produce an equal number of new-order transactions and payment transactions and to produce a single delivery transaction, a single order-status transaction and a single stock-level transaction for every ten new-order transactions.

Table 2 TPC-C Transactions Ratio

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6rb8sbfn5eup8rvbffkk.png)

There are two ways to execute the transactions – **as stored procedures** (which allow higher throughput) and in **standard interactive SQL mode**.

**Performance Metric – tpm-C**

The tpm-C metric is the number of new-order transactions executed per minute. Given the required mix and a wide range of complexity and types among the transactions, this metric most closely simulates a comprehensive business activity, not just one or two transactions or computer operations. For this reason, the tpm-C metric is considered to be a measure of business throughput.

The tpm-C unit of measure is expressed as transactions-per-minute-C, whereas “C” stands for TPC-C specific benchmark.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x2vte4hf926caebgbns0.png)

**System-Level Optimization**
Follow the instructions in the MOT Server Optimization – x86 section. The following section describes the key system-level optimizations for deploying the openGauss database on a Huawei Taishan server and on a Euler 2.8 operating system for ultimate performance.

**BenchmarkSQL – An Open-Source TPC-C Tool**
For example, to test TPCC, the **BenchmarkSQL** can be used, as follows –

**·** Download **benchmarksql** from the following link – https://osdn.net/frs/g_redir.php?m=kent&f=benchmarksql%2Fbenchmarksql-5.0.zip
**·** The schema creation scripts in the **benchmarksql** tool need to be adjusted to MOT syntax and unsupported DDLs need to be avoided. The adjusted scripts can be directly downloaded from the following link – https://opengauss.obs.cn-south-1.myhuaweicloud.com/1.0.0/MOT-TPCC-Benchmark.tar.gz. The contents of this tar file includes sql.common.opengauss.mot folder and jTPCCTData.java file as well as a sample configuration file postgresql.conf and a TPCC properties file props.mot for reference.
**·** Place the sql.common.opengauss.mot folder in the same level as sql.common under run folder and replace the file src/client/jTPCCTData.java with the downloaded java file.
**·** Edit the file runDatabaseBuild.sh under run folder to remove **extraHistID** from **AFTER_LOAD** list to avoid unsupported alter table DDL.
**·** Replace the JDBC driver under lib/postgres folder with the openGauss JDBC driver available from the following link – https://opengauss.org/en/download/.

The only change done in the downloaded java file (compared to the original one) was to comment the error log printing for serialization and duplicate key errors. These errors are normal in case of MOT, since it uses Optimistic Concurrency Control (OCC) mechanism.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yvdhbubrm7wby9e9knxk.png)

**Running the Benchmark**
Anyone can run the benchmark by starting up the server and running the **benchmarksql** scripts.

To run the benchmark –

1. Go to the **benchmarksql** run folder and rename sql.common to sql.common.orig.
2. Create a link sql.common to sql.common.opengauss.mot in order to test MOT.
3. Start up the database server.
4. Configure the props.pg file in the client.
5. Run the benchmark.

**Results Report**
**·** Results in CLI

BenchmarkSQL results should appear 
Over time, the benchmark measures and averages the committed transactions. The example above benchmarks for two minutes.

The score is **2.71M tmp-C** (new-orders per-minute), which is 45% of the total committed transactions, meaning the **tpmTOTAL**.

**·** Detailed Result Report

The following is an example of a detailed result report –

Figure 1 Detailed Result Report

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w5f6o1r4ibxome698rzv.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uhcd6eu7sc27gx5974ev.png)

BenchmarkSQL collects detailed performance statistics and operating system performance data (if configured).

This information can show the latency of the queries, and thus expose bottlenecks related to storage/network/CPU.

**·** Results of TPC-C of MOT on Huawei Taishan 2480

Our TPC-C benchmark dated 01-May-2020 with an openGauss database installed on Taishan 2480 server (a 4-socket ARM/Kunpeng server), achieved a throughput of 4.79M tpm‑C.

A near linear scalability was demonstrated, as shown below –

Figure 2 Results of TPC-C of MOT on Huawei Taishan 2480

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/46ztdzxigroelp85wlpx.png)