PostgreSQL数据优化——死元组清理

最近遇到一个奇怪的问题,一个百万级的PostgreSQL表,只有3个索引。但是每次执行insert或update语句就要几百ms以上。经过查询发现是一个狠简单的问题,数据库表死元组太多了,需要手动清理。

在 PG 中,update/delete 语句的实现通过 MVCC 机制的多版本链实现。如下图所示,更新一条元组时,会将原来的元组标记,并新增一条元组。后续的事物通过快照来判断元组的可见性。

对于一条已经被更新/删除的元组来说,当这条元组对所有事物都不可见后,它的存在就没有意义了,理应被删除,对于这种元组,我们称之为“死元组”。当一张表有大量更新/删除时,如果不做清理的话,表里面就会积攒很多这样的“死元组”,占用大量的空间,造成表空间膨胀。

一、清理前

  • 查询死元组数量SQL
SELECT
    c.relname 表名,
    (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples AS 自动分析阈值,
    (current_setting('autovacuum_vacuum_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_vacuum_scale_factor')::NUMERIC(12,4))*reltuples AS 自动清理阈值,
    reltuples::DECIMAL(19,0) 活元组数,
    n_dead_tup::DECIMAL(19,0) 死元组数
FROM
    pg_class c 

LEFT JOIN pg_stat_all_tables d

    ON C.relname = d.relname
WHERE
    c.relname ='你要查询的表名'  AND reltuples > 0
    AND n_dead_tup > (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples;

查询结果

  • 此时的数据库插入耗时测试,执行update语句

Affected rows: 1
时间: 1.371s

二、配置自动清理

AUTOVACUUM:自动清理元组。开启自动清理后,PostgreSQL会在合适的时候自动执行VACUUM操作。

-- 查看当前autovacuum的状态
SHOW autovacuum;
 
-- 开启autovacuum
SET autovacuum = on;

三、使用VACUUM手动清理

自动清理,有时候可能由于参数配置,效果不佳。可以使用VACUUM命令手动清理,注意,清理过程中会锁表

VACUUM FULL VERBOSE 模式名.表名;
VACUUM FULL VERBOSE ANALYZE 模式名.表名;

清理效果图
清理后update效果

Affected rows: 1
时间: 0.427s

四、查询历史清理信息

SELECT
    relname 表名,
    seq_scan 全表扫描次数,
    seq_tup_read 全表扫描记录数,
    idx_scan 索引扫描次数,
    idx_tup_fetch 索引扫描记录数,
    n_tup_ins 插入的条数,
    n_tup_upd 更新的条数,
    n_tup_del 删除的条数,
    n_tup_hot_upd 热更新条数,
    n_live_tup 活动元组估计数,
    n_dead_tup 死亡元组估计数,
     last_vacuum 最后一次手动清理时间,
    last_autovacuum 最后一次自动清理时间,
    last_analyze 最后一次手动分析时间,
    last_autoanalyze 最后一次自动分析时间,
    vacuum_count 手动清理的次数,
    autovacuum_count 自动清理的次数,
     analyze_count 手动分析此表的次数,
    autoanalyze_count 自动分析此表的次数,
    ( CASE WHEN n_live_tup > 0 THEN n_dead_tup :: float8 / n_live_tup :: float8 ELSE 0 END ) :: NUMERIC ( 12, 2 ) AS "死/活元组的比例"
FROM
    pg_stat_all_tables
WHERE
    schemaname = 'public'
ORDER BY n_dead_tup::float8 DESC;

清理结果

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/446667.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Axure原型设计项目效果 全国职业院校技能大赛物联网应用开发赛项项目原型设计题目

目录 前言 一、2022年任务书3效果图 二、2022年任务书5效果图 三、2022年国赛正式赛卷 四、2023年国赛第一套样题 五、2023年国赛第二套样题 六、2023年国赛第三套样题 七、2023年国赛第四套样题 八、2023年国赛第七套样题 九、2023年国赛正式赛题(第八套…

点赞功能真的有必要上 Redis 吗?(Mongo、MySQL、Redis、MQ 实测性能对比)

目录 一、你会怎么设计一个点赞功能? 1.1、点赞实现思路 1.2、点赞功能设计 1.2.1、MySQL 单表 1.2.2、单表 MySQL 关联表 1.2.3、MySQL 关联表 mq 1.2.4、redis mq 1.2.5、mongodb 关联文档 二、性能测试 2.1、前置说明 2.2、10 万数据准备 一、你会…

PyTorch完整的神经网络模型训练(使用GPU训练)

1.什么是CUDA: CUDA(Compute Unified Device Architecture)是由NVIDIA开发的一种并行计算平台和编程模型。它允许开发者在NVIDIA GPU上进行通用目的的并行计算,包括深度学习、科学计算、图形处理和加密等任务。 CUDA通过提供一组…

vulhub中Weblogic WLS Core Components 反序列化命令执行漏洞复现(CVE-2018-2628)

Oracle 2018年4月补丁中,修复了Weblogic Server WLS Core Components中出现的一个反序列化漏洞(CVE-2018-2628),该漏洞通过t3协议触发,可导致未授权的用户在远程服务器执行任意命令。 访问http://your-ip:7001/consol…

人工智能:探索智慧的未来

目录 前言1 人工智能的简介1.1 人工智能的定义1.2 任务范围1.3 模拟人类认知 2 人工智能发展2.1 起步阶段2.2 发展阶段2.3 繁荣阶段 3 弱人工智能和强人工智能3.1 弱人工智能(ANI)3.2 强人工智能(AGI) 4 人工智能主要技术4.1 机器…

【C++11】包装器和bind

文章目录 一. 为什么要有包装器?二. 什么是包装器?三. 包装器的使用四. bind 函数模板1. 为什么要有 bind ?2. 什么是 bind ?3. bind 的使用场景 一. 为什么要有包装器? function 包装器,也叫作适配器。C 中的 funct…

Elastic Stack--06--JavaAPI----索引(创建-查询- 删除)

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 文章目录 环境准备添加依赖&#xff1a;HelloElasticsearch JavaAPI-索引1.创建2.查询3.删除 环境准备 添加依赖&#xff1a; <dependencies><dependency><g…

第G3周:CGAN入门|生成手势图像

&#x1f368; 本文为&#x1f517;365天深度学习训练营 中的学习记录博客&#x1f356; 原作者&#xff1a;K同学啊 | 接辅导、项目定制 一、前置知识 CGAN&#xff08;条件生成对抗网络&#xff09;的原理是在原始GAN的基础上&#xff0c;为生成器和判别器提供 额外的条件信息…

vue3 ref获取子组件显示 __v_skip : true 获取不到组件的方法 怎么回事怎么解决

看代码 问题出现了 当我想要获取这个组件上的方法时 为什么获取不到这个组件上的方法呢 原來&#xff1a; __v_skip: true 是 Vue 3 中的一个特殊属性&#xff0c;用于跳过某些组件的渲染。当一个组件被标记为 __v_skip: true 时&#xff0c;Vue 将不会对该组件进行渲染&am…

ABAP接口-RFC连接(ABAP TO ABAP)

目录 ABAP接口-RFC连接&#xff08;ABAP TO ABAP&#xff09;创建ABAP连接RFC函数的调用 ABAP接口-RFC连接&#xff08;ABAP TO ABAP&#xff09; 创建ABAP连接 事务代码&#xff1a;SM59 点击创建&#xff0c;填写目标名称&#xff0c;选择连接类型&#xff1a; 填写主机名…

打卡--MySQL8.0 一(单机部署)

一路走来&#xff0c;所有遇到的人&#xff0c;帮助过我的、伤害过我的都是朋友&#xff0c;没有一个是敌人。如有侵权&#xff0c;请留言&#xff0c;我及时删除&#xff01; MySQL 8.0 简介 MySQL 8.0与5.7的区别主要体现在&#xff1a;1、性能提升&#xff1b;2、新的默认…

02-app端文章查看,静态化freemarker,分布式文件系统minIO

app端文章查看&#xff0c;静态化freemarker,分布式文件系统minIO 1)文章列表加载 1.1)需求分析 文章布局展示 1.2)表结构分析 ap_article 文章基本信息表 ap_article_config 文章配置表 ap_article_content 文章内容表 三张表关系分析 1.3)导入文章数据库 1.3.1)导入数据…

【vue.js】文档解读【day 2】 | 响应式基础

如果阅读有疑问的话&#xff0c;欢迎评论或私信&#xff01;&#xff01; 本人会很热心的阐述自己的想法&#xff01;谢谢&#xff01;&#xff01;&#xff01; 文章目录 响应式基础声明响应式状态(属性)响应式代理 vs 原始值声明方法深层响应性DOM 更新时机有状态方法 响应式…

电脑数据安全新防线:文件备份的终极指南

一、数据守护者的使命&#xff1a;文件备份的重要性 在数字化日益普及的今天&#xff0c;电脑已成为我们日常生活和工作的必备工具&#xff0c;文件作为我们储存、交流和处理信息的主要载体&#xff0c;其重要性不言而喻。然而&#xff0c;无论是由于硬件故障、软件崩溃&#…

Autosar教程-Mcal教程-GPT配置教程

3.3GPT配置、生成 3.3.1 GPT配置所需要的元素 GPT实际上就是硬件定时器,需要配置的元素有: 1)定时器时钟:定时器要工作需要使能它的时钟源 2)定时器分步:时钟源进到定时器后可以通过分频后再给到定时器 定时器模块选择:MCU有多个定时器模块,需要决定使用哪个定时器模块作…

【动态规划】代码随想录算法训练营第三十九天 |62.不同路径,63.不同路径II(待补充)

62.不同路径 1、题目链接&#xff1a;. - 力扣&#xff08;LeetCode&#xff09; 2、文章讲解&#xff1a;代码随想录 3、题目&#xff1a; 一个机器人位于一个 m x n 网格的左上角 &#xff08;起始点在下图中标记为 “Start” &#xff09;。 机器人每次只能向下或者向右…

JavaScript高级Ⅱ(全面版)

接上文 JavaScript高级Ⅰ JavaScript高级Ⅰ(自认为很全面版)-CSDN博客 目录 第2章 DOM编程 2.1 DOM编程概述 2.1.4 案例演示(商品全选) 2.1.5 dom操作内容 代码演示&#xff1a; 运行效果&#xff1a; 2.1.6 dom操作属性 代码演示&#xff1a; 运行效果&#xff1a; 2…

H264/265编码参数2: Profile Level Tier

profile和level profile和level是视频编码中两个很重要的概率&#xff0c;中文一般叫做档次和级别。 在MPEG2标准里边&#xff0c;按不同的压缩比分成五个档次&#xff0c;按视频清晰度分为四个级别&#xff0c;如下图所示&#xff1a; 档次和级别共有 20 种组合&#xff0c;…

2024年【化工自动化控制仪表】考试总结及化工自动化控制仪表作业考试题库

题库来源&#xff1a;安全生产模拟考试一点通公众号小程序 2024年【化工自动化控制仪表】考试总结及化工自动化控制仪表作业考试题库&#xff0c;包含化工自动化控制仪表考试总结答案和解析及化工自动化控制仪表作业考试题库练习。安全生产模拟考试一点通结合国家化工自动化控…

day-18 长度最小的子数组

运用队列的思维&#xff0c;求出每种满足题意的子数组长度&#xff0c;最小的即为答案&#xff0c;否则返回0 code class Solution {public int minSubArrayLen(int target, int[] nums) {int l0,r0;int ansInteger.MAX_VALUE;int total0;while(r<nums.length){totalnums[r…