PostgreSQL表膨胀问题解析

一、定义

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

二、原因

表膨胀在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 $$
DECLAREr RECORD;
BEGINFOR r INSELECT schemaname, tablename, bloatFROM (SELECTschemaname,tablename,ROUND(CASE WHEN otta=0 OR relpages=0 OR relpages=otta THEN 0.0 ELSE relpages/otta::numeric END, 2) AS bloatFROM (SELECTnn.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)ELSE0END AS ottaFROMpg_class ccJOIN pg_namespace nn ON cc.relnamespace = nn.oidLEFT JOIN (SELECTc.relname,c.reltuples,(c.reltuples * (c.rellen + pg_column_size(c.oid, 'ctid') + 24))::bigint AS total_bytesFROMpg_class cLEFT JOIN pg_stat_all_tables s ON c.relname = s.relnameWHEREs.schemaname NOT IN ('pg_catalog', 'information_schema')AND c.relkind = 'r') ce ON cc.relname = ce.relnameWHEREnn.nspname NOT IN ('pg_catalog', 'information_schema')AND cc.relkind = 'r') a) bWHERE bloat > 1.0LOOPRAISE 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_cataloginformation_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.rhkb.cn/news/479716.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

femor 第三方Emby应用全平台支持v1.0.54更新

femor v1.0.54 版本更新 mpv播放器增加切换后台和恢复时隐藏状态栏的功能修复服务器首页因为连接超时异常的问题 获取路径:【femor 历史版本收录】

如何搭建一个小程序:从零开始的详细指南

在当今数字化时代,小程序以其轻便、无需下载安装即可使用的特点,成为了连接用户与服务的重要桥梁。无论是零售、餐饮、教育还是娱乐行业,小程序都展现了巨大的潜力。如果你正考虑搭建一个小程序,本文将为你提供一个从零开始的详细…

nrm镜像管理工具使用方法

nrm(NPM Registry Manager)是一款专门用于管理 npm 包镜像源的命令行工具。在使用 npm 安装各种包时,默认会从官方的 npm 仓库(registry)获取资源,但有时候由于网络环境等因素,访问官方源可能速…

OpenCV截取指定图片区域

import cv2 img cv2.imread(F:/2024/Python/demo1/test1/man.jpg) cv2.imshow(Image, img) # 显示图片 #cv2.waitKey(0) # 等待按键x, y, w, h 500, 100, 200, 200 # 示例坐标 roi img[y:yh, x:xw] # 截取指定区域 cv2.imshow(ROI, roi) cv2.waitKey(0) cv…

易速鲜花聊天客服机器人的开发(下)

目录 “聊天机器人”项目说明 方案 1 :通过 Streamlit 部署聊天机器人 方案2 :通过 Gradio 部署聊天机器人 总结 上一节,咱们的聊天机器人已经基本完成,这节课,我们要看一看如何把它部署到网络上。 “聊天机器人”…

STM32笔记(串口IAP升级)

一、IAP简介 IAP(In Application Programming)即在应用编程, IAP 是用户自己的程序在运行过程中对 User Flash 的部分区域进行烧写,目的是为了在产品发布后可以方便地通过预留的通信口对产 品中的固件程序进行更新升级。 通常实…

斐波那契堆与二叉堆在Prim算法中的性能比较:稀疏图与稠密图的分析

斐波那契堆与二叉堆在Prim算法中的性能比较:稀疏图与稠密图的分析 引言基本概念回顾Prim算法的时间复杂度分析稀疏图中的性能比较稠密图中的性能比较|E| 和 |V| 的关系伪代码与C代码示例结论引言 在图论中,Prim算法是一种用于求解最小生成树(MST)的贪心算法。其性能高度依…

使用argo workflow 实现springboot 项目的CI、CD

文章目录 基础镜像制作基础镜像设置镜像源并安装工具git下载和安装 Maven设置环境变量设置工作目录默认命令最终dockerfile 制作ci argo workflow 模版volumeClaimTemplatestemplatesvolumes完整workflow文件 制作cd argo workflow 模版Workflow 结构Templates 定义创建 Kubern…

BUUCTF—Reverse—不一样的flag(7)

是不是做习惯了常规的逆向题目?试试这道题,看你在能不能在程序中找到真正的flag!注意:flag并非是flag{XXX}形式,就是一个’字符串‘,考验眼力的时候到了! 注意:得到的 flag 请包上 f…

insmod一个ko提供基础函数供后insmod的ko使用的方法

一、背景 在内核模块开发时,多个不同的内核模块,有时候可能需要都共用一些公共的函数,比如申请一些平台性的公共资源。但是,这些公共的函数又不方便去加入到内核镜像里,这时候就需要把这些各个内核模块需要用到的一些…

LangGraph中的State管理

本教程将介绍如何使用LangGraph库构建和测试状态图。我们将通过一系列示例代码,逐步解释程序的运行逻辑。 1. 基本状态图构建 首先,我们定义一个状态图的基本结构和节点。 定义状态类 from langgraph.graph import StateGraph, START, END from typi…

MATLAB中Simulink的基础知识

Simulink是MATLAB中的一种可视化仿真工具, 是一种基于MATLAB的框图设计环境,是实现动态系统建模、仿真和分析的一个软件包,被广泛应用于线性系统、非线性系统、数字控制及数字信号处理的建模和仿真中。 Simulink提供一个动态系统建模、仿真和…

最小生成树-Prim与Kruskal算法

文章目录 什么是最小生成树?Prim算法求最小生成树Python实现: Kruskal算法求最小生成树并查集 Python实现: Reference 什么是最小生成树? 在图论中,树是图的一种,无法构成闭合回路的节点-边连接组合称之为…

关闭AWS账号后,服务是否仍会继续运行?

在使用亚马逊网络服务(AWS)时,用户有时可能会考虑关闭自己的AWS账户。这可能是因为项目结束、费用过高,或是转向使用其他云服务平台。然而,许多人对关闭账户后的服务状态感到困惑,我们九河云和大家一起探讨…

Could not locate device support files.

报错信息:Failure Reason: The device may be running a version of iOS (13.6.1 17G80) that is not supported by this version of Xcode.[missing string: 869a8e318f07f3e2f42e11d435502286094f76de] 问题:xcode15升级到xcode16之后,13.…

Linux文件基础

目录 一、文件类型 二、文件权限 三、权限修改 Linux中一切皆文件,文件目录分布呈树状数据结构,/是根目录,目录的源头 一、文件类型 类型字符说明普通-Linux中最多的一种文件类型,包括 纯文本文件(ASCII)、二进制文件(binary…

自然语言处理基础之文本预处理

一. NLP介绍 1957年, 怛特摩斯会议 二. 文本预处理 文本预处理及作用 将文本转换成模型可以识别的数据 文本转化成张量(可以利用GPU计算), 规范张量的尺寸. 科学的文本预处理可以有效的指导模型超参数的选择, 提升模型的评估指标 文本处理形式 分词 词性标注 命名实体识别…

外卖点餐系统小程序

目录 开发前准备 项目展示项目分析项目初始化封装网络请求 任务1 商家首页 任务分析焦点图切换中间区域单击跳转到菜单列表底部商品展示 任务2 菜单列表 任务分析折扣信息区设计菜单列表布局请求数据实现菜单栏联动单品列表功能 任务3 购物车 任务分析设计底部购物车区域添加商…

彻底理解如何保证ElasticSearch和数据库数据一致性问题

一.业务场景举例 需求: 一个卖房业务,双十一前一天,维护楼盘的运营人员突然接到合作开发商的通知,需要上线一批热门的楼盘列表,上传完成后,C端小程序支持按楼盘的名称、户型、面积等产品属性全模糊搜索热门…

单片机将图片数组调出来显示MPU8_8bpp_Memory_Write

界面显示图片是很常见的需求,使用外挂的FLASH是最常用的方法。但是如果图片需求不大,比如说我们只要显示一个小图标,那么为了节省硬件成本,是不需要外挂一颗FLASH芯片的,我们可以将图标转成数组,存在单片机…