GBase 8c基于TPCH模型的性能调优方法介绍

原文链接：https://www.gbase.cn/community/post/4351
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在当今数据驱动的时代，数据库性能优化成为企业提升竞争力的关键。本文将深入探讨如何基于TPCH（Transaction Processing Performance Council Benchmark H）模型对南大通用GBase 8c数据库进行性能调优，旨在为读者提供一套科学严谨、启发引导、条理清晰的调优方法。

TPCH测试工具下载地址：TPC Current Specs

一、TPCH模型简介

TPCH是什么？
TPC-H是国际事务处理性能委员会（TPC，Transaction Processing Performance Council）于1994年制定的标准。是一款面向商品零售业务的决策支持系统测试基准。是衡量OLAP/HTAP数据库能力的一款测试工具。是典型的雪花模型，包含8张表，22个查询，遵循SQL92标准。

数据量的大小对查询数据有直接影响。TPC-H使用SF描述数据量，1SF对应1GB单位。SF由低到高依次是：1、10、30、100、300、1000、3000、10000。1SF对应的数据量是8个表总数据量，不包含索引。

表1  TPCH 22条SQL解读

SQL	名称	业务	语句特点	标准要求
Q1	价格统计报告查询	是查询lineItems的一个定价总结报告。在单个表lineitem上查询某个时间段内，对已经付款的、已经运送的等各类商品进行统计，包括业务量的计费、发货、折扣、税、平均价格等信息。	带有分组、排序、聚集操作并存的单表查询操作。这个查询会导致表上的数据有95%到97%行被读取到。
Q2	最小代价供货商查询	查询获得最小代价的供货商。得到给定的区域内，对于指定的零件（某一类型和大小的零件），哪个供应者能以最低的价格供应它，就可以选择哪个供应者来订货。	带有排序、聚集操作、子查询并存的多表查询操作。	查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。
Q3	运送优先级查询	查询得到收入在前10位的尚未运送的订单。在指定的日期之前还没有运送的订单中具有最大收入的订单的运送优先级（订单按照收入的降序排序）和潜在的收入（潜在的收入为l_extendedprice * (1-l_discount)的和）。	带有分组、排序、聚集操作并存的三表查询操作。	查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。
Q4	订单优先级查询	得到订单优先级统计值。计算给定的某三个月的订单的数量，在每个订单中至少有一行由顾客在它的提交日期之后收到。	带有分组、排序、聚集操作、子查询并存的单表查询操作。子查询是相关子查询。
Q5	某地区供货商为公司带来的收入查询	得到通过某个地区零件供货商而获得的收入（收入按sum(l_extendedprice * (1 -l_discount))计算）统计信息。可用于决定在给定的区域是否需要建立一个当地分配中心。	带有分组、排序、聚集操作、子查询并存的多表连接查询操作。
Q6	预测收入变化查询	得到某一年中通过变换折扣带来的增量收入。这是典型的“what-if”判断，用来寻找增加收入的途径。预测收入变化查询考虑了指定的一年中折扣在“DISCOUNT-0.01”和“DISCOUNT＋0.01”之间的已运送的所有订单，求解把l_quantity小于quantity的订单的折扣消除之后总收入增加的数量。	带有聚集操作的单表查询操作。查询语句使用了BETWEEN-AND操作符，有的数据库可以对BETWEEN-AND进行优化。
Q7	货运盈利情况查询	从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量用以帮助重新谈判货运合同。	带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询。
Q8	国家市场份额查询	查询在过去的两年中一个给定零件类型在某国某地区市场份额的变化情况。	带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。
Q9	产品类型利润估量查询	查询每个国家每一年所有被定购的零件在一年中的总利润。	带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。子查询中使用了LIKE操作符，有的查询优化器不支持对LIKE操作符进行优化。
Q10	货运存在问题的查询	查询每个国家在某时刻起的三个月内货运存在问题的客户和造成的损失。	带有分组、排序、聚集操作并存的多表连接查询操作。	查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。
Q11	库存价值查询	查询库存中某个国家供应的零件的价值。	带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。
Q12	货运模式和订单优先级查询	获得货运模式和订单优先级。可以帮助决策：选择便宜的货运模式是否会导致消费者更多的在合同日期之后收到货物，而对紧急优先命令产生负面影响。	带有分组、排序、聚集操作并存的两表连接查询操作。
Q13	消费者订单数量查询	获得消费者的订单数量，包括过去和现在都没有订单记录的消费者。	带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。	TPC-H标准定义了Q13语句等价的变形SQL，变形的SQL把子查询部分变为视图，然后基于视图做查询，因为有些数据库不支持
Q14	促销效果查询	获得某一个月的收入中有多大的百分比是来自促销零件。用以监视促销带来的市场反应。	带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。	TPC-H标准定义了Q14语句等价的变形SQL，与上述查询语句格式上基本相同，主要是目标列使用了不同的表达方式
Q15	头等供货商查询	获得某段时间内为总收入贡献最多的供货商（排名第一）的信息。可用以决定对哪些头等供货商给予奖励、给予更多订单、给予特别认证、给予鼓舞等激励。	带有分排序、聚集、聚集子查询操作并存的普通表与视图的连接操作。	TPC-H标准定义了Q15语句等价的变形SQL，与上述查询语句格式上不相同，上述查询语句首先定义了视图，然后用表与视图连接；变形的SQL定了WITH语句，然后用WITH的对象与表进行连接。
Q16	指定属性供应零件的供货商查询	获得能够以指定的贡献条件供应零件的供货商数量。可用于决定在订单量大，任务紧急时，是否有充足的供货商。	带有分组、排序、聚集、去重、NOT IN子查询操作并存的两表连接操作。
Q17	小订单收入查询	获得比平均供货量的百分之二十还低的小批量订单。对于指定品牌和指定包装类型的零件，决定在一个七年数据库的所有订单中这些订单零件的平均项目数量（过去的和未决的）。如果这些零件中少于平均数20％的订单不再被接纳，那平均一年会损失多少呢？所以此查询可用于计算出如果没有没有小量订单，平均年收入将损失多少（因为大量商品的货运，将降低管理费用）。	带有聚集、聚集子查询操作并存的两表连接操作。
Q18	大订单顾客查询	获得比指定供货量大的供货商信息。可用于决定在订单量大，任务紧急时，验证否有充足的供货商。	带有分组、排序、聚集、IN子查询操作并存的三表连接操作。	查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。
Q19	折扣收入查询	得到对一些空运或人工运输零件三个不同种类的所有订单的总折扣收入。零件的选择考虑特定品牌、包装和尺寸范围。本查询是用数据挖掘工具产生格式化代码的一个例子。	带有分组、排序、聚集、IN子查询操作并存的三表连接操作。
Q20	供货商竞争力查询	确定在某一年内，找出指定国家的能对某一零件商品提供更有竞争力价格的供货货。所谓更有竞争力的供货商，是指那些零件有过剩的供货商，超过供或商在某一年中货运给定国的某一零件的50％则为过剩。	带有排序、聚集、IN子查询、普通子查询操作并存的两表连接操作。
Q21	不能按时交货供货商查询	查询获得不能及时交货的供货商。	带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。	查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。
Q22	全球销售机会查询	获得消费者可能购买的地理分布。本查询计算在指定的国家，比平均水平更持肯定态度但还没下七年订单的消费者数量。能反应出普通消费者的的态度，即购买意向。	带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。

二、测试过程

GBase 8c多模多态数据库可以使用TPCH模型来衡量数据库性能处理能力。以下是GBase 8c数据库TPCH的测试过程。

（1）数据生成

生成100G数据

dbgen编译：

cp makefile.suite makefile
make clean
make

生成数据：

./dbgen -s 100 &

（2）建表

数据库已经安装部署完成，并且已经按照TPCH的业务模型部署完数据库。

gsql -p 5432 -d tpch -f tpch.sql

tpch.sql脚本可从https://www.tpc.org/tpch/官网获取。

（3）向数据库中加载数据

例如向GBase 8c分布式数据库中加载数据：

sh load.sh /data1/tpch/dbgen/data100/supplier/ 5432 tpch nation 
sh load.sh /data1/tpch/dbgen/data100/supplier/ 5432 tpch supplier
sh load.sh /data1/tpch/dbgen/data100/supplier/ 5432 tpch supplier
sh load.sh /data1/tpch/dbgen/data100/part/ 5432 tpch part
sh load.sh /data1/tpch/dbgen/data100/customer/ 5432 tpch customer
sh load.sh /data1/tpch/dbgen/data100/partsupp/ 5432 tpch partsupp
sh load.sh /data1/tpch/dbgen/data100/orders/ 5432 tpch orders
sh load.sh /data1/tpch/dbgen/data100/lineitem/ 5432 tpch lineitem

其中load.sh脚本内容供参考：

#!/bin/bash
# 参数检查
if [ "$#" -ne 3 ]; then
   echo "Usage: $0 <data_directory> <port> <database_name>"
   exit 1
fi
DATA_DIR=$1
PORT=$2
DB_NAME=$3
# 加载数据到数据库
for table in nation supplier part customer partsupp orders lineitem; do
   TABLE_FILE="${DATA_DIR}/${table}.tbl"
   if [ -f "$TABLE_FILE" ]; then
       echo "Loading ${table}..."
       psql -h localhost -p $PORT -d $DB_NAME -c "\copy ${table} FROM '${TABLE_FILE}' WITH DELIMITER '|' NULL AS ''"
   else
       echo "File ${TABLE_FILE} not found."
   fi
done
echo "Data loading completed."

（4）检查表中数据量，统计如下：

表名称	表中记录数
customer	15000000
lineitem	600037902
nation	25
orders	150000000
part	20000000
partsupp	80000000
region	5
supplier	1000000

（5）执行性能测试：

  gsql -p 5432 -d tpch -f tpch.sql

得到total time时间即为执行tpch性能指标项。执行过程中会根据执行计划及资源使用情况调整work_memory、cstore_buffers大小，以得到最小响应时间。

三、性能调优

数据库性能直接影响到应用的响应时间和用户体验。因此，对GBase 8c进行基于TPCH模型的性能调优，可以显著提升系统的处理能力和稳定性。调优的基本原则如下：
1. 理解系统架构：深入了解GBase 8c的存储引擎、查询优化器等组件。
2. 数据建模与索引优化：合理设计数据模型，创建有效的索引以加速查询。
3. 硬件资源调配：根据系统负载合理分配CPU、内存和存储资源。
4. SQL优化：编写高效的SQL语句，避免不必要的计算和数据访问。
5. 监控与分析：持续监控系统性能，分析瓶颈并进行针对性调优。

调优步骤如下：

1. 基准测试：使用TPCH模型对GBase 8c进行基线性能测试。
2. 问题定位：通过监控工具识别性能瓶颈，如CPU使用率、I/O等待时间等。
3. 参数调整：根据瓶颈调整数据库配置参数，如缓存大小、并发控制等。
4. 索引优化：分析查询模式，创建或调整索引以提高查询效率。
5. SQL调优：优化SQL语句，减少资源消耗和执行时间。
6. 硬件升级：根据需求升级硬件设备，如增加内存、更换更快的存储介质。
7. 重复测试：调整后再次进行TPCH测试，验证性能提升效果。

通过对GBase 8c数据库基于TPCH模型的性能调优实践，我们不仅提升了系统的处理能力，还深入理解了性能调优的原则和方法。未来，随着技术的不断进步，性能调优将继续发挥其在数据库管理中的核心作用。

原文链接：https://www.gbase.cn/community/post/4351
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。