数仓如何限制临时数据文件下盘量
source link: https://my.oschina.net/u/4526289/blog/5320946
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
摘要：查询的中间结果集如果太大导致落盘生成的临时数据文件，本文提供两种限制临时数据文件下盘数据量的方案，以防影响正常业务运行。
本文分享自华为云社区《GaussDB(DWS)如何限制临时数据文件下盘量》，作者： wangxiaojuan8。
有些SQL语句，会出现中间结果集太大，内存放不下，需要落盘到外存（比如存在对大数据量进行聚集等操作，导致聚集操作的中间结果集在内存中放不下时会下盘），且落盘生成的临时数据文件占用空间过大，则会影响正常的数据写入业务无法执行，磁盘只能提供只读操作。
对于上述场景，可以通过两种方式，来控制用户执行过程中间结果集可落盘的数额，当超过限额，会报错终止该语句的执行，以防临时数据文件占用空间过大：
1. 方案1：设置每个线程的临时文件落盘数据量限制
2. 方案2：为用户设置中间结果集落盘空间限额
方案1：设置每个线程的临时文件落盘数据量限制
设置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)方案2：为用户设置中间结果集落盘空间限额
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 USER
postgres=# grant select on customer_demographics to u1;
GRANT
postgres=# alter user u1 spill space '1G';
ALTER USER
postgres=# alter session set session authorization u1 password 'abcd@1234';
SET
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示例2：SQL查询不下推，中间结果集仅在CN上落盘
postgres=# set enable_stream_operator=off;
SET
postgres=# alter session set session authorization u1 password 'abcd@1234';
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: spill space is out of user's spill space limit总结
第一种方案偏重于限制每个线程的临时文件下盘量，第二种方案偏重于限制用户的临时文件下盘量，要结合业务的目的来选择更适合的参数及参数设置，避免发生临时文件下盘量过大影响正常业务执行。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK