暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / GaussDB(DWS)如何限制临时数据文件下盘量

GaussDB(DWS)如何限制临时数据文件下盘量

GaussDB DWS 2022-04-23
696

有些SQL语句，会出现中间结果集太大，内存放不下，需要落盘到外存（比如存在对大数据量进行聚集等操作，导致聚集操作的中间结果集在内存中放不下时会下盘），且落盘生成的临时数据文件占用空间过大，则会影响正常的数据写入业务无法执行，磁盘只能提供只读操作。
对于上述场景，可以通过两种方式，来控制用户执行过程中间结果集可落盘的数额，当超过限额，会报错终止该语句的执行，以防临时数据文件占用空间过大：
方案1：设置每个线程的临时文件落盘数据量限制
方案2：为用户设置中间结果集落盘空间限额

方案一：设置每个线程的临时文件罗盘数据量限制

设置GUC参数temp_file_limit可以限制每个线程的临时文件落盘数据量限制。temp_file_limit属于SUSET类型参数，取值范围：整型，单位为KB。其中-1表示没有限制。默认值：-1。1、如何设置temp_file_limit参数
可通过gs_guc工具进行全局设置，如下：gs_guc reload -Z coordinator -Z datanode -N all -I all -c "temp_file_limit = 1024"2、temp_file_limit取值计算公式可以用下面的公式粗略的计算一个temp_file_limit的取值：temp_file_limit = 预计的总下盘量/同时下盘线程数总下盘量一般可设置为可用空间的20%，这个百分比可根据用户的可接受程度进行调节。同时下盘线程数是业务运行中，通常情况下并发的query中产生中间临时数据下盘的线程数。随着数据库中存储的数据量增加，temp_file_limit的值要适时调整。注意：此参数是限制每个线程的临时文件落盘数据量，如果一个query有多个线程，单个线程落盘数据量超过此参数限制，query会报错退出。如果每个线程都没超过限制，但多个线程下盘数据量累计超过此参数限制，并不会报错退出。3、示例以TPC-DS 1x数据中的customer_demographics表为例。SQL查询不下推，中间结果集仅在CN上落盘
postgres=# show temp_file_limit;
temp_file_limit
-----------------
1MB
(1 row)

postgres=# set enable_stream_operator=off;
SET

postgres=# explain select * from customer_demographics c1, customer_demographics c2 order by c1.cd_demo_sk;
                                                         QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 id |                                operation                                 |    E-rows     | E-width |     E-costs      
----+--------------------------------------------------------------------------+---------------+---------+------------------
  1 | ->  Sort                                                                 | 3689472640000 |     112 | 2329492473090.72
  2 |    ->  Nested Loop (3,4)                                                 | 3689472640000 |     112 | 36894726400.00  
  3 |       ->  Data Node Scan on customer_demographics "_REMOTE_TABLE_QUERY_" |       1920800 |      56 | 0.00            
  4 |       ->  Data Node Scan on customer_demographics "_REMOTE_TABLE_QUERY_" |       1920800 |      56 | 0.00            
(6 rows)

postgres=# select * from customer_demographics c1, customer_demographics c2 order by c1.cd_demo_sk;
ERROR:  temporary file size exceeds temp_file_limit (1024kB)

方案二：为用户设置中间结果集落盘空间限额

1、如何设置用户中间结果集落盘空间限额
有两种方式可以设置用户中间结果集落盘空间限额：
1）通过CREATE USER指定SPILL SPACE，为新建用户设置中间结果集落盘限额
       CREATE USER user_name … SPILL SPACE 'spillspacelimit';
2）通过ALTER USER指定SPILL SPACE，修改已有用户的中间结果集落盘空间限额
       ALTER USER user_name … SPILL SPACE 'spillspacelimit';
比如：
CREATE USER u1 PASSWORD ‘abcd@1234’ SPILL SPACE 'unlimited'; --创建用户并设置中间结果集落盘限额为无限制
ALTER USER u1 SPILL SPACE '1G'; --修改用户u1的中间结果集落盘限额为1G
说明：
1）此设置是对所有节点生效的，即一条SQL在集群的CN和所有DN的落盘数据量之和超过限制，则语句就会报错终止。
2）当中间结果集落盘时，该用户的临时文件落盘数据量相应增加；当临时文件删除时，该用户的临时文件落盘数据量相应减少。
3）此设置是用户级的，如果同一用户同时并发运行多个query，则会累计每个query中间结果集落盘数据量。
注意：
要使上面的设置生效，需要设置GUC参数enable_perm_space为on。
如果多个用户都会执行大量中间结果集下盘操作，那么需要对涉及到的每个用户都进行设置。
2、示例
示例1：中间结果集在CN和DN上都会落盘，总的落盘数据量会超过1G
postgres=# create user u1 password 'abcd@1234';
CREATE USERpostgres=# grant select on customer_demographics to u1;GRANTpostgres=# alter user u1 spill space '1G';ALTER USERpostgres=# alter session set session authorization u1 password 'abcd@1234';SETpostgres=> select * from customer_demographics c1, customer_demographics c2 order by c1.cd_demo_sk;ERROR:  spill space is out of user's spill space limit
示例2：SQL查询不下推，中间结果集仅在CN上落盘
postgres=# set enable_stream_operator=off;SETpostgres=# alter session set session authorization u1 password 'abcd@1234';SETpostgres=> explain select * from customer_demographics c1, customer_demographics c2 order by c1.cd_demo_sk;                                                         QUERY PLAN                                                          ------------------------------------------------------------------------------------------------------------------------------ id |                                operation                                 |    E-rows     | E-width |     E-costs      ----+--------------------------------------------------------------------------+---------------+---------+------------------  1 | ->  Sort                                                                 | 3689472640000 |     112 | 2329492473090.72  2 |    ->  Nested Loop (3,4)                                                 | 3689472640000 |     112 | 36894726400.00    3 |       ->  Data Node Scan on customer_demographics "_REMOTE_TABLE_QUERY_" |       1920800 |      56 | 0.00              4 |       ->  Data Node Scan on customer_demographics "_REMOTE_TABLE_QUERY_" |       1920800 |      56 | 0.00            (6 rows)postgres=> select * from customer_demographics c1, customer_demographics c2 order by c1.cd_demo_sk;ERROR: spill space is out of user's spill space limit

总结

第一种方案偏重于限制每个线程的临时文件下盘量，第二种方案偏重于限制用户的临时文件下盘量，要结合业务的目的来选择更适合的参数及参数设置，避免发生临时文件下盘量过大影响正常业务执行。
“送一套环境，手把手教您驾驭华为云数仓”直播互动获奖名单已公示，小伙伴们快戳阅读原文查看名单吧~
往期精彩回顾




gaussdb
文章转载自GaussDB DWS，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论