Use of domestic database OpenGauss--TPC-C benchmark tool

This article describes how to use benchmark sql5 0 to test the OLTP performance of opengauss database, and two server performance monitoring tools, htop iostat.

1. Concept introduction

1.1. Introduction to TPC-C

TPC-C is an On-Line Transaction Processing Benchmark.
TPC-C is measured in transactions per minute (tpmC).

TPC-C is a set of benchmark s commonly used in the industry. It is formulated and released by the TPC committee to evaluate the online transaction processing (OLTP) capability of the database.
It mainly involves 10 tables, including five types of business transaction models (NewOrder – new order generation, Payment – order Payment, OrderStatus – recent order query, Delivery – distribution, StockLevel – inventory shortage status analysis).

TPC-C transaction model
TPC-C needs to deal with the following five types of transactions:

  • New order: the customer enters a new order transaction;
  • Payment: update the customer's account balance to reflect its payment status;
  • Delivery: shipment (simulating batch transaction);
  • Order status query: query the status of the customer's recent transaction;
  • Stock level query: query the inventory status of the warehouse so that replenishment can be made in time.

TPC-C measures the maximum effective throughput (MQTh) of the system through the tpmC value (Transactions per Minute), where Transactions are subject to NewOrder Transaction s, that is, the final measurement unit is the number of new orders processed per minute

TPC-C performance measurement index tpmC
Flow indicator (tpmC): according to the definition of TPC organization, flow indicator describes how many new order transactions can be processed by the system every minute while performing four transactions: payment operation, order status query, shipment and inventory status query.
The response time of all transactions must meet the requirements of TPC-C test specification, and the proportion of various transactions should also meet the requirements of TPC-C test specification. In this case, the larger the traffic index value, the higher the online transaction processing capacity of the system.

The model of TPC-C table is as follows:

1.2. BenchmarkSQL

BenchmarkSQL5.0 is the TPCC standard test tool.
benchmark is written in java language and compiled with ant, so you need to install and configure java and ant environment in advance

1.3. htop tools

Htop is a monitoring tool on linux. It can be considered as an enhanced version of top. During the operation of tpcc, it is recommended to use htop to monitor the resource status

Htop one click Install using yum source yum install htop

1.4. iostat tool

iostat tool monitors the system disk and analyzes the read and write status of the disk
htop one click Install sysstat using yum source

2. Test environment preparation

2.1. Install java/ant

Install JDK 1.8 x,ant 1.9.x. And configure the environment variables to compile benchmark sql5 with ant 0 and execute the java program

2.2. benchmark installation

2.2.1. benchmark Download

It can be downloaded from the following two addresses

2.2.2. Replace jdbc driver jar package

Enter the unzipped directory benchmark sql-5.0/lib/postgres

  • from Official website Download the corresponding jdbc driver package, extract it and put it in this directory
  • Back up or delete the self-contained postgresql driver jar package [otherwise, an error will be reported: 10 authentication type is not supported. Please check that you have configured pg_hba.conf file to contain the IP address or network section of the client, and that the authentication architecture mode supported by the driver is supported.]

2.2.3. compile

Enter the root directory of benchmark sql-5.0 and enter the ant command to compile.

3. Test TPC-C performance

3.1. Create tpcc database and database users

create user tpcc identified by 'test@123' profile default; 
alter user tpcc sysadmin; 
grant all privilege to tpcc;
create database tpccdb encoding 'UTF8' template=template0 ;

3.2. Configure props PG file

//db refers to the type of database. Currently, postgres and oracle are supported

//The number of warehouses, and the size of each warehouse is about 100MB
//The number of load processes used to initialize data in the database

//Number of terminals, i.e. number of concurrent clients. The maximum concurrent quantity set here corresponds to the maximum number of work on the server. 
//The number of fixed transactions run by each terminal,
//For example, if the value is set to 10, it means that each terminal runs 10 transactions. If there are 32 terminals, the test ends after 320 transactions are run as a whole.
//When this parameter is configured as a non-zero value, the following runMins parameter must be set to 0
//The overall time to be tested, in minutes,
//If runMins is set to 60, the test will last for 1 hour.
//When the value is set to a non-zero value, the runTxnsPerTerminal parameter must be set to 0.
//These two parameters cannot be set to positive integers at the same time. If one of them is set, the other must be 0,
//The main difference is that runMins defines the time length to control the test time; runTxnsPerTerminal defines the total number of transactions to control time.
//Limit the total number of transactions per minute. This parameter mainly controls the number of transactions processed per minute. The number of transactions is affected by the terminals parameter, which is 300 by default.
//If the number of terminals is greater than the limitTxnsPerMin value, it means that the number of concurrent transactions is greater than the total number of transactions per minute, and this parameter will become invalid,
//If terminals is set to 1000 and limittxnspermin is set to 300, then 1000 concurrent transactions are initiated at the same time, then setting the number of transactions per minute to 300 is meaningless,
//Therefore, to make this parameter valid, you can set the number to be greater than the concurrent number, or invalidate it

//The binding mode of terminal and warehouse can be run when it is set to true. 4 X compatibility mode means that each terminal has a fixed warehouse.
//When set to false, the overall database configuration can be used uniformly.
//TPCC stipulates that each terminal must have a bound warehouse, so the default value of true is generally used.
//The sum of the following five values is 100.
//The default value is 45, 43, 4, 4 & 4, which is consistent with the proportion defined in TPC-C test. During actual operation, the specific weight can be adjusted to adapt to various scenarios.

//The test data generation directory does not need to be modified by default. It is generated under the run directory by default. The name is like my_result_xxxx folder.
//Cancel this content by commenting.
//The operating system performance collection script does not need to be modified by default. The operating system needs to have a python environment
//The collection interval of the operating system is 1 second by default
//The operating system collects the corresponding host. If the local database is tested, the parameter can be annotated,
//If you want to test the remote server, fill in the user name and host name.
//The network card name and disk name of the collected server in the operating system,
//For example, if you use ifconfig to view the operating system network card name, ens2f1, then the following network card name is set to net_ens2f1 (net# prefix fixed);
//Use df -h or fdisk -l to view the disk mount directory, such as / dev/sdc, / dev/sde, and set the following disk name to blk_sda1 (blk# prefix fixed)
//osCollectorDevices=net_ens2f1 blk_nvme0n1 blk_sdc blk_sde

3.3. Replace table creation SQL

Replace benchmark sql-5.0/run/sql.xml with the following common/tableCreates. The contents of SQL.
Two tablespaces and some additional data attributes are mainly added to increase disk IO during testing

During the performance test, in order to increase IO throughput, pg_ The data under xlog, tablespace 2 and tablespace 3 are scattered on different storage media, and the soft connection pointing to the real location is given at the original location.
pg_xlog is located in the database directory, and tablespace 2 and tablespace 3 are located in the database directory PG respectively_ Location.

CREATE TABLESPACE example2 relative location 'tablespace2';
CREATE TABLESPACE example3 relative location 'tablespace3';
create table bmsql_config (
  cfg_name    varchar(30),
  cfg_value   varchar(50)
create table bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9)
create table bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9)
create table bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  c_payment_cnt  integer,
  c_delivery_cnt integer,
  c_street_1     varchar(20),
  c_street_2     varchar(20),
  c_city         varchar(20),
  c_state        char(2),
  c_zip          char(9),
  c_phone        char(16),
  c_since        timestamp,
  c_middle       char(2),
  c_data         varchar(500)
) WITH (FILLFACTOR=80) tablespace example2;
create sequence bmsql_hist_id_seq;
create table bmsql_history (
  hist_id  integer,
  h_c_id   integer,
  h_c_d_id integer,
  h_c_w_id integer,
  h_d_id   integer,
  h_w_id   integer,
  h_date   timestamp,
  h_amount decimal(6,2),
  h_data   varchar(24)
create table bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
create table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp
create table bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   not null,
  ol_number       integer   not null,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
create table bmsql_item (
  i_id     integer      not null,
  i_name   varchar(24),
  i_price  decimal(5,2),
  i_data   varchar(50),
  i_im_id  integer
create table bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  s_order_cnt  integer,
  s_remote_cnt integer,
  s_data       varchar(50),
  s_dist_01    char(24),
  s_dist_02    char(24),
  s_dist_03    char(24),
  s_dist_04    char(24),
  s_dist_05    char(24),
  s_dist_06    char(24),
  s_dist_07    char(24),
  s_dist_08    char(24),
  s_dist_09    char(24),
  s_dist_10    char(24)
) WITH (FILLFACTOR=80) tablespace example3;

3.4. Run benchmark

Just pay attention to tpmTOTAL and tpmC

cd benchmarksql-5.0/run
## Generate test data
## In order to facilitate multiple tests and reduce the time of importing data, the database can be backed up by stopping the database and copying the whole data directory once.
## TPCC test

##Clear test data

The test results are similar to the following figure. Under the single machine of OpenGauss, the tpmC can reach up to 1.5 million

3.5. Test auxiliary SQL or command

# View the database footprint,
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database

# View the number of records in each table in tpccdb
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc;

  • View the configuration of NUMA through numactl or bind the process to the specified CPU core
  • Observe the status of each NUMA node through the numastat command.
    • numa_hit indicates the number of times the CPU core in the node accesses local memory.
    • numa_miss indicates the number of times the node kernel accesses the memory of other nodes. Memory access across nodes will have high latency, which will reduce performance. Therefore, NUMA_ The value of miss should be as low as possible. If it is too high, binding should be considered.
# numa installation
yum -y install numactl numastat

# View the NUMA configuration of the current server
numactl -H

#The process is bound to the specified CPU core
#Use htop to monitor the CPU utilization of database server and tpcc client. Under the best performance test, the CPU utilization of each business is very high (> 90%).
#If the CPU utilization fails to meet the standard, it may be due to the wrong binding method or other problems. You need to locate the root cause for adjustment.
numactl -C 0-13,28-41,14-27,42-55 ./

# View the memory access hit rate of the current NUMA node

# Check the usage of each CPU through htop 

As can be seen from the numactl execution result, the sample server is divided into two NUMA nodes. Each node contains 28 CPU core s, and the memory size of each node is about 128GB. At the same time, the command also gives the distance between different nodes. The farther the distance, the greater the delay of cross NUMA memory access. Application runtime should reduce memory access across NUMA.

reference resources

Test TPCC performance

Keywords: Database benchmark opengauss

Added by jb60606 on Wed, 05 Jan 2022 02:56:23 +0200