RDS for PostgreSQL磁盘过载定位及处理建议

针对磁盘空间满问题提出以下建议：

• 数据生命周期管理：
  • 定期归档和清理：制定策略定期将冷数据归档（如归档到对象存储），并从生产库中删除。
  • 使用分区表（Partitioning）：按时间（如天、月）对大数据表进行分区，定期DROP PARTITION来删除历史数据。
• SQL与表结构优化：
  • 避免产生大临时文件：优化SQL语句，为排序、分组字段添加索引。
  • 审查大字段使用：优先为表中的每一列选择符合存储需要的最小的数据类型。优先考虑数字类型，其次为日期或二进制类型，最后是字符类型。列的字段类型越大，建立索引占据的空间就越大。
• 关注本地WAL日志增长情况：

  监控主备节点复制状态：复制延迟和节点异常及时处理，节点异常请提交工单处理。

• 监控告警配置与预防策略：
  • 设置监控报警：在云监控上设置磁盘利用率报警阈值（如80%重要、90%紧急），提前设置告警规则。
  • 关注磁盘空间变化趋势：空间概况模块展示了当前实例磁盘的空间使用率、剩余可用空间以及磁盘总空间大小、近一周日均增长量、预计可用天数等信息，可快速了解实例空间的整体情况。
  • 设置自动扩容策略： 根据磁盘增长情况，提前规划设置磁盘自动扩容策略。

针对磁盘IO过载问题提出以下建议：

• 设置监控报警：在云监控上为IOPS、硬盘读吞吐量、硬盘写吞吐量、硬盘读耗时、硬盘写耗时等指标配置监控告警。
• 定期进行慢SQL巡检：定期检查慢查询、索引使用情况，防患于未然。
• 测试环境提前压测：在新功能上线或大促前，在测试环境对数据库进行压力测试，了解其IO瓶颈所在。
• 监控主备节点复制状态：生产数据库的实例类型请选择主备类型，复制延迟和节点异常及时处理。

查询数据库、表、WAL日志等大小的SQL会占用较多的磁盘IO，请在业务低峰期运行。

• 查看WAL日志大小是否异常并进行处理
  • 查看wal日志大小

    可以通过rds040_transaction_logs_usage监控指标或者使用root用户执行以下SQL查看WAL日志大小，如果发现WAL非常多，可以通过后续步骤依次排查。

    select round(sum(size)/1024/1024/1024,2) "GB" from pg_ls_waldir();

    

    RDS for PostgreSQL 12之后的版本才有pg_ls_waldir() 函数。

    需要root用户执行pg_ls_waldir函数。

  • 查看WAL日志保留相关参数
    • 对于RDS for PostgreSQL 12及以下版本，查看“wal_keep_segments”参数（单位MB）的值；对于12以上版本，查看“wal_keep_size”参数（单位为MB）。
    • WAL日志保留参数的值不宜太大，一般设置要小于磁盘总空间的10%；也不宜太小，一般要大于4GB，否则容易导致主库将备库需要的wal日志清理，进而导致备库异常。
  • 查看复制槽状态，及延迟未清理的日志大小

    复制槽会阻塞WAL的回收，如果发现非活动的复制槽或者不需要的复制槽，可以根据需要进行删除。

    使用root用户执行以下SQL查询slot状态、WAL日志滞后量：

    select slot_name, active,
    pg_size_pretty(pg_wal_lsn_diff(b, a.restart_lsn)) as slot_latency
    from pg_replication_slots as a, pg_current_wal_lsn() as b;

    使用root用户执行以下SQL删除slot命令：

    select pg_drop_replication_slot('slot_name');

  • 查看写业务繁忙程度

    可以通过rds044_transaction_logs_generations指标查看写业务繁忙程度，该指标表示平均每秒生成的事务日志（WAL日志）大小。

    如果该指标较大，说明写业务较多，数据库内核会自动预留更多的WAL日志以便回收使用，WAL日志占用的磁盘空间会增加，建议通过磁盘扩容保证一定的磁盘冗余。

• 查看数据文件大小否异常并进行处理
  • 使用root用户执行以下SQL，查询磁盘占用前10的数据库：

    select datname, pg_database_size(oid)/1024/1024 as dbsize_mb from pg_database order by dbsize_mb desc limit 10;

  • 查看磁盘占用前10的对象（表/索引）

    使用root用户执行以下SQL，通过pg_class的“relpages”字段估算表或者索引的大小：

    select relname, relpages*8/1024 as tablesize_mb from pg_class order by tablesize_mb desc limit 10;

    如果要获取表或者索引的精确大小，需要通过以下函数获取：

    表1 函数说明

    名称	返回类型	描述
    pg_relation_size(relation regclass, fork text)	bigint	指定表或索引的指定分叉（'main'、'fsm'、'vm'或'init'）使用的磁盘空间。
    pg_relation_size(relation regclass)	bigint	pg_relation_size(..., 'main')的简写。
    pg_table_size(regclass)	bigint	被指定表使用的磁盘空间，排除索引（但包括 TOAST、空闲空间映射和可见性映射）。
    pg_total_relation_size(regclass)	bigint	指定表所用的总磁盘空间，包括所有的索引和TOAST数据。

  • 查看表是否发生了膨胀

    一旦确认了占用磁盘较多的表后，可以通过pgstattuple插件分析表是否发生了膨胀，插件可以通过如下方式安装，使用root用户执行以下SQL：

    create control_extension('create', 'pgstattuple');
    select * from pgstattuple('table_name');

    

    部分内核版本不支持pgstattuple插件，详见支持的插件列表。

    插件使用参考：https://www.postgresql.org/docs/15/pgstattuple.html

  • 清理表数据
    • 如果发现是表膨胀，可以选择在维护时间窗内对表的磁盘占用整理，使用root用户执行以下SQL。
      

      vacuum full会锁表，请确保操作期间没有DML等操作。

      vacuum full table_name;

    • 如果发现不需要的表或数据，可以通过truncate table或是drop table清理掉不需要的数据。

      truncate table table_name;

    • 通过执行delete操作不会释放磁盘空间，反而因生产大量wal日志加剧磁盘空间消耗。磁盘满时禁止通过delete来释放磁盘空间。

      由于PostgreSQL的MVCC机制，delete操作不会释放磁盘空间（被delete的数据被标记为不可见，空间不释放），需要结合vacuum full（会锁表）才能真正释放空间。vacuum full操作自身也会消耗空间，并且会锁表，影响业务，请于业务低峰期执行，并至少预留2倍现有表大小的空闲空间。

    • 另外，如果需要保留的数据相对较少，也可以新建一张表转移需要保留的数据，参考步骤：
      1. 保存原表的结构、索引等信息。
      2. 创建新表。
      3. 向新表插入数据。
      4. 检查新表的数据是否符合预期，符合则进行下一步，否则检查前面操作是否有异常。
      5. 删除原表。
      6. 将新表重命名、创建索引等。
    

    vacuum full（会锁表）会对表及其索引进行重建，重建期间还会生成WAL日志，需要预留足够的磁盘空间（假设重建后的表大小为1GB，索引为0.5GB，建议预留2.5GB以上的磁盘空间）。

    vacuum介绍：https://www.postgresql.org/docs/current/routine-vacuuming.html

• 磁盘使用率大于等于97%，实例会进入只读状态，此时无法通过drop、truncate进行清理，解决方法如下：
  • 扩容磁盘空间，确保磁盘空间足够。

    磁盘扩容后，对于磁盘空间小于1TB的实例，如果空间利用率小于87%，实例的只读状态会自动解除。对于磁盘空间大于等于1TB的实例，如果剩余磁盘空间大于150GB，实例的只读状态会自动解除，之后可以通过drop或truncate命令删除无用数据。注意：云盘实例可以设置存储空间自动扩容，在实例存储空间达到阈值时，会触发自动扩容，避免实例磁盘打满进入只读。

  • 如果不想进行扩容，可以通过界面解除只读或通过命令解除只读（set default_transaction_read_only = off;），再通过drop或truncate命令删除无用数据。注意：解除只读前请停止业务，避免继续写入。如果解开只读，数据继续写入，会导致磁盘再次爆满，实例异常。
• 查看临时文件大小是否异常并进行处理

  如果总的磁盘占用减去数据文件和WAL日志还有较大的剩余，那么可能是临时文件占用较多的磁盘空间。使用root用户执行以下SQL查看临时文件大小：

  select round(sum(size)/1024/1024/1024,2) "GB" from pg_ls_tmpdir();

  

  • RDS for PostgreSQL 12之后的版本才有pg_ls_waldir() 函数。
  • 需要root用户执行pg_ls_waldir函数。
  • 当临时文件非常多时，该SQL执行会非常缓慢。

  一般来说，临时文件会在复杂SQL执行完成后释放，但如果生过OOM等异常，可能会导致临时文件不能正常释放。当发现临时文件非常多时，一方面需要分析并优化慢SQL，减少临时文件的产生，另一方面需要在维护时间窗内对数据库进行重启，重启数据库可以清除所有的临时文件。

• 表结构与存储优化
  • 使用表分区：按时间或业务维度分区，便于历史数据清理。
  • 数据归档策略：
    • 历史数据迁移至归档表（普通表或外部表）。
    • 使用物化视图聚合历史报表数据。
• 索引优化
  • 定期重建膨胀索引：使用 `REINDEX` 或 `pg_repack` 回收索引空间。
  • 删除无用索引。
  • 表达式索引优化：避免在索引列上使用函数。
• 日志与膨胀清理
  • 定期手动VACUUM FULL：在业务低峰期执行，回收表膨胀空间。
  • 清理WAL日志：配置合适的WAL保留时间，避免日志堆积。
• 监控与预警体系
  • 容量预警：分别配置磁盘利用率 > 70% 时触发预警，以及磁盘利用率 > 80% 时触发告警的规则，详见设置告警规则。
  • 容量预估：计算预计磁盘增长量，如果发现当前可用磁盘容量不足以支撑该增长量，请扩容磁盘。

    预计磁盘增长量 =（每日数据增量 × 数据保留天数）× 1.5（膨胀系数）。其中，每日数据增量为容量预估中的“近一周日均增长”，数据保留天数为业务实际估算的保留天数。

• 架构层面优化
  • 弹性扩容：使用存储空间弹性扩容能力，在实例存储空间达到阈值时，会触发自动扩容。