• 售前

  • 售后

热门帖子
入门百科

PostgreSQL中的VACUUM命令用法阐明

[复制链接]
poney 显示全部楼层 发表于 2021-8-14 15:18:11 |阅读模式 打印 上一主题 下一主题
每当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. 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
  2. ORDER BY age(relfrozenxid) DESC LIMIT 20;
复制代码
这个命令是查询按照最老的XID排序,查看大于1G而且是排名前20的表。
下面是一个例子:
  1. relname | xid_age | table_size
  2. ------------------------+-----------+------------
  3. postgres_log | 199785216 | 12 GB
  4. statements | 4551790 | 1271 MB
  5. normal_statement_times | 31 | 12 GB
复制代码
然后你可以单独每个表进行vacuum:
  1. vacuumdb --analyze --verbose --table 'postgres_log' mydb
复制代码
以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。如有错误或未思量完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作