WEBKT

PostgreSQL 表膨胀深度解析:成因、影响与终极解决方案

140 0 0 0

PostgreSQL 表膨胀深度解析:成因、影响与终极解决方案

什么是表膨胀?

为什么会发生表膨胀?

表膨胀有什么危害?

如何诊断表膨胀?

如何解决表膨胀?

总结

PostgreSQL 表膨胀深度解析:成因、影响与终极解决方案

大家好,我是你们的数据库老伙计“波斯猫” (PostgreSQL 的谐音,有趣吧?)。今天咱们来聊聊 PostgreSQL 里一个让 DBA 和开发者都头疼的问题——表膨胀 (Table Bloat)。别担心,我会用大白话,结合实际案例,把这事儿掰开了揉碎了,让你彻底搞明白。

什么是表膨胀?

想象一下,你有一个气球,不断地往里面吹气 (插入数据),然后又放气 (删除数据),再吹气,再放气…… 如此反复,即使气球里的气体不多了 (实际数据量不大),但气球本身已经被撑大了 (表的物理文件大小远超实际数据量)。这就是表膨胀。

更技术一点的解释:PostgreSQL 为了实现 MVCC (多版本并发控制),在更新或删除数据时,并不会立即物理删除旧版本的数据行,而是标记为“死亡元组”(Dead Tuples)。这些死亡元组仍然占用磁盘空间,导致表的物理文件大小持续增长,即使有效数据量并没有增加,这就是表膨胀。

为什么会发生表膨胀?

罪魁祸首就是 PostgreSQL 的 MVCC 机制。当然,还有一些操作会加剧膨胀:

  1. 频繁的 UPDATE 和 DELETE 操作: 这是最主要的原因。每次 UPDATE 实际上是插入新版本行,然后将旧版本行标记为死亡元组。DELETE 也是将行标记为死亡元组。这些操作越多,死亡元组积累得越快。
  2. 长时间运行的事务: 长时间运行的事务会阻止 VACUUM 清理死亡元组。因为这些事务可能还需要访问旧版本的数据,VACUUM 不能贸然清理。
  3. 大量的批量导入/删除操作: 比如你一次性导入了几百万条数据,然后又删除了其中大部分,这会导致大量的死亡元组产生。
  4. 不合理的 VACUUM 设置: PostgreSQL 的 autovacuum 进程负责自动清理死亡元组。如果 autovacuum 设置不合理,比如触发阈值过高,或者运行频率过低,会导致死亡元组不能及时清理。
  5. FULL VACUUM 使用不当: 虽然 VACUUM FULL 可以彻底清理死亡元组并回收空间,但是它会锁定整个表,导致数据库在该期间无法提供服务。如果频繁使用,会严重影响数据库的可用性。所以说VACUUM FULL是重量级武器,非必要不使用。

表膨胀有什么危害?

表膨胀可不是闹着玩的,它会带来一系列问题:

  1. 磁盘空间浪费: 这是最直观的危害。大量的死亡元组占用了宝贵的磁盘空间,导致存储成本上升。
  2. 查询性能下降: 即使你只查询少量数据,PostgreSQL 也可能需要扫描大量的死亡元组才能找到你需要的数据。这会导致查询变慢,响应时间延长。
  3. 索引效率降低: 表膨胀也会影响索引的效率。因为索引中也会包含指向死亡元组的指针,这会导致索引扫描变慢。
  4. 备份和恢复时间延长: 表膨胀会导致备份文件变大,从而延长备份和恢复的时间。
  5. 数据库性能整体下降: 严重的表膨胀会导致数据库整体性能下降,甚至导致数据库崩溃。

如何诊断表膨胀?

“知己知彼,百战不殆”。要解决表膨胀,首先要学会诊断它。这里介绍几种常用的方法:

  1. 使用 pg_stat_all_tables 视图: 这个视图提供了表的统计信息,包括死亡元组的数量 (n_dead_tup)。你可以通过比较 n_live_tup (存活元组数量) 和 n_dead_tup 来判断表是否膨胀。

    SELECT relname, n_live_tup, n_dead_tup, (n_dead_tup * 100 / (n_live_tup + n_dead_tup))::float AS dead_ratio
    FROM pg_stat_all_tables
    WHERE schemaname = 'public' -- 替换成你的 schema
    AND n_live_tup > 0
    ORDER BY dead_ratio DESC;

    一般来说,dead_ratio 超过 20% 就需要关注了。

  2. 使用 pgstattuple 扩展: 这个扩展提供了更详细的表和索引的统计信息,包括表的膨胀率。安装好扩展后,你可以这样查询:

    SELECT * FROM pgstattuple('your_table_name');
    
  3. 使用 check_postgres 脚本: 这是 bucardo 项目提供的一个 Perl 脚本,可以用来检查 PostgreSQL 数据库的各种健康指标,包括表膨胀。使用方法参见官方文档。

  4. pg_bloat_check 脚本
    这是一个自定义的 SQL 脚本,原理与 pg_stat_all_tables 类似,但是提供了更友好的输出和估算的膨胀大小。

WITH constants AS (
-- define some constants for sizes of things
-- for reference, a tuple header is 23 bytes, a pointer is 4 bytes, an index entry is 8 bytes
SELECT current_setting('block_size')::numeric AS bs, 23 AS th, 4 AS ta, 8 AS ia
),
no_stats AS (
-- screen out table who have attributes
-- which dont have stats, such as JSON
SELECT table_schema, table_name
FROM information_schema.columns
LEFT OUTER JOIN pg_stats ON table_schema = schemaname AND table_name = tablename AND column_name = attname
WHERE attname IS NULL
),
null_headers AS (
-- calculate null header sizes
-- omitting tables which dont have complete stats
-- see the section about NULL definitions
SELECT
hdr+1+(sum(case when null_frac=0 then 0 else 1 end)/8) as nullhdr, --Size of the null bitmap
SUM((1-null_frac)*avg_width) as datawidth, -- Space required by valid columns
MAX(null_frac) as maxfracsum, -- Largest null fraction
schemaname, tablename, hdr, ma, bs
FROM pg_stats CROSS JOIN constants
LEFT OUTER JOIN no_stats ON schemaname = no_stats.table_schema AND tablename = no_stats.table_name
WHERE no_stats.table_name IS NULL AND attname IS NOT NULL
GROUP BY schemaname, tablename, hdr, ma, bs
),
headers AS (
-- estimate header and row size
SELECT
ma, bs, hdr, schemaname, tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, -- Actual size of the header + data
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 -- Actual size of the null header
FROM null_headers
),
rows AS (
-- calculate real row size
SELECT schemaname, tablename, bs, ma, hdr, datahdr, nullhdr2,
ceil((datahdr+nullhdr2)/ma) * ma AS otta -- "Overlay To Tuple Alignment"
FROM headers
),
estimates AS (
SELECT
schemaname, tablename, bs,
pg_class.reltuples,
pg_class.relpages,
otta,
(CEIL((pg_class.reltuples*otta)/bs)) AS expected_blocks -- expected number of blocks based on row size
FROM rows
JOIN pg_class ON tablename = relname AND schemaname = pg_namespace.nspname
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relkind = 'r'
)
-- and finally the output
SELECT
schemaname,
tablename,
reltuples::bigint,
relpages::bigint,
bs,
otta,
expected_blocks::bigint,
relpages::bigint - expected_blocks::bigint AS bloat_blocks, -- difference between real and expected
CASE WHEN relpages > expected_blocks THEN
(relpages::bigint - expected_blocks::bigint)::numeric * bs
ELSE 0 END AS bloat_size
-- approximate size of bloat
FROM estimates
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY bloat_size DESC;
  1. 直观感受: 如果你发现某个表的查询速度明显变慢,或者备份文件异常增大,而这个表又经常进行 UPDATE 和 DELETE 操作,那么很可能就是发生了表膨胀。

如何解决表膨胀?

既然找到了病因,接下来就是对症下药了。这里提供几种常用的解决方案:

  1. 定期 VACUUM: 这是最基本也是最重要的措施。PostgreSQL 的 autovacuum 进程会自动执行 VACUUM,但你需要根据实际情况调整 autovacuum 的参数,比如:

    • autovacuum_vacuum_threshold:触发 autovacuum 的基本阈值,默认是 50。
    • autovacuum_vacuum_scale_factor:触发 autovacuum 的比例因子,默认是 0.2 (20%)。当死亡元组数量超过 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples 时,就会触发 autovacuum。
    • autovacuum_analyze_thresholdautovacuum_analyze_scale_factor:控制 autovacuum 执行 ANALYZE 的阈值。ANALYZE 用于收集表的统计信息,对查询优化器非常重要。
    • autovacuum_naptime:autovacuum 进程的休眠时间.建议根据负载情况调整.

    你可以通过修改 postgresql.conf 文件来调整这些参数,也可以针对单个表进行设置:

    ALTER TABLE your_table_name SET (autovacuum_vacuum_scale_factor = 0.1);
    

    此外,你也可以手动执行 VACUUM:

    VACUUM your_table_name;
    
  2. 使用 VACUUM FULL (谨慎使用): 前面提到,VACUUM FULL 会锁定整个表。所以,只有在你能接受数据库停机的情况下,才可以使用它。一般建议在业务低峰期执行,或者在维护窗口期间执行。

    VACUUM FULL your_table_name;
    
  3. 使用 pg_repack 扩展: 这是解决表膨胀的终极武器。pg_repack 可以在不锁定表的情况下重建表和索引,从而彻底消除膨胀。它通过创建一张新表,然后将旧表的数据复制到新表,最后切换表名来实现。这个过程对应用是透明的,几乎不会影响数据库的正常运行。

    安装好 pg_repack 后,你可以这样使用:

    pg_repack -d your_database_name -t your_table_name
    

    pg_repack 也支持在线重建索引:

    pg_repack -d your_database_name -i your_index_name
    
  4. 优化应用逻辑:

    • 避免频繁的 UPDATE 和 DELETE 操作。如果可能,尽量使用 INSERT 代替 UPDATE,或者将多次 UPDATE 合并成一次。
    • 尽量缩短事务的运行时间。长时间运行的事务会阻止 VACUUM 清理死亡元组。
    • 对于批量导入/删除操作,可以考虑先将数据导入到临时表,然后进行处理,最后再将结果插入到目标表。或者分批次导入和删除。
    • 避免在高峰时段执行大批量更新或删除操作。
  5. 使用分区表: 如果你的表非常大,可以考虑使用分区表。将大表拆分成多个小表,可以提高查询性能,也可以更方便地管理表膨胀。你可以定期清理旧分区,或者将旧分区归档。

  6. 合理使用临时表
    对于一些中间计算结果,可以使用临时表存储,避免对主表进行频繁的更新和删除操作。

总结

表膨胀是 PostgreSQL 中一个常见的问题,但只要我们了解了它的成因、危害和解决方法,就能有效地应对它。记住这几点:

  • 定期 VACUUM: 这是最基本也是最重要的措施。
  • 谨慎使用 VACUUM FULL: 只有在你能接受数据库停机的情况下才使用。
  • pg_repack 是终极武器: 它可以在不锁定表的情况下重建表和索引。
  • 优化应用逻辑: 避免频繁的 UPDATE 和 DELETE 操作,缩短事务运行时间。
  • 分区表和大表拆分: 将大表拆分成多个小表。
  • 监控和预警: 建立完善的监控体系,及早发现并处理表膨胀问题。

希望这篇文章能帮助你更好地理解和解决 PostgreSQL 表膨胀问题。如果你还有其他问题,欢迎随时向我提问。记住,我是你的数据库老伙计“波斯猫”,我们下次再见!

波斯猫 PostgreSQL表膨胀数据库优化

评论点评

打赏赞助
sponsor

感谢您的支持让我们更好的前行

分享

QRcode

https://www.webkt.com/article/7796