实践系列-GaussDB(DWS)常用参数优化

1.【query_dop】 

SMP架构是一种利用富余资源来换取时间的方案，计划并行之后必定会引起资源消耗的增加，包括CPU、内存、I/O和网络带宽等资源的消耗都会出现明显的增长，而且随着并行度的增大，资源消耗也随之增大。

想要利用SMP提升查询性能需要满足以下条件：

系统的CPU、内存、I/O和网络带宽等资源充足。SMP架构是一种利用富余资源来换取时间的方案，计划并行之后必定会引起资源消耗的增加，当上述资源成为瓶颈的情况下，SMP无法提升性能，反而可能导致性能的劣化。在出现资源瓶颈的情况下，建议关闭SMP。

资源许可的情况下，并行度越高，性能提升效果越好。

SMP并行度支持会话级设置，推荐客户在执行符合要求的查询前，打开smp，执行结束后，关闭smp。以免在业务峰值时，对业务造成冲击。

query_dop的默认值是1， 可通过set query_dop=10；语句在会话中打开smp。

2.【enable_dynamic_workload】

动态负载管理指数据库内部根据用户负载情况，自动对复杂查询进行队列控制，不再需要手动设置参数，做到系统参数免调优。

该参数默认打开，有以下几个需要注意的点。

•集群有一个CN会作为中心协调节点（CCN），用于收集和调度作业执行，该节点可以通过gs_om -t status --detail查询到，Central Coordinator State会显示其状态。当CCN不存在时，作业不再受动态负载管理控制。

•简单查询作业（估算值<32MB）、非DML（即非INSERT、UPDATE、DELETE和SELECT）语句，不走自适应负载，需要通过max_active_statements来进行单CN的上限控制。

•默认work_mem数值为64MB，在自适应负载特性下，该数值不能变大，否则会引起内存不受控（例如未做Analyze的语句）。

•以下场景或语句由于内存使用特殊性和不确定性，可能导致大并发场景内存不受控，如果遇到需要降低并发数。

•单条元组占用内存过大的场景，例如，基表包含超过MB级别的宽列。

•完全下推语句的查询。

•需要在CN上耗费大量内存的语句，例如，不能下推的语句，withhold cursor场景。

•由于计划生成不当导致hashjoin算子建立的hash表重复值过多，占用大量内存。

•包含UDF的场景，且UDF中使用大量内存的场景。

该参数可配合query_dop=0使用，当query_dop设成0（自适应），系统会根据资源情况和计划特征，动态为每个查询选取[1,8]之间的最优的并行度。enable_dynamic_workload参数会动态分配内存。

3.【max_active_statements】

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

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

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

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

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

4.【session_timeout】

缺省情况下，客户端连接数据库后处于空闲状态时会根据参数的默认值自动断开连接。

建议设成0，以防止超时断连。增加使用体验 :)

5.影响数据库内存的参数主要是基友5兄弟: 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个数)。

•s【hared_buffers】

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

      建议设置shared_buffers值为内存的40%以内。主要用于行存表scan。

      计算公式：shared_buffers=(单服务器内存/单服务器DN个数)*0.4*0.25

      checkpoint_segments     

      提升此参数可加快大数据的导入速度，但需要结合shared_buffers参数统一考虑，默认值为64。例如shared_buffers被设为4G，可将DN 的checkpoint_segments设为256。

•【cstore_buffers】

设置列存和HDFS所使用的共享缓冲区的大小。

       计算公式可参考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等）中可使用的最大的内存。

      设置建议： 

      建议设置此参数的值大于work_mem，可以改进清理和恢复数据库转储的速度。因为在一个数据库会话里，任意时刻只有一个维护性操作可以执行，并且在执行维护性操作时不会有太多的会话。

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

例如当服务器内存为256G，6DN，跑10,20等并发的时候，根据计算公式可以设置参数如下

max_process_memory: 25G

shared_buffers: 4G (行存表）

cstore_buffers: 4G(列存表)

work_mem: 1G

maintenance_work_mem: 1G

6.【bulk_write_ring_size】

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

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

7.【comm_quota_size】

该参数控制每个流通道每次数据传输的大小，默认数值1M。大并发场景下，提升该数值时，可提升通信性能，但消耗更多的内存，需要根据实际场景进行调优。若通过查询DN的视图pg_total_memory_detail，发现通信层使用内存已达参数comm_usable_memory的阈值时，需要减少该数值，如修改为512K。

8.连接2兄弟: max_connections,max_prepared_transactions

•【max_connections】

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

    设置建议：

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

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

•【max_prepared_transactions】

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

注意：

max_connections取值的设置受max_prepared_transactions的影响，在设置max_connections之前，应确保max_prepared_transactions的值大于或等于max_connections的值，这样可确保每个会话都有一个等待中的预备事务。

9.【checkpoint_completion_target】

指定检查点完成的目标。

默认值为0.5，含义是每个checkpoint需要在checkpoints间隔时间的50%内完成。

为提高性能可改成0.9。

10.【data_replicate_buffer_size】

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

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

11.【wal_receiver_buffer_size】

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

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

以上参数是我们调优过程中会经常碰到的一些参数，希望众同学可以有所收获。