摘要：本文主要讲解数仓运维中遇到单SQL磁盘空间管控问题的解析和方案。 本文分享自华为云社区《GaussDB(DWS)运维 -- 单SQL磁盘空间管控》，作者： 譡里个檔。
【问题描述】
执行部分SQL语句时出现如下报错信息(具体数值可能因为配置有差异)，本文针对根因和场景触发场景，确定触发此类问题的根因
The space used on DN (209715224 kB) has exceeded the sql use space limit (209715200 kB)
【问题根因】
该报错表示用户执行的sql在单DN上所用空间超过了参数sql_use_spacelimit的限制。sql_use_spacelimit限制单个SQL在单个DN上，触发落盘操作时，落盘文件的空间大小，管控的空间包括普通表、临时表以及中间结果集落盘占用的空间
可以使用如下SQL查看所有实例上的配置参数sql_use_spacelimit的值- SELECT * FROM pgxc_settings WHERE name = 'sql_use_spacelimit';
当前现网最常见的此类错误一般都是INERT语句触发的，我们以常见如下语句为例，说明这类问题的解决方案- INSERT INTO dwljaa.bif_col_edw_dut_257_t
- (attribute1, attribute2, attribute3, attribute4, attribute5, column_name1, column_name2,
- column_name3, column_name4, column_name5, tag_code, tag_id, table_name, period, tbl_code,
- tag_grp_code, target_key_val, cycle_id, creation_date, target_key_num, priority)
- SELECT
- 'SCN_SVC_3003', NULL, NULL, NULL, NULL, 'BIZ_SCR_CODE', NULL, NULL, NULL, NULL,
- 'SCN_SVC_3003-02', 3026937, 'dwr_fin_hwip_man_je_f_tmp0', '202208', 'PL_E17360237',
- 'SUB_PL_PUB_SCN', A.record_seq_num, 20230321000000, SYSDATE, A.record_seq_num, 53333
- FROM (SELECT /*+PARALLEL(8) NO_EXPAND*/ T.record_seq_num
- FROM dwljaa.dwr_fin_hwip_man_je_f_tmp0 T
- INNER JOIN dwrdim.dwr_dim_department_d PL_E100134 ON T.COA_DEPT_KEY = PL_E100134.DEPT_KEY
- INNER JOIN dwrdim.dwr_dim_grp_acct_code_d PL_E100119 ON T.GROUP_ACCOUNT_CODE = PL_E100119.GROUP_ACCOUNT_CODE
- INNER JOIN dwrdim.dwr_dim_journal_category_d PL_E100147 ON T.JE_CATEGORY_ID = PL_E100147.JE_CATEGORY_ID
- INNER JOIN dwrdim.dwr_dim_product_d PL_E100121 ON T.MAJOR_PROD_KEY = PL_E100121.PROD_KEY
- INNER JOIN dwrdim.dwr_dim_product_d PL_E100122 ON T.MINOR_PROD_KEY = PL_E100122.PROD_KEY
- INNER JOIN dwrdim.dwr_dim_project_d PL_E100155 ON T.PROJ_KEY = PL_E100155.PROJ_KEY
- WHERE 1 = 1 AND ((((((((((PL_E100119.LVL1_ACCOUNT_CODE IN('501', '503', '506', '512')) OR(PL_E100119.GROUP_ACCOUNT_CODE = '5980406')) AND((PL_E100121.PROD_CLASS_FLAG = '0') OR(PL_E100122.PROD_CLASS_FLAG = '0'))) OR((PL_E100119.LVL1_ACCOUNT_CODE IN('504', 'SVC')) OR(PL_E100119.LVL2_ACCOUNT_CODE IN('55107', '57702', '57703', '58303')) OR(PL_E100119.GROUP_ACCOUNT_CODE IN('5980407', '5825107', '5827702', '5827703', 'C501'))) OR((PL_E100119.ACCOUNT_EXPENSE_CATG_CODE IN('E01', 'E03', 'E05', 'E06', 'E08', 'E09', 'E10', 'E11', 'E02', 'E04', 'E07', 'E12')) AND(PL_E100134.DEPT_EXPENSE_TYPE_CODE = 'Z4') AND((PL_E100155.PROJ_TYPE_CODE <> '02') OR(PL_E100155.PROJ_NUM IN('9000000')))) OR((PL_E100119.ACCOUNT_EXPENSE_CATG_CODE IN('E02', 'E04', 'E07', 'E12', 'E05', 'E09', 'E10')) AND(PL_E100134.DEPT_EXPENSE_TYPE_CODE IN('Z1', 'Z2', 'Z3')) AND(PL_E100155.PROJ_TYPE_CODE = '01'))) AND((PL_E100119.GROUP_ACCOUNT_CODE <> 'EXP5555')))) AND(PL_E100119.LVL2_ACCOUNT_CODE <> '58303') AND(PL_E100155.PROJ_TYPE_CODE = '01'))) AND((PL_E100119.LVL1_ACCOUNT_CODE = '504') AND(PL_E100147.CN_NAME IN('JV-PFC cooper cost', 'JV-ADJ PA Cooper/Constr', 'JV-Agent REV&COST Adj', 'JV-Agent totalvalue Adj')))) OR((PL_E100119.ACCOUNT_EXPENSE_CATG_CODE = 'E05') AND(PL_E100134.DEPT_EXPENSE_TYPE_CODE IN('Z1', 'Z2', 'Z3')) AND(PL_E100155.PROJ_TYPE_CODE = '01') AND(PL_E100147.CN_NAME <> 'JV-ADJ 557 WITH B CODE') AND((((((((PL_E100119.LVL1_ACCOUNT_CODE IN('501', '503', '506', '512')) OR(PL_E100119.GROUP_ACCOUNT_CODE = '5980406')) AND((PL_E100121.PROD_CLASS_FLAG = '0') OR(PL_E100122.PROD_CLASS_FLAG = '0'))) OR((PL_E100119.LVL1_ACCOUNT_CODE IN('504', 'SVC')) OR(PL_E100119.LVL2_ACCOUNT_CODE IN('55107', '57702', '57703', '58303')) OR(PL_E100119.GROUP_ACCOUNT_CODE IN('5980407', '5825107', '5827702', '5827703', 'C501'))) OR((PL_E100119.ACCOUNT_EXPENSE_CATG_CODE IN('E01', 'E03', 'E05', 'E06', 'E08', 'E09', 'E10', 'E11', 'E02', 'E04', 'E07', 'E12')) AND(PL_E100134.DEPT_EXPENSE_TYPE_CODE = 'Z4') AND((PL_E100155.PROJ_TYPE_CODE <> '02') OR(PL_E100155.PROJ_NUM IN('9000000')))) OR((PL_E100119.ACCOUNT_EXPENSE_CATG_CODE IN('E02', 'E04', 'E07', 'E12', 'E05', 'E09', 'E10')) AND(PL_E100134.DEPT_EXPENSE_TYPE_CODE IN('Z1', 'Z2', 'Z3')) AND(PL_E100155.PROJ_TYPE_CODE = '01'))) AND((PL_E100119.GROUP_ACCOUNT_CODE <> 'EXP5555')))) AND(PL_E100119.LVL2_ACCOUNT_CODE <> '58303') AND(PL_E100155.PROJ_TYPE_CODE = '01')))))
- AND T.PERIOD_ID >= 202208
- AND T.PERIOD_ID <= 202208
- ) A
点击关注，第一时间了解华为云新鲜技术~
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
