GBase 8c Performance Optimization: Practical TPC-C Debugging

GBase 8c is an enterprise-level online transaction processing (OLTP) database with features such as multi-mode, high performance, high availability, and high security. Naturally, developers and users are concerned about GBase 8c’s TPC-C performance testing.

What is TPC-C?
“TPC-C is a benchmark specifically designed for online transaction processing systems (OLTP systems), often referred to as business processing systems. It was released in July 1992. Almost all major foreign vendors offering software and hardware platforms in the OLTP market have published corresponding TPC-C test results. With the continuous development of computer technology, these test results are constantly being updated.”

Next, let’s perform a practical operation based on TPC-C testing on GBase 8c.

1. TPC-C Testing Theory

1.1 Standard Origin

The Transaction Processing Performance Council (TPC) is a non-profit organization primarily composed of foreign computer-related software and hardware manufacturers. It manages the formulation of standard specifications for business application benchmarks and performance and price metrics, and regularly publishes test results. Over time, it has developed into a relatively standardized performance testing standard for OLTP. Any manufacturer or tester can establish their own testing systems or platforms based on the specifications provided by TPC.

TPC-C is a specification provided by TPC specifically for OLTP systems (C refers to the C benchmark in TPC), using performance and price metrics. Among these, we focus on tpmC (transactions per minute), which measures the number of new orders processed per minute by the system. Additionally, TPC-C often reflects the system’s price-performance ratio, with a unit of $/tpmC, defined as the system’s total price (default in $)/tpmC value.

1.2 Specification Overview

OLTP systems have distinct characteristics, primarily as follows:

• Multiple transaction processes are executed concurrently, fully reflecting the complexity of transaction processing.
• Both online and offline transaction execution modes are present.
• Multiple online session terminals.
• Moderate system runtime and application runtime.
• A large amount of disk I/O data flow.
• Emphasis on transaction integrity requirements (ACID).
• Access using primary and secondary keys for non-uniformly distributed databases.
• The database consists of many interrelated tables of varying sizes and attributes.
• A significant amount of resource contention between data access and updates.

1.3 Test Model

The TPC-C test general model assumes a large wholesale company with multiple warehouses (Warehouse) distributed in different regions. When the business expands, the company adds new warehouses. Each warehouse is responsible for supplying 10 sales districts (Districts), with each district serving 3000 customers (Customers). Each customer’s order (Order) contains an average of 10 items (Items), with about 1% of the items not being in stock in their respective warehouses and needing to be supplied by other warehouses. Meanwhile, each warehouse maintains inventory records of 100,000 different items sold by the company.

The TPC-C evaluation system database consists of 9 tables, with the relationships depicted in the following diagram:

The diagram shows that 9 entities correspond to the 9 tables in the TPC-C test, and they also correspond to the participants in the wholesale model. A Warehouse is a warehouse, a District is a district, with each Warehouse providing goods to 10 Districts. A Customer is a customer, with each District having 3000 Customers, and each Customer placing at least one Order. Each Order is divided into New-Order and Order-line parts, with New-Order marking whether the order is new; after the order is completed, the records in New-Order are deleted. Order-line records the ordered items for each Order. Stock records the warehouse’s inventory, and Item records the items stored in the warehouse. The “+” sign indicates that there may be more of these corresponding relationships.

1.4 Transaction Description

The test model requires processing the following transaction types in the OLTP system:

• New-Order: A new order transaction input by the customer.
• Payment: Updates the customer’s account balance to reflect their payment status.
• Delivery: Delivery (simulates batch processing transactions).
• Order-Status: Queries the status of the customer’s most recent transaction.
• Stock-Level: Queries the warehouse’s inventory status to ensure timely replenishment.

1.5 Test Metrics

TPC-C test results mainly have two metrics: Throughput (tpmC) and Price/Performance (Price/tpmC).

• Throughput (tpmC): Defined by the TPC organization, this metric describes the number of new order transactions the system can process per minute while simultaneously executing Payment, Order-Status, Delivery, and Stock-Level transactions. All transactions must meet the response time requirements of the TPC-C test specification, and the number of transactions of each type must also meet the specification requirements. A higher throughput value indicates a higher online transaction processing capability of the system.
• Price/Performance (Price/tpmC): The ratio of the overall price of the test system to the throughput metric. Lower prices are better for obtaining the same tpmC value.

2. Practical TPC-C Testing

2.1 Introduction to BenchmarkSQL Tool

BenchmarkSQL is a classic open-source database testing tool embedded with TPC-C test scripts. It supports benchmark testing for various databases, including Oracle, MySQL, PostgreSQL, SQL Server, and others. BenchmarkSQL simulates various transaction processes such as New-Order, Payment, Order-Status, Delivery, and Stock-Level to stress test the database, thereby obtaining performance metrics. Compared to other tools, BenchmarkSQL more accurately simulates real-world application scenarios, making it increasingly popular for database stress testing. Additionally, BenchmarkSQL provides an easy-to-use JDBC benchmarking program, making it an ideal choice for testing database performance, closely aligning with the TPC-C standard for OLTP.

When using BenchmarkSQL for testing, users can configure and run it in a Linux environment. Simple operations allow for performance testing of databases. It supports mainstream foreign databases as well as some domestic databases, demonstrating its wide applicability and adaptability in the field of database performance testing.

2.2 BenchmarkSQL Debugging Based on GBase 8c Database

2.2.1 Test Overview

This example uses GBase 8c V5 S5.0.0 version to conduct TPC-C performance benchmark testing based on GBase 8c. The test environment consists of two servers, both using the Kunpeng 920 CPU architecture and the Kylin V10 operating system. One server runs BenchmarkSQL as the load generator, and the other server hosts the database, with a 10-gigabit network connection between the load generator and the database server routed through a 10-gigabit switch, not directly connected.

2.2.2 Common BenchmarkSQL Configuration Parameters

The following figure shows the commonly used BenchmarkSQL configuration (i.e., the props.gbase file used for data loading and stress testing):

Basic configuration parameters and their meanings:

• db=postgres: Specifies that the target database management system for stress testing is PG-based.
• driver=org.postgresql.Driver: Configures the Java Database Connectivity (JDBC) driver class for PG-based systems.
• conn=…: Configures the connection string for connecting to the database, including the host IP, port, and database name. Additional parameters specify whether to use SQL parsing cache, log level, batch mode, and the amount of data to fetch.
• user=test_gs: Configures the username for authenticating the connection to the database.
• password=gbase;123: Configures the password for the specified user.
• warehouses=1000: Configures the number of warehouses used in the stress test.
• loadWorkers=100: Configures the number of worker threads during the initial data loading phase.
• terminals=1000: Configures the number of simulated terminals (users) during the performance test, i.e., the level of concurrency.
• runTxnsPerTerminal=0: Configures the number of transactions each terminal will execute during the performance test.
• runMins=20: Configures the duration of the performance test, in minutes.
• limitTxnsPerMin=0: Configures the upper limit of transactions that can be executed per minute during the performance test.
• terminalWarehouseFixed=true: Indicates whether terminals should be assigned to fixed warehouses.
• newOrderWeight=45: Configures the weight of NewOrder transactions in the stress test workload.
• paymentWeight=43: Configures the weight of Payment transactions in the stress test workload.
• orderStatusWeight=4: Configures the weight of OrderStatus transactions in the stress test workload.
• deliveryWeight=4: Configures the weight of Delivery transactions in the stress test workload.
• stockLevelWeight=4: Configures the weight of StockLevel transactions in the stress test workload.

2.2.3 Steps for Using BenchmarkSQL with GBase 8c Database

After understanding the common parameters in the BenchmarkSQL configuration file, you can proceed with the TPC-C stress testing on the database. The typical testing process and steps are as follows:

1) Deploy GBase 8c Database

After deploying the GBase 8c database (refer to the “GBase 8c V5_5.0.0_Installation and Deployment Manual” for detailed steps), connect to the default postgres database. For example, execute:

 gsql -p 17400 -d postgres -r

2) Create a Database User

Create a database user test_gs and grant admin privileges:

 create user test_gs identified by 'gbase;123' profile default; alter user test_gs sysadmin;

3) Create a Test Database

Create a test database test_db owned by the user test_gs:

 create database test_db encoding 'UTF8' template=template0 owner test_gs;

4) Configure BenchmarkSQL and Load Data

Configure the BenchmarkSQL tool as needed and use it to load data into the test database. The props.gbase file mentioned here is the BenchmarkSQL configuration file referenced earlier.

 cd benchmarksql-5.0/run ./runDatabaseBuild.sh props.gbase

5) Conduct Stress Testing

After loading the data, continue to use the BenchmarkSQL tool to conduct stress testing on the test database. Again, the props.gbase file is the same BenchmarkSQL configuration file mentioned earlier.

 cd benchmarksql-5.0/run ./runBenchmark.sh props.gbase

6) Check the tpmC Metric

After the testing time reaches the duration set in runMins, the tool will output the tpmC metric.