Kunpeng Database Solution. Test Guides. Issue 01 Date HUAWEI TECHNOLOGIES CO., LTD.

Test Guides

Issue 01

No part of this document may be reproduced or transmitted in any form or by any means without prior written consent of Huawei Technologies Co., Ltd.

Trademarks and Permissions

and other Huawei trademarks are trademarks of Huawei Technologies Co., Ltd.

All other trademarks and trade names mentioned in this document are the property of their respective holders.

Notice

The purchased products, services and features are stipulated by the contract made between Huawei and the customer. All or part of the products, services and features described in this document may not be within the purchase scope or the usage scope. Unless otherwise specified in the contract, all statements, information, and recommendations in this document are provided "AS IS" without warranties, guarantees or representations of any kind, either express or implied.

The information in this document is subject to change without notice. Every effort has been made in the preparation of this document to ensure accuracy of the contents, but all statements, information, and recommendations in this document do not constitute a warranty of any kind, express or implied.

Huawei Technologies Co., Ltd.

Address: Huawei Industrial Base Bantian, Longgang Shenzhen 518129

People's Republic of China Website: https://www.huawei.com

Contents

1 BenchMarkSQL User Guide (CentOS 7.6)... 1

1.1 BenchmarkSQL Introduction... 1

1.2 Configuring the BenchmarkSQL Environment... 1

1.3 Using BenchmarkSQL... 2

1.3.1 Testing MySQL... 2

1.3.2 Testing PostgreSQL...4

2 Sysbench 0.5 Porting Guide (CentOS 7.6)... 8

2.1 Introduction... 8

2.2 Environment Requirements... 8

2.3 Installing Dependency Packages... 9

2.4 Obtaining the Source Code... 10

2.5 Compiling and Installing Sysbench... 11

2.6 Running and Verifying Sysbench... 12

3 TPC-H Test Guide (for MySQL)... 18

3.1 TPC-H... 18

3.2 Environment Requirements... 18

3.3 Using TPC-H...19

3.3.1 Obtaining the TPC-H Tool Package...19

3.3.2 Compiling and Installing the Tool Package... 20

3.3.3 Performing a TPC-H Test... 26

4 HammerDB Test Guide...28

4.1 Introduction to HammerDB... 28

4.2 Installing HammerDB... 28

4.3 Testing HammerDB... 29

4.4 Troubleshooting... 35

5 YCSB Test Guide (for MongoDB)... 40

5.1 Introduction... 40

5.2 Installing YCSB... 40

5.3 Testing YCSB... 42

1

BenchMarkSQL User Guide (CentOS 7.6)

1.1 BenchmarkSQL Introduction

1.2 Configuring the BenchmarkSQL Environment 1.3 Using BenchmarkSQL

1.1 BenchmarkSQL Introduction

BenchmarkSQL is an open-source database test tool with embedded TPC-C test scripts. It can be used to test PostgreSQL, MySQL, Oracle, and SQL Server databases. It uses Java Database Connectivity (JDBC) to perform Online TPC-C Transaction Processing (OLTP) tests.

1.2 Configuring the BenchmarkSQL Environment

Step 1 Obtain the installation package.

1. Download the BenchMarkSQL.zip installation package.

2. The BenchmarkSQL requires the Java environment. Therefore, you need to install the Java Development Kit (JDK).

a. Configure a local YUM source and mount the OS image. For details, see the MySQL 8.0.17 Porting Guide (CentOS 7.6).

b. Install RPM packages.

#yum install java

Step 2 Upload the BenchMarkSQL.zip package to the /home directory on the client

server.

Step 3 Go to the directory where the package is uploaded and decompress the package. #cd /home

#unzip BenchMarkSQL.zip ----End

1.3 Using BenchmarkSQL

1.3.1 Testing MySQL

Step 1 Log in to MySQL and create a database tpcc. >create database tpcc;

Step 2 Modify the configuration file.

1. Go to the run directory (/home/BenchMarkSQL/run in this example) of BenchmarkSQL and modify the configuration file according to the actual situation.

# cd /home/BenchMarkSQL/run # cp props.mysql my_mysql.properties # vi my_mysql.properties

2. Modify parameters according to the following table. You can retain the default values of other parameters.

Table 1-1 Database information

Parameter Description

conn 222.222.222.

216 Database server address. Set the valueaccording to the actual situation.

3306 MySQL database port. Set the value according

to the actual situation.

tpcc200 Database name (tpcc in this example). Set the value according to the actual situation. user/

password - Set the values to the user that creates thedatabase and its password.

warehouses - Number of data warehouses to be created

during initialization data loading. For example, the value 100 indicates that 100 data warehouses are created. The data volume of each data warehouse is about 76823.04 KB. The data volume may be slightly different because data entries may be added or deleted during the test.

loadworker - Number of processes submitted each time

Table 1-2 Program running parameters

Parameter Description

terminals Number of concurrent terminals, which the

concurrency level.

runTxnsPerTerminal Number of transactions executed by each terminal per minute.

runMins Execution duration, in minutes.

limitTnxsPermin Total number of transactions executed per minute.

terminalWarehouse-Fixed Binding mode of terminals and warehouses. If thisparameter is set to true, the 4.x compatibility mode is used (each terminal has a fixed

warehouse). If this parameter is set to false, the terminals can randomly access the warehouses.

CA UTION

The runMins and runTxnsPerTerminal parameters specify two running modes. runMins specifies the running time, and runTxnsPerTerminal specifies the number of transactions on each terminal. The two parameters cannot be enabled at the same time, and one of them must be set to 0.

1. Run the following command in the run directory to grant the execute permission on the file:

#chmod 777 *.sh CA UTION

If the required permission has been granted, skip this step. 2. Load data.

#./runDatabaseBuild.sh my_mysql.properties

* Create and initialize tables. The tables include warehouse, Stock, Item, Order-Line, New-Order, History, District, Customer, Order, and a configuration table.

Step 4 Run the BenchmarkSQL program.

#./runBenchmark.sh my_mysql.properties

After the execution is complete, pay attention to the tpmTOTAL value.

Step 5 Delete the database and data.

#./runDatabaseDestroy.sh my_mysql.properties ----End

1.3.2 Testing PostgreSQL

Step 1 Create the database users tpcc and database tpcc in the PostgreSQL, and grant

the superuser permission to the tpcc user.

>create database tpcc; Step 2 Modify the configuration file.

1. Access the run directory of the BenchmarkSQL and modify the configuration file according to the actual situation.

# cd /home/BenchMarkSQL/run/ # cp props.pg my_postgres.properties # vi my_postgres.properties

2. Modify parameters according to the following table. You can retain the default values of other parameters.

Table 1-3 Database information

Parameter Description

conn localhost Database server address. Set the value

according to the actual situation.

5432 PostgreSQL database port. Set the value

according to the actual situation.

postgres Database name (tpcc in this example). Set the value according to the actual situation. user/

password - Set the values to the user that creates thedatabase and its password. warehous

es - Number of data warehouses to be createdduring initialization data loading. For example, the value 100 indicates that 100 data warehouses are created. The data volume of each data warehouse is about 76823.04 KB. The data volume may be slightly different because data entries may be added or deleted during the test. loadworke

r - Number of processes submitted each timewhen data is loaded.

Table 1-4 Program running parameters

Parameter Description

terminals Number of concurrent terminals, which the

concurrency level.

runTxnsPerTerminal Number of transactions executed by each terminal per minute.

runMins Execution duration, in minutes.

Parameter Description

terminalWarehouse-Fixed Binding mode of terminals and warehouses. If thisparameter is set to true, the 4.x compatibility mode is used (each terminal has a fixed warehouse). If this parameter is set to false, the terminals can

randomly access the warehouses.

CA UTION

The runMins and runTxnsPerTerminal parameters specify two running modes. runMins specifies the running time, and runTxnsPerTerminal specifies the number of transactions on each terminal. The two parameters cannot be enabled at the same time, and one of them must be set to 0.

Step 3 Initialize the data.

1. Run the following command in the run directory to grant the execute permission on the file:

CA UTION

If the required permission has been granted, skip this step. 2. Load data.

#./runDatabaseBuild.sh my_postgres.properties

* Create and initialize tables. The tables include warehouse, Stock, Item, Order-Line, New-Order, History, District, Customer, Order, and a configuration table.

Step 4 Run the BenchmarkSQL program.

#./runBenchmark.sh my_postgres.properties

After the test is complete, the tpmC and tpmTOTAL are the test indicators.

Step 5 Delete the database and data.

#./runDatabaseDestroy.sh my_postgres.propertiess ----End

2

Sysbench 0.5 Porting Guide (CentOS 7.6)

2.1 Introduction

2.2 Environment Requirements 2.3 Installing Dependency Packages 2.4 Obtaining the Source Code

2.5 Compiling and Installing Sysbench 2.6 Running and Verifying Sysbench

2.1 Introduction

Sysbench, an open-source, cross-platform, and multiple-thread performance test tool, is used to verify the MySQL database performance. It is one of Alibaba's tools for evaluating RDS performance. Sysbench provides four main test models: select, update, insert, and delete. In addition, it provides the OLTP test model to evaluate the MySQL performance in online transactions.

2.2 Environment Requirements

This document is based on the newly installed CentOS Linux release 7.6.1810. During the system installation, minimum installation is selected and Development

2.3 Installing Dependency Packages

Step 1 Back up the original YUM repository files and write the new configuration to the repo file.

#mv /etc/yum.repos.d/ /etc/yum.repos.d-bak #mkdir /etc/yum.repos.d

#echo -e "[local]\nname=local\nbaseurl=file:///mnt \ngpgcheck=0\nenabled=1" > /etc/yum.repos.d/local.repo

Check whether the previous operation is successful.

Step 2 Mount the OS image.

Mount the OS image using either of the following methods: ● Mount the image using the KVM.

Mount the image to the /mnt/ directory.

#mount /dev/sr0 /mnt/

● Upload the OS image to the system.

Upload the OS image to the /home directory and mount the image to the /

#mount /home/CentOS-7-aarch64-Everything-1810.iso /mnt/ NO TE

The .iso file name used in this section is only an example. Step 3 Install the dependency.

#yum install automake libtool* mysql-devel ----End

2.4 Obtaining the Source Code

Step 1 Download the Sysbench source code package.

URL: https://github.com/akopytov/sysbench/tree/0.5

Select Clone or download and click Download ZIP.

Step 2 Upload the package to the /home directory on the server and decompress it. #cd /home

----End

2.5 Compiling and Installing Sysbench

Step 1 Go to the directory where the source code is located. #cd /home/sysbench-0.5

Step 2 Perform compilation and installation. #./autogen.sh

#make -j 64

#make install

----End

2.6 Running and Verifying Sysbench

This document uses MySQL as an example.

Step 1 Build a library.

Create the test database on the MySQL command-line interface (CLI).

Step 2 Load the data.

Run the following command to generate test data. The data volume is 25000 data records x 250 tables.

#/home/sysbench-0.5/sysbench/sysbench --db-driver=mysql --test=/home/ sysbench-0.5/sysbench/tests/db/parallel_prepare.lua

--oltp-test-mode=complex host=192.168.202.152 db=test mysql-password=123456 max-time=7200 max-requests=0 mysql-user=root mysql-table-engine=innodb oltp-table-size=25000 oltp-tables-count=250 --rand-type=special --rand-spec-pct=100 --num-threads=60 prepare

Step 3 Perform a test.

Run the following command to perform the test. The test model can be changed based on the site requirements.

#/home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/

sysbench/tests/db/oltp.lua db-driver=mysql debug=off mysql-db=test mysql-password=123456 oltp-tables-count=64 oltp-index-updates=10 --oltp-non-index-updates=0 --oltp-table-size=25000 --num-threads=100 --max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex host=192.168.202.152 port=3306 --mysql-user=root --oltp-user-delay-min=10 --oltp-user-delay-max=100 --report-interval=10 run

Main parameters are described as follows:

● --mysql-db: The database to which data is loaded must be the same as the

tested database. Otherwise, an error is reported indicating that the MySQL server cannot be connected.

● --num-threads: number of threads. If the message "Too many connections" is

displayed, decrease the value.

● --test: specifies a test model.

● --oltp-tables-count: sets the number of generated tables.

● --oltp-table-size: specifies the size of each generated table.

● --num-threads: specifies the number of threads to be tested.

● --mysql-password: specifies the password for logging in to the MySQL server.

● --mysql-user: specifies the user name for logging in to the MySQL server.

● --mysql-port: specifies the port number for logging in to the MySQL server.

Step 4 Check the test results.

Use transactions and read/write requests as test indicators.

● transactions: number of transactions per second

● read/write requests: number of read/write requests per second.

Step 5 Model description is as follows:

Model Remarks Example

OLTP

DISTINCT OLTP read scenario:deduplication query. SELECT DISTINCT c FROMtable_name WHERE id BETWEEN range_start AND range_start + (oltp_range_size - 1) ORDER BY c OLTP

INDEX OLTP write scenario: indexcolumn update. UPDATE table_name SET k=k+1WHERE id = sb_rand(1, oltp_table_size)

OLTP NONINDE X

OLTP write scenario: no

index column update. UPDATE table_name SET c='c_val'WHERE id=sb_rand(1, oltp_table_size) OLTP

ORDER OLTP read scenario: sortingquery. SELECT c FROM table_name WHEREid BETWEEN range_start AND range_start+(oltp_range_size - 1) ORDER BY c

OLTP

POINT OLTP read scenario: pointselection query. SELECT c FROM table_name WHEREid=sb_rand(1, oltp_table_size) OLTP

SIMPLE OLTP read scenario: rangequery. SELECT c FROM table_name WHEREid BETWEEN range_start AND range_start+(oltp_range_size - 1) OLTP

SUM OLTP read scenario: sumquery. SELECT SUM(K) FROM table_nameWHERE id BETWEEN range_start AND range_start + (oltp_range_size - 1)

Model Remarks Example

OLTP

DELETE OLTP write scenario: indexcolumn deletion and data insertion.

DELETE FROM table_name WHERE id=sb_rand(1, oltp_table_size)I INSERT INTO table_name (id, k, c, pad) VALUES string.format((%d, %d, '%s', '%s'),i, sb_rand(1,

oltp_table_size) , c_val, pad_val) OLTP MIX OLTP mixed scenario: The

preceding eight models are mixed. The default

proportion of the point scenario is 10, and that of other scenarios is 1.

The preceding scenarios are mixed.

Step 6 Execute a model.

Model Test Implementation

OLTP

DISTINCT /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=1 --oltp-index-updates=0 non-index-updates=0 order-ranges=0 point-selects=0 oltp-simple-ranges=0 oltp-sum-ranges=0 oltp_delete_inserts=0 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

OLTP

INDEX /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=0 --oltp-index-updates=1 non-index-updates=0 order-ranges=0 point-selects=0 oltp-simple-ranges=0 oltp-sum-ranges=0 oltp_delete_inserts=0 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

Model Test Implementation

OLTP NONINDE X

/home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/ sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=0 --oltp-index-updates=0 non-index-updates=1 order-ranges=0 point-selects=0 oltp-simple-ranges=0 oltp-sum-ranges=0 oltp_delete_inserts=0 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

OLTP

ORDER /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=0 --oltp-index-updates=0 non-index-updates=0 order-ranges=1 point-selects=0 oltp-simple-ranges=0 oltp-sum-ranges=0 oltp_delete_inserts=0 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

OLTP

POINT /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=0 --oltp-index-updates=0 non-index-updates=0 order-ranges=0 point-selects=1 oltp-simple-ranges=0 oltp-sum-ranges=0 oltp_delete_inserts=0 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

OLTP

SIMPLE /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=0 --oltp-index-updates=0 non-index-updates=0 order-ranges=0 point-selects=0 oltp-simple-ranges=1 oltp-sum-ranges=0 oltp_delete_inserts=0 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

Model Test Implementation

OLTP

SUM /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=0 --oltp-index-updates=0 non-index-updates=0 order-ranges=0 point-selects=0 oltp-simple-ranges=0 oltp-sum-ranges=1 oltp_delete_inserts=0 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

OLTP

DELETE /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 --oltp-distinct-ranges=0 --oltp-index-updates=0 non-index-updates=0 order-ranges=0 point-selects=0 oltp-simple-ranges=0 oltp-sum-ranges=0 oltp_delete_inserts=1 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

OLTP MIX /home/sysbench-0.5/sysbench/sysbench --test=/home/sysbench-0.5/ sysbench/tests/db/oltp.lua --db-driver=mysql --debug=off mysql-db=sysbench mysql-password=123456 oltp-tables-count=64 oltp-table-size=100000 num-threads=100 max-requests=0 --max-time=60 auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=192.168.189.31 --mysql-port=3306 --mysql-user=root min=10 --oltp-user-delay-max=100 --report-interval=10 run

3

TPC-H Test Guide (for MySQL)

3.1 TPC-H

3.2 Environment Requirements 3.3 Using TPC-H

3.1 TPC-H

The TPC-H benchmark test includes 22 queries (from Q1 to Q22). The key metric is the response time of each query, that is, the time required from submitting a query to obtaining the query result.

The measurement unit of the TPCH benchmark test is the number of queries executed per hour (QphH@size), where:

● H indicates the average number of complex queries executed by the system

per hour

● size indicates the size of the database

QphH@size reflects the system's capability to process queries.

3.2 Environment Requirements

Table 3-1 OS and software requirements

Software Version Description

CentOS 7.6 or later In this document,

CentOS Linux release 7.6.1810 is used. Minimum installation and Development Tools are selected during the OS installation.

Software Version Description

TPC-H Tools 2.18.0 For details, see 3.1

"Obtaining the TPCH Tool Package."

3.3 Using TPC-H

3.3.1 Obtaining the TPC-H Tool Package

How to

Obtain Description Tool PackageDifference

Download the tool package from the official website

The server must be able to access the official website over a network connection.

The tool package downloaded from the official website does not support MySQL. Some modifications are required.

Obtaining the TPC-H Test Tool

3.3.2 Compiling and Installing the Tool Package

You can save the tool package to the /home directory or another directory. The space of the directory must be greater than the size of the .tbl data file generated in Step 4.

Decompress the .zip file, go to the dbgen directory, and modify lines 103 to 112 in the makefile.suite file.

unzip tpch-pq.zip

cd /home/tpch-pq/2.18.0_rc2/dbgen/ vi makefile.suite

CC = gcc

# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata) # SQLSERVER, SYBASE, ORACLE, VECTORWISE # Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, # SGI, SUN, U2200, VMS, LINUX, WIN32 # Current values for WORKLOAD are: TPCH

DATABASE = MYSQL MACHINE = LINUX WORKLOAD = TPCH #

Save the modification, and change the file name from makefile.suite to

makefile. In this way, the file can be correctly used when you run the make

command.

cp makefile.suite makefile Step 2 Modify the tpcd.h file.

Open the tpcd.h file and add the following macro definition to the beginning of the file:

#ifdef MYSQL

#define GEN_QUERY_PLAN ""

#define START_TRAN "START TRANSACTION" #define END_TRAN "COMMIT"

#define SET_OUTPUT ""

#define SET_ROWCOUNT "limit %d;\n" #define SET_DBASE "use %s;\n" #endif

Step 3 Generate a dbgen file.

In the dbgen directory, run the make command. Then, many .o files and a dbgen file are generated in the dbgen directory.

make

Step 4 Generate .tbl data files.

Use the dbgen file to generate data, which consists of eight .tbl tables. In the following command, the value 1 indicates that 1 GB data will be generated. If you want to generate 10 GB data, change 1 to 10.

The eight tables are supplier.tbl, region.tbl, part.tbl, partsupp.tbl, orders.tbl,

nation.tbl, lineitem.tbl, and customer.tbl.

Generate data files. ./dbgen -s 1

Step 5 Modify the initialization scripts so that they can be used in the MySQL database.

The tool package contains the following scripts:

dss.ddl: used to create tables.

dss.ri: used to associate the primary key and foreign key in tables.

These scripts cannot be directly used in the MySQL database and need to be modified.

● Modifying dss.ddl

The dss.ddl script must start with some commands that are used to establish connections to the MySQL database. Add the following code at the beginning of the script:

DROP DATABASE tpch; CREATE DATABASE tpch; USE tpch;

The name of the table used in the TPCH test is in lower case, but the name of the table in dss.ddl is in upper case. Therefore, it is recommended that you change the name of the table in dss.ddl to lower case.

Use the vi editor to open the dss.ddl script. vi dss.ddl

In the command line mode (by pressing Esc), change the table name to lower case (by pressing Enter).

:%s/TABLE\(.*\)/TABLE\L\1

● Modifying dss.ri

Use the vi editor to open dss.ri, and then copy the following code to replace the original code.

CA UTION

The following eight -- ALTER TABLE tpch.* DROP PRIMARY KEY; lines start with two hyphens and a space "-- ".

Format: -- ALTER TABLE tpch.* DROP PRIMARY KEY;

-- Sccsid: @(#)dss.ri 2.1.8.1 -- tpch Benchmark Version 8.0 USE tpch;

-- ALTER TABLE tpch.region DROP PRIMARY KEY; -- ALTER TABLE tpch.nation DROP PRIMARY KEY; -- ALTER TABLE tpch.part DROP PRIMARY KEY; -- ALTER TABLE tpch.supplier DROP PRIMARY KEY; -- ALTER TABLE tpch.partsupp DROP PRIMARY KEY; -- ALTER TABLE tpch.orders DROP PRIMARY KEY; -- ALTER TABLE tpch.lineitem DROP PRIMARY KEY; -- ALTER TABLE tpch.customer DROP PRIMARY KEY; -- For table region

ALTER TABLE tpch.region

ADD PRIMARY KEY (R_REGIONKEY); -- For table nation

ALTER TABLE tpch.nation

ADD PRIMARY KEY (N_NATIONKEY); ALTER TABLE tpch.nation

ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references tpch.region(R_REGIONKEY);

COMMIT WORK; -- For table part ALTER TABLE tpch.part

ADD PRIMARY KEY (P_PARTKEY); COMMIT WORK;

-- For table supplier ALTER TABLE tpch.supplier ADD PRIMARY KEY (S_SUPPKEY); ALTER TABLE tpch.supplier

ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references tpch.nation(N_NATIONKEY);

COMMIT WORK; -- For table partsupp ALTER TABLE tpch.partsupp

ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY); COMMIT WORK;

-- For table customer ALTER TABLE tpch.customer ADD PRIMARY KEY (C_CUSTKEY); ALTER TABLE tpch.customer

ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references tpch.nation(N_NATIONKEY);

COMMIT WORK; -- For table lineitem

ALTER TABLE tpch.lineitem

ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER); COMMIT WORK;

-- For table orders ALTER TABLE tpch.orders

ADD PRIMARY KEY (O_ORDERKEY); COMMIT WORK;

-- For table partsupp ALTER TABLE tpch.partsupp

ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references tpch.supplier(S_SUPPKEY);

COMMIT WORK;

ALTER TABLE tpch.partsupp

ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references tpch.part(P_PARTKEY);

COMMIT WORK; -- For table orders ALTER TABLE tpch.orders

ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references tpch.customer(C_CUSTKEY);

COMMIT WORK; -- For table lineitem ALTER TABLE tpch.lineitem

ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references tpch.orders(O_ORDERKEY);

COMMIT WORK;

ALTER TABLE tpch.lineitem

ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references tpch.partsupp(PS_PARTKEY,PS_SUPPKEY);

COMMIT WORK;

Step 6 Create the tpch database and data tables.

Import the .ddl file to the MySQL database.

In the command, replace /home/tpch-pq/2.18.0_rc2 with the actual directory on your server. Then, check whether the database is successfully created. A database named tpch is displayed.

mysql> \. /home/tpch-pq/2.18.0_rc2/dbgen/dss.ddl Check the database.

mysql> SHOW DATABASES;

Step 7 Add the primary key and foreign key of the tpch database.

Import the dds.ri file to the MySQL database.

In the command, replace /home/tpch-pq/2.18.0_rc2 with the actual directory on your server.

mysql> \. /home/tpch-pq/2.18.0_rc2/dbgen/dss.ri Step 8 Import data.

vi load.sh

Enter the following code and save the file. Check whether the database name

tpch needs to be changed. #!/bin/bash write_to_file() { file="loaddata.sql" if [ ! -f "$file" ] ; then touch "$file" fi

echo 'USE tpch;' >> $file

echo 'SET FOREIGN_KEY_CHECKS=0;' >> $file DIR=`pwd`

for tbl in `ls *.tbl`; do table=$(echo "${tbl%.*}")

echo "LOAD DATA LOCAL INFILE '$DIR/$tbl' INTO TABLE $table" >> $file echo "FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';" >> $file done

echo 'SET FOREIGN_KEY_CHECKS=1;' >> $file }

write_to_file

Run the following command to execute the script: sh load.sh

The loaddata.sql file is generated in the same directory. The file contains the SQL commands used for importing data from the eight .tbl tables.

Run the following command to import data: mysql -uroot -p123456 < loaddata.sql

If an error occurs during the execution, add --local-infile to the command. You need to log in to the MySQL database and modify the global parameter as follows:

SET GLOBAL local_infile = 'ON';

mysql --local-infile -uroot -p123456 < loaddata.sql Wait until all data is imported.

Log in to the database and check whether the eight tables contain data. The time for importing data varies, depending on the size of data to be imported.

Step 9 Generate SQL statements for query.

Generate the SQL statement in the dbgen directory: cp qgen dists.dss queries/

Copy the qgen execution file and the dists.dss script to the directory of the

In the dbgen directory, run the following command to create the saveSql folder: mkdir ../saveSql

Go to the queries directory. cd queries

Run the SQL statement to be generated, for example, the 1.sql query statement. ./qgen -d 1 > ../../saveSql/1.sql

If the generated sql file does not work, do as follows:

Open the generated .sql files in the savaSql directory and modify the files according to Table 3-2.

Table 3-2 Modifications on .sql files

File Name Operation

File Name Operation

1.sql, 4.sql, 5.sql, 6.sql, 7.sql, 8.sql, 9.sql, 11.sql, 12.sql, 13.sql, 14.sql, 15.sql, 16.sql, 17.sql, 19.sql, 20.sql, and 22.sql

Delete limit -1; from the last line. You can run the following command to quickly delete it in the savaSql

directory:

sed -i "s/limit\ -1;//g" *.sql

2.sql, 3.sql, 10.sql, 18.sql, and 21.sql Delete the semicolon (;) in the second line from the bottom.

For example:

In 1.sql, delete (3) and limit - 1;.

----End

3.3.3 Performing a TPC-H Test

Step 3 Select an .sql file for test.

Import the .sql file to the MySQL database. \. /home/tpch-pq/2.18.0_rc2/saveSql/1.sql

In the last line of the query result, 16.95 sec indicates the time used for the query.

4

HammerDB Test Guide

4.1 Introduction to HammerDB

HammerDB is an open-source database pressure test benchmark tool that supports Linux and Windows operating systems. It provides GUI and command line modes. Oracle, SQL Server, DB2, MySQL, MariaDB, PostgreSQL (Greenplum), and Redis databases are supported.

HammerDB simulates the standard TPC-C and TPC-H test models. Compared with the standard TPC-C and TPC-H, HammerDB features low running cost and easy operation. It is a good choice for server database pressure tests.

4.2 Installing HammerDB

Step 1 Download the HammerDB-3.1.zip tool package.

Step 2 Upload the downloaded package to the /home directory on the pressure test

server and decompress the package.

----End

4.3 Testing HammerDB

This document uses the TPC-H model in Greenplum as an example.

Step 1 Switch to the installation directory. # cd HammerDB-3.2

Step 2 Run the test tool.

Table 4-1 Common commands

Command Description

buildschema Creates a database table and import data based on the configuration.

datagenrun Generates data list.

dbset Configures database options, including the database type and test model.

dgset Configures the tbl data parameters, including the number of concurrent data generation tasks, data volume, and data storage path.

diset Configures table creation parameters, such as database server addresses and database validation information, data volume, and so on.

librarychek Performs the dependency verification. loadscript Loads the test parameters.

print Prints the configuration information.

quit Quits the tool. The original configuration is automatically cleared when you quit the tool after configuring the interactive page. vucreate Creates a task.

vudestroy Stops a task.

Command Description

vuset Sets task running parameters, such as the number of concurrent tasks and log configuration.

vustatus Views the task execution status.

Step 3 Run the following commands in sequence to load data: >dbset db pg #Configure the database type.

>dbset bm tpc-h #Configure the test model.

>diset connection pg_host 90.90.67.106 #Configure the database service address.

>diset tpch pg_tpch_superuser gpadmin #Configures the database superuser. >diset tpch pg_tpch_superuserpass 123456 #Configures the database

superuser password.

>diset tpch pg_tpch_defaultdbase postgres #Configures the default superuser database

>diset tpch pg_scale_fact 1 #Configure data volume. The default value is 1. >diset tpch pg_num_tpch_threads 30 #Configure the number of concurrent threads.

>diset tpch pg_tpch_gpcompat true #Enable Greenplum-compatibility. >diset tpch pg_tpch_gpcompress true #Enable Greenplum parameters.

pg_tpch_user, pg_tpch_pass, and pg_tpch_dbase are default test databases.

Ensure that no user or database with the same name exists on the database server. If such a user or database exists, delete or run the diset command to modify related parameters.

"ALL VIRTUAL USERS COMPLETE" is returned, indicating that the data is imported successfully.

Step 4 Perform the test.

>vuset vu 1 #Configure the number of concurrent tasks. >vuset showoutput 1 #Enable the log function.

>vuset logtotemp 1 #Enable the function of automatically saving logs to the /tmp directory.

>vuset unique 1 #Configure log uniqueness, that is, logs are not overwritten. >print vuconf ##View the task configuration.

>vucreate #Create a task. >vurun #Run the task.

If "SUCCESS" is returned, the execution duration is used as the test indicator.

----End

4.4 Troubleshooting

Symptom

Data may fail to be imported for some server models. You can import the data using TBL files.

Procedure

Step 1 Generate TBL files. >dbset db pg >dbset bm tpc-h

>dgset directory /data/tbl #Storage path of the TBL files. The path must exist and have free space.

>dgset scale_fact 1 #Data volume

>dgset vu 1 #Number of concurrent tasks, which is related to the number of generated TBL files.

# scp -r /data/tbl/ 90.90.67.106:/data/

Step 3 Create a database on the database server and create a table based on the test

model. You can view the OLAP test script in src/postgresql/pgolap.tcl in the HammerDB installation path.

Build a table (using row-store by default):

CREATE TABLE ORDERS (O_ORDERDATE TIMESTAMP, O_ORDERKEY NUMERIC NOT NULL, O_CUSTKEY NUMERIC NOT NULL, O_ORDERPRIORITY CHAR(15), O_SHIPPRIORITY NUMERIC, O_CLERK CHAR(15), O_ORDERSTATUS CHAR(1), O_TOTALPRICE NUMERIC, O_COMMENT VARCHAR(79)) DISTRIBUTED BY (O_ORDERKEY);

CREATE TABLE PARTSUPP (PS_PARTKEY NUMERIC NOT NULL, PS_SUPPKEY NUMERIC NOT NULL, PS_SUPPLYCOST NUMERIC NOT NULL, PS_AVAILQTY NUMERIC, PS_COMMENT VARCHAR(199)) DISTRIBUTED BY

(PS_PARTKEY,PS_SUPPKEY);

CREATE TABLE CUSTOMER(C_CUSTKEY NUMERIC NOT NULL, C_MKTSEGMENT CHAR(10), C_NATIONKEY NUMERIC, C_NAME VARCHAR(25), C_ADDRESS VARCHAR(40), C_PHONE CHAR(15), C_ACCTBAL NUMERIC, C_COMMENT VARCHAR(118)) DISTRIBUTED BY (C_CUSTKEY);

CREATE TABLE PART(P_PARTKEY NUMERIC NOT NULL, P_TYPE VARCHAR(25), P_SIZE NUMERIC, P_BRAND CHAR(10), P_NAME VARCHAR(55), P_CONTAINER CHAR(10), P_MFGR CHAR(25), P_RETAILPRICE NUMERIC, P_COMMENT

VARCHAR(23)) DISTRIBUTED BY (P_PARTKEY);

CREATE TABLE SUPPLIER(S_SUPPKEY NUMERIC NOT NULL, S_NATIONKEY NUMERIC, S_COMMENT VARCHAR(102), S_NAME CHAR(25), S_ADDRESS VARCHAR(40), S_PHONE CHAR(15), S_ACCTBAL NUMERIC) DISTRIBUTED BY (S_SUPPKEY);

CREATE TABLE NATION(N_NATIONKEY NUMERIC NOT NULL, N_NAME CHAR(25), N_REGIONKEY NUMERIC, N_COMMENT VARCHAR(152)) DISTRIBUTED BY (N_NATIONKEY);

CREATE TABLE REGION(R_REGIONKEY NUMERIC, R_NAME CHAR(25), R_COMMENT VARCHAR(152)) DISTRIBUTED BY (R_REGIONKEY);

CREATE TABLE LINEITEM(L_SHIPDATE TIMESTAMP, L_ORDERKEY NUMERIC NOT NULL, L_DISCOUNT NUMERIC NOT NULL, L_EXTENDEDPRICE NUMERIC NOT NULL, L_SUPPKEY NUMERIC NOT NULL, L_QUANTITY NUMERIC NOT NULL, L_RETURNFLAG CHAR(1), L_PARTKEY NUMERIC NOT NULL,

L_LINESTATUS CHAR(1), L_TAX NUMERIC NOT NULL, L_COMMITDATE TIMESTAMP, L_RECEIPTDATE TIMESTAMP, L_SHIPMODE CHAR(10),

L_LINENUMBER NUMERIC NOT NULL, L_SHIPINSTRUCT CHAR(25), L_COMMENT VARCHAR(44)) DISTRIBUTED BY (L_LINENUMBER, L_ORDERKEY);

Step 4 The TBL files are imported in the database on the table basis.

# copy ${table_name} from '/data/tbl/${table_name}.tbl' WITH DELIMITER AS '|';

${table_name} indicates the table name. There are eight tables in total.

Step 5 Create indexes.

CREATE INDEX REGION_PK ON REGION (R_REGIONKEY); CREATE INDEX NATION_PK ON NATION (N_NATIONKEY); CREATE INDEX SUPPLIER_PK ON SUPPLIER (S_SUPPKEY);

CREATE INDEX PARTSUPP_PK ON PARTSUPP (PS_PARTKEY,PS_SUPPKEY); CREATE INDEX PART_PK ON PART (P_PARTKEY);

CREATE INDEX ORDERS_PK ON ORDERS (O_ORDERKEY,o_orderdate); CREATE INDEX LINEITEM_PK ON LINEITEM (L_LINENUMBER,

L_ORDERKEY,l_shipdate);

5

YCSB Test Guide (for MongoDB)

5.1 Introduction 5.2 Installing YCSB 5.3 Testing YCSB

5.1 Introduction

Yahoo! Cloud Serving Benchmark (YCSB) is a tool developed by Yahoo to perform basic tests on cloud services. It contains common NoSQL database products, such as Cassandra, MongoDB, HBase, and Redis. When running YCSB, you can configure different workloads and databases, or specify other parameters such as the

number of threads and the number of concurrent threads.

5.2 Installing YCSB

Step 1 Download the tool package.

URL: https://mirrors.huaweicloud.com/kunpeng/archive/kunpeng_solution/ native/YCSB-0.15.0.zip

Step 2 Upload the downloaded package to the /home directory on the pressure test

server and decompress the package.

Step 3 Install the dependency.

1. Back up the original Yum repository files and write the new configuration to the

repo file.

#mv /etc/yum.repos.d/ /etc/yum.repos.d-bak #mkdir /etc/yum.repos.d

#echo -e "[local]\nname=local\nbaseurl=file:///mnt \ngpgcheck=0\nenabled=1" > /etc/yum.repos.d/local.repo

Check whether the previous operation is successful.

2. Mount the OS image.

Mount the OS image using either of the following methods: ● Mount the OS image using the KVM.

To mount the OS image to the /mnt/ directory, run the following command:

#mount /dev/sr0 /mnt/

● Upload the OS image to the system.

To upload the OS image to the /home directory and mount the image to the /mnt/ directory, run the following command:

#mount /home/CentOS-7-aarch64-Everything-1810.iso /mnt/ NO TE

The .iso file name used in this section is only an example.

#yum install java maven ----End

5.3 Testing YCSB

Step 1 Switch to the installation directory. # cd YCSB-0.15.0

The main directories are described as follows:

● bin: contains an executable YCSB file, which is the command line interface for

user operations. The main logic of the YCSB is as follows: Parse the command line, set the Java environment, load java-libs, encapsulate the command into executable Java commands, and execute the command.

● workloads: contains various workload templates, which can be customized.

The default six test scenarios are as follows:

● workloada: read/write balancing, 50%/50%, reads/writes

● workloadb: read-intensive, 95%/5%, reads/writes

● workloadc: read-only, 100%, reads

● workloadd: reads the most recently written records, 95%/5%, reads/insert

● workloade: inter-cell scanning, 95%/5%, scan/insert

● workloadf: read/written records balancing , 50%/50%, reads/insert

● workload_template: parameter list template

Step 2 Configure the pressure test.

Select a test scenario and edit the corresponding configuration file. The following uses the read/write test as an example:

The parameters in the configuration file are as follows:

Table 5-1 Parameters in the configuration file

Parameter Description

recordcount Number of records loaded in the YCSB load phase. operationcount Total number of operations performed in the YCSB run

phase.

workload workload implementation class.

readallfields Indicates whether to read all fields of a record during query. readproportion Percentage of read operations.

updateproportion Percentage of update operations. scanproportion Percentage of insert operations.

requestdistribu-tion Request distribution mode, which can be uniform, zipfian,or latest. mongodb.url Link of the mongo instance to be tested. (Note: This is an

Parameter Description

mongodb.databas

e Name of the database used in the test. The default value isycsb. (Note: This is an added item.) Step 3 Load the data.

# bin/ycsb load mongodb -threads 100 -P workloads/workloada Figure 5-1 Data loading command output

Figure 5-2 Data loading command output

If Return=OK is returned, the data is imported successfully. The parameters are described as follows:

# ycsb [command] [database] [options]

● command options:

load: loads the test data. run: runs the test.

● database options: specify the database scenario for the test, such as

mongodb, cassandra, and memcached.

● options options:

-P file: specifies the workload file, which can be a relative or absolute path. -cp path: specifies an additional Java classpath.

-jvm-args args: specifies an additional JVM parameter.

-p key=value: sets the YCSB configuration item, which will overwrite the

configuration item in the workload file.

-s: displays the intermediate status during the running to stderr. -target n: indicates the total number of operations in 1s.

-threads n: sets the number of concurrent test threads of the YCSB client. The

default value is 1, indicating a single thread.

Step 4 Perform the test.

# bin/ycsb run mongodb -threads 100 -P workloads/workloada Figure 5-3 Test command output

If Return=OK is returned, the test is complete. The value of

[OVERALL],Throughput is used as the test indicator.

[OVERALL], RunTime(ms), 1123 #Duration of the test (ms)

[OVERALL], Throughput(ops/sec), 8904.719501335709 #Throughput during the test (ops/sec)

[READ], Operations, 5020 #Total number of read operations.

[READ], AverageLatency(us), 4126.0304780876495 #Average latency of each read operation (microsecond)

[READ], MinLatency(us), 299 #Minimum latency of each read operation (microsecond)

[READ], MaxLatency(us), 168959 #Maximum latency of each read operation (microsecond)

[READ], 95thPercentileLatency(us), 6659 #The latency of 95% read operations is within 6659 microseconds.

[READ], 99thPercentileLatency(us), 157311 #The latency of 99% read operations is within 157311 microseconds.

[READ], Return=OK, 5020 #Return success. The number of operations is 5020. [UPDATE], Operations, 4980 #Total number of update operations.

[UPDATE], AverageLatency(us), 3846.1317269076303 #Average delay of each update operation (microsecond)

[UPDATE], MinLatency(us), 321 #Minimum latency of each update operation (microsecond)

[UPDATE], MaxLatency(us), 168575 #Maximum latency of each update operation (microsecond)

[UPDATE], 95thPercentileLatency(us), 6555 #The latency of 95% update operations is within 6555 microseconds.

[UPDATE], 99thPercentileLatency(us), 26351 #The latency of 99% update operations is within 26351 microseconds.

[UPDATE], Return=OK, 4980 #Return success. The number of operations is 4980.

A

Change History

Date Description

2020-12-30 This issue is the fourth official release. Added 4.3 Testing HammerDB. 2020-09-24 This issue is the third official release.

Add the TPC-H Test Guide (for MySQL). 2020-04-27 This issue is the second official release.

HammerDB Test Guide (for Greenplum):

Modified the tool download path in 4.2 Installing HammerDB.

YCSB Test Guide (for MongoDB):

● Added the installation of dependencies in 5.2 Installing YCSB. ● Changed the format of the configuration file parameter

description to a table in 5.3 Testing YCSB. 2020-03-20 This issue is the first official release.