1. 前言

• 适用版本：【8.1.1 及以上】

GaussDB(DWS)性能调优系列专题文章，介绍了数据库性能调优的思路和总体策略。在系统级调优中数据库全局的 GUC 参数对整体性能的提升至关重要，而在语句级调优中 GUC 参数可以调整估算模型，选择查询计划中算子的类型，或者选择不同的执行计划。因此在 SQL 调优过程中合理的设置 GUC 参数十分重要。

2. 优化器 GUC 参数调优

在 GaussDB(DWS)中，SQL 语句的执行所需要经历的步骤如下图所示，其中红色部分为 DBA 可以介入实施调优的环节。

查询计划的生成是基于一定的模型和统计信息进行代码估算，在某些场景由于统计信息不准确或者代价估算有偏差时，就需要通过 GUC 参数设置的的方式选择更优的查询计划。

在 GaussDB(DWS)中，和 SQL 执行性能相关的 GUC 参数主要有以下几个：

• best_agg_plan: 进行聚集计算模型的设置

• enable_sort: 控制优化器是否使用的排序，主要用于让优化器选择使用 HashAgg 来实现聚集操作

• enable_hashagg：控制优化器是否使用 HashAgg 来实现聚集操作

• enable_force_vector_engine：开启参数后强制生成向量化的执行计划

• query_dop：用户自定义的查询并行度

2.1 best_agg_plan 参数

GaussDB(DWS)是分布式的数据库集群，数据计算尽量在各个 DN 上并行计算，可以得到最优的性能，在 Stream 框架下 Agg 操作可以分为两个场景。

Agg 下层算子输出结果集的分布列是 Group By 列的子集。

这种场景，直接对下层结果集进行汇聚的结果就是正确的汇聚结果，生成算子直接使用即可。例如以下语句，lineitem 的分布列是 l_orderkey，它是 Group By 的列。

selectl_orderkey,count(*) as count_orderfromlineitemgroup byl_orderkey;

查询计划如下：

Agg 下层算子输出结果集的分布列不是 Group By 列的子集。

对于这种场景 Stream 下的聚集（Agg）操作，优化器可以生成以下三种形态的查询计划：

• hashagg+gather(redistribute)+hashagg

• redistribute+hashagg(+gather)

• hashagg+redistribute+hashagg(+gather)

通常优化器总会选择最优的执行计划，但是众所周知代价估算，尤其是中间结果集的代价估算有时会有比较大的偏差。这种比较大的偏差就可能会导致聚集（agg）的计算方式出现比较大的偏差，这时候就需要通过 best_agg_plan 参数进行聚集计算模型的干预。

以下通过 TPC-H Q1 语句分析三种形态的查询计划：

-- TPC-H Q1selectl_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_orderfromlineitemwherel_shipdate <= date '1998-12-01' - interval '90' day (3)group byl_returnflag,l_linestatusorder byl_returnflag,l_linestatus;}

当 best_agg_plan=1 时，在 DN 上进行了一次聚集，然后结果通过 GATHER 算子汇总到 CN 上进行了二次聚集，对应的查询计划如下：

该方法适用于 DN 第一次聚集后结果集较少并且 DN 数较少的场景，在 CN 上进行第二次聚集时的结果集小，CN 不会成为计算瓶颈。

当 best_agg_plan=2 时，在 DN 上先按照 Group By 的列进行数据重分布，然后在 DN 上进行聚集操作，将汇总的结果返回给 CN，对应的查询计划如下：

该方法适用于 DN 第一次聚集后结果集缩减不明显的场景，因为这样可以省略 DN 上的第一次聚集操作。

当 best_agg_plan=3 时，在 DN 上进行一次聚集，然后将聚集结果按照 Group By 的列进行数据重分布，之后在 DN 上进行二次聚集得到结果，对应的查询计划如下：

该方法使用于 DN 第一次聚集后中间结果缩减明显，但最终结果行数比较大的场景。

GaussDB(DWS)中，以上三种方法的选择是根据代价来自动选择。在实际的 SQL 调优时，如果遇到有聚集方式不合理的场景，可以通过尝试设置 best_agg_plan 参数，选择最优的聚集方式。

2.2 enable_sort 参数

GaussDB(DWS)中实现分组聚集操作有两种方法：

• HashAgg：使用 Hash 表对数据进行去重，并同时进行聚集操作，适用于聚集后行数缩减较多的场景。

• Sort + GroupAgg：首先对数据进行排序，然后遍历排序后的数据，完成去重和聚集操作，适用于聚集后行数缩减较少的场景。

以下面的 SQL 为例：

selectl_orderkey,count(*) as count_orderfromlineitemgroup byl_orderkey;

如果使用 Sort + GroupAgg 的方式，在 Sort 排序算子里执行时间比较长，因为需要对大量数据进行排序操作。

以上这种场景，可以关闭 enable_sort 参数，选择使用 HashAgg 的方式来实现聚集操作，可以获得较好的执行性能。

2.3 enable_hashagg 参数

GaussDB(DWS)中通过 count distinct 来统计多个列的数据时，通常会使用 HashAgg 来实现每一个列的统计聚集操作，然后将结果通过 Join 方式关联起来得到最终结果。

以下面的 SQL 为例：

selectl_orderkey,count(distinct l_partkey) as count_partkey,count(distinct l_suppkey) as count_suppkey,count(distinct l_linenumber) as count_linenumber,count(distinct l_returnflag) as count_returnflag,count(distinct l_linestatus) as count_linestatus,count(distinct l_shipmode) as count_shipmodefromlineitemgroup byl_orderkey;

从查询计划来看，通过 count distinct 统计了 lineitem 表中的 6 列数据，是通过 6 个 HashAgg 操作来实现的，该 SQL 执行时消耗的资源相对较高。

如果关闭 enable_hashagg 参数，优化器会选择 Sort + GroupAgg 的方式，该 SQL 执行时消耗的资源相对较少。

在应用开发时，可以根据 SQL 并发和资源使用情况，通过设置 enable_hashagg 参数来选择合适的执行计划。

2.4 enable_force_vector_engine 参数

GaussDB(DWS)支持行存储和列存储两种存储模型，用户可以根据应用场景，建表的时候选择行存储还是列存储表。向量化执行将传统的执行模式由一次一元组的模型修改为一次一批元组，配合列存特性，可以带来巨大的性能提升。

如果使用行存表或者是行列混存的场景，由于行存表默认走的是行存执行引擎，最终查询无法走向量化执行引擎。

以下面的 SQL 为例：

selectl_orderkey,sum(l_extendedprice * (1 - l_discount)) as revenue,o_orderdate,o_shippriorityfromcustomer_row,orders,lineitemwherec_mktsegment = 'BUILDING'and c_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate < date '1995-03-15'and l_shipdate > date '1995-03-15'group byl_orderkey,o_orderdate,o_shippriorityorder byrevenue desc,o_orderdate    limit 10;

SQL 语句中的 customer_row 表为行存表，orders 和 lineitem 为列存表，该场景在默认参数的情况下无法走向量化引擎，Row Adapter 算子表示将列存数据转为行存数据，对应的查询计划为：

这种场景，可以选择开启 enable_force_vector_engine 参数，通过向量化执行引擎来执行，Vector Adapter 算子表示将行存数据转换为列存数据，每个算子前面的 Vector 表示改算子为向量化引擎的执行器算子，对应的查询计划为：

从上述计划可以看出，向量化引擎相比行执行引擎，执行性能有数倍的提升效果。

2.5 query_dop 参数

GaussDB(DWS)支持并行计算技术，当系统的 CPU、内存、I/O 和网络带宽等资源充足时，可以充分利用富余硬件资源，提升语句的执行速度。在 GaussDB(DWS)中，通过 query_dop 参数，来控制语句的并行度，取值如下：

• query_dop=1，串行执行

• query_dop=[2…N]，指定并行执行并行度

• query_dop=0，自适应调优，根据系统资源和语句复杂度情况自适应选择并行度

query_dop 参数设置的一些原则：

• 对于短查询为主的 TP 类业务中，如果不能通过 CN 轻量化或下发语句进行业务的调优，则生成 SMP 计划的时间较长，建议设置 query_dop=1。

• 对于 AP 类复杂语句的场景，建议设置 query_dop=0。

• 计划并行执行之后必定会引起资源消耗的增加，当资源成为瓶颈的情况下，SMP 无法提升性能，反而可能导致性能的劣化。出现资源瓶颈的情况下，建议关闭 SMP，即设置 query_dop=1。

设置 query_dop=0 可以实现自适应调优，在部分场景下语句执行的并行度没有达到最优，这种情况可以考虑通过 query_dop 参数设置并行度。

例如下面的 SQL：

select count(*) from (    select    l_orderkey,    count(*) as count_order    from    lineitem    group by    l_orderkey);

在 query_dop=0 时使用的并行度为 2。

设置 query_dop=4 时使用的并行度为 4，执行时间相比并行度为 2 时有明显的提升。

3. 数据库全局 GUC 参数

在使用 GaussDB(DWS)时，全局的 GUC 参数对集群整体性能影响很大，这里介绍一些常用参数以及推荐的配置。

3.1 数据内存参数

影响数据库性能的五大内存参数有：max_process_memory、shared_buffers、cstore_buffers、work_mem 和 maintenance_work_mem。

max_process_memory

max_process_memory 是逻辑内存管理参数，主要功能是控制单个 CN/DN 上可用内存的最大峰值。

计算公式：max_process_memory=物理内存*0.665/(1+主 DN 个数)。

shared_buffers

设置 DWS 使用的共享内存大小。增加此参数的值会使 DWS 比系统默认设置需要更多的 System V 共享内存。

建议设置 shared_buffers 值为内存的 40%以内。主要用于行存表 scan。计算公式：shared_buffers=(单服务器内存/单服务器 DN 个数)0.40.25

cstore_buffers

设置列存和 OBS、HDFS 外表列存格式（orc、parquet、carbondata）所使用的共享缓冲区的大小。

计算公式可参考 shared_buffers。

work_mem

设置内部排序操作和 Hash 表在开始写入临时磁盘文件之前使用的内存大小。

ORDER BY，DISTINCT 和 merge joins 都要用到排序操作。Hash 表在散列连接、散列为基础的聚集、散列为基础的 IN 子查询处理中都要用到。

对于复杂的查询，可能会同时并发运行好几个排序或者散列操作，每个都可以使用此参数所声明的内存量，不足时会使用临时文件。同样，好几个正在运行的会话可能会同时进行排序操作。因此使用的总内存可能是 work_mem 的好几倍。

计算公式：

对于串行无并发的复杂查询场景，平均每个查询有 5-10 关联操作，建议 work_mem=50%内存/10。

对于串行无并发的简单查询场景，平均每个查询有 2-5 个关联操作，建议 work_mem=50%内存/5。

对于并发场景，建议 work_mem=串行下的 work_mem/物理并发数。

maintenance_work_mem

maintenance_work_mem 用来设置维护性操作（比如 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 等）中可使用的最大的内存。

当自动清理进程运行时，autovacuum_max_workers 倍数的内存将会被分配，所以此时设置 maintenance_work_mem 的值应该不小于 work_mem。

3.2 连接相关 GUC 参数

连接相关的参数有两个：max_connections 和 max_prepared_transactions

max_connections

允许和数据库连接的最大并发连接数。此参数会影响集群的并发能力。

设置建议：CN 中此参数建议保持默认值。DN 中此参数建议设置为 CN 的个数乘以 CN 中此参数的值。

增大这个参数可能导致 GaussDB(DWS)要求更多的 System V 共享内存或者信号量，可能超过操作系统缺省配置的最大值。这种情况下，请酌情对数值加以调整。

max_prepared_transactions

设置可以同时处于"预备"状态的事务的最大数目。增加此参数的值会使 GaussDB(DWS)比系统默认设置需要更多的 System V 共享内存。

NOTICE:

max_connections 取值的设置受 max_prepared_transactions 的影响，在设

max_connections 之前，应确保 max_prepared_transactions 的值大于或等

max_connections 的值，这样可确保每个会话都有一个等待中的预备事务。

3.3 并发控制 GUC 参数

max_active_statements

设置全局的最大并发数量。此参数只应用到 CN，且针对一个 CN 上的执行作业。

需根据系统资源（如 CPU 资源、IO 资源和内存资源）情况，调整此数值大小，使得系统支持最大限度的并发作业，且防止并发执行作业过多，引起系统崩溃。

当取值-1 或者 0 时，不限制全局并发数。

在点查询的场景下，参数建议设置为 100。

在分析类查询的场景下，参数的值设置为 CPU 的核数除以 DN 个数，一般可以设置 5~8 个。

3.4 其他 GUC 参数

bulk_write_ring_size

数据并行导入使用的环形缓冲区大小。

该参数主要影响入库性能，建议导入压力大的场景增加 DN 上的该参数配置。

checkpoint_completion_target

指定检查点完成的目标。

含义是每个 checkpoint 需要在 checkpoints 间隔时间的 50%内完成。

默认值为 0.5，为提高性能可改成 0.9。

data_replicate_buffer_size

发送端与接收端传递数据页时，队列占用内存的大小。此参数会影响主备之间复制的缓冲大小。

默认值为 128MB，若服务器内存为 256G，可适当增大到 512MB。

wal_receiver_buffer_size

备机与从备接收 Xlog 存放到内存缓冲区的大小。

默认值为 64MB，若服务器内存为 256G，可适当增大到 128MB

4. 总结

本篇文章主要介绍了 GaussDB(DWS)性能调优涉及到的优化器和系统级 GUC 参数，通过合理配置这些 GUC 参数，能够充分利用好 CPU、内存、磁盘 IO 和网络 IO 等资源，提升语句的执行性能和 GaussDB(DWS)集群的整体性能。

5. 参考文档

1. GaussDB(DWS) SQL 进阶之 SQL 操作之聚集函数 https://bbs.huaweicloud.com/blogs/293963
   

2. PB 级数仓 GaussDB(DWS)性能黑科技之并行计算技术解密 https://bbs.huaweicloud.com/blogs/203426
   

3. 常见性能参数调优设计 https://support.huaweicloud.com/performance-dws/dws_10_0068.html
   

文章转载自：华为云开发者联盟

原文链接：https://www.cnblogs.com/huaweiyun/p/18119306

体验地址：http://www.jnpfsoft.com/?from=001