PostgreSQL中的VACUUM命令用法说明

每当PostgreSQL数据库中的表中的行被更新或删除时，死亡行会被遗留下来。VACUUM则会把它们除去来使空间能被重新利用。如果一个表没有被清空，它会变得臃肿，浪费磁盘空间而且会降低顺序表扫描的速度，而且在较小范围内也会降低索引扫描的速度。

VACUUM命令只可以移除这些不再被需要的行版本（也被称为元组）。如果被删除事务的事务ID（存储在xmax系统列中）比仍然活跃在PostgreSQL数据库（或者共享表的整个集群）中最老的事务（xmin界限）更老，那么这个元组将不再被需要。

注意以下三种情况就可以抑制PostgreSQL集群中的xmin界限

1、 查找长时间运行的事务

我们可以查找长时间运行的事务，然后使用pg_terminate_backend()函数去终止阻碍VACUUM命令的数据库会话。

2、 查找复制槽

复制槽是一种数据结构，它使PostgreSQL服务器免于丢弃备用服务器仍然需要的信息。如果复制被推迟或者备用服务器被关闭，复制槽就会阻止VACUUM命令删除旧的行。

复制槽提供了一种自动化的方式来确保主服务器不移除WAL块直到它们被所有的从服务器接收。而且主服务器即使当从服务器断开连接时也不移除可能导致恢复冲突的行。

复制槽只保留已知所需数量的WAL块而不是多于所需数量。

使用复制槽可以避免这个问题：在从服务器未连接的任意时间段内不提供保护。

我们可以使用pg_drop_replication_slot()函数去丢弃不需要的复制槽。

这种情况只会发生在当hot_standby_feedback参数设置为on时的物理复制中。如果是逻辑复制，那么会有一个相似的危险，但是只有系统目录会被影响。

3、查找准备好的事务

二阶段提交协议是一种原子性确认协议。它是一种分布式算法，用来协调参与分布式原子事务的所有进程，确定是否提交或者终止（回滚）这个事务。

在二阶段提交过程中，一个分布式事务首先使用PREPARE TRANSACTION，为二阶段提交准备当前事务。如果由于任何原因PREPARE TRANSACTION 命令失败，会变成ROLLBACK，而当前事务则会被取消。

然后我们使用COMMIT PREPARED，提交一个之前为两阶段提交预备的事务。

一旦一个事务被准备好，它会一直保持一种“游荡”状态直到被提交或者中止。通常情况下，事务不会在准备状态中保持很长时间，但有时会出现错误所以事务必须被管理员手动移除。

我们也可以使用ROLLBACK PREPARED，取消一个之前为两阶段提交准备好的事务。

补充：postgresql vacuum操作

PostgreSQL数据库管理工作中,定期vacuum是一个重要的工作.

vacuum的效果

1.1释放,再利用 更新/删除的行所占据的磁盘空间.

1.2更新POSTGRESQL查询计划中使用的统计数据

1.3防止因事务ID的重置而使非常老的数据丢失。

第一点的原因是PostgreSQL数据的插入,更新,删除操作并不是真正放到数据库空间.如果不定期释放空间的话,由于数据太多,查询速度会巨降.

第二点的原因是PostgreSQL在做查询处理的时候,为了是查询速度提高,会根据统计数据来确定执行计划.如果不及时更新的话,查询的效果可能不如预期.

第三点的原因是PostgreSQL中每一个事务都会产生一个事务ID,但这个数字是有上限的. 当事务ID达到最大值后,会重新从最小值开始循环.这样如果不及时把以前的数据释放掉的话,原来的老数据会因为事务ID的丢失而丢失掉.

虽然在新版本的Postgresql中有自动的vacuum，但是如果是大批量的数据IO可能会导致自动执行很慢，需要配合手动执行以及自己的脚本来清理数据库。

1. vacuumdb 是 SQL 命令 VACUUM的封装

所以用vacuumdb和vacuum来清理数据库都可以，效果是一样的。

2.vacuumdb 中的几个重要参数

可以用vacuumdb --help查询。

-a/--all vacuum所有的数据库

-d dbname 只vacuum dbname这个数据库

-f/--full 执行full的vacuum

-t table 只vacuum table这个数据表

-z/--analyze Calculate statistics for use by the optimizer

3. 切换到postgres用户下

vacuumdb -d yourdbname -f -z -v 来清理你的数据库。

或者加到conrtab中15 1 * * * postgres vacuumdb -d mydb -f -z -v >> /tmp/vacuumdb.log

每天的一点一刻开始进行清理。

4. 如何查询我的XID是否接近临界值的命令：

select age(datfrozenxid) from pg_database;

或者：

select max(age(datfrozenxid)) from pg_database;

5. 然而我们关心的是哪一个大的表组要真正的vacuum

SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;

这个命令是查询按照最老的XID排序，查看大于1G而且是排名前20的表。

下面是一个例子：

relname | xid_age | table_size
------------------------+-----------+------------
postgres_log | 199785216 | 12 GB
statements | 4551790 | 1271 MB
normal_statement_times | 31 | 12 GB

然后你可以单独每个表进行vacuum：

vacuumdb --analyze --verbose --table 'postgres_log' mydb

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • postgresql数据合并,多条数据合并成1条的操作

    postgresql数据合并,多条数据合并成1条的操作

    对于主表中一条记录,对应明细表中的96条数据,每一条数据相隔15分钟,明细中没96条数据对应主表中的一个日期trade_date,并且每条明细中有一个字段start_time, 即明细中每96条数据中第一条数据中start_time为00:00, 第二条为00:15,第三条为00:30,依次类推,直到23:45 ,现在要将明细表中的96条数据合并成24条,即第一条数据中start_time为00:00,第二条为01:00,第三条为02:00 sql:select max(de.bid_num)

  • Postgresql 查看SQL语句执行效率的操作

    Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看 SQL 语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句. Explain语法: explain select - from - [where ...] 例如: explain select * from dual; 这里有一个简单的例子,如下: EXPLAIN SELECT * FROM tenk1; QUERY PLAN ---------

  • PostgreSQL备份工具 pgBackRest使用详解

    前言 pgBackRest是一款开源的备份还原工具,目标旨在为备份和还原提供可靠易用的备份. 特性 并行备份和还原 备份操作期间压缩通常是其瓶颈所在.pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题. 本地远程操作 自定义协议允许 pgBackRest以最小化配置通过SSH在本地或者远程执行备份.还原和归档.并且该程序也通过协议层提供了PostgreSQL查询接口,以便于必须要再远程访问PostgreSQL,从而保证了其安全性能. 全量,增量和差异备份 支持全量,增量和差异备份.

  • postgreSQL使用pgAdmin备份服务器数据的方法

    postgreSQL使用pgAdmin备份服务器数据的方法

    使用postgresql的时候,有时候需要备份数据,但是数据库又比较多,比如我们的数据库如下: 这就很烦了,需要一种一键备份还原的方法来备份还原. 1.备份 备份可以使用pgadmin自带的备份服务器.右键服务器连接,点击备份服务器,再选择创建的文件与数据库编码(一般为UTF8),即可,感觉比命令行的方式好用多了. 当然也可以使用命令行的方式,以下来自百度,试过没有成功,觉得太麻烦了就放弃了,供参考: SQL备份 pg_dumpall.exe -h localhost -p 5432 -U po

  • PostgreSQL 如何查找需要收集的vacuum 表信息

    前言 通常,在PostgreSQL中,由于经常需要对表进行 UPDATE 和 DELETE,因此表会产生碎片空间. 在 PostgreSQL中,使用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未使用,以便以后重用这些空间,但是不能立即将占用的空间返还给操作系统,因此需要使用 VACUUM FULL,才可以释放空间,并立即将空间返还给操作系统. 实现脚本 记录收集表创建 CREATE TABLE IF NOT EXISTS tab_vacuum_record (sqltext

  • PostgreSQL对GROUP BY子句使用常量的特殊限制详解

    一.问题描述 最近,一个统计程序从Oracle移植到PostgreSQL(版本9.4)时,接连报告错误: 错误信息1: postgresql group by position 0 is not in select list. 错误信息2: non-integer constant in GROUP BY. 产生错误的sql类似于: insert into sum_tab (IntField1, IntField2, StrField1, StrField2, cnt) select IntFi

  • postgresql数据添加两个字段联合唯一的操作

    postgresql数据添加两个字段联合唯一的操作

    我就废话不多说了,大家还是直接看代码吧~ alter table tb_safety_commitment add constraint uk_mac_vendor unique (company_code,promise_date); 补充:一行数据中的 多字段值根据连接符拼接 concat_ws(':',a,b) 几行数据中的 同一 单字段值根据连接符拼接 string_agg(c,' \r\n ') 如果要将多个字段的值拼接成一个: string_agg(concat_ws(':',a,b

  • PostgreSQL中的VACUUM命令用法说明

    每当PostgreSQL数据库中的表中的行被更新或删除时,死亡行会被遗留下来.VACUUM则会把它们除去来使空间能被重新利用.如果一个表没有被清空,它会变得臃肿,浪费磁盘空间而且会降低顺序表扫描的速度,而且在较小范围内也会降低索引扫描的速度. VACUUM命令只可以移除这些不再被需要的行版本(也被称为元组).如果被删除事务的事务ID(存储在xmax系统列中)比仍然活跃在PostgreSQL数据库(或者共享表的整个集群)中最老的事务(xmin界限)更老,那么这个元组将不再被需要. 注意以下三种情况

  • 浅谈PostgreSQL中的孤儿文件用法(orphaned data files)

    浅谈PostgreSQL中的孤儿文件用法(orphaned data files)

    创建一个测试表 postgres=# create table t1(a int); CREATE TABLE postgres=# select pg_relation_filepath('t1'); pg_relation_filepath ---------------------- base/75062/75297 (1 row) postgres=# 在操作系统上已经可以看到该文件. $ ls -la $PGDATA/base/75062/75297 -rw------- 1 post

  • Linux shell中的test命令用法教程

    test命令 test命令是shell环境中测试条件表达式的实用工具,下面来看看test命令的用法介绍: 语法 test(选项) 选项 -b<文件>:如果文件为一个块特殊文件,则为真: -c<文件>:如果文件为一个字符特殊文件,则为真: -d<文件>:如果文件为一个目录,则为真: -e<文件>:如果文件存在,则为真: -f<文件>:如果文件为一个普通文件,则为真: -g<文件>:如果设置了文件的SGID位,则为真: -G<文件&

  • postgresql 中round函数的用法说明

    round函数是去小数点,我查过好多方法并不好用,于是有了 round(cast(a/b as numeric), 2) 这个就可以用,要加个cast才可以的 补充:postgresql中to_char和round的混合使用 在postgresql中大家都用过round吧,当遇到小数的时候该如何显示呢?看例子 SELECT to_char(round(127 * 0.1 / 67543,6)*10000,'90.99') 1.88 SELECT to_char(round(127 * 0.1 /

  • Linux中 find查找命令用法详解

    Linux下查找文件的命令有两个,分别是locate 和 find. locate指令和find找寻档案的功能类似,但locate是透过update程序将硬盘中的所有档案和目录资料先建立一个索引数据库,在 执行loacte时直接找该索引,查询速度会较快,索引数据库一般是由操作系统管理,但也可以直接下达update强迫系统立即修改索引数据库.简单介绍下它的两个选项. #locate -i        //查找文件的时候不区分大小写 比如:locate  –i   passwd -n      

  • PostgreSQL中的COMMENT用法说明

    PostgreSQL附带了一个命令 - COMMENT .如果想要记录数据库中的内容,这个命令很有用.本文将介绍如何使用此命令. 随着数据库的不断发展和数据关系变得越来越复杂,跟踪数据库中添加的所有内容会变得非常困难.要记录数据的组织方式以及可能随时间添加或更改的组件,有必要添加某种文档. 例如,文档可以写在外部文件中,但这会产生一种问题,他们很快就会变为过时的文件.PostgreSQL有一个解决这个问题的方法:COMMENT命令.使用它可以向各种数据库对象添加注释,例如在需要时更新的列,索引,

  • linux中scp命令和scp命令用法大全

    scp就是secure copy,是用来进行远程文件拷贝的.数据传输使用ssh1,并且和ssh1使用相同的认证方式,提供相同的安全保证.与rcp不同的是,scp会要求你输入密码如果需要的话. svn 删除所有的 .svn文件 find . -name .svn -type d -exec rm -fr {} \; linux之cp/scp命令+scp命令详解 名称:cp 使用权限:所有使用者 使用方式: cp [options] source dest cp [options] source..

  • linux中mount/umount命令的基本用法及开机自动挂载方法

    本文介绍了linux中mount/umount命令的基本用法及开机自动挂载,具体方法如下: mount命令格式如下: 格式:mount [-参数] [设备名称] [挂载点] 其中常用的参数有: -a 安装在/etc/fstab文件中类出的所有文件系统. -f 伪装mount,作出检查设备和目录的样子,但并不真正挂载文件系统. -n 不把安装记录在/etc/mtab 文件中. -r 讲文件系统安装为只读. -v 详细显示安装信息. -w 将文件系统安装为可写,为命令默认情况. -t <文件系统类型

  • shell中的source命令的巧妙用法

    shell中的source命令的巧妙用法

    首先,通常用于重新执行刚修改的初始化文件,使之立即生效,而不必注销并重新登录.例如,当我们修改了/etc/profile文件,并想让它立刻生效,而不用重新登录,就可以使用source命令,如source /etc/profile. 其次,source filepath或者. filepath,sh filepath或者./filepath区别: 1. sh filepath会重新建立一个子shell,在子shell中执行脚本里面的语句,该子shell继承父shell的环境变量,但子shell是新

  • 详解MySQL中EXPLAIN解释命令及用法讲解

    1,情景描述:同事教我在mysql中用explain,于是查看了一番返回内容的含义 2,现就有用处的内容做如下记录: 1,explain显示了mysql如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句. 使用方法,在select语句前加上explain就可以了: explain select count(DISTINCT uc_userid) as user_login from user_char_daily_gameapp_11 where uc_d

随机推荐