# 数据库系统参数调优 为了保证数据库尽可能高性能地运行，建议依据资源情况和业务实际进行数据库系统GUC参数的设置。本章节旨在介绍一些常用参数以及推荐配置，关于参数的详细设置方法请参考[设置GUC参数](https://support.huaweicloud.com/devg-911-dws/dws_04_0885.html)。 #### 数据库内存相关参数 表1数据库内存相关参数 | GUC参数 | 描述 | 建议 | |:---|:---|:---| | max_process_memory | max_process_memory设置单个CN/DN可用的最大物理内存。 | - DN上该数值需要根据系统物理内存及单节点部署主DN个数决定的。计算公式如下：（物理内存大小 -- vm.min_free_kbytes）\* 0.8 / (n+主DN个数)。该参数目的是尽可能保证系统的可靠性，不会因数据库内存膨胀导致节点OOM。这个公式中提到vm.min_free_kbytes，其含义是预留操作系统内存供内核使用，通常用作操作系统内核中通信收发内存分配，至少为5%内存。即， max_process_memory=物理内存\*0.8/(n+主DN个数)，其中，当集群规模小于256时， n=1；当集群规模大于256且小于512时， n=2；当集群规模超过512时，n=3。 - CN上该数值内存可设置与DN数值一样。 - RAM：集群规划时分配给集群的最大使用内存。 | | shared_buffers | 设置DWS使用的共享内存大小。增加此参数值会使DWS比系统默认设置需要更多的System V共享内存。 | 建议设置shared_buffers值为内存的40%以内。行存列存分别进行设置。行存设大，列存设小。列存：(单服务器内存/单服务器DN个数)\*0.4\*0.25。 如果设置较大的shared_buffers需要同时增加checkpoint_segments的值，因为写入大量新增、修改数据需要消耗更多的时间周期。 | | cstore_buffers | 设置列存和OBS、HDFS外表列存格式（orc、parquet、carbondata）所使用的共享缓冲区的大小。 | 列存表使用cstore_buffers设置的共享缓冲区，几乎不用shared_buffers。因此在列存表为主的场景中，应减少shared_buffers，增加cstore_buffers。 OBS、HDFS外表使用cstore_buffers设置ORC、Parquet、Carbondata的元数据和数据的缓存，元数据缓存大小为cstore_buffers的1/4，最大不超过2GB，其余缓存空间为列存数据和外表列存格式数据共享使用。 | | work_mem | 设置内部排序操作和Hash表在开始写入临时磁盘文件之前使用的内存大小。 | 该参数默认小规格内存为512MB， 大规格内存为2GB（max_process_memory大于等于30GB为大规格内存，否则为小规格内存）。 建议参数work_mem依据查询特点和并发来确定，一旦work_mem限定的物理内存不够，算子运算数据将写入临时表空间，带来5-10倍的性能下降，查询响应时间从秒级下降到分钟级。 - 对于串行无并发的复杂查询场景，平均每个查询有5-10关联操作，建议work_mem=50%内存/10。 - 对于串行无并发的简单查询场景，平均每个查询有2-5个关联操作，建议work_mem=50%内存/5。 - 对于并发场景，建议work_mem=串行下的work_mem/物理并发数。 | | maintenance_work_mem | 设置维护性操作（比如VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY等）中可使用的最大的内存。 | 建议设置此参数的值等于work_mem，可以改进清理和恢复数据库转储的速度。因为在一个数据库会话里，任意时刻只有一个维护性操作可以执行，并且在执行维护性操作时不会有太多的会话。 当自动清理进程运行时，autovacuum_max_workers倍数的内存将会被分配，所以此时设置maintenance_work_mem的值应该不小于work_mem。 | #### 数据库并发队列相关参数 | GUC参数 | 描述 | 建议 | |:---|:---|:---| | max_active_statements（全局并发队列） | 控制单个CN上运行并发执行的作业数量。 | 采用全局并发队列机制将控制所有普通用户的执行作业，不区分复杂度，即执行语句都将作为一个执行单元，当并发执行的作业数量达到此参数阈值时，将进入队列等待。对于管理员执行的作业，不受全局并发控制的限制。 需根据系统资源（如CPU资源、IO资源和内存资源）情况，调整此数值大小，使得系统支持最大限度的并发作业，且防止并发执行作业过多，引起系统崩溃。 | | parctl_min_cost（局部并发队列） | 控制在一个CN上同一资源池内的并发作业数量。该GUC参数需联系技术支持设置。 | 局部并发控制机制根据执行作业的cost，控制复杂查询的并发作业数量。 | ![](https://support.huaweicloud.com/devg-911-dws/public_sys-resources/note_3.0-zh-cn.png) 全局并发队列参数max_active_statements调整需注意： - 如果参数max_active_statements设置为-1，也就是不限制全局并发数，会导致通信大并发下存在连接断开报错。 - 在点查询的场景下，参数max_active_statements建议设置为100。 - 在分析类查询的场景下，参数max_active_statements的值设置为CPU的核数除以DN个数，一般可以设置5\~8个。 #### 数据库通信相关参数 数据库集群默认使用TCP代理通信库进行节点间通信。 表2数据库通信相关参数 | GUC参数 | 描述 | 建议 | |:---|:---|:---| | comm_quota_size | 参数comm_quota_size控制每个流通道每次数据传输的大小，默认数值1M。 | 大并发场景下，提升该数值时，可提升通信性能，但消耗更多的内存，需要根据实际场景进行调优。若通过查询DN的视图pg_total_memory_detail，发现通信层使用内存已达参数comm_usable_memory的阈值时，需要减少该数值，如修改为512K。 | | comm_usable_memory | 参数comm_usable_memory用于控制数据库传输时，整个DN上可以用于通信的内存使用量。 | 该参数的取值并不是用于总量控制，仅作为内存流控的手段，即默认流控数值为1M，若内存使用量超过了该参数取值的1/2时，流控数值自动调整为默认流控的一半，若仅剩20%时，则流控数值改为最小流控值8K。 | #### 数据库连接相关参数 表3数据库连接相关参数 | GUC参数 | 描述 | 建议 | |:---|:---|:---| | max_connections | 允许和数据库连接的最大并发连接数。此参数会影响集群的并发能力。 | CN中此参数建议保持默认值。DN中此参数建议设置为CN的个数乘以CN中此参数的值。 增大这个参数可能导致DWS要求更多的System V共享内存或者信号量，可能超过操作系统缺省配置的最大值。这种情况下，请酌情对数值加以调整。 | | max_prepared_transactions | 设置可以同时处于"预备"状态的事务的最大数目。增加此参数的值会使DWS比系统默认设置需要更多的System V共享内存。 | max_connections取值的设置受max_prepared_transactions的影响，在设置max_connections之前，应确保max_prepared_transactions的值大于或等于max_connections的值，这样可确保每个会话都有一个等待中的预备事务。 | | session_timeout | 客户端连接数据库后处于空闲状态时会根据该参数的默认值自动断开连接。 | 0-86400，最小单位为秒（s），0表示关闭超时设置。一般不建议设置为0。 | #### 其他性能相关参数 表4其他性能相关参数 | GUC参数 | 描述 | 建议 | |:---|:---|:---| | enable_dynamic_workload | 设置是否开启动态负载管理。 动态负载管理指数据库内部根据用户负载情况，自动对复杂查询进行队列控制，不再需要手动设置参数，做到系统参数免调优。 | 该参数默认打开，需要注意以下几点： - 简单查询作业（估算值\<32MB）、非DML（即非INSERT、UPDATE、DELETE和SELECT）语句，不走自适应负载，需要通过max_active_statements来进行单CN的上限控制。 - 在自适应负载特性下，参数work_mem的默认数值不能变大，否则会引起内存不受控（例如未做Analyze的语句）。 - 以下场景或语句由于内存使用的特殊性和不确定性，可能导致大并发场景内存不受控，遇到需要降低并发数： - 单条元组占用内存过大，例如，基表包含超过MB级别的宽列。 - 完全下推语句的查询。 - 需要在CN上耗费大量内存的语句，例如，不能下推的语句、withhold cursor场景。 - 由于计划生成不当导致hashjoin算子建立的hash表重复值过多，占用大量内存。 - 包含UDF，且UDF中使用大量内存。 - 该参数可配合query_dop=0使用，当query_dop设置为0（自适应），系统会根据资源情况和计划特征，动态为每个查询选取最优并行度。enable_dynamic_workload参数会动态分配内存。 | | bulk_write_ring_size | 数据并行导入使用的环形缓冲区大小。 | 该参数主要影响入库性能，建议导入压力大的场景增加DN上的该参数配置。 默认值为2GB。 | | data_replicate_buffer_size | 发送端与接收端传递数据页时，队列占用内存的大小。 | 该参数会影响主备之间复制的缓冲大小。 CN默认值为16MB，DN默认值为128MB。若服务器内存为256GB，可适当增大到512MB。 |