数仓如何限制临时数据文件下盘量

2021 年 11 月 23 日
本文字数：3221 字
阅读完需：约 11 分钟

摘要：查询的中间结果集如果太大导致落盘生成的临时数据文件，本文提供两种限制临时数据文件下盘数据量的方案，以防影响正常业务运行。

本文分享自华为云社区《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 -Iall -c "temp_file_limit = 1024"

2. temp_file_limit 取值计算公式

可以用下面的公式粗略的计算一个 temp_file_limit 的取值：temp_file_limit = 预计的总下盘量/同时下盘线程数

总下盘量一般可设置为可用空间的 20%，这个百分比可根据用户的可接受程度进行调节。同时下盘线程数是业务运行中，通常情况下并发的 query 中产生中间临时数据下盘的线程数。随着数据库中存储的数据量增加，temp_file_limit 的值要适时调整。

注意：此参数是限制每个线程的临时文件落盘数据量，如果一个 query 有多个线程，单个线程落盘数据量超过此参数限制，query 会报错退出。如果每个线程都没超过限制，但多个线程下盘数据量累计超过此参数限制，并不会报错退出。

3. 示例

以 TPC-DS1x 数据中的 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 指定 SPILLSPACE，为新建用户设置中间结果集落盘限额

CREATE USER user_name … SPILLSPACE 'spillspacelimit';

2) 通过 ALTER USER 指定 SPILLSPACE，修改已有用户的中间结果集落盘空间限额

ALTER USER user_name … SPILLSPACE '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

复制代码

总结

第一种方案偏重于限制每个线程的临时文件下盘量，第二种方案偏重于限制用户的临时文件下盘量，要结合业务的目的来选择更适合的参数及参数设置，避免发生临时文件下盘量过大影响正常业务执行。

点击关注，第一时间了解华为云新鲜技术~

发布于: 3 小时前阅读数: 5

原文链接:【http://xie.infoq.cn/article/6ba353306f9c390c7f02995ec】。文章转载请联系作者。

华为云开发者社区

关注

提供全面深入的云计算技术干货 2020.07.14 加入

华为云开发者社区，提供全面深入的云计算前景分析、丰富的技术干货、程序样例，分享华为云前沿资讯动态，方便开发者快速成长与发展，欢迎提问、互动，多方位了解云计算！传送门：https://bbs.huaweicloud.com/

发布

暂无评论

创作场景

数仓如何限制临时数据文件下盘量

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

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

总结

华为云开发者社区

评论