当前位置： 首页 > article >正文

PostgreSQL表膨胀问题解析

article 2024/11/28 11:37:25

一、定义

表膨胀是指表的数据和索引所占文件系统的空间在有效数据量并未发生大的变化的情况下不断增大。这种现象会导致关系文件被大量空洞填满，从而浪费大量的磁盘空间。

二、原因

表膨胀在PostgreSQL中通常是由于UNDO数据（用于回滚事务和维护事务的一致性视图）和表数据混合存储引起的。具体原因包括以下几个方面：

1. MVCC（多版本并发控制）机制：

  • 旧版本数据保留：PostgreSQL使用MVCC机制来处理并发访问，允许读取操作在不锁定表的情况下进行。当一条记录被更新或删除时，原始记录不会立即从磁盘上移除，而是被标记为不可见，以支持未提交的事务回滚或者用于快照读。这些旧版本的数据如果不能得到及时清理，就会占用磁盘空间，导致表膨胀。
  • 死元组（dead tuple）：随着时间的推移，表中会积累大量的“死”行（即不再可达的行），这些死元组如果不及时清理，就会占用磁盘空间。

2. 频繁的更新和删除操作：

  • 死元组积累：频繁的更新和删除操作直接导致表中大量的“死”行。在高更新和删除率的环境中，表膨胀尤为严重，因为每次这些操作发生时，都会留下不再可达的行。

3. 未提交的事务：

  • 资源占用：长时间未提交或终止的事务会占用资源，导致“死”行的积累，进而导致表膨胀。

4. 填充因子（fillfactor）设置：

  • 空闲空间：表的填充因子设置也会影响表膨胀。较低的填充因子意味着每个数据页中会留出更多的空闲空间，以减少页面因更新而频繁分裂的可能性。但这会增加每个页面的空闲空间，导致表的实际磁盘使用量增加。相反，较高的填充因子可能导致数据行更新时空间不足，需要重新分配页面。

5. autovacuum机制不足：

  • 清理不及时：虽然PostgreSQL提供了自动的autovacuum机制来定期清理“死”行，但在某些情况下，如高并发事务、长事务等，autovacuum可能无法及时清理死元组，导致表膨胀。

6. 其他因素：

  • 失效复制槽：失效的复制槽可能导致autovacuum无法正常工作。
  • 索引状态问题：表和索引的并发访问可能影响VACUUM的效果。
  • 磁盘I/O性能：磁盘I/O性能差可能导致VACUUM的效率低下，死元组不能及时清理。

三、影响

表膨胀对数据库的性能和稳定性有显著影响，具体包括以下几个方面：

1. 存储成本增加：

膨胀的表占用更多磁盘空间，增加存储成本。

2. 查询性能下降：

  • 数据集增大：数据库需要在更大的数据集中搜索，导致查询执行时间延长。
  • 索引效率降低：表膨胀可能导致索引结构效率下降。

3. 备份恢复时间延长：

表变大后相应的备份恢复时间也会延长。

4. 系统资源消耗增加：

  • CPU、内存和I/O资源：处理膨胀的表需要更多的CPU、内存和I/O资源。

5. 数据碎片化：

表膨胀可能导致数据碎片化，进一步影响性能并增加数据库管理的复杂性。

四、解决方案

解决表膨胀问题通常涉及到以下几个步骤：

1. 定期执行VACUUM操作：

  • 普通VACUUM：清理死元组，但不会进行空间重组，磁盘上的空间不会释放，但后续的插入会根据空闲空间管理优先插入空闲空间。
  • VACUUM FULL：清理释放磁盘空间，但获取的锁级别较高，会阻塞一切访问，适用于经常进行大批量更新数据的表，可以在业务低峰期执行。
  • 手动VACUUM：通过调整VACUUM的行为（如VACUUM(FULL, FREEZE)），可以更快地清理UNDO数据。

2. 启用和配置autovacuum机制：

  • 确保autovacuum开启：PostgreSQL提供了自动的autovacuum机制，可以根据阈值自动触发vacuum操作。
  • 调整autovacuum参数：如autovacuum_vacuum_cost_delay和autovacuum_naptime，以确保autovacuum进程能够及时清理“死”行。
  • 监控autovacuum效果：定期检查autovacuum的执行情况和效果，确保其正常工作。

3. 使用pg_repack或pg_reorg工具：

  • 在线重组：对于膨胀严重的表，可以使用pg_repack或pg_reorg等工具重新组织表和索引以回收空间。这些工具可以在不锁定表的情况下工作，对生产环境影响较小。
  • 执行过程：
    • 准备阶段：预留足够的磁盘空间，调整数据库参数（如idle_in_transaction_session_timeout）。
    • 执行阶段：创建新表，复制数据，建立索引，交换表等。
    • 监控和日志：监控重组过程，记录日志以便问题排查。

4. 合理设计数据库和查询：

  • 避免频繁的更新和删除操作：减少“死”行的积累。
  • 使用分区表：对于频繁更新的大表，可以考虑分区表以减少单个表的大小和膨胀程度。
  • 合理设置填充因子：根据表的更新频率和数据量合理设置填充因子以减少表膨胀的可能性。

5. 监控和预警：

  • 建立监控体系：对表的膨胀情况进行实时监测并设置阈值告警，一旦发现表膨胀现象能快速响应处理。
  • 定期分析：定期分析表的膨胀情况和原因，采取相应的优化措施。

6. 其他优化措施：

  • 配置REDO日志：如果可能，可以配置REDO日志使得UNDO数据和REDO日志分离以减少表膨胀的影响。
  • 数据库维护最佳实践：定期的数据库维护活动如索引优化、统计信息更新等也有助于管理UNDO数据。

五、实施说明

1. 启用和配置autovacuum机制：

  • 确保autovacuum开启：
    ALTER SYSTEM SET autovacuum = on;
SELECT pg_reload_conf();
  • 调整autovacuum参数：
    ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 20ms;
ALTER SYSTEM SET autovacuum_naptime = 1min;
SELECT pg_reload_conf();
  • 监控autovacuum效果：
    SELECT * FROM pg_stat_autovacuum;

2. 定期执行VACUUM操作：

  • 手动执行VACUUM：
    VACUUM FULL tablename;
  • 设置定时任务：
    0 2 * * * psql -d yourdatabase -c "VACUUM FULL tablename"

3. 使用pg_repack工具：

  • 安装pg_repack扩展：
    CREATE EXTENSION pg_repack;
  • 执行pg_repack：
    pg_repack -h your_host -p your_port -d your_database -t your_table
  • 监控重组过程：
    SELECT * FROM pg_stat_activity WHERE query LIKE '%pg_repack%';

4. 合理设计数据库和查询：

  • 使用分区表：
    CREATE TABLE your_table (
    id serial PRIMARY KEY,
    data text
) PARTITION BY RANGE (id);

CREATE TABLE your_table_partition1 PARTITION OF your_table
FOR VALUES FROM (1) TO (1000000);

CREATE TABLE your_table_partition2 PARTITION OF your_table
FOR VALUES FROM (1000001) TO (2000000);
  • 合理设置填充因子：
    ALTER TABLE your_table SET (fillfactor = 80);

5. 监控和预警：

  • 建立监控体系：
CREATE OR REPLACE FUNCTION check_table_bloat() RETURNS void AS $$
DECLARE
   r RECORD;
BEGIN
   FOR r IN
       SELECT schemaname, tablename, bloat
       FROM (
           SELECT
               schemaname,
               tablename,
               ROUND(CASE WHEN otta=0 OR relpages=0 OR relpages=otta THEN 0.0 ELSE relpages/otta::numeric END, 2) AS bloat
           FROM (
               SELECT
                   nn.nspname AS schemaname,
                   cc.relname AS tablename,
                   COALESCE(cc.reltuples, 0) AS reltuples,
                   COALESCE(cc.relpages, 0) AS relpages,
                   COALESCE(ce.reltuples, 0) AS expected_reltuples,
                   CASE WHEN ce.reltuples > 0 THEN
                       (cc.reltuples::bigint * cc.rellen)::bigint / (ce.reltuples::bigint * (SELECT setting FROM pg_settings WHERE name='block_size')::int)
                   ELSE
                       0
                   END AS otta
               FROM
                   pg_class cc
                   JOIN pg_namespace nn ON cc.relnamespace = nn.oid
                   LEFT JOIN (
                       SELECT
                           c.relname,
                           c.reltuples,
                           (c.reltuples * (c.rellen + pg_column_size(c.oid, 'ctid') + 24))::bigint AS total_bytes
                       FROM
                           pg_class c
                           LEFT JOIN pg_stat_all_tables s ON c.relname = s.relname
                       WHERE
                           s.schemaname NOT IN ('pg_catalog', 'information_schema')
                           AND c.relkind = 'r'
                   ) ce ON cc.relname = ce.relname
               WHERE
                   nn.nspname NOT IN ('pg_catalog', 'information_schema')
                   AND cc.relkind = 'r'
           ) a
       ) b
       WHERE bloat > 1.0
   LOOP
       RAISE NOTICE 'Schema: %, Table: %, Bloat: %', r.schemaname, r.tablename, r.bloat;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

这个函数check_table_bloat的目的是检查PostgreSQL数据库中的表是否存在“膨胀”（bloat）现象，即表占用的磁盘空间是否超过了其实际存储的数据量所需的空间。函数通过一系列嵌套的查询来计算每个表的“膨胀率”（bloat），并对于膨胀率大于1.0的表，使用RAISE NOTICE语句输出相关信息。

说明

  1. 外层查询：遍历所有计算出的膨胀率大于1.0的表，并输出其模式名（schemaname）、表名（tablename）和膨胀率（bloat）。

  2. 内层查询：计算每个表的膨胀率。这里使用了多个嵌套的子查询：

    • 第一个子查询（别名为a）计算了每个表的实际页数（relpages）与理想页数（otta）的比率，即膨胀率。理想页数是根据表的行数（reltuples）和每行的大小（rellen）以及块大小（通过查询pg_settings表中的block_size设置得到）计算出来的。
    • 第二个子查询（别名为ce）计算了每个表预期的行数和总字节数，用于后续计算理想页数。

  3. 过滤条件：排除了系统模式（pg_catalog和information_schema）和非常规表（relkind不等于’r’，即不是普通表）。

  4. 函数定义：使用CREATE OR REPLACE FUNCTION语句定义了一个名为check_table_bloat的函数，该函数没有参数，返回类型为void，表示不返回任何值。函数体使用PL/pgSQL语言编写。

  5. 循环和输出：使用FOR ... IN ... LOOP语句遍历查询结果，并使用RAISE NOTICE语句输出膨胀信息。

请根据您的实际数据库环境和需求，对函数进行适当的调整和优化。这个函数可以作为数据库维护的一部分，定期运行以检查并处理表的膨胀问题。

总结

表膨胀是PostgreSQL数据库中常见的问题，主要表现为表数据和索引占用空间不断增大，而实际数据量并未显著变化。这主要由MVCC机制、频繁更新删除、未提交事务、填充因子设置及autovacuum机制不足等因素引起。膨胀的表会导致存储成本增加、查询性能下降、备份恢复时间延长及系统资源消耗增加等问题。为解决这些问题，可以定期执行VACUUM操作，启用和配置autovacuum机制，使用pg_repack或pg_reorg工具进行在线重组，合理设计数据库和查询，以及建立监控和预警体系。特别是，可以通过创建check_table_bloat函数来定期检查表的膨胀情况，并及时采取措施处理，以确保数据库的性能和稳定性。


http://www.kler.cn/a/412708.html

相关文章：

  • 斐波那契堆与二叉堆在Prim算法中的性能比较：稀疏图与稠密图的分析
  • Paddle Inference部署推理（三）
  • ⭐️ GitHub Star 数量前十的工作流项目
  • mysql函数、查询、排序场景使用
  • 第三方数据库连接免费使用和安装
  • uniapp+vue2+uview2.0导航栏组件二次封装
  • python-docx -- 读取word页眉、页脚
  • 上海迪士尼奇幻冬日巡游：IP营销如何出圈？
  • wordpress拖拽式自定义文章排序插件Simple Custom Post Order，支持对wordpress文章、页面、自定义文章进行自定义排序
  • 通信与网络安全之IPSEC
  • [DL]深度学习_扩散模型正弦时间编码
  • 关于如何在k8s中搭建一个nsfw黄图鉴定模型
  • Spring |（四）IoC/DI配置管理第三方bean
  • NLP 2、机器学习简介
  • Dart 中 initializer lists
  • 【MySQL】自动刷新flush privileges命令
  • 【技术文档：技术传播的灯塔】
  • Python学习——猜拳小游戏
  • 组会 | 大语言模型 + LoRA
  • chrome允许http网站打开摄像头和麦克风
  • C++优质学习资源汇总
  • 【开源项目】ChinaAddressCrawler 中国行政区划数据（1980-2023年）采集及转换（Java版），含SQL格式及JSON格式
  • python+django自动化部署日志采用‌WebSocket前端实时展示
  • 第76期 | GPTSecurity周报
  • 23种设计模式-生成器(Builder)设计模式
  • 解决首次加载数据空指针异常