资源管理优秀实践

场景介绍

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

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

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

以xx银行为例，该银行业务场景主要分为联机交易（OLTP）和报表分析（OLAP）两大类，其中报表服务的优先级相对较低，在合理的情况下优先保障业务系统的正常运行。业务系统中运行的SQL分为简单SQL和复杂SQL，大量复杂SQL的并发执行会导致数据库服务器资源争抢，简单SQL的大量并发对服务器不构成持续压力，短时间内可执行完成，不会造成业务堆积。其中报表服务中运行的SQL以复杂SQL居多，整体业务逻辑相对复杂，在数据库层面需要分别对核心交易和报表服务进行合理的资源管控，以保障业务系统正常运行。

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

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

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

• 报表用户资源配置（对应资源池pool_1）：CPU=20%，内存=20%，存储=1024000MB，并发=20。
• 交易用户资源配置（对应资源池pool_2）：CPU=60%，内存=60%，存储=1024000MB，并发=200。

设置单个语句最大内存使用量，超过使用量则报错退出，避免单个语句占用过多内存。

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

步骤一：创建集群

参见创建集群完成集群创建。

步骤二：连接集群并导入数据

1. 参见使用gsql命令行客户端连接集群连接集群。
2. 导入样例数据。参见导入TPC-H数据。
3. 执行以下语句创建核心交易用户budget_config_user和报表用户report_user。

   1 2	CREATE USER budget_config_user PASSWORD 'password'; CREATE USER report_user PASSWORD 'password';

4. 为测试需要，将tpch模式下所有表的所有权限授予两个用户。

   1	GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA tpch to budget_config_user,report_user;

5. 查看当前两个用户的资源分配情况。

   1	SELECT * FROM PG_TOTAL_USER_RESOURCE_INFO where username in ('budget_config_user' , 'report_user');

   

步骤三：创建资源池

1. 登录GaussDB(DWS) 管理控制台，在集群列表中单击集群名称，切换至“资源管理”页签。
2. 单击“添加资源池”创建资源池。参见场景介绍的模型分别创建报表业务资源池pool_1和核心交易资源池pool_2。

   

   

3. 修改异常规则。
   1. 单击创建好的pool_1资源池。
   2. 在异常规则中，修改“阻塞时间”和“执行所消耗时间”分别为1200s和1800s。
   3. 单击“保存”。
   4. 重复以上步骤，修改pool_2。

   

4. 关联用户。
   1. 左侧单击“pool_1”资源池。
   2. 单击“关联用户”右侧的“添加”。
   3. 勾选报表业务对应的用户report_user，单击“确定”。
   4. 重复以上步骤，将核心交易的用户budget_config_user添加入pool_2的资源池中。

   

   

步骤四：异常规则验证

1. 使用用户report_user登录数据库。
2. 执行如下命令查看用户report_user所属资源池。

   1	SELECT usename,respool FROM pg_user WHERE usename = 'report_user';

   

   查询显示用户report_user所属资源池为pool_1。

3. 校验资源池pool_1所绑定的异常规则。

   1	SELECT respool_name,mem_percent,active_statements,except_rule FROM pg_resource_pool WHERE respool_name='pool_1';

   

   确认资源池pool_1所绑定的是异常规则rule_1。

4. 查看当前用户异常规则的规则类型和阈值。

   1	SELECT * FROM pg_except_rule WHERE name = 'rule_1';

   

   查询显示rule_1中所绑定的规则为3设置的“阻塞时间1200秒，运行时长1800秒”，则会终止查询。

   

   • PG_EXCEPT_RULE系统表存储关于异常规则的信息，该系统表仅8.2.0及以上集群版本支持。
   • 同一条异常规则内的参数项相互之间为且的关系。

5. 执行作业，当运行时长超过“阻塞时间1200秒，运行时长1800秒”时，报错提示作业被取消并提示所触发的异常规则限制。

   

   作业执行过程中，如果出现类似“ERROR: canceling statement due to workload manager exception.”的报错信息，表示该作业超过异常规则的规则阈值限制被终止。若规则设置合理，那么就需要考虑从业务角度进行语句优化，减少执行时间。