服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - PostgreSQL中的VACUUM命令用法说明

PostgreSQL中的VACUUM命令用法说明

2021-04-16 22:05瀚高PG实验室 PostgreSQL

这篇文章主要介绍了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这个数据表

?
1
-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是否接近临界值的命令:

?
1
select age(datfrozenxid) from pg_database;

或者:

?
1
select max(age(datfrozenxid)) from pg_database;

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

?
1
2
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的表。

下面是一个例子:

?
1
2
3
4
5
relname | xid_age | table_size
------------------------+-----------+------------
postgres_log | 199785216 | 12 GB
statements | 4551790 | 1271 MB
normal_statement_times | 31 | 12 GB

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

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

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

原文链接:https://blog.csdn.net/pg_hgdb/article/details/79611256

延伸 · 阅读

精彩推荐
  • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

    RDS PostgreSQL一键大版本升级技术解密

    一、PostgreSQL行业位置 (一)行业位置 在讨论PostgreSQL(下面简称为PG)在整个数据库行业的位置之前,我们先看一下阿里云数据库在全球的数据库行业里的...

    未知1192023-05-07
  • PostgreSQL分布式 PostgreSQL之Citus 架构

    分布式 PostgreSQL之Citus 架构

    节点 Citus 是一种 PostgreSQL 扩展,它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群,允许 PostgreSQL 保存比单...

    未知802023-05-07
  • PostgreSQLpostgresql 数据库中的数据转换

    postgresql 数据库中的数据转换

    postgres8.3以后,字段数据之间的默认转换取消了。如果需要进行数据变换的话,在postgresql数据库中,我们可以用"::"来进行字段数据的类型转换。...

    postgresql教程网12482021-10-08
  • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

    深入理解PostgreSQL的MVCC并发处理方式

    这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

    PostgreSQL教程网3622020-04-25
  • PostgreSQLpostgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    这篇文章主要介绍了postgresql 中的to_char()常用操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    J符离13432021-04-12
  • PostgreSQLPostgresql开启远程访问的步骤全纪录

    Postgresql开启远程访问的步骤全纪录

    postgre一般默认为本地连接,不支持远程访问,所以如果要开启远程访问,需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

    我勒个去6812020-04-30
  • PostgreSQLPostgresql查询效率计算初探

    Postgresql查询效率计算初探

    这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一定的参考学习价...

    轨迹4622020-05-03
  • PostgreSQLPostgreSQL标准建表语句分享

    PostgreSQL标准建表语句分享

    这篇文章主要介绍了PostgreSQL标准建表语句分享,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    码上得天下7962021-02-27