一、    场景介绍

当有多个数据库用户同时在DWS上执行SQL作业时，可能出现以下情况：

1. 一些复杂SQL可能会长时间占用集群资源，从而影响其他查询的性能。例如一组数据库用户不断提交复杂、耗时的查询，而另一组用户经常提交短查询。在这种情况下，短时查询可能不得不在队列中等待耗时查询完成。
2. 一些SQL由于数据倾斜、执行计划未调优等原因，占用过多内存空间，导致其他语句因申请不到内存而报错，或占用过多磁盘空间，导致磁盘满而触发集群只读，无法进行写入。

         为了提高系统整体吞吐量，避免坏SQL影响系统整体运行，您可以使用DWS工作负载管理功能处理这类问题，DWS工作负载管理以工作负载队列为资源承载，对于不同的业务类型可以创建不同的工作负载队列，为这些队列配置不同的资源占比，然后将数据库用户添加至对应的队列中，以此来限制这些数据库用户的资源使用。例如，将经常提交复杂查询作业的数据库用户分为一类，为这类用户创建一个工作负载队列并给这个队列分配多一些的资源，之后将这类用户添加至这个队列中，那么这类用户所提交的复杂作业只能使用所创建队列拥有的资源；同时再创建一个占用资源较少的队列分配给执行短查询的用户使用，这样两种作业就能够同时执行互不影响。

二、    框架介绍

在实际业务运行中，系统压力可能集中在集群中的一部分节点或者系统资源中的某项资源，导致系统资源不能充分利用，集群性能不能充分发挥。因此，GaussDB(DWS)提供了基于用户资源池的资源管理功能，将不同类型的作业关联到不同的资源池分别管理，以实现系统资源（并发、CPU、内存、存储空间）的隔离和作业的异常处理，避免发生资源的不合理占用。

工作负载管理支持配置的队列资源包含：并发、内存、CPU及磁盘空间，关联该队列的用户执行的所有作业均受队列资源限制。总体架构如下：

三、    配置指南

(一)    页面基本操作

1.  进入工作负载管理

步骤1：登录华为云或HCS首页，选择数据仓库服务跳转到DWS管理控制台。

步骤2：在集群列表中单击需要访问“工作负载管理”页面的集群名称，例如DWS_openlab。

步骤3：切换至“工作负载管理”页签。

在工作负载管理页面，您可以修改工作负载管理的全局配置，可以在工作负载管理页面添加、创建、修改工作负载队列，添加数据库用户至队列，也可以将队列中的某个数据库用户从队列中移除。负载管理页面添加、创建、修改工作负载队列，添加数据库用户至队列，也可以将队列中的某个数据库用户从队列中移除，页面概览如下。

----结束

2.  打开或关闭工作负载管理

工作负载管理配置包括工作负载开关、全局最大并发数。这里的全局最大并发数指的是单个CN上的最大并发数，如果您通过工作负载开关关闭了工作负载管理功能，那么所有的工作负载管理功能将不再可用。

3.  资源配置

在“资源配置”一栏，您可以浏览当前工作负载队列的资源配置情况，包括“CPU资源（%）”、“内存资源（%）”、“存储资源（MB）”、“查询并发”。

4.  设置异常规则

在“异常规则”一栏，您可以浏览当前工作负载队列中的异常规则设置情况，异常规则允许您对队列中用户执行的作业做异常控制。

5.  关联用户

在“关联用户”一栏，您可以浏览当前工作负载队列中已关联的用户，且可以看到当前时间每个用户已使用的内存以及磁盘使用量，如下图所示。

(二)    添加工作负载队列

步骤1： 登录DWS管理控制台。

步骤2： 在集群列表中单击需要访问“工作负载管理”页面的集群名称。

步骤3： 切换至“工作负载管理”页签。

步骤4： 单击工作负载队列旁的“+”符号添加队列。

【说明】最多可创建63个工作负载队列。

步骤5： 参见表1-1填写工作负载队列的名称和相关资源配置。

表1-1 工作负载队列参数

显示添加工作负载队列：

【说明】参数项中，CPU资源是配额，当系统有空闲CPU资源时，可以允许超过CPU设置的配额值。内存资源、储存资源、查询并发参数是限额。

步骤6： 核对信息后，单击“确定”，添加队列完成。

----结束

(三)    修改工作负载队列

在工作负载队列中，您可以对某个工作负载队列的参数进行修改。

步骤1： 登录DWS管理控制台。

步骤2： 在集群列表中单击需要访问“工作负载管理”页面的集群名称。

步骤3： 切换至“工作负载管理”页签。

步骤4： 在左侧“工作负载队列”中单击需要修改的队列名称，出现如下页面，包括“短查询配置”、“资源配置”、“异常规则”、“关联用户”。

步骤5： 修改短查询配置。修改为相应取值，单击右侧“保存”。

如果集群中运行的是查询业务，或查询跑批混合业务，建议开启短查询加速，并根据当前队列在每个CN上可能运行的最大短查询并发数来设置短查询并发。这样，在系统中占用内存小于32MB的短查询将会单独排队运行，不和跑批复杂查询一起排队，加快短查询响应速度。

配置完成后点击保存：

步骤6： 修改资源配置。

（1）单击右侧“编辑”，参见表1-2修改相应参数。

表1-2 工作负载队列参数

（2）单击“确定”。

【说明】

• CPU和内存支持动态调整。

约束条件：参数值调小时无限制；参数值调大时，如果总配置值（参数调整值+已配置值）超过100%，需要先将空闲队列参数值调小，再将目标队列参数值调大，控制参数总配置值不超过100%。

• 存储资源支持动态调整。

约束条件：参数值调大时无限制；参数值调小时，如果设置值大于已使用空间，支持修改，如果设置值小于已使用空间，不支持修改（报错）。

• 查询并发支持动态调整。

约束条件：无。

步骤7： 修改异常规则。

（1）参见表1-3修改相应参数。

说明：异常规则允许您对队列中用户执行的作业做异常控制，目前支持表1-3的相关配置。

– 如选择“终止”，则需要设置相应时间。

– 如选择“不约束”，则无异常规则约束。

表1-3 异常规则参数

（2）单击“保存”。

步骤8： 关联用户。

说明：

• 一个数据库用户只有被添加到某个队列中之后，该用户运行作业所使用的资源才能被管控。
• 一个数据库用户只能被添加至一个队列中，从队列中移除的用户可以再次添加至其他队列。

（1） 单击右侧“添加”。

（2）从当前用户列表中，勾选需要添加的用户，一次可勾选多个。

（3）单击“确定”。

（4）如果需要删除用户，则单击待删除用户所在行右边的“删除”即可。

----结束

(四)    查询概览

步骤1： 登录DWS管理控制台。

步骤2： 在集群列表中单击需要访问“工作负载管理”页面的集群名称。

步骤3： 切换至“工作负载管理”页签。

步骤4： 在左侧“工作负载队列”中单击需要查看的队列名称。

在“查询概览”区域：可以看到当前时间当前队列中正在运行的长短查询数，图表信息15秒刷新一次。

----结束

(五)    删除工作负载队列

步骤1： 登录DWS管理控制台。

步骤2： 在集群列表中单击需要访问“工作负载管理”页面的集群名称。

步骤3： 切换至“工作负载管理”页签。

步骤4： 在左侧“工作负载队列”中单击需要删除的队列名称。

步骤5： 单击右侧的“删除”。

说明：删除队列时如果队列中有关联的数据库用户，那么队列删除后这些用户将被关联默认队列。

(六)    资源限制相关参数

除了页面提供的工作负载管理功能外，DWS也提供了一些guc参数，来限制单语句占用的最大资源，避免单个语句占用过多资源，导致系统不可用。

1. query_max_memory: 限制单语句在单DN上占用的最大内存量，如单语句分配内存超过这个值，将进行报错，将内存归还给系统。建议按如下方法配置：

• 登陆任意CN查询：execute direct on (dn_1) 'select * from pg_total_memory_detail where memorytype = ''max_dynamic_memory''';查出来的值即为单个DN上所有SQL可以使用的总内存。
• 建议根据业务并发数，将query_max_memory设置为上步查出总内存的1/2, 1/3,假设系统表发数较小(<20),可设置为1/2, 如为中大并发(>20),可设置为1/3。

1. sql_use_spacelimit：限制单语句在单个DN上最大插入的数据量大小，如单语句在单个DN上插入数据量超过这个值，将进行报错，避免单个SQL在单个DN上插入的数据量过大，造成DN严重倾斜，导致性能差，及磁盘满问题。建议此参数设置为单DN可用总空间的1/5。

----结束

四、    最佳实践

以下以两个实践案例来说明工作负载管理的实际应用。其中案例一为XX财政工作负载管理配置，可以作为小规模集群(一般小于50节点)混合业务场景下的配置参考。案例二为XX银行工作负载管理配置，可以作为中大规模集群联机查询和复杂跑批混合业务场景下的配置参考。其中的配置指标需要根据实际业务需求确定。

(一)    小规模集群：XX财政工作负载管理规划

XX财政业务场景主要分为联机交易（OLTP）和报表分析（OLAP）两大类，其中报表服务的优先级相对较低，在合理的情况下优先保障业务系统的正常运行。XX财政节点规模较小，其工作负载管理目标为避免报表分析业务影响联机交易，其资源负载管理配置可供中小规模集群(<60节点)的数据集市、BI分析、小型数仓集群参考。

业务系统中运行的SQL分为简单SQL和复杂SQL，大量复杂SQL的并发执行会导致数据库服务器资源争抢，简单SQL的大量并发对服务器不构成持续压力，短时间内可执行完成，不会造成业务堆积。其中报表服务中运行的SQL以复杂SQL居多，整体业务逻辑相对复杂，在数据库层面需要分别对核心交易和报表服务进行合理的资源管控，以保障业务系统正常运行。

报表分析类业务的优先级和实时性相对较低，但是复杂度更高，为有效进行资源管控，将报表分析和核心交易业务进行数据库用户分离，例如核心交易业务使用数据库用户budget_config_user，报表分析业务使用数据库用户report_user。针对交易用户和报表用户分别进行CPU资源和并发数控制以保障数据库稳定运行。

结合报表分析业务的负载调研、日常监控和测试验证，50并发以内的复杂报表SQL不会引起服务器资源争抢，不会引起业务系统卡慢，配置报表用户可使用20%的CPU资源。

结合核心交易业务的负载调研、日常监控和测试验证，100并发以内的查询SQL不会对系统造成持续压力，配合交易用户可使用60%的CPU资源。

• 交易用户资源配置：CPU=60%，并发=200，内存=60%，存储=1024000MB。
• 报表用户资源配置：CPU=20%，并发=20，内存=20%，存储=1024000MB。
• 设置单个语句最大内存使用量，超过使用量则报错退出，避免单个语句占用过多内存。

异常规则中设置阻塞时间=1200S，执行所消耗时间1800s,强制终止。

(二)    中大规模集群：XX银行工作负载管理规划

XX银行使用DWS作为银行核心数据仓库，每日运行批处理任务，也供业务用户进行一些灵活查询。为了更充分的利用系统资源，同时兼顾到各个类型业务的要求，根据DWS工作负载管理提供的能力，设计了XX银行工作负载管理方案。

XX银行节点规模大，达240节点，其工作负载管理目标为合理调配多种业务占用的资源，达到资源利用效率的最佳配置，其资源负载管理配置可供中大规模集群(>60节点)的企业级数据仓库、行业云数据仓库参考。

XX银行数据仓库中的任务可以分为以下三大类：

1. 跑批任务。跑批任务是数据仓库内主要运行的任务，主要分为数据加载、批量加工和数据导出三类。其中，加载作业先运行，然后是批量加工作业，最后由数据导出作业将加工的结果同步到下游系统。加载作业主要在0:00~05:00运行，批量加工作业基本持续一整天，需要和其他类型任务一起综合考虑资源分配。导出任务主要在12:00~14:00运行。
2. 业务用户灵活查询。业务灵活查询由业务分析师、数据挖掘工程师等发起，主要在上班时间内进行，即每天09:00~12:00， 14:00~20:00两个时间段。
3. 运维任务(备份等)。运维任务主要是数据备份。当前XX银行数据备份采用的是GDS导出方案，主要在20:00~24:00运行。

具体工作负载计划见下表所示：

因此，根据上述6类任务类型，划分6个队列，每个队列都可以在相应时间段内按照负载计划自动调整资源利用情况(自动调整资源功能需升级到最新版本才能使用)，在每个时间段内分配不同的并发、CPU、内存资源，实现了不同时间段内作业优先级的动态调整，即保证了业务优先级，也使得系统总体资源利用最大化。同时，每个队列也根据业务实际需求分配了最大空间使用量。下图为相应的负载模型，将不同的用户加入到不同的队列中，来实现对用户占用资源的管控。

此外，针对异常作业占用过多资源问题，XX银行也利用DWS异常处理功能，设置了丰富的异常SQL处理规则，见下表所示：

    同时，为了避免单个语句占用过多磁盘、内存资源，也可以使用以下参数来对单语句使用的内存、磁盘空间资源进行限制。

query_max_memory:限制单语句在单DN上占用的最大内存量，如单语句分配内存超过这个值，将进行报错，将内存归还给系统。XX银行单DN最大可用动态内存为100GB，并发数为60，query_max_memory设置为30GB。

sql_use_spacelimit：限制单语句在单个DN上最大插入的数据量大小，XX银行单个DN可用磁盘空间为2TB，sql_use_spacelimit设置为400GB。

GaussDB(DWS)实践系列-工作负载管理交付指南（线下版本）
博文： https://bbs.huaweicloud.com/blogs/266993
论坛： https://bbs.huaweicloud.com/forum/forum.php?mod=viewthread&tid=128274

想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料~