DB Performance Testing - The 3 Common Suites - A Step-by-Step Guide to Running TPCH

Posted on In , Views: Word count in article: 1.3k Reading time ≈ 5 mins.
Performance Testing -- DB Performance Testing - The 3 Common Suites - A Step-by-Step Guide to Running TPCH

Abstract

Sharing a note I wrote in the past. The three most commonly used database testing suites are: sysbench – OLTP testing, tpch – OLAP testing, and tpcc – transaction performance testing.
This article walks you through running TPCH step by step. Even if you’ve never run a database test before, you can follow along and run TPCH directly. This article runs TPCH on MySQL. If you want to run TPCH against Postgres or another database, you can use this post as a starting point and then search GitHub for the corresponding database’s TPCH repository.

The whole process is divided into:

  • Introduction
  • Compilation
  • Data Generation
  • Data Loading
  • Performance Testing
  • Table Schema Overview

Introduction

TPC’s current test standards are TPC-E, TPC-C, TPC-H, and TPC-App. Based on these 4 benchmarks, TPC currently has 4 main technical subcommittees: the TPC-E Technical Subcommittee, the TPC-C Technical Subcommittee, the TPC-H Technical Subcommittee, and the TPC-App Technical Subcommittee. Standards that TPC used earlier but has since retired include TPC-A, TPC-B (a benchmark for database processing capacity), TPC-D, TPC-R (a benchmark for decision-support systems, similar to TPC-H), and TPC-W (a benchmark for web processing capacity).

TPC-H (the business intelligence computing test) is a test set developed by the Transaction Processing Performance Council (TPC) to simulate decision-support applications. It is currently widely used in both academia and industry to evaluate the performance of decision-support technology applications. This commercial test comprehensively evaluates a system’s overall business computing capability, places higher demands on vendors, and has broad commercial practical significance. It is widely applied in bank credit analysis and credit card analysis, telecom operations analysis, tax analysis, and decision analysis in the tobacco industry.

The TPC-H benchmark evolved from TPC-D (a standard designated by the TPC organization in 1994 for testing decision-support systems). TPC-H implements a data warehouse using 3NF, comprising 8 base relations, with a data volume that can be set anywhere from 1G to 3T. The TPC-H benchmark includes 22 queries (Q1–Q22), and its main evaluation metric is the response time of each query—that is, the time from submitting a query to the result being returned. The TPC-H benchmark’s unit of measure is the number of queries executed per hour (QphH@size), where H represents the average number of complex queries the system executes per hour, and size represents the scale of the database; it reflects the system’s capability in handling queries. TPC-H is modeled on a real production environment, which allows it to evaluate key performance parameters that some other tests cannot. In short, the TPC-H standard published by the TPC organization meets the testing needs of the data warehouse field and pushes vendors and research institutions to drive the technology to its limits.

For details, see tpch_reference

Compilation

Download the source package tpch

tpch_download

  1. Open the dbgen directory.
1
cd dbgen
  1. Copy the makefile.
1
cp makefile.suite Makefile
  1. Modify the parameter definitions in the Makefile, such as CC, DATABASE, MACHINE, and WORKLOAD.
    Open the Makefile.
    Modify the definitions of the CC, DATABASE, MACHINE, and WORKLOAD parameters.
1
2
3
4
5
6
7
8
9
10
11
12
################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, ORACLE,
#                                  SQLSERVER, SYBASE, TDAT (Teradata)
# 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

Press the ESC key, then type :wq to exit and save.

  1. Modify the tpcd.h file and add a new macro definition.
    Open the tpcd.h file.
    vim tpcd.h
    Add the following macro definition.
1
2
3
4
5
6
7
8
#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

Press the ESC key, then type :wq to exit and save.

  1. Compile the files.
1
make

After compilation, two executables are generated in this directory:

  • dbgen: the data generation tool. When testing with InfiniDB’s official test scripts, you need this tool to generate the TPCH table data.
  • qgen: the SQL generation tool. It generates the initial test queries. Since different seeds generate different queries, for reproducible results, please use the 22 queries provided in the attachment.

Generating Data

Generating Test Data

You can generate TPCH 10g, 100g, or even 1TB. This example uses 100g. The 100g record count is around 600 million rows, roughly comparable to the large-table scale of an ordinary small-to-medium company.

1
2
3
./dbgen -s 100
mkdir tpch100
mv *.tbl tpch100

Generating Query SQL

  1. Copy qgen and dists.dss into the queries directory.
1
2
cp qgen queries
cp dists.dss queries
  1. Use the following script to generate the queries.
    In the queries directory, create the script gen.sh
1
2
3
4
5
#!/usr/bin/bash
for i in {1..22}
do  
  ./qgen -d $i -s 100 > db"$i".sql
done
1
./gen.sh
  1. Adjust the query SQL
1
dos2unix *

Remove the “limit -1” from the generated files, and remove the (3) after day. Taking q1 as an example, the SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- using default substitutions


select
        l_returnflag,
        l_linestatus,
        sum(l_quantity) as sum_qty,
        sum(l_extendedprice) as sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
        avg(l_quantity) as avg_qty,
        avg(l_extendedprice) as avg_price,
        avg(l_discount) as avg_disc,
        count(*) as count_order
from
        lineitem
where
        l_shipdate <= date '1998-12-01' - interval '90' day (3)   --- remove the (3)
group by
        l_returnflag,
        l_linestatus
order by
        l_returnflag,
        l_linestatus;
limit -1;              ---  remove this line

Loading Data

  1. Download the load scripts
1
2
3
4
5
6
7
mkdir load
cd load
wget https://raw.githubusercontent.com/longdafeng/test/master/shell/tpch/load.sh ./
wget https://raw.githubusercontent.com/longdafeng/test/master/shell/tpch/polar.index.sh ./
chmod +x *
cp ../dss.ri  ../dss.ddl ./

Because this test includes a PolarDB index-creation script, readers who don’t use PolarDB can skip downloading polar.index.sh and modify the load.sh file to remove the index-setting step.

  1. Modify the dss.ri script
    dss.ri mainly sets the primary keys and foreign keys.
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Sccsid:     @(#)dss.ri       2.1.8.1
-- TPCD Benchmark Version 8.0

CONNECT TO TPCD;           

--ALTER TABLE TPCD.REGION DROP PRIMARY KEY;
--ALTER TABLE TPCD.NATION DROP PRIMARY KEY;
--ALTER TABLE TPCD.PART DROP PRIMARY KEY;
--ALTER TABLE TPCD.SUPPLIER DROP PRIMARY KEY;
--ALTER TABLE TPCD.PARTSUPP DROP PRIMARY KEY;
--ALTER TABLE TPCD.ORDERS DROP PRIMARY KEY;
--ALTER TABLE TPCD.LINEITEM DROP PRIMARY KEY;
--ALTER TABLE TPCD.CUSTOMER DROP PRIMARY KEY;

Delete the “CONNECT TO TPCD;” and remove all the “TPCD.” prefixes.

If you don’t want to modify dss.ri, you can directly download the ready-made dss.ri

  1. Install the MySQL client
1
yum install mysql -y
  1. Start loading
1
2
nohup./load.sh hostxxx portxxx userxxx passwordxxx dbxxx > load.log 2>&1 &
tail -f load.log

Here:

  • hostxxx is the DB address
  • portxxx is the DB port
  • userxxx is the username — the username must be created in advance; for cloud users, you also need to set the allowlist, adding the machine’s IP to it
  • passwordxxx is the user’s password
  • dbxxx is the name of the database to be created. Because the script automatically loads from the directory created in the “Generating Data” section (tpch100 in our example), the database name must also match the directory created when generating data in the previous section.

Starting the Test

Download the test script from https://github.com/longdafeng/test/tree/master/python/tpch

Configure the config file example.cfg

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
{

    "host":"xxxx"                 // database machine name
    "port":"3306"                 // database port number
    "username":"xxxxx"            // database username
    "password":"xxxx"             // database password
    "database":"xxxxx"            // database name
    //input dir
    "input_dir":"mysql"           // the directory where the query SQL is stored. For testing the MySQL family, it's mysql here; for pg, you need to generate the pg query SQL
    
    //output_dir
    "output_dir":"polardb80"      // the directory for printing logs
    
    //mysql_setting, set mysql variable
    //"mysql_setting": "set max_parallel_degree=32;"
    "mysql_setting": ""

    //query per sql times
    "times_per_sql":"1"            // the number of times each SQL is executed; the average will be taken

}

Run the script

1
2
nohup ./tpch.py -f example.cfg > run.log 2>&1 &
tail -f run.log

Finally, go into the directory specified by “output_dir” in the config file and check the result file.