Tool | how to perform TPC-C test on MySQL?

Time:2022-5-29

Author: Ding Yuan, radondb test director

Responsible for the quality performance test and iterative verification of radondb cloud database and container database. Have in-depth research on cloud database and container database performance and high availability solutions.

background

According to the report on the significance of China’s self-developed database reaching the top of TPC-C [1] released by dworks in 2020, more than 67.9% of the respondents said that they would refer to the test results of TPC-C when selecting the database. For users, performance is one of the most important indicators in database selection. As an authoritative test benchmark, TPC-C is a way to directly reflect the performance of software and hardware.

Tool | how to perform TPC-C test on MySQL?

Picture source: 2020 dworks the significance of China self research database reaching the top of TPC-C

Several concepts

An association

TPC (Transaction Processing Performance Council) is a large non-profit organization. TPC mainly formulates the standard specifications, performance and price metrics of the business application standard program (benchmark), and manages the release of test results. Any manufacturer or tester can perform standard performance tests according to the specifications.

A standard

TPC-C is the benchmark program for online transaction processing (OLTP). A performance test specification specifically for online transaction processing system (OLTP). The test results can provide reference standards for users when selecting the corresponding solution platform.

A tool

Tpcc-mysql[2] is a standard specification derived from percona based on TPC-C, which is specially used for MySQL benchmark. It can run on windows, gnu/linux, UNIX and Mac OS systems.

A scene

TPC-C has a representative OLTP simulation scenario:Online order processing system

Suppose there is a large commodity wholesaler with n warehouses located in different regions, each warehouse is responsible for supplying goods to 10 sales points, each sales point has 3000 customers, and each customer has an average order of 10 products. Since it is impossible to store all the goods of the company in one warehouse, some requests must be sent to other warehouses. Therefore, the database is logically distributed. N is a variable parameter, and the tester can change n at will to obtain the best test effect.

Five types of affairs

In this scenario, the TPC-C specification corresponds to five types of transactions:

New-Order Customer enters a new order transaction
Payment Update customer account balance to reflect their payment status
Delivery Shipment (batch transaction)
Order-Status Query the status of the customer’s recent transaction
Stock-Level Query the inventory status of the warehouse so that the goods can be replenished in time

After the test is completed, the throughput and latency of these five types of transactions will be output. However, there are only two TPC-C core performance indicators concerned in the industry:

  • Throughput of new order transactions (TPM)
  • delay

The reason is that the TPC committee focuses onThe ability of the database to handle new orders to reveal the commercial costs of the database. Overall database quotation / TPM = database cost per order. This indicator has a very practical guiding role in measuring the cost performance of a database.

Tool | how to perform TPC-C test on MySQL?

TPC-C simulated business scenario

Next, we will introduce the use of TPC-C tools to simulate business test scenarios.

Environmental preparation

Operating system: Ubuntu 18.04.5 LTS

Container platform: kubesphere v3.1.1

Database: radondb MySQL kubernetes

Tool | how to perform TPC-C test on MySQL?

Kubesphere interface

The environment is ready. Radondb MySQL kubernetes[3] is visible in the kubesphere[4] management interface.

Create test pod

kubectl run -i --tty --rm --image ubuntu test-shell bash
kubectl exec -ti test-shell -c test-shell /bin/bash
apt-get update

Installation tools

First install make, GCC, GIT and other tools

apt-get install make
apt-get install gcc
apt-get install git

Install the MySQL client and development environment required for testing

apt-get install mysql-server
apt-get install libmysqlclient-dev

Source code installation TPCC MySQL

git clone https://github.com/Percona-Lab/tpcc-mysql.git
cd tpcc-mysql/src
make

Data preparation

In a real test scenario, the number of warehouses is generally not recommended to be less than 100, depending on the server hardware configuration. If a high IOPs device such as SSD or PCIe SSD is configured, it is recommended to configure at least 1000 warehouses.

Create user

Create users and authorize them.

mysql> CREATE USER [email protected] IDENTIFIED BY 'mysql_password';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant all privileges on *.* to 'radondb'@'%'identified by 'mysql_password' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Create required library tables

Use the mysqladmin tool to create the test database tpcc1000.

mysqladmin create  tpcc1000  -h server_host   -u mysql_user -p mysql_password

The TPCC MySQL tool comes with the previously described test scenario data tablecreate_table.sqlFile, index fileadd_fkey_idx.sqlDocuments.

mysql -D tpcc1000 -h  server_host  -u mysql_user -p mysql_password < create_table.sql
mysql -D tpcc1000 -h  server_host  -u mysql_user -p mysql_password < add_fkey_idx.sql 

Add data

Using TPCC_ Load tool to add data for the specified database.

./tpcc_load -h server_host  -d tpcc1000 -u mysql_user -p mysql_password -w 20

TPC-C test

Start test

Execute the following command to open a test case.

./tpcc_start -h server_host  -d tpcc1000 -u mysql_user -p mysql_password  -w 20 -c 128 -r 120 -l 200  - >tpcc-output-log

Parameter Description:

parameter explain
-w Specify the warehouse quantity.
-c Specifies the number of concurrent connections.
-r Specify the time for warmup before starting the test. After preheating, the test effect is better.
-l Specify the test duration.
-i Specifies the length of time between report generation.
-f Specifies the file name of the generated report.

Display of test results

Generate chart

Install the drawing tool gnuplot[5] and generate tcpp Gif picture.

yum install -y gnuplot
cat log.conf | gnuplot

Tool | how to perform TPC-C test on MySQL?

The above is the whole process of using container pod to test radondb MySQL database. You can try to adjust the test conditions to obtain more test data.

summary

The test results of TPC-C mainly refer to the two indicators of flow and cost performance.

flow

Throughput, referred to as TPMC for short. According to the definition of TPC, the traffic indicator describes the number of new order transactions processed by the system per minute when the system executes four transactions: payment, order status, delivery and stock level. The response time of all transactions must meet the requirements of TPC-C test specification.

The larger the flow value, the better!

cost performance

Price/performance, or price/tpmc for short. That is, the ratio of the test system price (referring to the quotation in the United States) to the flow index.

The smaller the cost performance, the better!

Reference

[1] : significance of China’s self-developed database reaching the top of TPC-C:https://zhuanlan.zhihu.com/p/…

[2]:TPCC-MySQL:https://github.com/Percona-La…

[3]:RadonDB MySQL Kubernetes:https://github.com/radondb/ra…

[4]:KubeSphere:https://kubesphere.com.cn

[5]:gnuplot:http://www.gnuplot.info