华为云DWS磁盘使用率超90%后集群只读

一、故障现象凌晨作业调度时段ETL任务连续报错二、根因分析DWS集群规则单数据盘使用率 ≥ 90%时集群自动进入只读模式default_transaction_read_only被强制打开所有INSERT/UPDATE/DELETE/DDL全部cancel。实际排查发现大量中间临时表dm_tmp_*、mid_*、dws_tmp_*未清理累计占用 2TB单表最大超过 180GB。凌晨ETL集中写入时磁盘使用率突破90%阈值触发集群只读。三、排错过程Step 1确认集群状态SHOW transaction_read_only;Step 2扫描大表定位大块头单表大小查询SELECT pg_size_pretty(pg_total_relation_size(tmp.tmp_test));全库大表扫描10GBWITH all_table_size AS ( SELECT n.nspname AS schema_name, c.relname AS table_name, c.reltuples::BIGINT AS estimate_rows, pg_total_relation_size(c.oid)/1024/1024/1024 AS total_bytes, pg_table_size(c.oid)/1024/1024/1024 AS table_only_bytes, pg_indexes_size(c.oid)/1024/1024/1024 AS index_bytes, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size_pretty, pg_size_pretty(pg_table_size(c.oid)) AS table_size_pretty, pg_size_pretty(pg_indexes_size(c.oid)) AS index_size_pretty FROM pg_class c LEFT JOIN pg_namespace n ON c.relnamespace n.oid WHERE c.relkind r AND n.nspname NOT IN (pg_catalog,information_schema,pg_toast,cstore,pmk) AND n.nspname !~ ^pg_temp_ ) SELECT * FROM all_table_size WHERE total_bytes 10 ORDER BY total_bytes DESC;Step 3解除只读 清理必须在事务内执行否则SET transaction_read_only off会被集群级只读参数拦截。方式一显示事务关闭只读START TRANSACTION READWRITE; DROP TABLE IF EXISTS dm.dm_tmp_xxx; TRUNCATE TABLE dm.dm_tmp_yyy; COMMIT;方式二设置事务只读关闭START TRANSACTION; SET transaction_read_only off; DROP/TRUNCATE 目标表; COMMIT;Step 4清理优先级优先级清理对象动作风险P0临时表tmp_、mid_、dm_tmp_DROP TABLE无P1业务已废弃的备份表_bak、_v1DROP TABLE低P2大表历史分区DROP PARTITION中清理目标磁盘使用率降至80% 以下集群自动退出只读模式。四、踩坑记录坑1临时表不会自动释放CREATE TEMP TABLE会话级临时表才会随连接断开释放CREATE TABLE dm.dm_tmp_xxx AS ...是物理表永远不会自动删除。命名带tmp不等于临时表。坑2TRUNCATE不立即释放空间DWS列存下TRUNCATE释放空间依赖底层存储回收存在延迟大量数据要彻底释放必须用DROP TABLE。坑3VACUUM FULL会临时占更多空间VACUUM FULL执行期间需要额外空间 ≈ 表大小 × (1 - 脏页率)最高磁盘使用率 70% 时必须串行清理一张一张来否则会再次打满磁盘。坑4单SQL可写满整盘未设置sql_use_spacelimit时单条SQL可无限制写入磁盘是定时炸弹。五、调优方案作业结尾强制清理最关键所有ETL作业模板末尾追加-- 显式删除已知临时表 DROP TABLE IF EXISTS dm.dm_tmp_step1_{biz_date}; DROP TABLE IF EXISTS dm.dm_tmp_step2_{biz_date}; -- 模糊匹配清理兜底 DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON c.relnamespace n.oid WHERE n.nspname dm AND c.relname ~ ^dm_tmp_ AND c.relname LIKE %{biz_date}% LOOP EXECUTE format(DROP TABLE IF EXISTS %I.%I, r.nspname, r.relname); END LOOP; END $$;使用真正的TEMP TABLE中间结果只在当前会话使用时-- 事务级临时表COMMIT后自动释放最严格 CREATE TEMP TABLE tmp_step1 ON COMMIT DROP AS SELECT ...;兜底清理作业每日巡检清理7天未访问的疑似临时表SELECT n.nspname || . || c.relname AS full_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON c.relnamespace n.oid JOIN pg_stat_user_tables s ON s.schemaname n.nspname AND s.relname c.relname WHERE n.nspname NOT IN (pg_catalog,information_schema,pg_toast,cstore,pmk) AND (c.relname ~ ^(tmp_|temp_|mid_|dm_tmp_|dws_tmp_) OR c.relname ~ (_bak|_old|_v1)$) AND GREATEST(s.last_analyze, s.last_autoanalyze, s.last_vacuum, s.last_autovacuum) (CURRENT_TIMESTAMP - INTERVAL 7 days) ORDER BY pg_total_relation_size(c.oid) DESC;SQL空间管控参数-- 单SQL最大可用空间建议磁盘总容量×10% ALTER DATABASE dm SET sql_use_spacelimit TO 500GB; -- 会话级临时文件限制 ALTER DATABASE dm SET temp_file_limit TO 100GB; -- 紧急一次性写入可临时关闭 SET sql_use_spacelimit 0;VACUUM回收脏页8.1.3 版本用智能运维控制台 → 智能运维 → 添加 Vacuum 周期任务。低版本手动VACUUM FULL ANALYZE dm.dm_pur_pe_unclear_amount;注意会锁表需低峰执行且最高磁盘使用率 70% 时必须串行。六、SQL空间管控参数重复一下这块最重要参数作用建议值sql_use_spacelimit单SQL最大可用空间磁盘总容量 × 10%temp_file_limit会话临时文件上限100GB设置ALTER DATABASE dm SET sql_use_spacelimit TO 500GB; ALTER DATABASE dm SET temp_file_limit TO 100GB;七、总结核心问题临时表生命周期失控凌晨ETL集中写入打爆磁盘。三板斧排错显示事务START TRANSACTION READWRITE 删临时表腾出空间。踩坑临时表要DROP不用TRUNCATE命名带tmp不等于临时表KILL 任务时清理语句不执行。调优作业末尾强制清理 优先用TEMP TABLE 每日兜底作业 设置sql_use_spacelimit。参考DWS磁盘空间满处理方案