tencent cloud

TPC-H Testing Method
Download PDF
Last updated: 2025-05-09 11:51:25
TPC-H Testing Method
Last updated: 2025-05-09 11:51:25
Download PDF
The read-only analysis engine of TencentDB for MySQL is designed primarily for complex SQL queries and data analysis scenarios. In the industry, TPC-H benchmark test sets are commonly used to assess performance in such use cases. Therefore, this document uses the TPC-H benchmark to test the performance of the read-only analysis engine for user reference.

Prerequisites

You have prepared a TencentDB for MySQL instance. See Creating a MySQL Instance. For the architecture, select either a two-node or three-node configuration.
You have created a read-only analysis engine based on the MySQL instance above, and configured it with an appropriate instance specification. See Enabling Read-Only Analysis Engine.
You have prepared a database account for data queries. See Creating an Account.
Note:
The test results are proportional to the compute specifications of the instance. In data analysis scenarios, achieving better performance requires more resources. You can select an appropriate instance specification based on your actual needs.

Building a Test Table

The TPC-H test set includes eight data tables and one view. Use the following statements to create the tables in TencentDB for MySQL.
create database tpch;
use tpch;

drop table if exists customer;
create table customer (
  c_custkey bigint(20) not null,
  c_name varchar(25) not null,
  c_address varchar(40) not null,
  c_nationkey bigint(20) not null,
  c_phone char(15) not null,
  c_acctbal decimal(15,2) not null,
  c_mktsegment char(10) not null,
  c_comment varchar(117) not null,
  primary key (c_custkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

drop table if exists lineitem;
create table lineitem (
  l_orderkey bigint(20) not null,
  l_partkey bigint(20) not null,
  l_suppkey bigint(20) not null,
  l_linenumber bigint(20) not null,
  l_quantity decimal(15,2) not null,
  l_extendedprice decimal(15,2) not null,
  l_discount decimal(15,2) not null,
  l_tax decimal(15,2) not null,
  l_returnflag char(1) not null,
  l_linestatus char(1) not null,
  l_shipdate date not null,
  l_commitdate date not null,
  l_receiptdate date not null,
  l_shipinstruct char(25) not null,
  l_shipmode char(10) not null,
  l_comment varchar(44) not null,
  primary key (l_orderkey,l_linenumber) 
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

drop table if exists nation;
create table nation (
  n_nationkey bigint(20) not null,
  n_name char(25) not null,
  n_regionkey bigint(20) not null,
  n_comment varchar(152) default null,
  primary key (n_nationkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

drop table if exists orders;
create table orders (
  o_orderkey bigint(20) not null,
  o_custkey bigint(20) not null,
  o_orderstatus char(1) not null,
  o_totalprice decimal(15,2) not null,
  o_orderdate date not null,
  o_orderpriority char(15) not null,
  o_clerk char(15) not null,
  o_shippriority bigint(20) not null,
  o_comment varchar(79) not null,
  primary key (o_orderkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

drop table if exists part;
create table part (
  p_partkey bigint(20) not null,
  p_name varchar(55) not null,
  p_mfgr char(25) not null,
  p_brand char(10) not null,
  p_type varchar(25) not null,
  p_size bigint(20) not null,
  p_container char(10) not null,
  p_retailprice decimal(15,2) not null,
  p_comment varchar(23) not null,
  primary key (p_partkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

drop table if exists partsupp;
create table partsupp (
  ps_partkey bigint(20) not null,
  ps_suppkey bigint(20) not null,
  ps_availqty bigint(20) not null,
  ps_supplycost decimal(15,2) not null,
  ps_comment varchar(199) not null,
  primary key (ps_partkey,ps_suppkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

drop table if exists region;
create table region (
  r_regionkey bigint(20) not null,
  r_name char(25) not null,
  r_comment varchar(152) default null,
  primary key (r_regionkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

drop table if exists supplier;
create table supplier (
  s_suppkey bigint(20) not null,
  s_name char(25) not null,
  s_address varchar(40) not null,
  s_nationkey bigint(20) not null,
  s_phone char(15) not null,
  s_acctbal decimal(15,2) not null,
  s_comment varchar(101) not null,
  primary key (s_suppkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

##  View
create view revenue0 (supplier_no, total_revenue) as
    select l_suppkey, sum(l_extendedprice * (1 - l_discount))
        from
            lineitem
        where
            l_shipdate >= date '1995-02-01'
            and l_shipdate < date '1995-02-01' + interval '3' month
        group by
           l_suppkey;

Generating Test Data

You can register and download the official TPC-H testing tools from the TPC Official Website. After downloading, upload the files to your server and proceed with compiling the data generation tool.
unzip TPC-H_Tools_v3.0.0.zip
cd TPC-H_Tools_v3.0.0/dbgen
make
Note:
If the server does not have compilation tools such as gcc or make installed, use the server's installation image to install the required components.
After the compilation is completed, the dbgen tool will be generated. Run the following command to proceed.
scale=100
chunk=10
for i in seq 1 $chunk
do
./dbgen -s $scale -C $chunk -S $i -f
done
Note:
In the command above, scale specifies the size of the generated data. For example, setting it to 100 generates 100 GB of data. chunk defines how many parts the data files will be split into. These two parameters can be adjusted based on actual needs. For large data set testing scenarios, it is recommended to set a higher chunk value, which results in smaller sub-files and allows for concurrent data imports.

Importing Data

The following section explains how to import TPC-H test data into the TencentDB for MySQL instance. The table below lists the number of records for each table in the 100 GB TPC-H data set as a reference. For other data sizes, the number of records increases or decreases proportionally. For example, a 10 GB data set contains one-tenth the number of records compared to the 100 GB data set.
Table Name
Number of Records
customer
15000000
lineitem
600037902
nation
25
orders
150000000
part
20000000
partsupp
80000000
region
5
supplier
1000000
The table files generated by the TPC-H dbgen tool are stored in the same directory as the tool and use the .tbl file extension. If the data was split during generation, the files will include a numeric suffix, following a format such as partsupp.tbl.1.
ls *.tbl
customer.tbl  lineitem.tbl  nation.tbl  orders.tbl  partsupp.tbl  part.tbl  region.tbl  supplier.tbl
For TencentDB for MySQL instances, you can use the LOAD DATA command to import the data. First, navigate to the directory where the generated test table data is stored, then run the following command to import the data into the read-write instance:
Note:
The following commands navigate to the directory where the TPC-H data is stored and import the files into the database one by one. You can adjust the script as needed based on your actual environment.
cd /data/tpchdata 
HOST=172.16.0.22
PORT=3306
USER=root
Password=xxxxx
DATABASE=tpch
ls *.tbl* | while read filename
do
    tablename=echo $filename | awk -F'.tbl' '{print $1}'
    mysql -u${USER} -h${HOST} -P${PORT} -p${PASSWORD} ${DATABASE} -e "LOAD DATA LOCAL INFILE '${filename}' INTO TABLE ${tablename} FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\n';"
done
After importing the data into the read-write instance, you can use SQL commands to verify that the data has been accurately and completely imported. You can also go to the console and directly configure full data loading for the tpch database.
You can also log in to the read-only analysis engine via the command line to execute SQL queries, or check the data loading status through the console.
mysql -uroot -p'' -h10.1.1.3 -P3306 -c
show replication tables;
When the value of the REPLICATION_STEP field for a table is Change Propagation, it indicates that the data has been fully loaded.
Once the data has been fully loaded into the read-only analysis engine, you can proceed to collect table statistics.
ANALYZE TABLE customer;
ANALYZE TABLE lineitem;
ANALYZE TABLE nation;
ANALYZE TABLE orders;
ANALYZE TABLE part;
ANALYZE TABLE partsupp;
ANALYZE TABLE region;
ANALYZE TABLE supplier;
After collecting the statistics, you can log in to the read-only analysis engine to run the TPC-H test SQL queries. There are 22 TPC-H queries in total. The complete SQL statements are provided below. You can copy the SQL and paste it into the read-only analysis engine for execution.
Note:
Note: When using the MySQL client to log in to the read-only analysis engine and execute SQL statements, you need to include the -c parameter. This ensures that hints within the SQL statements are properly passed to the database, allowing for better performance. For example, mysql -uroot -p'' -h10.1.1.3 -P3306 -c.
# Q1
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_sub('1998-12-01', interval 108 day) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;
# Q2
select /*+  SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0) PX_JOIN_FILTER_ID(0[BLOOM_FILTER],2[BLOOM_FILTER]) */ s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, supplier, partsupp, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 30 and p_type like '%STEEL'and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA'and ps_supplycost = (select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA') order by s_acctbal desc, n_name, s_name, p_partkey limit 100;
# Q3
select  l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'AUTOMOBILE' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < '1995-03-13' and l_shipdate > '1995-03-13' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10;
# Q4
select o_orderpriority, count(*) as order_count from orders where o_orderdate >= '1995-01-01' and o_orderdate < date_add('1995-01-01', interval '3' month) and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;
# Q5
select /*+  SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0) PX_JOIN_FILTER_ID(3[BLOOM_FILTER],4[BLOOM_FILTER]) */ n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'MIDDLE EAST' and o_orderdate >= '1994-01-01' and o_orderdate < date_add('1994-01-01', interval '1' year) group by n_name order by revenue desc;
# Q6
select  sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= '1994-01-01' and l_shipdate < date_add('1994-01-01', interval '1' year) and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24;
# Q7
select /*+   SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0)  PX_JOIN_FILTER_ID(1[BLOOM_FILTER], 3[BLOOM_FILTER]) */  supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select /*+  leading((n1,supplier),lineitem,((n2,customer),orders)) */ n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'JAPAN' and n2.n_name = 'INDIA') or (n1.n_name = 'INDIA' and n2.n_name = 'JAPAN') ) and l_shipdate between '1995-01-01' and '1996-12-31' ) as shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year;
# Q8
select /*+ SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0)  PX_JOIN_FILTER_ID(1[BLOOM_FILTER], 2[BLOOM_FILTER],5[BLOOM_FILTER]) */ o_year, sum(case when nation = 'INDIA' then volume else 0 end) / sum(volume) as mkt_share from ( select /*+ leading(region,n1,customer,orders,(part,lineitem),(supplier,n2)) */ extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'ASIA' and s_nationkey = n2.n_nationkey and o_orderdate between '1995-01-01' and '1996-12-31' and p_type = 'SMALL PLATED COPPER' ) as all_nations group by o_year order by o_year;
# Q9
select /*+ SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0)    PX_JOIN_FILTER_ID(4[BLOOM_FILTER]) JOIN_FILTER_NDV_AMP_RATIO(4:0.1)  */  nation, o_year, sum(amount) as sum_profit from ( select /*+ hash_join_probe(orders) */n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%dim%' ) as profit group by nation, o_year order by nation, o_year desc;
# Q10
select /*+  HASH_JOIN_PROBE((nation,customer)), leading(nation, customer,(orders, lineitem))  */ c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= '1993-08-01' and o_orderdate < date_add('1993-08-01', interval '3' month) and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20;
# Q11
select ps_partkey, sum(ps_supplycost * ps_availqty) as value from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001000000 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' ) order by value desc;
# Q12
select /*+  SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0)   PX_JOIN_FILTER_ID(0[BLOOM_FILTER]) */ l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('RAIL', 'FOB') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= '1997-01-01' and l_receiptdate < date_add('1997-01-01', interval '1' year) group by l_shipmode order by l_shipmode;
# Q13
select  c_count, count(*) as custdist from (select c_custkey, count(o_orderkey) as c_count from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%pending%deposits%'group by c_custkey ) c_orders group by c_count order by custdist desc, c_count desc;
# Q14
select  100.00 * sum(case when p_type like 'PROMO%'then l_extendedprice * (1 - l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >= '1996-12-01'and l_shipdate < date_add('1996-12-01', interval '1' month);
# Q15
select  s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue0 where s_suppkey = supplier_no and total_revenue = (select max(total_revenue) from revenue0 ) order by s_suppkey;
# Q16
select  p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from partsupp, part where p_partkey = ps_partkey and p_brand <> 'Brand#34'and p_type not like 'LARGE BRUSHED%'and p_size in (48, 19, 12, 4, 41, 7, 21, 39) and ps_suppkey not in (select s_suppkey from supplier where s_comment like '%Customer%Complaints%') group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size;
# Q17
select /*+ SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0)  PX_JOIN_FILTER_ID(0[BLOOM_FILTER],1[BLOOM_FILTER]) */ sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#44'and p_container = 'WRAP PKG'and l_quantity < (select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey );
# Q18
select /*+  PX_JOIN_FILTER_ID(0,1,2)  leading(customer, (orders, lineitem@sel_2))  hash_join_probe(customer) JOIN_FILTER_NDV_AMP_RATIO(0:0.00001)  JOIN_FILTER_NDV_AMP_RATIO(2:0.00001) JOIN_FILTER_NDV_AMP_RATIO(1:0.0001) */ c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in (select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 314 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate limit 100;
# Q19
select /*+  SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0)  PX_JOIN_FILTER_ID(0[BLOOM_FILTER]) */ sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where (p_partkey = l_partkey and p_brand = 'Brand#52'and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON') or (p_partkey = l_partkey and p_brand = 'Brand#11'and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON') or (p_partkey = l_partkey and p_brand = 'Brand#51'and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 29 and l_quantity <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON');
# Q20
select  s_name, s_address from supplier, nation where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'green%') and ps_availqty > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= '1993-01-01'and l_shipdate < date_add('1993-01-01', interval '1' year) ) ) and s_nationkey = n_nationkey and n_name = 'ALGERIA'order by s_name;
# Q21
select /*+  SET_VAR(libra_enable_runtime_filter=1) SET_VAR(libra_enable_cost_based_runtime_filter=0)  JOIN_FILTER_NDV_AMP_RATIO(0:0.5) JOIN_FILTER_NDV_AMP_RATIO(1:0.5) PX_JOIN_FILTER_ID(3[BLOOM_FILTER],1,0) */ s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = 'EGYPT' group by s_name order by numwait desc, s_name limit 100;
# Q22
select  cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal from (select substring(c_phone from 1 for 2) as cntrycode, c_acctbal from customer where substring(c_phone from 1 for 2) in ('20', '40', '22', '30', '39', '42', '21') and c_acctbal > (select avg(c_acctbal) from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('20', '40', '22', '30', '39', '42', '21') ) and not exists (select * from orders where o_custkey = c_custkey ) ) as custsale group by cntrycode order by cntrycode;

Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback